Declare and Initialize String Array in VBA

Question

This should work according to another stack overflow post but its not:

Dim arrWsNames As String() = {"Value1", "Value2"}

Can anyone let me know what is wrong?

Monoploid

Note: The curly braces syntax does NOT work inside VBA, it is designed for VB.NET. For your own sanity, do not get these two environments confused. – Jillene

If you're using Excel (and you're content with a Variant array), you can use Dim x() As Variant: x = [{"Value1", "Value2"}] – Chickamauga

For anyone who's looking at this comment, almost two years later (like me). It seems that VBA/Excel does NOT like the syntax Dim x() As Variant: x = [{"Value1", "Value2"}] IF you are using variables... i.e. if v1 = "Value1"; v2 = "Value2", then x = [{v1, v2}] will generate an error, whereas x = [{"Value1", "Value2"}] will not. – Nympholepsy

Does this answer your question? Microsoft Visual Basic: how to initialize an array variable? – Bridgid

Answer 1

Try this:

' Variant array    
Dim myVariantArray As Variant
myVariantArray = Array("Cat", "Dog", "Rabbit")

' String array
Dim myStringArray() As String
myStringArray = Split("Cat,Dog,Rabbit", ",")

Neil

technically creates a variant array, not a string array. Of course the variant array might be an array of only strings, but this approach would also allow non-string data types: myArray = Array("A", "B", 12345, "D"...) – Defoe

What about Dim myStringArray() As String ... myStringArray = Array( "Cat", "Dog" , "Rabbit"). Variants - yuck! – Adage

if you want to have it in one line, you can use the colon after the declaration: Dim arrWsNames() As String: arrWsNames = Split("Value1,Value2", ",") The initialization from comment above does not work for me, because Array() creates an Array of Variants and not Strings – Miscellaneous

not a good answer as 1) it's a variant containing an array and 2) variants are the slowest data type in VBA – Stoneham

@Stoneham and 3) VBA doesn't have String array initializer. But you can use Split for example. – Neil

This creates an array of variants inside of another variant. To make matters worse, that variant, which is not an array, is named "myarray". Not correct, not recommended. – Bignonia

This does not create an array but a variant. As variants can hold any kind of variable types (including arrays), it won't complain when you assign it an array in the second line, but it's bad style and may lead to all kind of confusion and other problems if you do so. I'd recommend to be clear and explicit and use Dim myStringArray() As String, as was previously suggested here. – Benzo

@Andez: it would be great if Dim a() As String ... a = Array(...) worked, but it doesn't -- that's a Type mismatch error... Unfortunately there's no good way to do this in VBA. – Lananna

@Lananna Indeed... Not sure where I tried that 4 years ago. Although "matan justme" answer has been marked down - this is what I just did before reading down this. His answer works. You could have a global function to do this initialisation without using Variant as your defined type which makes more readable although I would name the function StringArray. That would be my chosen answer. – Adage

@Adage I don't know how I missed that answer, it looks great indeed, thanks! – Lananna

Make it Dim myarray() as Variant, and I'm with this answer. To those suggesting arrays of anything but Variants, be advised you can't use Array() to assign values to it. Only Variants work. – Plosion

Answer 2

In the specific case of a String array you could initialize the array using the Split Function as it returns a String array rather than a Variant array:

Dim arrWsNames() As String
arrWsNames = Split("Value1,Value2,Value3", ",")

This allows you to avoid using the Variant data type and preserve the desired type for arrWsNames.

Salamone

This definitely makes passing it along to other functions cleaner; not to mention saving you memory... – Mcburney

Answer 3

The problem here is that the length of your array is undefined, and this confuses VBA if the array is explicitly defined as a string. Variants, however, seem to be able to resize as needed (because they hog a bunch of memory, and people generally avoid them for a bunch of reasons).

The following code works just fine, but it's a bit manual compared to some of the other languages out there:

Dim SomeArray(3) As String

SomeArray(0) = "Zero"
SomeArray(1) = "One"
SomeArray(2) = "Two"
SomeArray(3) = "Three"

