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

User · Answer

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"

User · Answer

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

User · Answer

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

User · Answer

Try this       Dim myarray As Variant myarray   Array  Cat    Dog    Rabbit

User · Answer

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  quot hi quot    quot bye quot    quot hi quot    quot bcd quot    quot bYe quot    Result  TT 0   quot hi quot  TT 1   quot bye quot  TT 2   quot hi quot  TT 3   quot bcd quot  TT 4   quot bYe quot   Enjoy  Edit  I removed the duplicatedtexts deleting feature and made the code smaller and easier to use

User · Answer

Using  Dim myarray As Variant   works but   Dim myarray As String   doesn t so I sitck to Variant

[arrays] Declare and Initialize String Array in VBA

Examples related to arrays

Examples related to vba

Examples related to initialization