# Array in array

Tested in Excel 365 (16.8730.2046) 64-bit

1. Public Sub ArrayInArray()
2.     Dim a, w, i, j
3.     Dim b() As Variant
4.     For w = 1 To Worksheets.Count
5.         ReDim Preserve b(w)
6.         Worksheets(w).Select
7.    ' read a whole range into an array
8.        a = Worksheets(w).Range(Cells(1, 1), Cells(5, 8)).Value
9.         b(w) = a
10.     Next w
11.     'Debug.Print b(1)(1, 4)
12.    'Debug.Print b(1)(4, 1)
13.
14.  ' access the elements
15.    For w = 1 To Worksheets.Count
16.         For i = 1 To UBound(b(w), 1) 'max row
17.            For j = 1 To UBound(b(w), 2) 'max col
18.                b(w)(i, j) = b(w)(i, j) * 4
19.                 Worksheets(w).Cells(i, j).Value = b(w)(i, j)
20.             Next j
21.         Next i
22.     Next w
23. End Sub

Or as function:

1. Option Explicit
2.  Public Function ArrayInArray(LastRow, LastCol) As Variant
3.     Dim a, w, i, j
4.     Dim b() As Variant
5.
6.     For w = 1 To Worksheets.Count
7.         ReDim Preserve b(w)
8.         Worksheets(w).Select
9.         ' read a whole range into an array
10.        a = Worksheets(w).Range(Cells(1, 1), Cells(LastRow, LastCol)).Value
11.         b(w) = a
12.     Next w
13.
14.     ArrayInArray = b
15. End Function
16. ' you can use it like this
17. Public Sub Main()
18.     Dim b As Variant
19.     Dim w, i, j
20.     b = ArrayInArray(7, 5)
21.
22. '   access the elements like
23.    'Debug.Print b(1)(1, 4)
24.    'Debug.Print b(1)(4, 1)
25. '   or (let's say we multiply with 100)
26.    For w = 1 To Worksheets.Count
27.         For i = 1 To UBound(b(w), 1) 'max row
28.            For j = 1 To UBound(b(w), 2) 'max col
29.                b(w)(i, j) = b(w)(i, j) * 100
30.                 Worksheets(w).Cells(i, j).Value = b(w)(i, j)
31.             Next j
32.         Next i
33.     Next w
34. End Sub

This site uses Akismet to reduce spam. Learn how your comment data is processed.