VBAにおける配列の扱いと、セルへの転記

TAKESAN

発端

このような話題がありました↓

配列の添字が1で始まると考えていたと思しきコードで、数十万セルに1つずつ書き込み、転記は1セルずつコピー→ペーストをしていた、という話。

この種のものは、技術が充分で無い事例を挙げて馬鹿にするという面があって、それは気持ちの良いものでは無いですが、それはともかくとして、実際、Excelの機能やVBAの使いかたを知らないが故に非効率なものを作ってしまう、のは結構ある事ですので、今回は、

  • VBAでの配列の扱い

  • 複数セルからなる範囲への転記

について検討します。
なお、本記事では、基本的なExcelの機能、及びVBAの構文などについては把握しているのを前提します。

VBAによる、配列とセル転記の扱いかた

VBAの配列

まず、配列の添字について。
プログラミング言語によっても配列の添字の開始番号は異なりますが、VBAはゼロ始まりです。
ただし、モジュール冒頭で

Option Base 1


このように宣言する事により、1始まりに出来ます。ですので、Option Base 1とした上で

配列の添字を1開始にする
Sub Test()
    Dim sushi(5) As String
    sushi(1) = "maguro"
    Debug.Print sushi(1)
End Sub

このようにすると正常に動きますが、
ゼロ番目に代入しようとすると、

sushi(0) = "maguro"
インデックスが範囲外となる

エラーが発生します。

また、宣言時に添字の範囲を指定できます。

Dim sushi(1 To 10) As String

↑このようにします。

1から始まっている

なお、宣言時に指定するのは、添字であり要素数（配列の長さ）では無いです。Option Basesステートメントは添字の下限を定義するもので、これが既定のゼロであれば、定義時に1つの数値を渡せば、それに1を足したのが配列の要素数となります。ですから、sushi(5)としたら要素数は6となります。

セルへの逐次処理

次に、セルへの値の設定を考えます。次のようなコードを書きました。
なお、説明も兼ねて、大部分にコメントを入れます。

Option Explicit

Sub Test()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim r As Long '行方向カウンター
    Dim c As Long '列方向カウンター
    
    For r = 1 To 20000 '20000行
        For c = 1 To 20 '20列
            ArrayTest.Cells(r, c).Value = r & "-" & c '行番号-列番号を入れる
        Next
    Next
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

20000行（縦）かける20列（横）のセル範囲に対し、1つずつ値を設定するコードです。話題では80万セルでしたが、ここでは40万セルにしてあります。最後に経過時間が出ます。

このコードを数回実行した所、私の環境では80秒くらいかかりました（3年くらい前のRyzen 7、メインメモリー16GB）。

結果

1分以上かかるので、だいぶ遅いですね。このように、1つずつ値を設定するのでは、かなり時間がかかります。

では、配列を使って同じ事をやってみましょう。

配列を使用した範囲処理

やる事は、

  • 値を転記する用途の配列を作る

  • 作った配列を転記する

こうです。まず、配列を作りましょう。

データ用配列作成（部分）
    Dim data(19999, 19) As String '転記用データ配列
    
    Dim i As Long 'ループカウンター縦
    Dim j As Long 'ループカウンター横
    For i = 0 To 19999 '配列の0番目から19999番目→サイズ20000
        For j = 0 To 19 '配列の0番目から19番目→サイズ20
            data(i, j) = (i + 1) & "-" & (j + 1) '行番号-列番号を入れる
        Next
    Next

まず、配列変数dataを定義します。2次元配列なので、添字は2つ指定します。標準のゼロ開始なので、上限の添字は、要素数から1引いてあります。
次に、ループを2段階で回して、「{行番号}-{列番号}」の値を配列に入れていきます（6行4列目なら「6-4」）。添字は行番号より1少ないので、ここで1足します。これでデータ配列の出来上がりです。

そして、作ったデータをセル範囲に転記します。開始をCells(1,1)セルとしますが、

