2025年5月8日 / 最終更新日時 : 2025年5月8日 hashimoto Tech

ExcelVBA：配列でマクロの処理時間を高速化する方法の紹介

掲載日: 2025/05/08

マクロを実行して長い時間を待ったり、Excelがフリーズしたりしてしまうことが起こったことがありませんか？

大量のデータを管理する時のこの悩みを解決する一つの方法、配列を使用することを紹介したいと思います。

なぜ配列？

少量のデータを管理するときに Range を使うことが多いかと思います。なぜなら、ワークシート上のセルをそのまま参照できるため、変数の宣言や代入の手間が少ないからです。ただし、Range は指定したセル範囲のすべての情報を持っており、ループ処理で1セルずつアクセスするため、処理が重くなりがちです。

配列は、基本的に複数のデータを格納したデータ構造なので、要らないデータを使用せず、セール範囲の値だけを渡せば軽く動作してくれます。

Rangeと配列の比較

さて、Rangeと配列の処理時間を見てみましょう。

環境設定

まず、マクロが有効なExcelブック(.xlsm)を作成します。マクロを作成するときに保存する必要があるので事前にファイルを保存しましょう。

次は、大量なデータが必要なため、データベースを無料に配布するサイトがおすすめです。今回使用しているデータは2025年3月Steamのゲームデータベースです。データ量は約9万行と非常に多く、処理負荷が高いケースとして適しています。このデータをExcelブックにコピーして、シート名を「Data」に変更していきましょう。

最後に、Excelブックにもう一つのシートを作成して「Out」という名前で設定していきます。

比較例：データ写し処理

例として、簡単な処理で Range を使って「Data」シートのA列のデータを「Out」シートのA列に写します。

※日本語をソースコードに書くと、他のブックなどにコピーすると文字化けが起きる場合があるため、できる限り英語で書いていきましょう。

〈全体ソースコード〉

Sub Test_Out()

‘Init data and output sheets
Dim ws, wsOut As Worksheet
Set ws = ThisWorkbook.Sheets(“Data”)
Set wsOut = ThisWorkbook.Sheets(“Out”)

‘ Init Active and Target cells
Dim activeCell, targetCell As Range
Set activeCell = ws.Range(“A1”)
Set targetCell = wsOut.Range(“A1”)

‘ For loop to end
Dim i As Long
For i = 1 To 89625
If Not IsEmpty(activeCell) Then
targetCell.Value = activeCell.Value
End If

Set activeCell = activeCell.Offset(1, 0)
Set targetCell = targetCell.Offset(1, 0)
Next i
‘ Finish Message Box
MsgBox “Data Copy Completed!”

End Sub

最後に、経過時間を簡単に計算します。

〈全体ソースコード〉

Sub Test_Out()
‘ Init Timer
Dim startTime, endTime, processTime As Double
startTime = Timer

‘ Init data and output sheets
Dim ws, wsOut As Worksheet
Set ws = ThisWorkbook.Sheets(“Data”)
Set wsOut = ThisWorkbook.Sheets(“Out”)

‘ For loop to end
Dim i As Long
For i = 1 To 89625
If Not IsEmpty(ws.Cells(i, 1).Value) Then

wsOut.Cells(i, 1).Value = ws.Cells(i, 1).Value

End If
Next i

‘ Calculate Process Time
endTime = Timer
processTime = endTime – startTime

MsgBox “Data Copy Completed!” & vbNewLine & “Process Time: “ & processTime

End Sub

では、この処理を何秒使用するか実行していきましょう。

なんと、Excel がフリーズしてしまいました！

それなら、いったんデータの範囲を 1000 まで縮めましょう。

‘ For loop to end
Dim i As Long
For i = 1 To 1000
If Not IsEmpty(ws.Cells(i, 1).Value) Then
wsOut.Cells(i, 1).Value = ws.Cells(i, 1).Value
End If
Next i

結果は…

1000セルのデータ写しは9秒もかかりますね。

さて、配列はどうなるかみていきましょう。

配列処理のソースコードは少し違います。

配列はシートに使用している範囲をデータ配列に代入し、列の数が同じなので出力配列に同じ値を宣言していきます。

Ubound()を使用して最後のデータ数が取得できるので、ループはFor文で使っていきます。

‘ Init data and output array
Dim arrData, arrOut As Variant
arrData = ws.UsedRange.Value
ReDim arrOut(1 To UBound(arrData), 1)

また、確認のため、メッセージボックスの変更を忘れずに！

MsgBox “(Array) Data CopyCompleted!” & vbNewLine & “Process Time: “ & processTime

※シートに配列の値を直接配置する場合は、二次元以上の配列が必要になります。今回は二次元に空にしておきました。

〈全体ソースコード〉

Sub Test_OutArray()

‘ Init Timer
Dim startTime, endTime, processTime As Double
startTime = Timer

‘ Init data and output sheets
Dim ws, wsOut As Worksheet
Set ws = ThisWorkbook.Sheets(“Data”)
Set wsOut = ThisWorkbook.Sheets(“Out”)

‘ Init data and output array
Dim arrData, arrOut As Variant
arrData = ws.UsedRange.Value
ReDim arrOut(1 To UBound(arrData), 1)

‘ Loop until last data
Dim i As Long
For i = 1 To UBound(arrData)
arrOut(i, 0) = arrData(i, 1)
Next i

‘ Deploy output to range
wsOut.Range(“A1”).Resize(UBound(arrOut), 1).Value = arrOut

endTime = Timer
processTime = endTime – startTime

