Properly define string array in VBA

Question

I usually use variants when I need an array:

Sub test()   'runs fine
    Dim s
    s = Array("a", "b", "c")
    Debug.Print s(1)
End Sub

Now I want performance for a specific case, so I thought I could save a few millisec by using a String array. How do I do that?

My try:

Sub test2()
    Dim s() As String
    s = Array("a", "b", "c")     'run time error 13: type mismatch
    Debug.Print s(1)
End Sub

Thanks.

Did you see [this question](https://stackoverflow.com/questions/19369132/declare-and-initialize-string-array-in-vba)? — Daniel Dušek, Dec 20 '17 at 12:03

score 3 · Accepted Answer · answered Dec 20 '17 at 12:08

3

Try this:

Sub test2()
    Dim s1 As Variant
    Dim s2() As String
    s1 = Array("a", "b", "c")
    s2 = Split("a,b,c", ",")

    Debug.Print VarType(s1) & " Array of variants"
    Debug.Print VarType(s2) & " Array of strings"
End Sub

Output:

8204 Array of variant
8200 Array of string

answered Dec 20 '17 at 12:08

SBF

1,252
3
12
21

Excellent ! That exactly covers my case, and makes the array easier to code as a bonus. All I have to see now is if it's any faster :-) – iDevlop Dec 20 '17 at 12:13
Strangely, the 'pure string' version seems slower. – iDevlop Dec 20 '17 at 13:41

Properly define string array in VBA

1 Answers1