Assigning arrays to a variable. VBA code not clear

Question

Could you please explain to me why the following code works?

Sub MyTest()
Dim arrL1(3) As Variant
Dim arrTemp(10) As Variant
Dim i As Long, j As Long, s As String
Dim varTemp As Variant

For i = LBound(arrL1) To UBound(arrL1)
    For j = LBound(arrTemp) To UBound(arrTemp)
        arrTemp(j) = i + j
    Next

    **arrL1(i) = arrTemp**
Next
' 2nd part of the code

**For i = LBound(arrL1) To UBound(arrL1)**
    varTemp = arrL1(i)
    s = "" 
    For j = LBound(varTemp) To UBound(varTemp)      
        s = s & Format(varTemp(j), "@@@")
    Next
    Debug.Print s 
Next

End Sub

What is confusing me is the first "starred" line arrL1(i) = arrTemp. When I try to assign an array in this way in my project I get an Object or Application defined error.

Another confusing line is in the 2nd part, i.e., varTemp = arrL1(i). Do we really need another variable varTemp? Can we not get away with just arrL1(i)?

Thank you in advance.

Shai Rado · Accepted Answer · 2016-08-04 09:16:39Z

1

Regarding your first question, did you define Dim arrL1(3) As Variant , and did you set it exactly like in the loop ?

Regarding your second question, it is not necessary to have varTemp = arrL1(i) , your code will work without it (just like in the code below).

For i = LBound(arrL1) To UBound(arrL1)
    s = ""
    For j = LBound(arrL1(i)) To UBound(arrL1(i))
        s = s & Format(arrL1(i)(j), "@@@")
    Next

    Debug.Print s
Next

answered Aug 4, 2016 at 9:16

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Lola Over a year ago

@ Shai Rado: As per first question, I've declared "Dim b As Variant", as I don't know in advance the size of my array. Could this be the problem?

Shai Rado Over a year ago

@Lola you use b instead of arrL1(3) ?

Lola Over a year ago

@ Shai Rado: Sorry, I should've been clearer. The code above comes from a textbook. I struggle however to use it in my own code s.t. assigning an array (a table n x k) to a variant without having to redim it, didn't lead to an error.

Shai Rado Over a year ago

@Lola , you should open another post, show a screen-shot of your worksheeet, od data range you are tyring to put in this array.

Lola Over a year ago

@ Shai Rado: ok. But you could you please explain me what is going on in this code, considering my first question? arrL1(3) has got 4 entries with dimention 1, and we're assigning a two-dimentional array of size (1*11) to each of its 4 entries?

|

Collectives™ on Stack Overflow

Assigning arrays to a variable. VBA code not clear

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related