【実証済み!】VBA配列 これであなたも爆速【配列の基礎作り】

配列で10倍速く

【実証済み!】VBA配列の基礎作り方3つ紹介【簡単な配列紹介】

画像
配列って難しいな…

VBA配列について学びたいけど、理解するのが難しい…。エクセルの大量のデータを効率よく扱いたいけど、どうしたらいいのだろう。また、コードが長くて複雑になり、メンテナンスが難しい問題にも直面している。

VBA配列のを使う理由

配列は知っているけど、中身をもっと知りたい。使う理由は?理解を深めましょう。

配列とは
  • VBA配列の概念
  • 大量のエクセルデータを効率的に処理する方法を知る
  • コードが長くならないようにする配列を書く

この記事はこのような疑問と使う利点をお伝えします。

【具体的に学ぶ!】VBA配列を使ったコードの書き方

本記事では、以下の3つのテーマについて解説します。

配列とは
  • VBA配列を使った具体的なコードの書き方
  • VBA配列を使ってExcelの大量データを一度に処理する方法
  • VBA配列を使い、シンプルで読みやすいコードを作成するためのポイント

私は、VBAのプログラミングに10年以上の経験があります。この経験を活かして、皆さんがVBA配列を使ったコードの書き方を理解できるように、具体的なコードの例を示しながら説明していきます。

補足:これから紹介するコードを基に大量のデータを扱う場合、単純なFor文との処理速度の差は歴然になり、10倍かそれ以上の体感を見る事ができます。

注目

始めに配列を使う理由

大きなデータを取り扱うと避けて通れなくなる。配列を使う理由がこれ一択です。処理に2分も3分も待てない!現実はこのようになります。

なぜ配列なのかは単純です。配列を使わない場合、セルを読みいくのも書き込みにいくのも1回ずつセルにアクセスします。このアクセスが大量になると時間がかかるようになります。

メモリ上で読み書きして一気に吐き出す。これが配列です。アクセス回数をとにかく少なして処理速度を上げましょう。

VBA配列を使った具体的なコードの書き方

配列にデータを入れて作成するところからがスタートです。

配列にデータを入れて作成する方法は3つ

配列の作り方は≪3つ≫あります。(以下、部屋とは配列で使うメモリの数ことをいいます)

  • □一つ目:部屋を最初から用意しておく方法その⓵:連続する複数のセルを一度に取込み、配列を作る
  • □二つ目:部屋を最初から用意しておく方法その⓶:配列の宣言する時に部屋の個数を最初から決めておく
  • □三つ目:部屋をだんだん増やしていく方法その⓷:部屋数は最初ゼロ個から始まり、データが追加される度、部屋を1つずつ足していく
👉一つ目:部屋を最初から用意しておく方法その⓵:連続する複数のセルを一度に取込み、配列を作る

例えば次のコードが連続する複数のセルを一度に取込むコードです。F列からH列までを取込み2次元配列を作っています。

arr という名前の配列を定義し、データを取込んでいます。

Dim arr
arr = Range(Range("F2"), Cells(Rows.Count, 8).End(xlUp))

これは、Excelのシートは2次元(行と列)のデータ構造を持っているため、どこまで範囲を変えても2次元配列です。

👉二つ目:部屋を最初から用意しておく方法その⓶:配列の宣言する時に部屋の個数を最初から決めておく

一次元配列:

Dim arr(10)

二次元配列:

Dim arr(10, 20)

次元数は任意に増やせます。

👉三つ目:部屋をだんだん増やしていく方法その⓷:部屋数は最初ゼロ個から始まり、データが追加される度、部屋を1つずつ足していく
Dim arr() As long , num as long

On Error Resume Next
For Each i In range("A1:A100")
 If i <> empty Then
  num = UBound(arr) + 1
  ReDim Preserve arr(num)
  arr(num) = i
 End If
Next i

コードを上から順に説明

1.  Dim arr() As String, num as Long :配列 arr を長整数型の動的配列として、num を長整数型の変数として宣言します。

2.  On Error Resume Next :最初に num = UBound(arr) + 1 を通る時に、配列 arr がまだ初期化されていないため、最初の   UBound(arr) の呼び出しでエラーが発生します。エラーを無視させる為にOn Error Resume Next を使います。エラーは無視され、プログラムは実行を続けます。

⚠️On Error Resume Next は、以後の処理のエラーを無視し続けます。On Error Goto 0 を使ってエラー無視解除をケースバイケースで施すことをお勧めします。

