[HW 1.5b] Member Function

Write a function in VBA that tests whether the argument it receives is contained within a given range. If it is, return the numerical value “1”, otherwise, return “0”.

Here’s an example of how it should work:

Once you’ve done this, please write a second function called =NONMEMBER. Once you’ve written =MEMBER, this one is easy. =NONMEMBER should call the =MEMBER function.  To do this, you need to figure out how to write a “for loop”.

Note: There are other ways to accomplish the same thing without VBA.  For example, you could use the =MATCH function or the =VLOOKUP function, and both would error out if there was no match.  And, if there were a match, those functions would return the match or its position.  So, it’s possible to use out of the box functions, just a bit more work.


One comment

  1. I came up with the following member function:

    Function member(testCase As String, inputRange As Range) As Integer
    For Each cell In inputRange                     'iterate through each row in the range
      If (StrComp(testCase, cell.Value, vbTextCompare) = 0) Then                'compare row to testCase
        member = 1
        Exit For                              'exit if found (important)
        member = 0
      End If
    End Function


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: