VBA: Is it possible to create an array of string variables?

Question

I am trying to create an array of string variables (not an array of text strings, so to speak), with the goal of passing each variable into the following procedure.

In my actual use case, I have two large blocks of code that are exactly the same for each of two string variables, and I am really just trying to shorten / consolidate my code.

I have pasted an example of the code below.

So I get it...the test will always print "Fail" because it's essentially testing if "strA" = "Apple" and then if "strB" = "Apple", but my intent is for it to test if strA = "Apple" and then if strB = "Apple" (where strA and strB are treated as variables).

Any advice? Is what I am trying to accomplish even possible? Thank you much.

Sub ArrayTest()

'Declare variables
Dim strA As String: strA = "Apple"
Dim strB As String: strB = "Banana"
Dim strArray() As String: strArray = Split("strA,strB", ",")
Dim i As Long
Dim strResult As String

'Test the array
For i = 0 To UBound(strArray)
    If strArray(i) = "Apple" Or strArray(i) = "Banana" Then
        strResult = "Pass"
    Else: strResult = "Fail"
    End If
    Debug.Print strResult
    Next

End Sub

Why not use a dictionary keyed by "A", "B" etc.? There is no direct way of doing what you want to do, but even if there were, it would probably be a bad decision. Seems like a classic case of an XY problem. What problem are you actually trying to solve? — John Coleman
– John Coleman, Commented Feb 27, 2021 at 22:01
@JohnColeman I have two large blocks of code that are exactly the same for each of two string variables (aside from the string variable referenced in each block, of course), and I'm really just trying to shorten / consolidate my code. As for your suggestion using a dictionary, I will research how to do so as I'm unfamiliar with this concept, and I appreciate your advice. — Chase
– Chase, Commented Feb 27, 2021 at 22:18
@Chase I was going to vote to close as a duplicate of stackoverflow.com/q/6646864/11683, now I'm wondering whether what you are asking about is how to pass a variable to a procedure. — GSerg
– GSerg, Commented Feb 27, 2021 at 22:20
@GSerg Thanks for your feedback GSerg - I have edited my question for clarity (and you are correct, that is what I'm attempting to do). — Chase
– Chase, Commented Feb 27, 2021 at 22:24
@Chase So have Sub ThatLargeBlockOfCode(byval s as string) and call it ThatLargeBlockOfCode "Apple" and ThatLargeBlockOfCode "Banana"? — GSerg
– GSerg, Commented Feb 28, 2021 at 7:03

Dy.Lee · Accepted Answer · 2021-02-27 22:35:13Z

0

Wrong linking of variables and characters.

Sub ArrayTest()

'Declare variables
Dim strA As String: strA = "Apple"
Dim strB As String: strB = "Banana"
Dim strC As String
strC = strA & "," & strB
Dim strArray() As String: strArray = Split(strC, ",")
Dim i As Long
Dim strResult As String

'Test the array
For i = 0 To UBound(strArray)
    If strArray(i) = "Apple" Or strArray(i) = "Banana" Then
        strResult = "Pass"
    Else: strResult = "Fail"
    End If
    Debug.Print strResult
    Next

End Sub

answered Feb 27, 2021 at 22:35

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

VBA: Is it possible to create an array of string variables?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related