これでは失敗する
    ArrayTest.Cells(1, 1).Value = data 'data配列を転記する

↑これではいけません。結果は次のようになります。

1つのセルだけ

起点のセルに値が入るだけです。Cells(1, 1).Valueとしているのだから当然ですね。したがって、転記先の範囲を適切に指定する必要があります。
Range("A1:T20000")でも良いですが、数値と列のアルファベットを対応させるのは面倒ですので、数値のみで指定できたほうが便利です。そして、セル範囲を示すRangeオブジェクトには、実に便利なResizeプロパティがあります。これは、あるセル範囲に数値を指定し、新しいセル範囲を返すものです。いまは、20000行かける20列ですので、

Range.Resizeプロパティ
    ArrayTest.Cells(1, 1).Resize(20000, 20).Value = data

このようにすれば良いです。つまり、

起点のセルから行方向に20000、列方向に20拡大した範囲

が返されます。

全体のコードは次のようです。

コード全体
Option Explicit

Sub Test()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim data(19999, 19) As String '転記用データ配列
    
    Dim i As Long 'ループカウンター縦
    Dim j As Long 'ループカウンター横
    For i = 0 To 19999 '配列の0番目から19999番目→サイズ20000
        For j = 0 To 19 '配列の0番目から19番目→サイズ20
            data(i, j) = (i + 1) & "-" & (j + 1) '行番号-列番号を入れる
        Next
    Next

    ArrayTest.Cells(1, 1).Resize(20000, 20).Value = data 'data配列を転記する
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

何回か実行した結果は次のようになりました。

かなり短縮できた

1つずつ値を設定した場合より、数十倍速くなりました。

配列添字の変更

ところで、最初のコードでは、ループカウンターにrとcの変数を使っていました。これは言うまでも無く、rowとcolumnの略ですが、配列を使うほうでは、iとjにしました。行番号と列番号に対して数値がそのまま対応しないので紛らわしいために、そうしました。VBAでは配列の添字開始は標準でゼロですが、対してRanageオブジェクトの添字（単一セルを示すインデックス）は1から始まるため、数値を足したりして調整する必要があったわけです。
その結果、とても読みにくいコードになっています。

であれば、です。

最初から配列の添字を変えれば良い

ではありませんか。そうすれば読みやすくなるはずです。やってみましょう。

変更したコード
配列の定義を変えた
Option Explicit

Sub Test()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim data(1 To 20000, 1 To 20) As String '転記用データ配列
    
    Dim r As Long 'ループカウンター縦→行番号と同じ
    Dim c As Long 'ループカウンター横→列番号と同じ
    For r = 1 To 20000 '配列の1番目から20000番目→サイズ20000
        For c = 1 To 20 '配列の1番目から20番目→サイズ20
            data(r, c) = r & "-" & c '行番号-列番号を入れる
        Next
    Next

    ArrayTest.Cells(1, 1).Resize(20000, 20).Value = data 'data配列を転記する
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

配列の宣言の際、1 To 20000のようにすれば、最小と最大の添字を定義できるのでした。であれば、いまは20000行かける20列の範囲を扱うので、それに合わせて、最初から1始まりの配列を宣言すれば良いのです。それに合わせて、ループカウンター変数もrとcに戻しました。For文の開始も1にして配列に格納しているので、直感に合うし、コードも整理されます。つまり、VBAでExcelのセル範囲を扱う場合、

添字のゼロ開始にこだわる必要は無い

と言えます。読みやすく扱いやすいように宣言・定義して使えば良いのであって、Forループをゼロから始めないといけないとか、添字はゼロ始まりにするとか、そういう所を守る必要は特にありません。

もうちょっと工夫しましょうか。いまは、行数と列数を、各所で繰り返し直打ちしています。これでは、範囲が変われば全部を変更する必要がありますね。良くないので、最初に行数と列数を定数として宣言しておきましょう。

