スタイル

テーブルへの配列の代入は間接的に行う

エクセルVBAの処理を高速化するためには、「配列を使う」ことが推奨されています。

ただし、この記事でも強調されているとおり、高速化の鍵は「配列をセルに代入する」ことです。

一方、VBAアセットでは、「テーブルを使う」ことを推奨しています。

そこで、「配列」を使う場合、セルではなく「テーブルに代入」することになります。

問題点

VBAアセットでは、VBAを使って共同編集を可能にする方法を紹介しています。

この機能を実現するためには、配列をテーブルに代入する処理が必要となります。ところが、これがめっぽう遅い場合があるのです。どういった場合に遅くなるのかは、良く分かっていません(というか、ちゃんと調べていません)が、VBAアセットで紹介している「電話メモ」では遅くならないのに「事件データベース」では遅くなってしまいます。

対策

ということで、原因は良く分からないのですが、対策はあります。それは、いったんシート上のセルに代入してから、テーブルのセルに代入するということです。

比較してみます。

テーブルに直接代入した場合

rngTable(テーブルを設定した範囲名)にvarTable(バリアント変数に格納した配列)を代入します。

rngTable.Resize(UBound(varTable, 1), UBound(varTable, 2)) = varTable

「事件データベース」で、このコードを使って1万行のデータを代入するのには、約34秒もかかってしまいます。(「電話メモ」だと約2秒で終わります。)

いったんシート上のセルに代入してからテーブルに代入した場合。

同じく、rngTable(テーブルを設定した範囲名)にvarTable(バリアント変数に格納した配列)を代入します。

With Worksheets(rngTable.Parent.Name)
    'テーブル外のシートにテーブル配列を代入する
    'テーブル内に配列を代入すると遅いので一旦テーブル外に代入します。
    Dim k As Long, l As Long    'テーブル直下の行及び列
    k = rngTable.Row + rngTable.Rows.Count
    l = rngTable.Column
    Range(.Cells(k, l), .Cells(k - 1 + UBound(varTable, 1), l - 1 + UBound(varTable, 2))) = varTable

    'シート上の値をテーブル内にカットアンドペーストする
    Range(.Cells(k, l), .Cells(k - 1 + UBound(varTable, 1), l - 1 + UBound(varTable, 2))).Cut (rngTable(1, 1))
End With

このコードを使えば、「事件データベース」でも1万行のデータを代入するのに約2秒しかかかりません。

というわけで、VBAアセットでは、「テーブルへの配列の代入は直接行わず、シート上のセルを介して間接的に行う」ようにしています。

コメント

タイトルとURLをコピーしました