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
– psychicebola, Commented Aug 26, 2015 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
– Grade 'Eh' Bacon, Commented Aug 26, 2015 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
– psychicebola, Commented Aug 26, 2015 at 13:21
aryTitle = Array(Array("Desciption", "Value"), Array("Description2", "Value2")) ... but this would give you Debug.Print (aryTitle(0)(0) + ":" + aryTitle(0)(1)). — gembird
– gembird, Commented Aug 26, 2015 at 13:26
possible duplicate of Pre defining multi dimensional array in Excel VBA — brettdj
– brettdj, Commented Aug 27, 2015 at 8:55

Dick Kusleika · Accepted Answer · 2015-08-26 13:57:27Z

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

John Coleman · Accepted Answer · 2015-08-26 13:57:37Z

3

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

answered Aug 26, 2015 at 13:57

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Collectives™ on Stack Overflow

VBA - Setting multidimensional array values in one line

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related