VBA

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?

How do you assign values to a multidimensional array?

Assign a value to each element using index Here we declare the array using the size and then assign value to each element using the index. As you can see we assigned the value to each element of the array using the index. That's all for assigning the value to two dimensional array.

How do I create a multi dimensional array in VBA?

To create a multiple dimensional array, you need to define the dimensions while declaring the array. Well, you can define as many as dimensions that you need (VBA allows 60 dimensions) but you will probably not need to use more than 2 or 3 dimensions of any of the arrays.

How do you assign a value to an array in VBA?

Passing the VBA Array to a SubYou declare the parameter using parenthesis similar to how you declare a dynamic array. Passing to the procedure using ByRef means you are passing a reference of the array. So if you change the array in the procedure it will be changed when you return.

How do I create a dynamic array in VBA?

Create a Dynamic Array in VBAFirst, declare an array with its name. After that, the elements count left the parentheses empty. Now, use the ReDim statement. In the end, specify the count of elements you want to add to the array.

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

VBA - Setting multidimensional array values in one line

Tags:

multidimensional-array

José

People also ask

1 Answers

Dick Kusleika

Recent Activity

Donate For Us