[VBA] Introduction to 2D Arrays | Processing Excel Tabular Data at High Speed

Introduction

In VBA, an “Array” usually refers to a “1-dimensional” list of data. However, when handling table-style data consisting of rows and columns—like an Excel worksheet—the more powerful “2-dimensional (2D) array” is extremely useful.

A 2D array is like a large box with vertical and horizontal dividers, where you access each piece of data using the format (Row Number, Column Number).

Mastering 2D arrays can dramatically improve processing speed, especially when dealing with large amounts of cell data.

In this article, I will explain everything from how to declare a 2D array and assign values using loops, to the high-speed technique of writing array data to a cell range instantly.

Basic Sample Code: Using a 2D Array

This macro creates a 2D array with 3 rows and 4 columns, stores strings in the format (Row, Column) in each element, and then writes the entire data set to the cell range B2:E4 at once.

' Basic usage of a 2D array
Sub TwoDimensionalArrayExample()
    
    '--- 1. Declare the 2D array ---
    ' Declare a String array with rows 1 to 3 and columns 1 to 4
    Dim dataTable(1 To 3, 1 To 4) As String
    Dim r As Long, c As Long ' Loop counters for row (r) and column (c)
    
    '--- 2. Assign values to each element using nested loops ---
    For r = 1 To 3 ' Loop through rows (Outer)
        For c = 1 To 4 ' Loop through columns (Inner)
            ' Store values using (Row, Column) index
            dataTable(r, c) = "R" & r & ", C" & c
        Next c
    Next r
    
    '--- 3. Write array data to cell range at once ---
    Worksheets("Sheet1").Range("B2:E4").Value = dataTable
    
    MsgBox "The content of the 2D array has been written to the cells at once."

End Sub

Explanation of the Code

1. Declaring a 2D Array

Dim dataTable(1 To 3, 1 To 4) As String

This is the part where the 2D array is declared.

• (1 To 3, 1 To 4): By separating them with a comma, you specify the index ranges for the 1st Dimension (Rows) and 2nd Dimension (Columns).
• In this example, a table-like array with “Rows 1 to 3” and “Columns 1 to 4” (a 3×4 grid) is created.

2. Assigning Values with Nested Loops

For r = 1 To 3 ... For c = 1 To 4 ... Next c ... Next r

To process all elements of a 2D array, it is standard practice to nest For loops (put one loop inside another). The outer loop processes the rows, and the inner loop processes the columns in order.

• dataTable(r, c) = ...: Assignment and retrieval of values are done using the format ArrayName(RowIndex, ColumnIndex).

3. Bulk Output to Cell Range

Worksheets("Sheet1").Range("B2:E4").Value = dataTable

This single line represents one of the biggest benefits of using 2D arrays.

If the dimensions and size of the array (3 rows, 4 columns) match the size of the destination cell range (B2:E4 is also 3 rows, 4 columns), you can simply assign the array variable directly to the .Value property of the range. All data is transferred instantly.

This technique is essential for handling large data because it is overwhelmingly faster than writing to cells one by one inside a For loop.

Summary

In this article, I explained the basic usage of 2D arrays in VBA.

1. Declare an array to store table data using Dim ArrayName(RowRange, ColumnRange).
2. Process all elements efficiently using nested For loops.
3. Transfer data to a sheet at high speed using the Range.Value = Array syntax.

Mastering 2D arrays will dramatically improve your data processing capabilities in Excel VBA. Please try using this powerful tool in your projects.