なぜ配列をつかうのか、爆速で終わらせる配列コード編 106回

爆速で終わらせる配列の転記について

マクロ講座 動画編 106回後編
マクロ講座

動画でExcel なぜ配列をつかうのか、爆速で終わらせる配列コード編つづき

爆速で転記、データを配列で取得して別ブックのシートに転記する

動画版「マクロ講座106回後編」です。
前回は配列が100倍速く処理をするということをタイマーを使って実証し、そのコードを解説しました。
今回は、その配列を使って、取得したデータを別シートや別ブックに転記する例です。

爆速で転記、データを配列で取得して別ブックのシートに転記する

配列で元データを取得する

  1. 配列で取得するデータの範囲です。入力
    しているシートは Sh_data、転機するシートはSh_otherという 名前をつけています。
    そして 別ブックは、”別ブック.xlsx” という名前を付けてい ます。

    NAMAE1
  2. 今回使う変数をあらかじめ設定して宣言、セクションに記載しておきます。
    配列として取得するデータを入れる変数は、myArrayという名前にして、Variant型で宣言します。

    NAMAE2
  3. 行数と列数を取得するために、MaxRow、MaxColを用意しました。
    Dim myArray, sh As Worksheet
    Dim MaxRow As Long
    Dim MaxCol As Long

    NAMAE3
  4. 一番簡単な範囲の取得の仕方は、CurrentRegionです。 それで、CurrentRegionでコードを書いていき ましょう。
    これでもう配列を取得できます。

    NAMAE4
    Public Sub 配列で取得するCurrentRegion()
        Set sh = Worksheets("sh_data")
        myArray = sh.Range("A1").CurrentRegion
    End Sub
  5. マクロを実行して、配列がどのように取得されたのかを見てみましょう。
    これがウォッチウィンドーに表示された配列の様子です。

    NAMAE5

別シートを設定する

  1. では、データを取得できたので、次にsheetに転記しましょう。 Dim sh_other As Worksheet
    Set sh_other = Worksheets("sh_other")

    NAMAE6
  2. アクティブなシート以外に転記する場合は、しっかりとその親オブジェクトから上下関係を指定する必要があります。住所と同じです。

    NAMAE7
  3. 別シートの転記先は、Rangeで範囲をきちんと指定します。その際にMaxRow、MaxColを使います。
    転記先の範囲を、転記元のMyArrayと同じサイズにしてやります。ここがちょっと難関なんです。
    MaxRowもMaxColもまだ値が入っていませんので、値を取得します。

    NAMAE8
  4. MaxRowの行数は、データのあるシートの行数を数えて、一番下に降りて行った場所から、データのあるところまで飛んできた、その行という意味で、
    MaxRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
    そして、MaxColも考え方は同じで、
    MaxCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
    で値を取得できます。

  5. 転記元からデータは取得できていますので、転記先の範囲が決まれば、データを転記することができるのです。

    Public Sub 配列で取得OtherSheet()
    Dim myArray, sh As Worksheet
    Dim MaxRow As Long
    Dim MaxCol As Long
    
        Set sh = Worksheets("sh_data")
        myArray = sh.Range("A1").CurrentRegion
        
        MaxRow = sh.Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
        MaxCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
        Dim sh_other As Worksheet
        Set sh_other = Worksheets("sh_other")
        
        sh_other.Range(Cells(1, 1), Cells(MaxRow, MaxCol)) = myArray
    
    End Sub

配列で別ブックへの転記

  1. 別シートへの転記がわかるように、ウィンドウをもう1つ表示させておきます。この状態でマクロを実行してみましょう。

    NAMAE11
  2. 後ろのシートがデータのあるシート、手前のシートが転記先のシートです。

    NAMAE12
  3. 今度は別ブックにコピーして転記しましょう。
    別ブックという名前のブックを用意しています。
    そしてこのブックはすでにOpenしてあるんです。
    それで転記先が、別ブックのシートだとわかるように、以下のコードのようにSetしてください。

        Dim sh_other As Worksheet
        Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
    
  4. 別シートとの違いは、頭に Workbooks("別ブック.xlsx").という親オブジェクトをつけただけですね。

    NAMAE13
  5. マクロを実行すると、別ブックに転記されました。

    NAMAE15

別ブックへをOPENしてからの転記

  1. 別ブックを開いて転記する場合は、
    Workbooks.Open Filename:="ファイルのフルパス"で指定します。
    Dドライブの『顧客データ』というフォルダであれば、

      Workbooks.Open Filename:="D:\顧客データ\別ブック.xlsx"

    その開いたブックに対して先ほどと同じ以下のコードを記述します。

        Dim sh_other As Worksheet
        Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
    
  2. 先に転記するワークブックを開いてから、別シートを宣言して、セットしてやっています。
    次にそこへデータ元のから取得した配列を転記するという流れです。

    Public Sub 配列で取得別ブックOpen_OtherSheet()
        Dim myArray, sh As Worksheet
        Dim MaxRow As Long
        Dim MaxCol As Long
        Set sh = Worksheets("sh_data")
        myArray = sh.Range("A1").CurrentRegion
        Workbooks.Open Filename:="D:\顧客データ\別ブック.xlsx"
        Dim sh_other As Worksheet
        Set sh_other = Workbooks("別ブック.xlsx").Worksheets("sh_other")
        
        sh_other.Range(Cells(1, 1), Cells(MaxRow, MaxCol)) = myArray
        Workbooks("別ブック.xlsx").Close SaveChanges:=True
    End Sub

    最後のコードは、ブックを閉じるコードですが、引数に保存して閉じるを指定しています。
    Workbook.Close メソッドを使っています。