VBA - Setting multidimensional array values in one line

Question

Right, so using Python I would create a multidimensional list and set the values on one line of code (as per the below).

aryTitle = [["Desciption", "Value"],["Description2", "Value2"]]
print(aryTitle[0,0] + aryTitle[0,1])

I like the way I can set the values on one line. In VBA I am doing this by:

Dim aryTitle(0 To 1, 0 To 1) As String
aryTitle(0, 0) = "Description"
aryTitle(0, 1) = "Value"
aryTitle(1, 0) = "Description2"
aryTitle(1, 1) = "Value2"
MsgBox (aryTitle(0, 0) & aryTitle(0, 1))

Is there a way to set the values in one line of code?

not as far as i know, if you have the values in a worksheet you can use loops to add the values to the array. — psychicebola, Aug 26 '15 at 13:17
Define 'On one row'. Do you mean a row of Excel cells? You could do this using the Range command, which selects a cell based on row and column. Use a loop to loop through the columns, and change the Range.formula method to aryTitle(x,y). — Grade 'Eh' Bacon, Aug 26 '15 at 13:19
if you need a two dimension array use the `library` object. It provides `key value` pairs and is easier to resize. — psychicebola, Aug 26 '15 at 13:21
Sorry - I see now you mean "set it in 1 line of code" not "set the values onto 1 row of Excel cells". My comment is not relevant. Note that if your values are already in a worksheet as a range [group of Excel cells], you can directly copy an entire range onto another, but I do not believe you can do this directly with an array of strings; a loop may be your only option. — Grade 'Eh' Bacon, Aug 26 '15 at 13:23
```aryTitle = Array(Array("Desciption", "Value"), Array("Description2", "Value2"))``` ... but this would give you ```Debug.Print (aryTitle(0)(0) + ":" + aryTitle(0)(1))```. — Daniel Dušek, Aug 26 '15 at 13:26
possible duplicate of [Pre defining multi dimensional array in Excel VBA](http://stackoverflow.com/questions/6938614/pre-defining-multi-dimensional-array-in-excel-vba) — brettdj, Aug 27 '15 at 08:55

score 3 · Accepted Answer · answered Aug 26 '15 at 13:57

Not natively, no. But you can write a function for it. The only reason Python can do that is someone wrote a function to do it. The difference is that they had access to the source so they could make the syntax whatever they like. You'll be limited to VBA function syntax. Here's a function to create a 2-dim array. It's not technically 'one line of code', but throw it in your MUtilities module and forget about it and it will feel like one line of code.

Public Function FillTwoDim(ParamArray KeyValue() As Variant) As Variant

    Dim aReturn() As Variant
    Dim i As Long
    Dim lCnt As Long

    ReDim aReturn(0 To ((UBound(KeyValue) + 1) \ 2) - 1, 0 To 1)

    For i = LBound(KeyValue) To UBound(KeyValue) Step 2
        If i + 1 <= UBound(KeyValue) Then
            aReturn(lCnt, 0) = KeyValue(i)
            aReturn(lCnt, 1) = KeyValue(i + 1)
            lCnt = lCnt + 1
        End If
    Next i

    FillTwoDim = aReturn

End Function

Sub test()

    Dim vaArr As Variant
    Dim i As Long
    Dim j As Long

    vaArr = FillTwoDim("Description", "Value", "Description2", "Value2")

    For i = LBound(vaArr, 1) To UBound(vaArr, 1)
        For j = LBound(vaArr, 2) To UBound(vaArr, 2)
            Debug.Print i, j, vaArr(i, j)
        Next j
    Next i

End Sub

If you supply an odd number of arguments, it ignores the last one. If you use 3-dim arrays, you could write a function for that. You could also write a fancy function that could handle any dims, but I'm not sure it's worth it. And if you're using more than 3-dim arrays, you probably don't need my help writing a function.

The output from the above

 0             0            Description
 0             1            Value
 1             0            Description2
 1             1            Value2

score 3 · Answer 2 · answered Aug 26 '15 at 13:57

You can write a helper function:

Function MultiSplit(s As String, Optional delim1 As String = ",", Optional delim2 As String = ";") As Variant
    Dim V As Variant, W As Variant, A As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    V = Split(s, delim2)
    m = UBound(V)
    n = UBound(Split(V(0), delim1))
    ReDim A(0 To m, 0 To n)
    For i = 0 To m
        For j = 0 To n
            W = Split(V(i), delim1)
            A(i, j) = Trim(W(j))
        Next j
    Next i
    MultiSplit = A
End Function

Used like this:

Sub test()
    Dim A As Variant
    A = MultiSplit("Desciption, Value; Description2, Value2")
    Range("A1:B2").Value = A
End Sub

VBA - Setting multidimensional array values in one line

2 Answers2

Linked