[Excel VBA] Basic Technique to Speed Up Processing by Loading Cell Ranges into a 2D Array

2025年12月29日

When processing large amounts of cell data in VBA, reading and writing cells one by one (using loops like For Each) can become extremely slow as the data volume increases.

The most effective and basic way to solve this performance issue is to load the cell range values into an “array” all at once and process them in VBA’s memory.

This article explains how to use arrays to minimize the interaction between the sheet and VBA, dramatically speeding up your macros.

Why Using Arrays is Faster

One of the biggest causes of slow VBA performance is frequent data transfer between the VBA program and the Excel worksheet.

The Slow Way (Looping cell by cell): If you process 10,000 cells, communication between VBA and Excel happens 10,000 times. This is inefficient, like going to the supermarket 10,000 times to buy one item each time.
The Fast Way (Batch assignment to an array): You load all 10,000 cell values into VBA memory (an array) in just one communication. Processing in memory is very fast, so the subsequent work completes instantly. This is like going to the supermarket once with a shopping list.

Complete VBA Code

Below is the VBA code that reads all values from a table area (CurrentRegion) including cell C2 into a 2D array at once, and then displays part of the content.

Sub LoadRangeIntoArray()

    ' 配列を格納するための変数は、必ずVariant型で宣言します
    Dim dataArray As Variant
    Dim sourceRange As Range
    
    '--- 1. 配列に読み込みたいセル範囲を特定 ---
    ' CurrentRegionは、Ctrl + * で選択される範囲と同じです
    Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("C2").CurrentRegion

    '--- 2. セル範囲の.Valueを、Variant型の変数に一括で代入 ---
    ' この一行だけで、範囲の値が2次元配列として格納されます
    dataArray = sourceRange.Value

    '--- 3. 配列の要素にアクセスして内容を確認 ---
    ' 配列のインデックスは (行番号, 列番号) で、1から始まります
    MsgBox "配列の1行目、1列目の値は: " & dataArray(1, 1) & vbCrLf & _
           "配列の3行目、2列目の値は: " & dataArray(3, 2), vbInformation, "配列の内容確認"
           
    '--- (応用) メモリ上で配列をループ処理 ---
    Dim i As Long
    For i = 1 To UBound(dataArray, 1)
        ' 例: 3列目の値を大文字に変換してデバッグ出力
        Debug.Print UCase(dataArray(i, 3))
    Next i

End Sub

Key Points of the Code

1. Dim dataArray As Variant

You must declare the variable that receives values from a cell range as a Variant type. If you declare it as a specific array type like Dim dataArray() As String, you will get an error when trying to assign values all at once.

2. dataArray = sourceRange.Value

This is the core of the code. When you assign the .Value property of a Range object (containing multiple cells) to a Variant variable, VBA automatically creates a 2-dimensional array storing the values of that range.

3. Array Index

The array created by Range.Value has the following characteristics:

Starts from 1: The array index starts from 1, not 0 (which is common in other programming languages). This matches the row and column numbers in Excel, making it intuitive to handle.
2-Dimensional (Row, Column): You access values by specifying dataArray(RowNumber, ColumnNumber).
Relative Position: The row and column numbers are relative to the top-left corner of the loaded range, which is (1, 1). For example, if you load the range C2:D10, the value of cell C2 is stored in dataArray(1, 1).

Summary

The most important technique for improving VBA processing speed is to minimize the number of times you access the worksheet.

Use myArray = myRange.Value to read the data range into a Variant array all at once.
Perform all processing (like For loops) on the array in memory.
(If writing is needed) Write the processed array back to the cell range all at once.

By strictly following this “read all at once, write all at once” approach, you can dramatically improve the performance of your VBA macros.

よかったらシェアしてね！