VBA配列処理の速さの理由 コードを説明(配列を使った場合の転記の コード)動画と記事解説

【VBA配列速さの理由】列を自由に選んで別シートに転記するケース

VBA配列速さの理由(配列を使った場合の転記の コード編)
VBA配列速さの理由

転記における配列のコードと普通のコードを比較することで明らかになる

配列のコードと普通のコードを比較

前の記事では、列を選んで別シートに転記するコードの通常のコードを解説したのですが、続きのコードで配列を使った場合の記事解説です。
今回の解説では、配列を使った転記処理のコードについて説明します。
こちらは0.023秒と言う通常の転記処理に比べて1/10以下の速さとなりました。
普通のコードと配列をつかったコードに、それほど違いは見当たらないが、どうして早いのかを、ステップ実行しながら解説しています。
結論から言えば、配列が早いのは、すでにメモリ上にデータを取得しているからです。

練習ファイルにはマクロは記述されていません。(練習ファイルは、こちらから マクロ98回練習ファイル

静止画像とテキストでは、理解しづらい事もありますので、その時は下記動画をご覧になり参考にしてください。

この記事と動画で学べる内容

  • 3つのデータ転記方法の紹介:通常の方法、配列を使用する方法、連想配列や辞書を使用する方法
  • マクロ作成時の準備と手順の重要性
  • 通常のコードと配列を使用するコードの比較
  • 配列を使用する方法の速度が速く、効率的であること
  • メモリ内で処理が行われるため配列使用が高速であること
  • 配列のサイズをあらかじめ決めてメモリ内に場所を確保する手法
  • 配列ベースの転記方法で瞬時にデータが挿入される様子
  • 配列を理解するためのデバッグ方法として、F8キーやF5キーを使用し、ウォッチウィンドウやローカルウィンドウを確認する

マクロ作成時の準備と手順の重要性

  1. 通常の転記の方法も配列を使った転記の方法も初期設定(コードを記述するための準備)は同じです。必要な変数は、配列を宣言した変数以外同じです。

    VBA配列速さの理由、変数の設定は普通のコードとほぼ同じ
  2. こちらは通常の列を選んで転記する場合のコードです。変数やセットはほとんどかわりません。

    VBA配列速さの理由、タイトル01
  3. こちらは、配列を使って転記する場合のコードです。
    配列を使うので、データを配列に格納するための変数を宣言しています。違いはこの程度。

    VBA配列速さの理由、タイトル02

変数と設定までのコード

Sub ArrayTranspose()
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim srcHeader As Range, destHeader As Range
    Dim srcLastRow As Long, srcLastCol As Long, destLastCol As Long
    Dim srcData As Variant, destData As Variant
    Dim srcCol As Long, destCol As Long
    Dim i As Long, j As Long

    ' ソースシートとターゲットシートを設定
    Set srcWS = ThisWorkbook.Worksheets("データ")
    Set destWS = ThisWorkbook.Worksheets("転記")
    destWS.Range("A2:E" & destWS.Rows.Count).Clear
    ' ソースシートとターゲットシートの見出し行を設定
    Set srcHeader = srcWS.Rows(1)
    Set destHeader = destWS.Rows(1)

    ' ソースシートの最終行と最終列を取得,ターゲットシート最終列
    srcLastRow = srcWS.Cells(srcWS.Rows.Count, 1).End(xlUp).Row
    srcLastCol = srcWS.Cells(1, srcWS.Columns.Count).End(xlToLeft).Column
    destLastCol = destWS.Cells(1, destWS.Columns.Count).End(xlToLeft).Column

処理のコードの違い

  1. 見出し行を除外したソースシートのデータを配列に格納するコードです。たった1行でデータは取得できます。

    VBA配列速さの理由、タイトル03
  2.  srcData = srcWS.Range(srcWS.Cells(2, 1), srcWS.Cells(srcLastRow, srcLastCol)).Value
  3. ソースシートの最終行とターゲットシートの最終カラムが取得できたので、 ターゲットの配列 ”destData”もサイズが決まります。

    VBA配列速さの理由、タイトル04
  4. ReDim destData(1 To srcLastRow - 1, 1 To destLastCol)
  5. 外側のFor文では、カラム1から5まで見ていくということになります。
    For destCol = 1 To destHeader.Cells.Count
    転記先シートの列を左から右へ1つずつ見ていきます。 このとき、 ソースシートのカラムは、srcCol = 0 となります。どれが同じか、まだ見つかっていないからです。

    VBA配列速さの理由、タイトル05

    そして、内側のFor文では、データシートの見出し行で、転記先シートの見出しと一致する列を探します。
    For i = 1 To srcHeader.Cells.Countループを使って行われています。
    見出しのセルを1から10まで、同じ値の入った見出しを探すためにチェックしてループします。
    見つかった列の番号は、srcColという変数に格納されます。

  6. 同じ値の見出しセルが見つかったら、データを配列”destData”にいれています。

    VBA配列速さの理由、タイトル06
  7. destData(j, destCol) = srcData(j, srcCol)
  8. 一つの列データが移動したら、Next destCol 転記先シートの次の列に移動して、同じ手順を繰り返します。
    最後にデータの転記をします。

        ' 配列からターゲットシートにデータを書き込む
        destWS.Range(destWS.Cells(2, 1), _
                destWS.Cells(srcLastRow, destLastCol)).Value = destData
    
    VBA配列速さの理由、タイトル07
  9. 操作の部分のコード最後まで

       ' ソースシートのデータを配列に格納(見出し行を除く)
        srcData = srcWS.Range(srcWS.Cells(2, 1), _
                                            srcWS.Cells(srcLastRow, srcLastCol)).Value
        ' ターゲットシート用の配列を準備
        ReDim destData(1 To srcLastRow - 1, 1 To destLastCol)
        ' 各列を取得
        For destCol = 1 To destLastCol
            ' ソースシートの見出しと一致する列を見つける
            srcCol = 0
            For i = 1 To srcLastCol
                If srcHeader.Cells(i).Value = destHeader.Cells(destCol).Value Then
                    srcCol = i
                    ' 一致する列が見つかった場合、データをdestColに取得する
                    For j = 1 To UBound(srcData, 1)
                        destData(j, destCol) = srcData(j, srcCol)
                    Next j
                    Exit For ' 一致するセルが見つかったら、次のdestColに移動
                End If
            Next i
        Next destCol
    
        ' 配列からターゲットシートにデータを書き込む
        destWS.Range(destWS.Cells(2, 1), _
                destWS.Cells(srcLastRow, destLastCol)).Value = destData
    End Sub
  10. 元データから列を選んで配列を利用して転記するコードは、順番にコードの意味を見ていくと、それほど難しい物ではないということが分っていただけると思います。
    次回は、連想配列Dictionaryを使って転記をしてみましょう。

    VBA配列速さの理由、タイトル08