行数と列数の定義
    Const ROW_LEN As Long = 20000 '行数
    Const COL_LEN As Long = 20 '列数

こうすれば、適用範囲が変わっても、ここの宣言を変更すれば良くなります。これに基づいてコードを書き直しましょう。他の部分も少し変えます。

書き直したコード
Option Explicit

Sub Test()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Const ROW_LEN As Long = 20000 '行数
    Const COL_LEN As Long = 20 '列数
    
    Dim data(1 To ROW_LEN, 1 To COL_LEN) As String '転記用データ配列
    
    Dim r As Long 'ループカウンター縦→行番号と同じ
    Dim c As Long 'ループカウンター横→列番号と同じ
    For r = 1 To ROW_LEN '配列の1番目から20000番目→サイズ20000
        For c = 1 To COL_LEN '配列の1番目から20番目→サイズ20
            data(r, c) = r & "-" & c '行番号-列番号を入れる
        Next
    Next

    Dim dataStartCell As Range
    Set dataStartCell = ArrayTest.Cells(1, 1) 'データ転記を開始するセル
    
    dataStartCell.Resize(ROW_LEN, COL_LEN).Value = data 'data配列を転記する
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

配列の最大添字とカウンター最終値には定数によって定義した行数列数（ROW_LENとCOL_LEN）を使用し、変更に強くしてあります。また、転記開始セル（dataStartCell）を定義して分離しました。まあまあ読みやすくなったのではないでしょうか。
ちなみに、ワークシートを指定する場合、私はシート名を使わずオブジェクト名（WorkSheet.CodeNameプロパティ）を使います。ユーザー側の変更に強いからです（紛らわしそうならVBAProjectを前につける）。

セル範囲からの転記

toggeterの話題では、データのコピーペーストの繰り返しをおこなっているとの事でした。当然ですが、範囲のセルを走査してコピーするのは遅いです。やってみましょう。
準備として、

  • 貼り付け元用のソースシートを作る：SrcSheet

  • 貼り付け先用のシートを作る：DestSheet

↑これを追加しておきます。コードは下記の通りです。

セルを1つずつコピー・ペーストするコード
Option Explicit

Sub Test2()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim srcSht As Worksheet
    Set srcSht = SrcSheet '貼り付け元シート。ソース
    Dim destSht As Worksheet
    Set destSht = DestSheet '貼り付け先シート
    
    Dim srcRng As Range
    Set srcRng = srcSht.Cells(1, 1).CurrentRegion '貼り付け元セル範囲。ソース
    
    Dim r As Long 'ループカウンター。行
    Dim c As Long 'ループカウンター。列
    Dim currentCell As Range '走査中の現在セル
    For r = 1 To srcRng.Rows.Count 'ソース範囲の行数
        For c = 1 To srcRng.Columns.Count 'ソース範囲の列数
            Set currentCell = srcRng(r, c) '現在セル
            currentCell.Copy '現在セルをコピー
            destSht.Range(currentCell.Address).PasteSpecial xlPasteValues '貼り付け先シートに値貼り付け
        Next
    Next
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

処理の流れはこうです。

  1. 貼り付け元（ソース）と貼り付け先シートを用意

  2. 貼り付け元のセル範囲をソースシートから用意

  3. 貼り付け元のセル範囲を走査

  4. 走査中のセルをコピーし、貼り付け先シートの同じアドレスに貼り付ける

ポイントは、処理の遅い値貼り付けをおこなっている所。
貼り付け元のソース範囲は、最初に作った自動生成を利用しました。整然とした範囲なので、CurrentRegionプロパティで綺麗に取れます。
ただし、あまりにも処理が遅いので、ひとまず対象にするのは、200行かける20列の4000セルの範囲です。
結果はこうです↓

遅い！

117秒かかりました。遅いですね。単純計算で、先に設定した40万セルであれば、3時間以上！かかります。
それもそのはず。実行途中でブレイクして確かめれば解りますが、Copyメソッドを実行したら、セル内容がクリップボードにコピーされます。それを貼り付けるので、遅くて当然です。シートを見ていると、実行中は手作業と同じようにアニメーションして、シートのステータスが順次変化する様子が見えます。