Honolulu

so in the array declaration we don't define the size (4) but the highest index (3)? Do I understand it right? – Scoutmaster

@dpelisek, no. It's a mistake. In the example, the correct size should be 4. – Jessiajessica

Answer 4

Dim myStringArray() As String
*code*
redim myStringArray(size_of_your_array)

Then you can do something static like this:

myStringArray = { item_1, item_2, ... }

Or something iterative like this:

Dim x
For x = 0 To size_of_your_array
    myStringArray(x) = data_source(x).Name
Next x

Offprint

Answer 5

Public Function _
CreateTextArrayFromSourceTexts(ParamArray SourceTexts() As Variant) As String()

    ReDim TargetTextArray(0 To UBound(SourceTexts)) As String
    
    For SourceTextsCellNumber = 0 To UBound(SourceTexts)
        TargetTextArray(SourceTextsCellNumber) = SourceTexts(SourceTextsCellNumber)
    Next SourceTextsCellNumber

    CreateTextArrayFromSourceTexts = TargetTextArray
End Function

Example:

Dim TT() As String
TT = CreateTextArrayFromSourceTexts("hi", "bye", "hi", "bcd", "bYe")

Result:

TT(0)="hi"
TT(1)="bye"
TT(2)="hi"
TT(3)="bcd"
TT(4)="bYe"

Enjoy!

Edit: I removed the duplicatedtexts deleting feature and made the code smaller and easier to use.

Anthem

This should be the answer - although there isn't any built in way to initialise, surely a global function as such to do this keeps the code readable and it doesn't inflict that your definition has to be a variant – Adage

Answer 6

An only-what's-needed function that works just like array() but gives a string type. You have to first dim the array as string, as shown below:

Sub UseStringArray()

    Dim sample() As String
    sample = StringArray("dog", "cat", "horse")

End Sub

Function StringArray(ParamArray ArgList())

    ReDim tempArray(UBound(ArgList)) As String
    For i = 0 To UBound(ArgList)
        tempArray(i) = ArgList(i)
    Next
    StringArray = tempArray

End Function

For more on converting array types see here: How transform Variant to Double format and vice versa in VBA

Morose

I've been using VBA for more than 20 years and I didn't know ParamArray existed. You are a wizard, thank you ! – Dragging

Answer 7

A Functional Approach

Using the same solution as @matan_justme and @mark_e, I think the structure can be cleaned up a bit.

Just as the built in function Array we can build our own custom function that uses a ParamArray to accept an array of items as the argument, and return a String Array.

By default, when assigning values to a String Array it will implicitly convert any non-String values into a String.

Public Function StringArray(ParamArray values() As Variant) As String()
    Dim temp() As String
    ReDim temp(LBound(values) To UBound(values))

    Dim index As Long
    For index = LBound(temp) To UBound(temp)
        temp(index) = values(index)
    Next
    StringArray = temp
End Function

Reusability of this structure

The nice thing with this structure is that it can be applied to different data types with intuitive naming conventions. For instance, if we need an Array with Long values, we simply need to change every instance where String is located.

Public Function LongArray(ParamArray values() As Variant) As Long()
    Dim temp() As Long
    ReDim temp(LBound(values) To UBound(values))

    Dim index As Long
    For index = LBound(temp) To UBound(temp)
        temp(index) = values(index)
    Next
    LongArray = temp
End Function

Other Data Type examples could include:

• Single
• Double
• Date

The beauty is an error will be thrown when a value is not the correct data type and it can not be converted over, you will receive a Run-time error '13': Type mismatch error.

Arielariela

Answer 8

Using

Dim myarray As Variant

works but

Dim myarray As String

doesn't so I sitck to Variant

Mehalick

That's because you should be adding parentheses at the end of myarray. The parentheses lets VBA know that it's an array. Dimming as a string makes it a String-only array. – Exposition

you have to declare the bounderies of the array. Either a dynamic Array : Dim MyArray() as String, or a fixed size Array : Dim MyArray(1 to 10) as String. – Garland