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?

Answer 1

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"

Answer 2

Try this:

Dim myarray As Variant
myarray = Array("Cat", "Dog", "Rabbit")

Answer 3

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.

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

Answer 5

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.

Answer 6

Using

Dim myarray As Variant

works but

Dim myarray As String

doesn't so I sitck to Variant