コピー・ペースト処理を変更

ではどうすれば良いでしょうか。次のように変更しましょう。
いまのコードで、コピー→ペーストする箇所を見ます。

currentCell.Copy '現在セルをコピー
destSht.Range(currentCell.Address).PasteSpecial xlPasteValues '貼り付け先シートに値貼り付け

先ほども書いたようにこれは、

  1. セル内容をコピーする

  2. コピーした内容を、貼り付け先アドレスのセルに値として貼り付ける

こういう流れでした。クリップボードを介してデータをやり取りするので、とても遅いのでした。その理由で遅いのであれば、

クリップボードを介さない

ようにすれば良いではありませんか。次のように書きます。

destSht.Range(currentCell.Address).Value = currentCell.Value '値を直接代入

つまり、

  1. いまいるセルの内容をクリップボードにコピーし

  2. 貼り付け先のセルにクリップボードから値を貼り付ける

この流れを、

  • 貼り付け先セルの値は、いまいるセルの値である

と書くのです。そうすれば、クリップボードを経由せずに貼り付け先の値が変更できるという寸法です。

コピー・ペーストをやめた
Option Explicit

Sub Test2()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim srcSht As Worksheet
    Set srcSht = SrcSheet '貼り付け元シート。ソース
    Dim destSht As Worksheet
    Set destSht = DestSheet '貼り付け先シート
    
    Dim srcRng As Range
    Set srcRng = srcSht.Cells(1, 1).CurrentRegion '貼り付け元セル範囲。ソース
    
    Dim r As Long 'ループカウンター。行
    Dim c As Long 'ループカウンター。列
    Dim currentCell As Range '走査中の現在セル
    For r = 1 To srcRng.Rows.Count 'ソース範囲の行数
        For c = 1 To srcRng.Columns.Count 'ソース範囲の列数
            Set currentCell = srcRng(r, c) '現在セル
            destSht.Range(currentCell.Address).Value = currentCell.Value '値を直接代入
        Next
    Next
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

結果は歴然です。

速くなった👍️

0.8秒くらい。だいぶ速いですね。途中でブレイクしてクリップボードから貼り付けようとしても、貼り付け元セルの内容は貼り付けられない事から、クリップボードを介していないのが解ります。
このコードを使って、最初に生成した20000行かける20列、40万セルの転記をしてみましょう。

40万セルの転記

143秒。4000セルに117秒かかったのを考えれば、遥かに高速ですが、けっこうかかっています。更に速くできないでしょうか。

一括貼り付け処理

そもそも先のコードでは、貼り付け元セル範囲（ソース）のセルを1つずつ走査して処理していました。コピーしてペーストするのは論外にしても、直接に値を代入する方法でも、1セルずつ処理するのに違いはありません。そこで、ループ処理を無くし、次を追加します。

    Dim destStartCell As Range
    Set destStartCell = destSht.Cells(1, 1) '貼り付け先セル範囲の起点
    
    srcRng.Copy 'ソースのコピー
    destStartCell.PasteSpecial xlPasteValues '貼り付け先起点セルに値の貼り付け

まず、貼り付け先のセル範囲の起点を定義します（destStartCell）。貼り付け先のシートは前もってdestShtで定義したので、そこの1番目のセルを起点とします。
次に、srcRngと定義した、セル範囲ソース（20000行かける20列の範囲）をコピーします。値を貼り付けたいので処理を分けます。そしてdestStartCellなる起点で、値を指定してPasteSpecialメソッドで貼り付けます。
要するに、

1つずつ貼り付けるのが遅いなら、最初から全体を貼り付ければ良い

のです。手作業でコピー・ペーストする時も、セル1つずつに処理するはず無いのです。

ソース全体の一括貼り付け
Option Explicit

