CODEWITHSUNDEEP
vbaexcel
Greenberet
Greenberet

Reputation: 500

How to declare (initialize) 1-D and 2-D fixed-size array by enumerating the values?

I want to store constant name pairs in a 2-D array of strings. I can write:

Dim a(2, 1) As String     '0 to 2, and 0 to 1
a(0, 0) = "one"
a(0, 1) = "first"
a(1, 0) = "two"
a(1, 1) = "second"
a(2, 0) = "three"
a(2, 1) = "third"

But I think it would be more elegant and more extendable if I made VBA set the sizes of the array by looking at the instantly given content. I imagine something like this:

 Dim a({"one","first"}, _
       {"two","second"}, _
       {"three","third"}) as String

Then I realized I don't even know how to declare a 1-D array by instant enumeration (initialization).

Is this possible at all? If yes, how?

Is there official documentation out there covering this Q? (This isn't.)

Upvotes: 1

Views: 429

Answers (2)

PASUMPON V N
PASUMPON V N

Reputation: 1186

For one dimensional Array

Sub aArray()

Dim myarray As Variant

myarray = Array("One", "Two", "Three")


For i = 0 To UBound(myarray, 1)

Debug.Print myarray(i)

Next


End Sub

Upvotes: 1

mielk
mielk

Reputation: 3940

The most reasonable way to make it more elegant is by creating the new function that creates 2D array based on the given string.

It could look like below:

Public Function create2DStringArray(ParamArray values() As Variant) As String()
    Dim varItem As Variant
    Dim strArray() As String
    Dim arraySize As Long
    Dim result() As String
    Dim iterator As Long
    '-------------------------------------------------------------------------------------------------

    On Error Resume Next
    arraySize = UBound(values) - LBound(values) + 1
    On Error GoTo 0


    ReDim result(0 To arraySize - 1, 0 To 1)


    For Each varItem In values
        strArray = VBA.Split(varItem, ";")
        result(iterator, 0) = strArray(0)
        result(iterator, 1) = strArray(1)
        iterator = iterator + 1
    Next varItem


    create2DStringArray = result


End Function

Now, you could initialize your String array like that:

Dim a() As String
a = create2DStringArray("one;first", "two;second", "three;third")

Note that the function above has some limitations:

  • it can take only up to 30 parameters,

  • it doesn't work if any of the given string contains semicolon inside (unless you chose another separator, I used semicolon above).

Upvotes: 2

Related Questions