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.

Array() function returns a Variant containing an array.

SBF
– SBF

2017-12-20 11:56:35 +00:00
Commented Dec 20, 2017 at 11:56 — SBF
– SBF, Commented Dec 20, 2017 at 11:56
Did you see this question?

gembird
– gembird

2017-12-20 12:03:38 +00:00
Commented Dec 20, 2017 at 12:03 — gembird
– gembird, Commented Dec 20, 2017 at 12:03

SBF · Accepted Answer · 2017-12-20 12:08:53Z

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, 2017 at 12:08

SBF

1,4193 gold badges13 silver badges25 bronze badges

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

2 Comments

Patrick Honorez Over a year ago

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 :-)

Patrick Honorez Over a year ago

Strangely, the 'pure string' version seems slower.

Collectives™ on Stack Overflow

Properly define string array in VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related