Sub Test2()
    Dim startTime As Double '開始時刻
    startTime = Timer '開始時刻を入れる
    
    Dim srcSht As Worksheet
    Set srcSht = SrcSheet '貼り付け元シート。ソース
    Dim destSht As Worksheet
    Set destSht = DestSheet '貼り付け先シート
    
    Dim srcRng As Range
    Set srcRng = srcSht.Cells(1, 1).CurrentRegion '貼り付け元セル範囲。ソース
    
    Dim destStartCell As Range
    Set destStartCell = destSht.Cells(1, 1) '貼り付け先セル範囲の起点
    
    srcRng.Copy 'ソースのコピー
    destStartCell.PasteSpecial xlPasteValues '貼り付け先起点セルに値の貼り付け
    
    Debug.Print "経過時間: " & Timer - startTime '経過時間の表示
End Sub

結果はこう。一瞬ですね↓

一瞬で完了

Excelの機能とVBAの連携

Office TANAKAの田中亨氏もよく主張なさっていますが、実務上でExcel VBAを使う際に重要なのは、

いかにワークシートの構造と機能を理解するか

です。プログラミング言語における色々のアルゴリズムの実装方法を知っているとか、そういうのも重要なのは言うまでもありませんが、最も知っておくべきは、VBAなるプログラミング言語によって、いかにワークシートが有する機能にアプローチするか、その術を知っておく事です。

これは、私が遭遇した実例です。
ある業務で、20万行くらいあるそこそこ大きなデータをExcelで管理していました。その表を並び替えたり抽出したりする機能を、VBAを書ける人が実装しようとしていましたが、なんと、わざわざソートアルゴリズムを書いていたのです。結果、かなり遅いコードが出来ていました。
VBAには、ワークシートが持つ便利な機能を使用するAutoFilterメソッドやSortメソッドがあり、それらは超高速に処理できるのに、そういう事をしていたわけです。
その時は、私がそれらメソッドを使って実装し、高速化しました。しかるにそれが出来たのは、私の知識が豊富であったからではありません。当時はVBAを勉強し始めであり、単に

VBAをよく知らなかったから、手っ取り早くシートの機能を使おうとした

のです。その結果、出来上がったものは、前よりも性能の高いものでした。これが、Excelのワークシートが有する構造機能とプログラミング言語の仕様とを接続して使いこなす事の重要さを示すエピソードです。

今回の記事で紹介したのは、

  • 配列の扱い

  • 配列からのセル範囲への転記

  • セル範囲からセル範囲への転記

などのしくみでした。基本的な部分の説明に過ぎませんが、その基本を把握しておくのがとても重要です。
実務においてはたとえば、業務システム方面からCSV等でデータを取得し、それをExcelに取り込んでシートに転記して作業に使う、というようなシナリオがよくあります。そういう場合、VBAの配列まわりの仕様は、最近のPythonやJavaScriptなどモダンな言語に比して古くて貧弱なので、それをある程度把握しておき、シートへの転記などについても種々のやりかたを知っておかないと、冒頭のtogetterで紹介されたような、恐ろしく効率の悪いコードが成果物として出てくる可能性があります。

余談：同じ処理に対するアプローチのバリエーション

いま例に出したCSV取り込みの話のついでです。

CSVを取り込むと一口に言っても

  • FileSystemObjectのCreateTextFileメソッドでTextStreamオブジェクトを取得

  • ADODBのStreamを使って取得

  • QueryTablesのAddメソッドでインポート

など色々の方法があります。それぞれ一長一短があり、どういうデータを扱ってどういう処理に使うかによっても分けて考える必要があります（単に転記するならQueryTablesが圧倒的にシンプルに書ける）。それも含めて、

  • 実務上の要件

  • ワークシートのありよう

  • VBAの仕様

これらをしっかり把握・連携させて業務に役立てるのが肝要です。

参考資料


いいなと思ったら応援しよう！