MsgBox “(Array) Data CopyCompleted!” & vbNewLine & “Process Time: “ & processTime

End Sub

「Data」シートのA列を配列で写す時間はこちらです。

いかがでしょうか？

全体データだけでなく、処理時間までもこんなに短縮できますね。

デバッグ機能

マクロを作成している時に、デバッグ機能を使っていませんか？Range() で処理を作成するときに、Excel側を開けなくて困ったりしていますか？

配列にはもう一つのメリットがあります。それはマクロ処理をメモリ上に動作させるため、ブレークポイントをかけて、Excelデータを見ながらソースコードの確認ができます。

おまけ：CSV出力

おまけに、ExcelのデータをフィルタリングしてCSVに出力する処理を紹介したいと思います。

今回の例は、データベースの中の「アプリID」、「リリース日」、「お勧め」のデータを使用して、無料ゲームのみフィルタリングして、CSVに出力していこうと思います。

CSVに出力するのに、以下2つ重要なポイントがあります。

１．各列の値はカンマ「,」で区切る

２．各行のデータは改行コードで区切る

ということで、各列のデータを追加するとき、その間に「 , 」を追加する必要があります。各行の区切りには、データを配列に差し込んだあと、Join( 配列データ, 改行コード ) を使用して文字列に変換できます。

〈行内のデータ〉

arrData(i, 1) & “,” & arrData(i, 3) & “,” & arrData(i, 21)

〈列のデータ〉

Join(arrOut, vbCrLf)

また、配列でマクロをさらに高速化するのに、Range.UsedRange の代わりにデータの範囲を宣言することが必要です。ソースコードに、最後の行（LR）と最後の列の文字（LC）を計算し、Range() 関数に参照していきます。

‘ Data array
Dim arrData As Variant
Dim LR As Long
Dim LC As String
LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
LC = Split(Columns(ws.Range(“A1”).End(xlToRight).Column).Address(, False), “:”)(1)
arrData = ws.Range(“A1:” & LC & LR).Value

ただし、フィルタリングすると出力しないデータを飛ばす必要があり、配列の中をきれいにするのに無用なデータを入れないことに注意しなければなりません。ここでは、空の配列を初期化して、対象なデータがある次第、配列のサイズを変更していきます。

※最初の行はちょっと特別なので、ループに入る前に代入する必要があります。

〈初期化と最初の行〉

‘ Output array
Dim arrOut As Variant
ReDim arrOut(0)

‘ First Row
arrOut(UBound(arrOut)) = arrData(1, 1) & “,” & arrData(1, 3) & “,” & arrData(1, 21)

〈配列のサイズを変更して対象データ入れ込み〉

If UBound(arrOut) < i – 1 Then
ReDim Preserve arrOut(0 To UBound(arrOut) + 1)
End If
arrOut(UBound(arrOut)) = steamLinkPrefix & arrData(i, 1) & “,” & arrData(i, 3) & “,” & arrData(i, 21)

全体ソースコードはこちらです。

〈全体ソースコード〉

Sub PrintFreeGameAndLink()
‘ Timer
Dim startTime, endTime, processTime As Double
startTime = Timer

‘ Data worksheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Data”)

‘ Steam website prefix
Dim steamLinkPrefix As String
steamLinkPrefix = “https://store.steampowered.com/app/”

‘ Output array
Dim arrOut As Variant
ReDim arrOut(0)

‘ First Row
arrOut(UBound(arrOut)) = arrData(1, 1) & “,” & arrData(1, 3) & “,” & arrData(1, 21)

‘ 1: appid
‘ 3: release_date
‘ 21: recommendations
Dim i As Long
For i = 1 To LR
If arrData(i, 5) = 0 Then
If UBound(arrOut) < i – 1 Then
ReDim Preserve arrOut(0 To UBound(arrOut) + 1)
End If
arrOut(UBound(arrOut)) = steamLinkPrefix & arrData(i, 1) & “,” & arrData(i, 3) & “,” & arrData(i, 21)
End If
Next i

‘ CSV string output text
Dim buf As String
buf = Join(arrOut, vbCrLf)

Dim objStream As Object
Set objStream = CreateObject(“ADODB.Stream”)
With objStream
.Charset = “UTF-8”
.Open
.WriteText Left(buf, Len(buf) – 1), 1
.SaveToFile ThisWorkbook.Path & “\Free Games.csv”, 2
.Close
End With

endTime = Timer
processTime = endTime – startTime

MsgBox “Print Free Steam Games Completed!” & vbNewLine & “Process Time: “ & processTime

End Sub

さて、処理時間とCSVファイルを見ていきましょう！

〈使用時間〉

〈CSV〉

最後に

マクロの処理に配列を使うことは確かに便利になりますが、間違いなく書き方のルールが時々手間になってしまうことがあります。しかし、処理速度の向上やコードの見通しの良さを考えると、慣れておく価値は十分にあります。特に、大量のデータを扱う場面では、セルを直接操作するよりも配列を使った方が圧倒的に効率的です。最初は戸惑うかもしれませんが、小さなサンプルから少しずつ試していくことで、確実にスキルアップできます。

ぜひ、日々のVBA開発の中で積極的に配列を活用してみてください。

使用サンプルデータ

https://www.kaggle.com/datasets

https://www.kaggle.com/datasets/artermiloff/steam-games-dataset?select=games_march2025_cleaned.csv

カテゴリー: Tech

タグ: ツールプログラム

ExcelVBA：配列でマクロの処理時間を高速化する方法の紹介