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
– user2864740, Commented Jul 16, 2015 at 19:49
In this case you probably want to do the 'or' (but see 'orselse') in the condition itself. — user2864740
– user2864740, Commented Jul 16, 2015 at 19:51
See this post: stackoverflow.com/questions/10951687/… for an example of how to do this using an array. That way you'd be able to change it at the top. — OpiesDad
– OpiesDad, Commented Jul 16, 2015 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
– watercoder, Commented Jul 16, 2015 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
– OpiesDad, Commented Jul 17, 2015 at 13:48

C. Tewalt · Accepted Answer · 2015-07-16 19:51:54Z

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, 2015 at 19:51

C. Tewalt

2,5072 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

watercoder Over a year ago

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.

Dan Donoghue · Accepted Answer · 2015-07-16 23:27:48Z

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, 2015 at 23:27

Dan Donoghue

6,2662 gold badges20 silver badges36 bronze badges

4 Comments

watercoder Over a year ago

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 Over a year ago

And thank you for your time and effort, I truly appreciate it ^_^

watercoder Over a year ago

Nevermind, I figured it out. Does exactly what I want it to do, thank you!

Dan Donoghue Over a year ago

Glad it worked for you but yes, you're correct, I should have dimmed Thing as a string

Rob Cutmore · Accepted Answer · 2015-07-16 22:54:03Z

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

Collectives™ on Stack Overflow

Setting a Variable to have multiple possible values

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related