セル範囲⇔配列（VBA高速化必須テクニック）

最終更新日：2019-07-07

第114回.セル範囲⇔配列（マクロVBA高速化必須テクニック）

セル範囲をVariant型変数に入れる事で、配列を作成することができます。
また、配列をセル範囲にまとめて出力する事も出来ます。

これは、マクロVBAを高速処理したい時の必須テクニックになります、
マクロの処理が遅い場合は、このテクニックが使えないか検討してください。

マクロが遅いという相談の多くは、Applicationのプロパティ設定で大幅に速くなることが多いですが、
そもそもデータ量が多いために時間がかかっている場合は、マクロVBAを根本的に見直す必要が出てきます。

第57回.Applicationのプロパティ（マクロ高速化と警告停止等）

Applicationは、Excel全体をあらわすオブジェクトです、つまり、エクセルそのものだと考えて下さい。ここでは、そのプロパティの一部を紹介します。ここで紹介するApplicationのプロパティはほんの一部です。

マクロVBAの処理速度を改善するときの根本として、「セル範囲⇔配列」が必要となります。

セル範囲⇔配列の基本VBA


  
    
      
      Dim MyArray

MyArray = Range("A1:B100")

・・・処理・・・

Range("A1:B100") = MyArray

変数MyArrayは、データ型を指定していませんので、Variant型になっています。
MyArray = Range("A1:B100")
これで、MyArrayは配列になります。
MyArray(1 To 100, 1 To 2)
このような配列です。
LBoundは1になる点を注意して下さい。

Range("A1:B100") = MyArray
これで、配列の中の値が全てセル範囲に一括で出力する事が出来ます。

使用例

以下は、配列を使わない場合と、配列を使った場合のサンプルになります。
速度の違いは一目瞭然ですので、実際にやって体感してみて下さい。


  
    
      Sub sample1()

      　　Dim i As Long

      　　Application.ScreenUpdating = False

      　　For i = 1 To 100000

      　　　　Cells(i, 3) = Cells(i, 1) * Cells(i, 2)

      　　Next i

      　　Application.ScreenUpdating = False

      End Sub


  
    
      Sub sample2()

      　　Dim i As Long

      　　Dim MyArray1

      　　Dim MyArray2

      　　MyArray1 = Range("A1:B100000")

      　　ReDim MyArray2(1 To 100000, 1 To 1)

      　　For i = LBound(MyArray1, 1) To UBound(MyArray1, 1)

      　　　　MyArray2(i, 1) = MyArray1(i, 1) * MyArray1(i, 2)

      　　Next i

      　　Range("C1:C100000") = MyArray2

      End Sub

単に、100,000行の掛け算をしています。

セル範囲とやり取りする配列は、必ず２次元の配列になっている必要があります。
2次元配列として定義するか、Variant型への代入の結果が2次元配列になっていればよいです。

上記で、
ReDim MyArray2(1 To 100000, 1 To 1)
これは、
C列に出力する為の配列で、２次元で定義しています。
1次元目が行、2次元目が列に相当します。
LBoundは0でも問題ありませんが、1にした方が理解しやすいのではないかと思います。

これほど単純な処理は、あまり無いと思いますが、
大量データを扱う場合は、適宜配列を使うようにする事で、高速処理を実現する事が出来ます。
ぜひ、マスターして頂きたいテクニックになります。

配列およびマクロVBAの高速化に関するページ

配列の使い方について

今回は、配列についての基礎知識をまとめました。配列とはシートのセルを考えて下さい。縦１列だけを取り出した場合は、１次元の配列です。縦横の複数行列を取り出した場合は、２次元の配列です。このようなデータを変数で扱うのが、配列になります。

VBAの配列まとめ（静的配列、動的配列）

VBAで配列を必要とするのは処理速度を上げる為だと言えますそもそもエクセルにはセルの2次元配列であるシートがあります。にもかかわらずVBAの学習を進めると必ず配列が出てきますではなぜVBAで配列必須になるかと言うとセルを使うと処理速度が非常に遅くこれを高速に処理するために配列が必要となるからです。

最終行の判定、Rangeオブジェクトと配列、高速化の為に

最終行の判定エクセル顧客管理の記事からのスピンオフ記事になります。以前にモジュール「顧客一覧へ登録」において.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが

記述による処理速度の違い

記述の違いで、どの程度処理速度に変化があるかを検証します。どのような記述が処理速度に影響するかという点を分かり易くするために、あえて極端なマクロVBAで検証をしています。※本記事は2013年に書いたものを2019/2に再計測しつつ一部書き直したものです。

速度比較決定版【Range,Cells,Do,For,For Each】

何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いかとか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。

エクセルVBAのパフォーマンス・処理速度に関するレポート

ExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。

VBAのFindメソッドの使い方には注意が必要です

vbafindでの検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、アクセス解析で分かった事ですが正直少し戸惑っています。なぜなら私はFindメソッドをほとんど使いません、Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。

WorksheetFunction.Matchで配列を指定した場合の制限について

WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。

マクロVBAの高速化・速度対策の具体的手順と検証

マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。

動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

動的配列を使い様々な処理をした後にシートへ出力しようとしたとき縦横が違っている為そのまま出力できませんそもそも動的配列の要素数をRedimで変更できるのは最下位の次元のみになります。2次元配列の場合ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが

大量データで処理時間がかかる関数の対処方法（WorksheetFunction）

大量データ処理において、一般的な速度対策をやってさえ、時に何時間もかかってしまう事があります、そういう場合でも、多くの場合は何らかの対策があるものです、個別のロジックの記述でこれに対応する一つの有効なマクロVBAコ－ドについて解説します。以下の例で解説します。

大量データにおける処理方法の速度王決定戦

VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。

遅い文字列結合を最速処理する方法について

VBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型（可変長文字列）についての基礎知識が必要です。

大量VlookupをVBAで高速に処理する方法について

大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。

Withステートメントのマクロ実行速度と注意点

マクロVBAにおいてWithステートメントはとても重要です、可読性（読みやすさ、理解しやすさ）、実行速度においては、その役割はとても大きいものになります。本記事では、Withステートメントを使うか使わないかでのマクロ実行速度の差に焦点を絞って検証します。

IfステートメントとIIF関数とMax関数の速度比較

VBAの実行速度比較はとても良く読まれている人気記事となっていますがそのほとんどは配列やDictionaryを使った少し高度なVBAでの比較が多くなっています。今回は極めて基本的な大小比較して大きい方を返すという処理においてIfステートメントVBA関数のIIF関数シート関数のMax関数これらの処理速度を比較検証してみます。