Setting a Variable to have multiple possible values

Question

Trying to get a macro to do the same function in multiple sheets and I was thinking I could use this sort of code to select the data I wanted:

Sub Foo
Dim OtherStuff As Things
Dim Thing1 as Variant

Thing1 = "a" Or "b" Or "c"

'more things

If cell.value = Thing1 Then
Do stuff
End If
Next Cell
End Sub

I keep getting hit with the good ol' happytime "Run-time error '13': Type Mismatch".

Am I trying to do something that variant won't do? Is there a way to do it that I just haven't learned yet? I've looked around a good bit and haven't found much. Thanks for the help!

A value cannot be this or that. It is only itself. Multiple values can be stored in a collection, such as an array (or comma separate string, ick). But the collection is a single value (itself) and all the values it contains are single values (representing themselves). — user2864740, Jul 16 '15 at 19:49
In this case you probably want to do the 'or' (but see 'orselse') in the condition itself. — user2864740, Jul 16 '15 at 19:51
See this post: http://stackoverflow.com/questions/10951687/how-to-search-for-string-in-ms-access-vba-array for an example of how to do this using an array. That way you'd be able to change it at the top. — OpiesDad, Jul 16 '15 at 20:12
@OpiesDad: Are you talking about the "Dictionary" option? That sounds like it might work. Thanks for the tip! Never even saw that thread during my earlier search. — watercoder, Jul 16 '15 at 22:19
I was actually refering to the array method that was used (and that Dan provided below). You could use his array of variants, but I would prefer to have an array of strings using the Split() function as done in the linked post. You wouldn't need to use a function and could just do the check in code, but could use the function if you wanted to. A dictionary would also work well, though, if that's how you want to do it. — OpiesDad, Jul 17 '15 at 13:48

score 1 · Answer 1 · answered Jul 16 '15 at 19:51

1

You should change your code so it's something like this. You can't set a variable to multiple values, but you can have a condition which checks for multiple values.

Not the slickest way, but it will work.

Sub Foo
Dim OtherStuff As Things
Dim Thing1 as Variant

' won't work -> Thing1 = "a" Or "b" Or "c"

'more things

'this should work
If cell.value = "a" Or cell.value = "b" cell.value = "c" Then
Do stuff
End If
Next Cell
End Sub

answered Jul 16 '15 at 19:51

C. Tewalt

2,271
2
30
49

Thats how I had it initially, but I was trying to it like this so that when I had to change variables (eg personnel moving around) I could change it at the top and not have to futz around with the rest of it. Might have to just suck it up. – watercoder Jul 16 '15 at 19:59

score 0 · Answer 2 · answered Jul 16 '15 at 22:54

In response to your comment to the answer provided by matrixugly you can maintain a boolean variable at the top of the subroutine. If you need to add or remove cases you can manage it there.

Sub Foo()
    Dim valueMatches As Boolean
    valueMatches = cell.value = "a" Or _
                   cell.value = "b" Or _
                   cell.value = "c"

    ' ...more code...

    If valueMatches Then
        ' Do stuff
    End If
End Sub

You could also do the check in a separate function and maintain any changes there.

Private Function ValueMatches(ByVal value As String) As Boolean
    ' Maintain values to match in this single function.
    ' Note: Function will need to be public instead of
    ' private if stored in another module.
    ValueMatches = value = "a" Or value = "b" Or value = "c"
End Function

Sub Foo()
    ' ...more code...

    If ValueMatches(cell.value) Then
        ' Do stuff
    End If
End Sub

score 0 · Accepted Answer · answered Jul 16 '15 at 23:27

0

You code is very close, you need Thing1 to be an array (You already defined it correctly as a variant for this). Then you just loop through the elements.

Here is the modified code. Pretty simple to follow but post back if you have questions:

Sub Foo()
'Dim OtherStuff As things
Dim Thing1 As Variant, Cell As Range

Thing1 = Array("a", "b", "c")
'more things
For Each Cell In Range("A1:A10")
    For Each Thing In Thing1
        If Cell.Value = Thing Then
            'Do stuff
        End If
    Next Thing
Next Cell
End Sub

answered Jul 16 '15 at 23:27

Dan Donoghue

6,056
2
18
36

I've got a question on the "For Each Thing in Thing1" Line. The lines above and below make sense. What is "Thing"? Do I have do Dim it? What's going on with this line? – watercoder Jul 17 '15 at 16:45
And thank you for your time and effort, I truly appreciate it ^_^ – watercoder Jul 17 '15 at 16:54
Nevermind, I figured it out. Does exactly what I want it to do, thank you! – watercoder Jul 17 '15 at 18:10
Glad it worked for you but yes, you're correct, I should have dimmed Thing as a string – Dan Donoghue Jul 17 '15 at 21:14

Setting a Variable to have multiple possible values

3 Answers3