マクロ講座VBA 列を選んで転記することで連想配列と比較する

VBA 列を選んで転記する3つの方法の詳しい解説

マクロ 97回

連想配列の凄さを確認するために列を選んで転記する方法を比較する

VBAで列を自由に選んで転記する3通りの方法を紹介します。 目的は、連想配列（Dictionary）の使い良さを知ってほしいからです。 Dictionaryがなくてもマクロは作れますし、Excelシートの操作はできるのですが、 配列の次はぜひDictionaryをマスターしてほしいと思います。 そこでDictionaryとの違いを分かってほしいということで、今回の動画を作成しサイトで解説することにしました。

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

列を自由に選んで別シートに転記するケース、普通、配列、連想配列（Dictionary）どれが一番使いやすいか　マクロ中級

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

【VBA列を選んで転記】列を自由に選んで別シートに転記するケース、普通、配列、連想配列（Dictionary）どれが一番使いやすいか　マクロ中級
以下はコードの解説となります。

この記事で学べる内容

• VBAの配列とDictionaryの違いや使い方がわかる
• 列を選んで別シートに転記する方法の比較を学ぶ
• VBAコードの効率的な書き方や変数の使い方がわかる
• 通常の方法、配列、連想配列（Dictionary）を使った転記処理の速度比較を学ぶ
• VBAコードを読んで理解する力を向上する

列を選んで別シートに転記する

配列や連想配列を使わないで列を選んで転記する通常の方法と配列を使った場合、連想配列を使った場合の速度を最初に検証してみます。

1. Excelのシートの元データは件数550件列数は10列あります。そして転記先のシートには転記すべき列5列の見出しだけが入力されています。

   

2. 通常の方法で転記した場合の速度は、実行時間0.266となりました。

   

3. 処理にかかった時間は別のマクロ”実行時間”を用意して計測しました。

   

4. 次は、配列を使った転記処理のコードです。こちらは0.023秒と言う通常の転記処理に比べて1/10の速さとなりました。

   

5. 最後に連想配列を使用した転記処理のコードの実行速度です。連想配列を使用した場合は0.188秒となりました。実効速度は普通の転記処理と比べて若干早いという程度にとどまりました。

   

6. 今回の動画内にて、処理時間の計測に使用したマクロコードを掲載しておきます。

  Sub 処理実行時間()
  Dim startTime As Double, endTime As Double
  startTime = Timer
  Call （計測したいマクロ名）
  endTime = Timer
  Debug.Print "実行時間: " & _
          Format(endTime - startTime, "0.000") & " 秒"
End Sub

列を選んで別シートに転記する通常の方法

6. では通常の方法で記述した列を選んで別シートに転記するマクロコードの説明をします。

   まず、コードを書く時は、作成するコードを書く方針が必要です。 最初にどんな変数が必要か、などということを決めるわけです。 しっかりマクロを作成する手順を考えておくことでミスも少なくなります。

   

7. 変数が沢山ありますが、それは元データの変数と転記先の方の変数があるので、多いのは当然です。

   

8.   Dim srcWS As Worksheet, destWS As Worksheet
     Dim srcHeader As Range, destHeader As Range
   

   まず最初に ソースシートのWorksheets("データ")と ターゲットシートのWorksheets("転記")を変数で区別しています。
   それが、Dim srcWS As Worksheet, destWS As Worksheet というコードです。

   SRCはよく使われますけれど ソースという意味です。 だから今回は source と destination ディスティネーションは目的地。 ターゲットとかも言いますけど ターゲットよりもディスティネーションの方が 一般的なようです。

   そして次のコード、
   Dim srcHeader As Range, destHeader As Range は
   ソースシートの見出し行であり、ターゲットシートの見出し行ということになります。

   

9. 次は”srcLastRow”ソースシートの最終行で、”srcLastCo”は、最終列、そして”destLastCol ”はターゲットシートの最終列を変数にしています。

     Dim srcLastRow As Long, srcLastCol As Long, destLastCol As Long
     Dim srcCol As Long, destCol As Long
     Dim i As Long, j As Long
   

   ”srcCol”と”destCol”は、それぞれ1つずつの列という意味で使用しています。
   そして”i”と”j”はカウント用の変数です。

       Set srcWS = ThisWorkbook.Worksheets("データ")
       Set destWS = ThisWorkbook.Worksheets("転記")
       destWS.Range("A2:E" & destWS.Rows.Count).Clear
   

10. さらに変数を宣言しただけでは、まだどれがその対象かは決まっていません。Setステートメントで、対象を指定します。
    また以下のコードは、すでに入力されていたデータを消去しています。
    destWS.Range("A2:E" & destWS.Rows.Count).Clear
    

    

11. またソースの見出し行とターゲットの見出し行が何行目かもExcelに伝える必要があります。

        Set srcHeader = srcWS.Rows(1)
        Set destHeader = destWS.Rows(1)
    

    

いよいよ操作のコード

12. プログラムを書くための準備として、設定のためのコードが以上です。
    ここからが実際にプログラムとしてのコードになります。ソースシートの最終行”srcLastRow”と最終列”srcLastCol”を取得するコードです。

        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
      

    

13. For文を使って、１つのカラムごとにチェックします。

    

14. If文を使って、見出しの値が同じセルを見つけたら、データを転記します。

    

    If srcHeader.Cells(i).Value = destHeader.Cells(destCol).Value Then
      srcCol = i
    ' 一致する列が見つかった場合、データを転記
      For j = 2 To srcLastRow
          destWS.Cells(j, destCol).Value = srcWS.Cells(j, srcCol).Value
      Next j
    Exit For ' 一致するセルが見つかったら、次のdestColに移動

15. 一見難しそうなコードですけれども、最初のFor文は、
    For destCol = 1 To destLastCol
    外側のFor文はターゲットシートの見出し行の列5つを1つずつ回すためのFor文です。
    2番目のFor文は、
    For i = 1 To srcLastCol
    ソースシートの見出し行のカラムを１つずつチェックしていくためのFor文です。

16. 最後のFor文は、見つかったカラムのデータを転記するためのFor文です。

    For j = 2 To srcLastRow
      destWS.Cells(j, destCol).Value = srcWS.Cells(j, srcCol).Value
    Next j
      

    

列を選んで別シートに転記するコード

  Sub NormalTranspose()
  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 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
  
  ' ソースシートの見出しと一致する列を見つけ、各列を転記
  For destCol = 1 To destLastCol
      srcCol = 0
      For i = 1 To srcLastCol
          If srcHeader.Cells(i).Value = destHeader.Cells(destCol).Value Then
              srcCol = i
              ' 一致する列が見つかった場合、データを転記
              For j = 2 To srcLastRow
                  destWS.Cells(j, destCol).Value = srcWS.Cells(j, srcCol).Value
              Next j
              Exit For ' 一致するセルが見つかったら、次のdestColに移動
          End If
      Next i
  Next destCol
End Sub  

17. コードの長さに圧倒されるかもしれませんが、コードのほぼ半分は準備のためです。
    難しそうなコードも、どれが変数で、何を表しているのかを理解すれば、すんなりと理解できるようになります。

18. 理解しにくい部分はぜひ動画で確認してください。