3.  For Each i In range(“A1:A100”) :この行は、エクセルの範囲”A1:A100″の各セルに対するループを開始します。

4.  If i <> Empty Then :セルの値が空(Empty)でないかどうかをチェックします。空でない場合のみ以下の処理を実行します。

5.  num = UBound(arr) + 1 :配列 arr の現在の上限数(UBound)にプラス 1 をした値を num に代入しています。この num は、配列に新たに追加する要素の部屋の数(インデックス番号と言う)として使われます。

6.  ReDim Preserve arr(num) :配列 arr の部屋数を num で再設定します。 ここで、だんだん部屋数が増えていくのですが、ReDimは通常、配列内のデータを初期化させます。Preserve を使うことで、配列の既存のデータは維持されることになるのでここで使います。

7.  arr(num) = i :セル番地 i 内のデータを配列 arr の num 番目の部屋、に追加しています。

8.  Next i :次のセルに移動し、上記の処理を繰り返します。

このコードにより、範囲”A1:A100″の各セルの値が配列 arr に順に格納されます。ただし、If i <> Empty Then によりセルが空の場合は無視されることになります。

ChatGPTのプロンプトを考える
C

配列の中身を見る方法

配列に格納されたデータは、メモリの場所を示す番号(インデックスと呼びます)インデックス番号を使ってデータを呼び出すことができます。

VBAでは、配列のインデックス番号は通常0から始まることに注意してください。つまり、配列`arr`の最初の要素、インデックス番号は`arr(0)`、2つ目の要素は`arr(1)`になります。

具体的な配列内データの参照方法

一次元配列の場合、次のようにインデックスを指定します:

Dim arr(10)
arr(0) = "1次元参照"
Debug.Print arr(0) '出力結果:1次元参照

二次元配列の場合は、インデックスを2つ指定します:

Dim arr(10, 20)
arr(0, 0) = "2次元参照"
Debug.Print arr(0, 0) '出力結果:2次元参照

この例では、 Debug.Print を使って配列の内容をVBAエディタの即時ウィンドウに出力しています。 Debug.Print はVBAでのデバッグ作業に非常に役立ちます。ショートカット ctrl + G

また、配列のすべての要素を順に参照する場合は For ループを使用します。配列 arr のすべての要素を出力するには、以下のようにします:

Dim arr(10) As String
Dim i As Long

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i

この例では、 LBound 関数と UBound を使って配列の最小インデックスと最大インデックスを取得しています。これにより、配列の大きさが変わってもコードの変更が不要になります。

以上のように、配列の中身を呼び出すにはインデックスを使います。一次元配列なら1つ、二次元配列なら2つのインデックスを指定します。また、配列のすべての要素を参照するには For ループと組み合わせて使うことが一般的です。これらの手法を理解し、活用することでVBA配列の有効活用が可能になります。

通常は配列を呼び出して、他の処理します。他の処理はあなたのする処理をコーディングしてください。

配列を使った高速検索、応用編

それでは、下記に完成形の配列コードを見てください。配列を使い、難しいコードを最小限に抑えた上で処理速度の向上が、かなり見込めるコードに仕上げています。

Dim mh As Worksheet, sh As Worksheet
Dim arr, rng
Dim c As Range
Dim numRows As Long, numCols As Long
Dim i As Long, gyom As Long, gyos as long

Set mh = Sheets("Sheet1")
Set sh = Sheets("Sheet2")

arr = mh.Range(mh.Range("F2"), mh.Cells(Rows.Count, 8).End(xlUp)) 

gyom = mh.Cells(Rows.Count, 6).End(xlUp).Row
gyos = sh.Cells(Rows.Count, 1).End(xlUp).Row

