How to Loop Through a List of Strings in VBA

Here's a quick (and dirty-ish) way to loop through a list of strings in VBA:

Sub TheThreeStooges()
    Dim Stooge As Variant
    For Each Stooge In Array("Larry", "Moe", "Curly")
        Debug.Print Stooge
    Next Stooge
End Sub

Fixing Common Errors

For Each Control Variable Must be Variant or Object

If you change the first line in the above code to Dim Stooge As String you will receive the following compile error:

Compile error:
For Each control variable must be Variant or Object

The error message is pretty self-explanatory.  To fix the error, make sure the variable that immediately follows the For Each keywords is declared as a Variant.

ByRef Argument Type Mismatch

This is a common error that you will see if you try to pass the "For Each control variable" to a subroutine or function that expects a String.

For example, the following code will generate the error shown below:

Sub GiveMeAString(Val As String)
End Sub

Sub TheThreeStooges()
    Dim Stooge As Variant
    For Each Stooge In Array("Larry", "Moe", "Curly")
        GiveMeAString Stooge
    Next Stooge
End Sub

Compile error:
ByRef argument type mismatch

There are several ways to resolve this error.

The CStr() Function

The CStr() function will coerce the variable you pass to it into a strongly-typed String.

To implement this fix, change GiveMeAString Stooge to GiveMeAString CStr(Stooge).

This is the method I prefer because it does the best job of conveying your intent as a developer.

Force the Variable to be Passed By Value

In the previous fix, we focused in on the word "type" in the error message.  With this "fix," we focus in on the word "ByRef."

To implement this "fix," change GiveMeAString Stooge to GiveMeAString (Stooge).

If you're not familiar with the differences between passing variables by value versus by reference, the above "fix" is probably quite puzzling.  At first glance, it seems that nothing at all really changed.  Why would enclosing the variable Stooge in parentheses resolve this error?

Here's why this resolves the error:  by placing Stooge in parentheses, we are forcing VBA to evaluate the Stooge expression and then send the results of that evaluation to the GiveMeAString routine.  VBA is no longer able to simply pass the Stooge variable's location in memory to GiveMeAString.  Instead, it places the results of evaluating the Stooge expression into a new area of memory and passes that memory location on to the GiveMeAString routine.

I hate this "fix."  That's why I keep writing "fix" inside scare quotes.  For this approach, I prefer to say that this resolves the error message.  It does not fix anything.  

The reason I hate this code so much is that it's too clever.  And (gratuitously) clever code is bad code.  Don't write clever code!

Don’t Write Clever Code

There are two problems with clever code. 1) The next person might not know what you were doing. 2) They might not know if *you* knew what you were doing.

No Longer SetMike Wolfe

It takes a fairly deep understanding of passing by value, passing by reference, expression evaluation, and implementation details of the VBA language (such as the fact that pass-by-reference is the default way to pass arguments to functions and methods) to appreciate why putting the variable inside parentheses resolves the error.

Someday, a different developer will come along, see these seemingly unnecessary parentheses, and simply remove them.  At which point, the code will cease to compile and the new developer may or may not realize what changed to suddenly "break" what had been working code.

So, now that I've explained how to stick peas up your nose to get out of eating vegetables, PLEASE DON'T STICK PEAS UP YOUR NOSE TO GET OUT OF EATING VEGETABLES.

Update the Method to Receive the String Argument By Value

This fix works for the same reason as the parentheses hack I just described.

To implement this fix, change Sub GiveMeAString(Val As String) to Sub GiveMeAString(ByVal Val As String).  The line GiveMeAString Stooge remains unchanged.

If you want to resolve the error by focusing on the ByRef vs. ByVal nature of the compile error, than this is the better way to do it.

One thing to be aware of if you go down this road is that you could be breaking existing functionality if you do this.  For example, if there is different code that relies on the GiveMeAString method modifying the passed value, changing the method signature from (an implied) ByRef to ByVal will break that code.

Because of the potential for side effects, I don't recommend this fix unless you have a firm grasp of the differences between ByVal and ByRef.  However, this is a much better option than the previous parentheses hack.

That said, some codebase conventions and some programmers call for using ByVal as a matter of routine.  One such advocate is Excel MVP Mathieu Guindon, of the esteemed Rubberduck VBA project.

I, personally, prefer to use the default ByRef method of passing variables for signal vs. noise reasons (i.e., I don't like all those extra ByVal's cluttering up my code), but Mathieu makes a compelling case for preferring ByVal.

If you follow the ByVal-by-default convention, you're unlikely to run into this error in the first place.

Image by marian anbu juwan from Pixabay (If I were a rock star, these are exactly the sorts of nerdy things I would sign on my fans' merch.  Which is probably why I'm not a rock star...)