Declare and Initialize String Array in VBA

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?

7 Answers

Try this:

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

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.

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"

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

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.

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

Using