With sh.UsedRange.Columns(1)

     For i = LBound(arr) To UBound(arr)  
        Set c = .Find(What:=arr(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
         If Not c Is Nothing Then 
          arr(i, 3) = arr(i, 3) + c.Offset(0, 223) '例えば合ったら足し算とか(例)
         End If    
     Next i

End With

' arrのサイズを取得
numRows = UBound(arr, 1)
numCols = UBound(arr, 2)

' F2セルから始まる範囲を指定
Set rng = mh.Range("F2").Resize(numRows, numCols)

' arrをシートに貼り付ける
rng.Value = arr

‘リセットコード


Set c = Sheets(1).Range(“A1″).Find(What:=””, LookIn:=xlValues, LookAt:=xlPart)

上記コードの趣旨

長年VBAに携わってきた私が説明します。処理速度を上げるコーディング技術も高度になるとDictionaryオブジェクトがあります。そちらは入力から出力まで一貫して処理速度を上げる要素が完璧に入っていて処理速度の最高峰。ですが難易度高くて扱いづらいのです。多くのシステムを量産したり、コーディングの速さ、後のメンテナンスも考えたら、Dictionaryは見送り、頭にスッと入って流れが掴みやすくコスパの良い上記のコーディングとして上記のコードを紹介をしました。

Findメソッドの趣旨

Findメソッドを使う理由は、コードをステップイン(F8)で一つずつ実行するとした場合 、F8 キーをトータル何回押すのかカウントした時に、Findメソッドは1回で探すので、一般的によく使われるFor文のネストのFor文で探すより圧倒的に早いと考えるからです。
Findメソッドについては、参考書やネットにもあまり紹介されていないようですが、私が企業でたくさんのシステムを構築し、一致処理のほとんどをFindメソッドでコーデイングしている実績がありますので、紹介しています。

リセットコードの趣旨

リセットコード Set c = Sheets(1).Range(“A1″).Find(What:=””, LookIn:=xlValues, LookAt:=xlPart) は、プログラムを走らせた時に実際には何も探さない検索操作を行いつつ、Excelの [Ctrl] + [F] に当たる検索オプションを「部分一致」にリセットするためのコードです。これにより、次に手動で[Ctrl] + [F]を押して検索を行うとき、検索オプションが「全体一致」から「部分一致」にリセットされ、ユーザーが予期しない結果に遭遇するのを防ぎます。手動検索とマクロでの検索が同期しているため、このようなリセット操作が必要となる場合があります。

なお、上記でのFindメソッドコード自体は特に探し出すべき情報を指定していないため、検索自体は特に何も見つけません。つまり、このコードは単に検索設定をリセットするためだけに存在している、と理解していただければと思います。

▼上記のコードを分解した説明▼

シートのF列に商品No.があるモデルとして話します。

①先ず、一回でF列からH列までを配列に入れて2次元配列を作ります。
 arr = my.Range(mh.Range("F2"), Cells(Rows.Count, 8).End(xlUp))

「配列を使うことで処理速度を上げる」という部分については、Excelのシート上で直接操作を行うよりも、VBAの配列内で操作を行った方が高速に処理が可能であるという事実を指しています。Excelシートへのアクセスは比較的時間がかかるため、まとめてデータを配列に読み込み、処理を行い、その結果を再度シートに書き出すという流れが高速に動作させることができます。

この処理を実現するために arr の配列を作り、Forループの中で配列内にデータを蓄積していく処理になります。

②For文を使い、処理速度を上げる為に配列を使います。
 For i = LBound(arr) To UBound(arr)

この For i = LBound(arr) To UBound(arr) ループは、「配列 arr の最初の要素から最後の要素まで、順番に一つずつ取り出して処理する」という操作を行います。For文で、データの入出力を直接セルにアクセスしません。

③For文内の処理は、プログラムが動く回数を最小限にするためにFindメソッドを使う。

For文内で一致検索を行う際に、行数を最小限に抑え、プログラムが各行を読み込み一致を探す処理の回数を最小限にするため、Findメソッドを使用します。具体的には、このコードをステップイン(F8キー)で一つずつ実行する場合を想像してみてください。単純なFor文とFor文を用いて一致を調べる場合と、Findメソッドを使用した場合とで、F8キーを押す回数は大きく異なるはずです。Findメソッドを使用すると、より少ない回数のステップインで同じ結果を得ることができます。この効率化のために Set c = .Find(What:=arr(i, 1), LookIn:=xlValues, LookAt:=xlWhole) を使用しています。

Dim  sh As Worksheet
Set sh = Sheets("Sheet2")

With sh.UsedRange.Columns(1)
     For i = LBound(arr) To UBound(arr)  
        Set c = .Find(What:=arr(i, 1), LookIn:=xlValues, LookAt:=xlWhole) 
         If Not c Is Nothing Then 
          arr(i, 3) = ""
         End If    
     Next i
End With
④上記のコードでarr(i, 3)にデータが蓄積されたら、arrのサイズを取得します

このコードで配列のサイズを変数に入れます

numRows = UBound(arr, 1)
numCols = UBound(arr, 2)
⑤貼り付ける範囲と起点のセル番地を指定する

F2セルから始まる範囲を指定します。rng にその範囲の情報を代入します。

Set rng = Range("F2").Resize(numRows, numCols)
⑥arrをシートに貼り付ける

範囲指定した rng に配列 arr を貼り付ける。

rng.Value = arr
⑦コードの最後にFindメソッドのリセットを忘れず入れましょう。

プログラム内でFindメソッドを用いて完全一致で検索を行うと、その設定は手動の[Ctrl] + [F]の検索にも引き継がれます。その結果、次に手動で検索を行うときも「完全一致」の設定が適用される状態になります。これを避けるために、プログラムの最後にはFindメソッドの設定をリセットするコードを必ず含めるようにしましょう。このリセットは「部分一致」の検索を行うことで達成できます。下記のリセットコードを参照ください:

リセットコード

 
Set c = Sheets(1).Range(“A1″).Find(What:=””, LookIn:=xlValues, LookAt:=xlPart)

重要なのは、挑戦する意志と継続する心だけです。

VBAの配列を使用する際の注意点

  1. 配列だけではありませんが、自身があまり理解しにくい難易度の高すぎるコーディングは避けましょう。時間が経ってメンテナンスするときに解読するのに手間取りますので気を付けましょう。

  2. のちに、他の人が自身のコードを触る可能性はゼロではない環境であるなら、記述の仕方も分かりやすく書くことを意識しましょう。例えば、変数が何に使われていか読めばなんとなく分かるような文字列である等。

  3. 配列を使うことは、高度なコーディング技術になってきます。出力確認も通常と違ってしにくいので、正確性をきっちり確認することを忘れずに。

これらのポイントを心に留めて、これからもVBAの学習を進めていきましょう。

おまけ

⏬上記で説明したコードをDictionaryを使ったコーディングにしたら下記のようになります⏬

Dim mh As Worksheet, sh As Worksheet
Dim arr, arr2D, rng
Dim numRows As Long, numCols As Long
Dim i As Long
Dim keyValue As Variant
Dim Dict As Object

Set mh = Sheets("Sheet1")
Set sh = Sheets("Sheet2")

arr = mh.Range(mh.Range("F2"), mh.Cells(Rows.Count, 8).End(xlUp)) 

gyom = mh.Cells(Rows.Count, 6).End(xlUp).Row
gyos = sh.Cells(Rows.Count, 1).End(xlUp).Row

' Create dictionary
'このコードにより、Dict 変数にディクショナリオブジェクトが格納され、その後の処理でキーと値のペアを追加したり、検索を行ったりすることができます。
Set Dict = CreateObject("Scripting.Dictionary") 'オブジェクトのインスタンスを生成し、Dict 変数に代入しています。


For i = 2 To gyos
    keyValue = sh.Cells(i, 1).Value '検証ツールシートの1列目のセルの値をkeyValue に代入します。
    If Not Dict.Exists(keyValue) Then 'ディクショナリに keyValue が存在しないことを確認し、存在しない場合にのみ次の処理が実行されます。
        Dict.Add keyValue, sh.Cells(i, 224).Value 'ディクショナリにキーとして keyValue と、値として sh.Cells(i, 224).Value をペアにして追加します。Dictに入れている。
    End If
Next i

For i = LBound(arr) To UBound(arr)
    keyValue = arr(i, 1)
    If Dict.Exists(keyValue) Then 'ディクショナリ(Dict)内に keyValue と一致するキーが存在するかどうかを確認しています。
        arr(i, 3) = arr(i, 3) + Dict(keyValue) 'arr 配列の i 番目の行の3列目の値に、ディクショナリの対応するアイテム(値)を加算しています。
    End If
Next i

' arrのサイズを取得
numRows = UBound(arr, 1)
numCols = UBound(arr, 2)

' F2セルから始まる範囲を指定
'F2 セルを起点とする範囲を設定し、その範囲のサイズを numRows(行数)と numCols(列数)にリサイズして、rng 変数に代入しています。
Set rng = mh.Range("F2").Resize(numRows, numCols)

' arrをシートに貼り付ける
rng.Value = arr '配列の内容を一括で rng の範囲に書き込んでいます。

いかかでしょうか、ここまでであなたのプログラミングの参考になる事を願っています。

関連記事:テックジムでプログラミング学習の新しい方法を体験しよう!

Pythonのこともっと深く知りたい、習得したいともう方はこんな近道もあります。チェックしてみましょう。

テックジムへ

この記事にはアフィリエイトリンクが含まれています。クリックして商品を購入すると、当サイトは一部の収益を得る可能性があります。

Copy Protected by Tech Tips's CopyProtect Wordpress Blogs.