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

Add a Comment

Your email address will not be published. Required fields are marked *

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