[arrays] 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?

This question is related to arrays vba initialization

The answer is


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.


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.


Using

Dim myarray As Variant

works but

Dim myarray As String

doesn't so I sitck to Variant


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

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"

Examples related to arrays

PHP array value passes to next row Use NSInteger as array index How do I show a message in the foreach loop? Objects are not valid as a React child. If you meant to render a collection of children, use an array instead Iterating over arrays in Python 3 Best way to "push" into C# array Sort Array of object by object field in Angular 6 Checking for duplicate strings in JavaScript array what does numpy ndarray shape do? How to round a numpy array?

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?

Examples related to initialization

"error: assignment to expression with array type error" when I assign a struct field (C) How to set default values in Go structs How to declare an ArrayList with values? Initialize array of strings Initializing a dictionary in python with a key value and no corresponding values Declare and Initialize String Array in VBA VBA (Excel) Initialize Entire Array without Looping Default values and initialization in Java Initializing array of structures C char array initialization