Excel vba - combine multiple arrays into one

Question

If I have 5 arrays that have same dimension, can I combine them into one array with 5 subarrays.

va = ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp)).Value   
vb = ws1.Range("D2", ws1.Cells(Rows.Count, "D").End(xlUp)).Value    
vc = ws1.Range("F2", ws1.Cells(Rows.Count, "F").End(xlUp)).Value    
vd = ws1.Range("C2", ws1.Cells(Rows.Count, "C").End(xlUp)).Value    
ve = ws1.Range("E2", ws1.Cells(Rows.Count, "E").End(xlUp)).Value

Can I do this:

ReDim vx(1 To UBound(va, 1), 1 To 5, 1 To 1)
vx(1,1,1) = va
vx(1,2,1) = vb
vx(1,3,1) = vc
vx(1,4,1) = vd
vx(1,5,1) = ve

Or do I have to do it one element by one?

Vityata · Accepted Answer · 2017-11-07 10:58:12Z

2

Combining arrays into array is named usually jagged array (info for C# jagged arrays and How do I set up a "jagged array" in VBA? and Can you declare jagged arrays in excel VBA directly?), if the number of elements is not the same. In the case below va has one element more than vb and vc:

Option Explicit

Public Sub TestMe()

    Dim va, vb, vc, vx

    va = Array(1, 2, 3, 4)
    vb = Array(11, 22, 33)
    vc = Array(111, 222, 333)

    ReDim vx(3)
    vx(0) = va
    vx(1) = vb
    vx(2) = vc

    Debug.Print vx(0)(1)
    Debug.Print vx(0)(2)

    Debug.Print vx(1)(1)
    Debug.Print vx(2)(2)

End Sub

You simply declare vx and you assign va, vb, vc to it as they are. Then you access the elements like this -> vx(1)(0)

edited Nov 7, 2017 at 10:58

answered Nov 7, 2017 at 10:52

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

Excel vba - combine multiple arrays into one

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related