VBA - Using array of values

[Fishboy]

Hi all,

OK, so I have come a long way since I joined back in February. I started out asking about how different formulas worked to achieve my goals but have since started down the VBA road and am even helping others out along the way.

Progress.

Something I have been trying to do with my own code for some time now, but can't quite get my head around either the syntax or even what it is called to successfully look it up, is how to use an array of values in a VBA If statement. I apologise if I am describing this badly, it might be easier to try and demonstrate what i mean.

So lets say that I want to check if a cell contains one of a number of possible values...

If ActiveCell.Value =

But then I want to check it against a list of possible options. I have seen these in other people's code before where these values are listed in curly braces like this:

{"Red","Yellow","Green","Blue"}

What is the correct syntax or layout for this? Is it:

If ActiveCell.Value = {"Red","Yellow","Green","Blue"} Then...
The rest of my code

At present if I wanted to carry out a check like this I would have to resort to lots of OR's...

If ActiveCell.Value = "Red" Or ActiveCell.Value = "Yellow" Or ActiveCell.Value = "Green" Or ActiveCell.Value = "Blue" Then...
The rest of my code

As mentioned above, not only cannot I not seem to get the syntax right, I don't even know how to word this properly for a decent google search.

If any of the gurus are able to steer me in the right direction it would be greatly appreciated.

 

[Gary McMaster]

Have a look at "Select Case" in VBA help.

 

[Fishboy]

Have a look at "Select Case" in VBA help.

Hi Gary, thanks for the reply.

Although a "Select Case" type of code will kinda do what I described, it isn't quite what I was referring to.

To use my example (I now realise it was probably a bit vague to be any use), I am not trying to match the cell value to a case and react accordingly, but rather if the cell value is any from the braced list then the check is TRUE and the next line of code would run.

Also, just to explain, this is a hypothetical query at the moment, I don't have example data to work with here.

 

[Eric W]

You can do something like:

Sub temp1()
Dim MyList As Object


    Set MyList = CreateObject("Scripting.Dictionary")
     MyList.Add "red", 1
     MyList.Add "yellow", 2
     MyList.Add "green", 3
     MyList.Add "blue", 4
     
     If MyList.exists("red") Then Debug.Print "yes"
     
End Sub

 

[Fishboy]

Hmm, I think I clearly haven't described by query very well at all. Lets see if some dummy data might make it easier...

Excel 2010

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Column To Check[/TD]
[TD="align: center"]Column To Update[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Matches List[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"]Matches List[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]NOT VALID[/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Matches List[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]NOT VALID[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]Matches List[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]NOT VALID[/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]NOT VALID[/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Matches List[/TD]

</tbody>

Sheet1​

In the above table column A is (as labelled) the one I want to check against. In this particular instance I might try to use the following code:

Sub DesiredMethod()
Dim Cell, cRange As Range
Set cRange = Range("A2:A10")
     For Each Cell in cRange
          If Cell.Value = {"Red","Green","Blue","Yellow"} Then
               Cell.Offset(0,1).Value = "Matches List"
          End If
     Next Cell
End Sub

I'll admit this is a very simplified example (the list in braces might be a lot bigger for example, or the action to undertake after the Then might be more complicated code), but at present my method for this is a lot more time consuming and not the most efficient / elegant.

Sub CurrentMethod()
Dim Cell, cRange As Range
Set cRange = Range("A2:A10")
     For Each Cell in cRange
          If Cell.Value = "Red" Or Cell.Value = "Green" Or Cell.Value = "Blue" or Cell.Value = "Yellow" Then
               Cell.Offset(0,1).Value = "Matches List"
          End If
     Next Cell
End Sub

 

[MickG]

You could try the Filter Function See Vb Help !!!
If you place "Blue" in "A1" the Filter will check if its in the array

Dim ray
ray = Filter(Array("Red", "Yellow", "Green", "Blue"), Range("A1").Value, True)
If ray(0) = ActiveCell Then
MsgBox ray(0)
'More code  !!!
End If

 

[Eric W]

That is what I suggested, although I didn't go into details. To recode your example, try:

Sub DesiredMethod()
Dim Cell, cRange As Range
Dim MyList as Object


Set MyList = CreateObject("Scripting.Dictionary")
     MyList.Add "Red", 1
     MyList.Add "Yellow", 2
     MyList.Add "Green", 3
     MyList.Add "Blue", 4


Set cRange = Range("A2:A10")
     For Each Cell in cRange
          If MyList.Exists(Cell.Value) Then
               Cell.Offset(0,1).Value = "Matches List"
          End If
     Next Cell
End Sub

And to tweak MickG's code a bit, you can try:

If UBound(Filter(Array("Red","Yellow","Green","Blue"),Cell.Value,TRUE,1))> -1 Then

 

[Fishboy]

You could try the Filter Function See Vb Help !!!
If you place "Blue" in "A1" the Filter will check if its in the array

Dim ray
ray = Filter(Array("Red", "Yellow", "Green", "Blue"), Range("A1").Value, True)
If ray(0) = ActiveCell Then
MsgBox ray(0)
'More code  !!!
End If

Aha! Although this still isn't quite what I was expecting, this is still good to know and certainly getting closer to what I mean. At the very least this would actually do what I wanted as of the 'More Code part.

I fear I am going to have to go through all of my old posts to find an example of where I gave someone a solution to a problem and someone else gave them a better way of doing it using the method I have (poorly) outlined above.

Anyway, many thanks to everyone who has responded thus far.

 

[AlphaFrog]

Or the Match function...

If IsNumeric(Application.Match(Range("A1").Value, Array("Red", "Yellow", "Green", "Blue"), 0)) Then

 

[Fishboy]

Or the Match function...

If IsNumeric(Application.Match(Range("A1").Value, Array("Red", "Yellow", "Green", "Blue"), 0)) Then

This is by far the closest we have come so far, and it is entirely possible that the version that is running through my head is not really an accurate memory to base this on. I am fairly certain though that what I am thinking of used curly braces around the values which indicated to Excel that it was an array rather than having to state it with the word array in the code.

Anyway, this is close enough and also narrows the check down to a single line of code, which is good enough for me.