Is there a way of asking "is a member of" in VBA?

[IanKR]

I often have to test whether any of a range of numbers (x in the example
below) is represented in a set of values - which may or may not be entered
in a range of cells. If I put the set of values into an array (ArrValues
below - could be one- or two-dimensional), is there a neat way of asking:

If x "is a member of" ArrValues

on one line of code, or do I have to cycle through all the members of
ArrValues in turn (say using For loop) and check whether each one is equal
to x? I've tried using the Match WorksheetFunction with 0 as the third
argument:


With WorksheetFunction
For x = 1 To 100
If Not .IsNA(.Match(x, ArrValues, 0)) Then
Cells(x, 14) = x 'report matching values in column 14
End If
Next k
End With

but it doesn't work - I get an "Unable to get the Match property of the
WorksheetFunction class".

Instead, I'm having to do this, for example:

For i = 1 To 10
For j = 1 To 10
For x = 1 To 100
If ArrValues(i, j) = x Then
Cells(x, 14) = x 'report values in column 14
End If
Next k
Next j
Next i

The example may seem trivial, but I have other instances where it's not so
trivial.

Thanks

Ian

 

[Jim Cone]

Ian,

' If you set row_num or column_num to 0 (zero), then
' Index returns the array of values for the entire column or row
' (looping thru the array column could be as fast or faster)

Sub MakeSomethingUp()
Dim N As Variant
Dim arr As Variant
Dim arr2 As Variant
Dim strMessage As String

'Fill the array.
arr = Range("A1:B50").Value

'Return the second column of the array
arr2 = Application.Index(arr, 0, 2)
'Find the position in the array of "77"
N = Application.Match(77, arr2, 0)
If Not IsError(N) Then
strMessage = "Found in row " & N
Else
strMessage = "Not Found"
End If
MsgBox strMessage
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"IanKR" <ik.rip_@_ntlworld.com>
wrote in message
I often have to test whether any of a range of numbers (x in the example
below) is represented in a set of values - which may or may not be entered
in a range of cells. If I put the set of values into an array (ArrValues
below - could be one- or two-dimensional), is there a neat way of asking:

If x "is a member of" ArrValues

on one line of code, or do I have to cycle through all the members of
ArrValues in turn (say using For loop) and check whether each one is equal
to x? I've tried using the Match WorksheetFunction with 0 as the third
argument:

With WorksheetFunction
For x = 1 To 100
If Not .IsNA(.Match(x, ArrValues, 0)) Then
Cells(x, 14) = x 'report matching values in column 14
End If
Next k
End With

but it doesn't work - I get an "Unable to get the Match property of the
WorksheetFunction class".
Instead, I'm having to do this, for example:

For i = 1 To 10
For j = 1 To 10
For x = 1 To 100
If ArrValues(i, j) = x Then
Cells(x, 14) = x 'report values in column 14
End If
Next k
Next j
Next i

The example may seem trivial, but I have other instances where it's not so
trivial.
Thanks
Ian

 

[IanKR]

Many thanks for this, Jim.

I see that you can use Match (successfully) with a 1-dimensional array -
this is presumably where I went wrong, as I was trying to use it with a
2-dim'l array. So I'd have to use Index against each column of my array in
turn, then. It would probably be quicker to use For... loops, as I was doing
already?! Is this what you mean by "looping thru the array column could be
as fast or faster"?

One thing I notice is that you don't:

Dim arr (1 To 50, 1 To 2) as Variant

and

Dim arr2 (1 To 50) as Variant

i.e. you leave out the Bounds in the brackets, and also that you don't Redim
them with Bounds within the Sub. Presumably this is not necessary? Does
Dim-ing them with Bounds only serve to save on memory?

As an aside, the stuff I've read in Excel VBA books on arrays has always
completely baffled me. They always mention the need to declare them as per:

Dim arr (1 To 50, 1 To 2)

for example, and they talk about LBounds and UBounds, but very little on how
to actually USE them in a meaningful way (and nothing on how to populate
them with values - I never knew until today that you could do

arr = Range("A1:B50").Value

to populate an array from a worksheet range).

But thanks again - I've learned a few useful things, including how to use
the Index worksheet function. But I'm still dreadfully confused by what you
can, can't, must and mustn't do with arrays!

Ian

 

[Jim Cone]

Some comments that I hope make sense...
In time critical code you should try alternative methods and time them.
Sometimes you can be surprised.

The lower bound is simply the starting row or column and
the upper bound is the ending row or column...
Arr( 2 to 10, 3 to 4) has rows numbered from 2 to 10 (9 rows) and
columns numbered from 3 to 4 (two columns).
Arr(2, 3) is the top left corner and Arr(10, 4) is the bottom right corner.

A range can be considered as an array of values, for example ...
'Run this one and see what you get.
Sub RangeIsAnArray()
Dim rng As Excel.Range
Set rng = Range("A10:B55")
'(10, 2) the value in the 10th row / 2nd col of the range (not the worksheet)
MsgBox rng(10, 2)
End Sub

A Variant is a data type that can contain almost anything.
(it also requires more memory than any other data type)
So...
Dim Arr as Variant
When the range values are assigned to it ... Arr = Range("A1:B50").Value
Arr ends up as a variant containing an array.
Arr(1, 2) holds the value (in this case) of cell B1.

To my simple way of thinking an Array is simply a memory structure
that allows you to store and retrieve data. In order to use it, you have
to tell Excel that you want an array and what kind of data it will hold...
Dim strArry() as String

Then you have to tell Excel how much room you need...
ReDim strArry(1 to 10, 1 to 1)
(you need 10 rows and 1 column)

'Then assign something to the first element of the array.
strArry(1, 1) = "Ian"
'Retrieve the value...
MsgBox strArry(1, 1)

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"IanKR" <ik.rip_@_ntlworld.com>
wrote in message
Many thanks for this, Jim.

I see that you can use Match (successfully) with a 1-dimensional array -
this is presumably where I went wrong, as I was trying to use it with a
2-dim'l array. So I'd have to use Index against each column of my array in
turn, then. It would probably be quicker to use For... loops, as I was doing
already?! Is this what you mean by "looping thru the array column could be
as fast or faster"?

One thing I notice is that you don't:
Dim arr (1 To 50, 1 To 2) as Variant
and
Dim arr2 (1 To 50) as Variant

i.e. you leave out the Bounds in the brackets, and also that you don't Redim
them with Bounds within the Sub. Presumably this is not necessary? Does
Dim-ing them with Bounds only serve to save on memory?

As an aside, the stuff I've read in Excel VBA books on arrays has always
completely baffled me. They always mention the need to declare them as per:
Dim arr (1 To 50, 1 To 2)
for example, and they talk about LBounds and UBounds, but very little on how
to actually USE them in a meaningful way (and nothing on how to populate
them with values - I never knew until today that you could do

arr = Range("A1:B50").Value
to populate an array from a worksheet range).
But thanks again - I've learned a few useful things, including how to use
the Index worksheet function. But I'm still dreadfully confused by what you
can, can't, must and mustn't do with arrays!
Ian

 

[IanKR]

Many thanks Jim - what you say makes absolute sense and you've explained it
very clearly. I just wish the VB books I've read had done the same! Perhaps
you should write a book yourself...

 

[Jim Cone]

Ian,
You are welcome.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"IanKR" <ik.rip_@_ntlworld.com>
wrote in message
Many thanks Jim - what you say makes absolute sense and you've explained it
very clearly. I just wish the VB books I've read had done the same! Perhaps
you should write a book yourself...