VBAで自動化 VBA一覧 配列 配列・データ操作

【VBA】範囲指定のセルの値を配列に格納する方法｜処理速度と保守性を両立する実務設計

Excel VBAで大量のデータを扱うとき、「セルを1つずつ読み取る処理が遅い」「処理時間が長くなってしまう」「ループ処理が重い」と感じたことはないでしょうか。特に、数千行や数万行のデータを扱う業務では、処理速度が業務効率に直結します。こうした場面で非常に効果的なのが、範囲指定したセルの値を配列にまとめて格納する方法です。

一見すると少し難しそうに感じるかもしれませんが、実務ではこの考え方が「高速化」「安定化」「保守性向上」のすべてに関わってきます。この記事では、範囲指定したセルの値を配列に格納する基本から、実務で壊れにくい設計、注意点、応用例まで、実務目線で丁寧に解説します。

✅ VBAで範囲指定のセルの値を配列に格納する基本

配列を使ったデータ処理は、VBAの中でも非常に重要な技術です。しかし、最初は「なぜ配列にする必要があるのか」「普通にループすればいいのではないか」と感じることも多いでしょう。実際、小さなデータであればセルを1つずつ処理しても問題ありません。ただし、データ件数が増えてくると、セルに何度もアクセスする処理は大きな負担になります。特に、毎日実行するマクロや定期処理では、数秒の差が積み重なって業務時間に影響することもあります。最初から配列を使う必要はありませんが、「データ量が増えたときに備えておく」ことが重要です。この章では、まず基本となる配列格納の考え方を整理していきましょう。

・範囲指定したセルを配列へ格納する基本コード

まずは、最も基本となるコードを確認します。

Sub StoreRangeValuesToArray()

    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim dataArray As Variant

    '対象となるシートを明確に指定する
    Set targetSheet = ThisWorkbook.Worksheets("入力")

    'A列を基準に最終行を取得する
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    'A2:C最終行の範囲を配列にまとめて格納する
    dataArray = targetSheet.Range("A2:C" & lastRow).Value

    MsgBox "配列へデータを格納しました"

End Sub

このコードでは、A2からC列の最終行までのデータを一度に配列へ格納しています。

ここで重要なのは、次の1行です。

dataArray = targetSheet.Range("A2:C" & lastRow).Value

この1行で、複数のセルの値を一括で取得し、配列として保持しています。

・なぜ配列を使うと処理が速くなるのか

処理速度が改善される理由は、シートへのアクセス回数が減るからです。

たとえば、次のような処理を考えてみましょう。

For currentRow = 2 To lastRow
    value = targetSheet.Cells(currentRow, "A").Value
Next currentRow

この処理では、ループのたびにExcelシートへアクセスしています。

一方、配列を使うと次のようになります。

dataArray = targetSheet.Range("A2:C" & lastRow).Value

この時点で、すべてのデータがメモリ上に読み込まれます。
その後の処理は、シートではなくメモリ上のデータを扱うため、非常に高速になります。

・Variant型を使う理由

配列を扱うときは、通常次のように宣言します。

Dim dataArray As Variant

これは、Rangeから取得した値が2次元配列として格納されるためです。

また、次のようなメリットもあります。

  • 数値と文字列が混在しても対応できる
  • 空白セルがあってもエラーにならない
  • 柔軟にデータを扱える

実務ではデータ形式が完全に統一されているとは限らないため、
Variant型が最も安全で実用的です。

範囲指定したセルの値を配列へ格納する際には、柔軟にデータを扱えるVariant型がよく使われます。ただし、便利な一方で処理速度やメモリ使用量に影響することもあるため、使いどころを理解しておくことが重要です。Variant型の特徴や注意点を整理しておきたい場合は、「【VBA】Variant型とは？便利だが危険な理由と使いどころ｜実務で失敗しないための徹底ガイド」もあわせて確認しておくと安心です。

✅ 配列に格納したデータをループで処理する方法

配列へデータを格納しただけでは意味がありません。実務では、そのデータを1件ずつ確認したり、条件判定を行ったり、別シートへ転記したりする処理が必要になります。ここで大切なのは、配列の構造を正しく理解することです。Rangeから取得した配列は「2次元配列」になります。つまり、行と列の両方を持っています。この仕組みを理解していないと、「なぜ1から始まるのか」「なぜ0ではないのか」といった疑問が生まれます。また、配列の範囲を固定してしまうと、データ件数が変わったときに不具合が起きることがあります。そのため、配列のサイズを動的に取得する方法を押さえておくことが重要です。

・配列データを順番に処理するコード例

Sub ProcessArrayData()

    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim dataArray As Variant
    Dim rowIndex As Long

    Set targetSheet = ThisWorkbook.Worksheets("入力")

    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    dataArray = targetSheet.Range("A2:C" & lastRow).Value

    For rowIndex = 1 To UBound(dataArray, 1)

        Debug.Print dataArray(rowIndex, 1)

    Next rowIndex

End Sub

・UBoundを使う理由（実務では必須）

この部分が非常に重要です。

UBound(dataArray, 1)

これは、配列の最大行数を取得するためのものです。

もし次のように書いた場合：

For rowIndex = 1 To 100

データが増減したときに不具合が起きます。

一方、UBoundを使えば：

  • データが増えても対応できる
  • データが減っても対応できる
  • 修正が不要になる

つまり、壊れにくいコードになります。

配列をループで処理するときは、データ件数が変わっても正しく動作するように、配列の範囲を自動で取得することが重要になります。その際によく使われるのがLBound関数やUBound関数です。配列の開始位置や終了位置を安全に取得する基本を整理しておきたい場合は、「【VBA】ExcelVBAのLBound関数・UBound関数を使用する配列の基本」もあわせて確認しておくと、配列処理の安定性が大きく向上します。

✅ 配列を使う設計にするべきタイミング

配列は非常に便利ですが、すべての処理で使う必要はありません。小さなデータや単純な処理であれば、通常のループでも十分です。むしろ、無理に配列を使うことでコードが複雑になることもあります。重要なのは、「いつ配列を使うべきか」を判断できることです。この判断ができると、処理速度と保守性のバランスを取ることができます。また、最初から配列を使うのではなく、「遅くなったら配列化する」という段階的な改善も現実的です。ここでは、実務での判断基準を整理しておきましょう。

・配列を使うべき典型的な場面

次のようなケースでは、配列が非常に効果的です。

  • 数千行以上のデータを扱う
  • 同じデータを何度も参照する
  • 条件判定を大量に行う
  • 処理速度が問題になっている
  • 定期処理（毎日・毎月）で使う

・配列を使わなくてもよいケース

逆に、次のようなケースでは配列は不要です。

  • 数十行程度のデータ
  • 単純な1回処理
  • 処理速度が問題になっていない
  • 可読性を最優先したい

つまり、

配列は「必要なときに使う」技術

です。

✅ 配列を使った別シートへの一括転記

配列の最大のメリットの1つが、「一括出力」です。通常のループ処理では、1セルずつ書き込みを行います。しかし、この方法では処理回数が増えるため、データ件数が多いと時間がかかります。一方、配列を使えば、データをまとめて出力できます。これは、実務で非常に大きな効果があります。特に、日次処理や定期レポート作成では、処理時間の短縮がそのまま業務効率につながります。また、一括転記はコードもシンプルになります。処理の意図が明確になり、保守性も向上します。

・配列を使って一括出力するコード

Sub OutputArrayToAnotherSheet()

    Dim sourceSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim lastRow As Long
    Dim dataArray As Variant

    Set sourceSheet = ThisWorkbook.Worksheets("入力")
    Set outputSheet = ThisWorkbook.Worksheets("出力")

    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

    dataArray = sourceSheet.Range("A2:C" & lastRow).Value

    outputSheet.Range("A2").Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray

End Sub

・Resizeを使う理由

この部分が重要です。

Resize(UBound(dataArray, 1), UBound(dataArray, 2))

これは：

  • 行数を配列サイズに合わせる
  • 列数を配列サイズに合わせる

ための処理です。

つまり、

配列サイズに応じて出力範囲を自動調整

しています。

配列を使った一括転記では、出力するデータ件数に応じてセル範囲を自動で調整することが重要になります。その際によく使われるのが.Resizeメソッドです。行数や列数に合わせて出力範囲を柔軟に変更する仕組みを正しく理解しておきたい場合は、「【VBA】Excel VBAで.Resizeメソッドを活用する方法を徹底解説」もあわせて確認しておくと、配列処理の応用力が大きく向上します。

✅ 配列処理でよくある失敗と対策

配列は便利ですが、慣れていないとエラーが発生しやすい部分でもあります。特に、空白データや最終行の取得ミス、配列のサイズ指定ミスなどは、初心者だけでなく経験者でも起きることがあります。また、エラーが出ないまま処理結果が間違っているケースもあります。これが最も危険です。そのため、配列処理では「前提条件」を明確にしておくことが重要です。たとえば、データが必ず1行以上あるのか、見出し行が存在するのかなどです。こうした条件を確認しておくことで、トラブルを防ぐことができます。

・データが存在しない場合の対策

If lastRow < 2 Then
    MsgBox "データが存在しません"
    Exit Sub
End If

このチェックは非常に重要です。

なぜなら：

  • 空配列になる
  • エラーになる
  • 処理結果が不正になる

可能性があるからです。

・配列処理で最も多い設計ミス

最も多いミスは：

固定値でループすること

です。

For i = 1 To 100

ではなく、

For i = 1 To UBound(dataArray, 1)

にすることが重要です。

これは：

将来の変更に強いコード

になります。

 

✅ まとめ：配列格納はVBA高速化の基本技術

範囲指定したセルの値を配列に格納する技術は、VBA自動化の中でも非常に重要な基本技術です。処理速度を改善するだけでなく、コードの構造をシンプルにし、保守性を高める効果があります。最初は少し難しく感じるかもしれませんが、一度理解すると多くの業務で応用できます。

  • 範囲指定したセルは一括で配列に格納できる
  • 配列を使うと処理速度が大幅に改善する
  • UBoundを使うと壊れにくいコードになる
  • 配列は大量データ処理に非常に有効
  • 一括転記で処理を高速化できる
  • 配列は必要な場面で使うことが重要

まずは、小さなデータでも一度配列を使ってみてください。
その経験が、将来の業務自動化や処理速度改善に大きく役立ちます。

    -VBAで自動化, VBA一覧, 配列, 配列・データ操作