第114回.セル範囲⇔配列（マクロVBA高速化必須テクニック）
セル範囲をVariant型変数に入れる事で、配列を作成することができます。
また、配列をセル範囲にまとめて出力する事も出来ます。
マクロの処理が遅い場合は、このテクニックが使えないか検討してください。
そもそもデータ量が多いために時間がかかっている場合は、マクロVBAを根本的に見直す必要が出てきます。
第57回.Applicationのプロパティ（マクロ高速化と警告停止等）
・Applicationの主要プロパティ ・ScreenUpdating（マクロVBAの高速化） ・DisplayAlerts（警告停止） ・Interactive（ユーザー操作の禁止） ・Calculation（計算方法） ・StatusBar ・Cursor ・その他
マクロVBAの処理速度を改善するときの基本として、「セル範囲⇔配列」が必要となります。
セル範囲⇔配列の基本VBA
Dim MyArray
MyArray = Range("A1:B100").Value
'・・・処理・・・
Range("A1:B100").Value = MyArray
=CELL("address",A1)
各セルにこのような数式を入れて実行すると、
各セルにこのような数式を入れて実行すると、
MyArray = Range("A1:B100").Value
これで、MyArrayは配列になります。
MyArray(1 To 100, 1 To 2)
このような縦横の2次元配列です。
Range("A1:B100").Value = MyArray
これで、配列の中の値が全てセル範囲に一括で出力する事が出来ます。
これで、MyArrayは配列になります。
このような縦横の2次元配列です。
これで、配列の中の値が全てセル範囲に一括で出力する事が出来ます。
セル範囲を受け取る変数宣言について
変数MyArrayは、データ型を指定していませんのでVariant型になっています。
Dim MyArray
Dim MyArray As Variant
これは、どちらも単なるVariant宣言です。
Dim MyArray()
Dim MyArray() As Variant
これはVariantの配列宣言になります。
セル範囲とのやりとりにおいては、どちらでも構いません。
VBA内部としては厳密には違いがありますが、一般的に気にするような違いはありません。
※本サイト内では、
Dim MyArray
この記述を基本としていますが、他の記述をしている場合もあります。
Dim MyArray As Variant
これは、どちらも単なるVariant宣言です。
Dim MyArray() As Variant
これはVariantの配列宣言になります。
VBA内部としては厳密には違いがありますが、一般的に気にするような違いはありません。
Dim MyArray
この記述を基本としていますが、他の記述をしている場合もあります。
配列の下限について
セル範囲のValueを入れた配列の下限は1になります。
LBoundが必ず1になる点に注意して下さい。
ただし、セルに出力する場合の配列下限は1でも0でも、どちらでも構いません。
第113回.配列に関連する関数
LBoundが必ず1になる点に注意して下さい。
ただし、セルに出力する場合の配列下限は1でも0でも、どちらでも構いません。
・LBound関数とUBound 関数 ・Array関数 ・IsArray 関数 ・Join関数 ・Filter関数 ・Eraseステートメント
VBAにおける配列やコレクションの起点について・LBound関数とUBound 関数 ・Array関数 ・IsArray 関数 ・Join関数 ・Filter関数 ・Eraseステートメント
使用例
以下は、配列を使わない場合と、配列を使った場合のサンプルになります。
速度の違いは一目瞭然ですので、実際にやって体感してみて下さい。
単に、100,000行の掛け算をしています。
セル範囲とやり取りする配列は、
必ず2次元の配列になっている必要があります。
2次元配列として定義するか、Variant型への代入の結果が2次元配列になっていればよいです。
速度の違いは一目瞭然ですので、実際にやって体感してみて下さい。
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次元の配列になっている必要があります。
2次元配列として定義するか、Variant型への代入の結果が2次元配列になっていればよいです。
セルに出力するだけなら1次元配列でも出力可能です。
Range("A1:C1") = Array(1, 2, 3)
1次元配列は、横向きのセル範囲に出力できます。
Range("A1:C1") = Array(1, 2, 3)
1次元配列は、横向きのセル範囲に出力できます。
上記の、
ReDim MyArray2(1 To 100000, 1 To 1)
これは、
C列に出力する為の配列で、2次元で定義しています。
1次元目が行、2次元目が列に相当します。
要素下限を0開始にしても構いませんが、セル範囲とやり取りすることを考えると、
1開始にしたほうが方が理解しやすいと思います。
ReDim MyArray2(1 To 100000, 1 To 1)
これは、
C列に出力する為の配列で、2次元で定義しています。
1次元目が行、2次元目が列に相当します。
1開始にしたほうが方が理解しやすいと思います。
これほど単純な処理は、あまり無いと思いますが、
大量データを扱う場合は、適宜配列を使うようにする事で高速処理を実現する事が出来ます。
ぜひ、マスターして頂きたいテクニックになります。
大量データを扱う場合は、適宜配列を使うようにする事で高速処理を実現する事が出来ます。
ぜひ、マスターして頂きたいテクニックになります。
配列およびマクロVBAの高速化に関するページ
配列の使い方について
・配列とは ・１次元の配列 ・2次元の配列 ・3次元以上の配列 ・動的配列 ・動的配列 ・動的配列の要素数の取得 ・配列使用時の注意
VBAの配列まとめ（静的配列、動的配列）・配列の概念 ・静的配列 ・動的配列 ・セル範囲⇔配列の基本 ・配列で必要となるVBA関数とステートメント ・配列に関する記事の一覧
最終行の判定、Rangeオブジェクトと配列、高速化の為に最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。以前に、モジュール「顧客一覧へ登録」において、.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが、
記述による処理速度の違い・１．変数の型指定 ・2．罫線の引き方 ・3．行高の変更 ・4．配列を使用した処理 ・処理速度を早くする為には
速度比較決定版【Range,Cells,Do,For,For Each】何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いか とか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。
エクセルVBAのパフォーマンス・処理速度に関するレポートExcelのマクロVBAは遅い・重いと良く言われることが多いようですが、マクロVBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。
VBAのFindメソッドの使い方には注意が必要です・1.処理速度が遅い ・2.指定オプションがシート操作とリンクしている ・「値」で検索した場合は、表示形式に依存した検索になる ・最後に
WorksheetFunction.Matchで配列を指定した場合の制限についてWorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時、他の部分の記述との関係で、配列を指定してMatchを行う事があります。以下のようなVBAコードになります。これは正しく動作します。
マクロVBAの高速化・速度対策の具体的手順と検証マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。2次元配列の場合、ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが、
大量データで処理時間がかかる関数の対処方法（WorksheetFunction）・大量データで処理時間がかかるサンプルデータ ・普通にマクロVBAコ－ドを書いた場合 ・指定範囲を絞ってみる ・配列を使って書いてみる ・アルゴリズムを考えてみる ・Dictionary（連想配列）を使う ・大量データで処理時間がかかる関数の対処方法の最後に
大量データにおける処理方法の速度王決定戦VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。
遅い文字列結合を最速処理する方法についてVBAは遅い… よく聞くことですが、確かに普通にコード記述しているととても遅いことがあります、その代表の一つに、文字列結合があります、文字列結合を最速処理する方法について解説します。そもそも文字列結合は、なぜ遅いのか、String型（可変長文字列）についての基礎知識が必要です。
大量VlookupをVBAで高速に処理する方法について大量データ同士のVlookup処理は、非常に時間のかかる処理となります、マクロVBAで、これを高速に処理する方法について、VBAコードを示し解説します。ワークシート上の関数の場合 シートに関数を入れる場合は、以下を参照してください。【奥義】大量データでの高速VLOOKUP 以下の表で検証します。
Withステートメントのマクロ実行速度と注意点・マクロVBAのテストコード(Worksheet) ・マクロVBAのテストコード(Range) ・Withステートメントの注意点 ・最後に
IfステートメントとIIF関数とMax関数の速度比較・IfとIIFとMaxの比較マクロVBAコード ・IfとIIFとMaxの実行速度の実測値 ・最後に
同じテーマ「マクロVBA入門」の記事
・配列とは ・静的配列と動的配列 ・配列の宣言 ・多次元配列 ・要素の下限の変更 ・配列について
第112回.動的配列（Redim）・ReDimステートメント ・要素数の変更について ・配列について
第113回.配列に関連する関数・LBound関数とUBound 関数 ・Array関数 ・IsArray 関数 ・Join関数 ・Filter関数 ・Eraseステートメント
第114回.セル範囲⇔配列（マクロVBA高速化必須テクニック）第115回.Split関数
・Split関数 ・区切り文字が文字列式内に存在しない場合 ・空文字（""）をSplitした場合 ・Split関数の使用例
第116回.ファイル操作Ⅱ（OpenとClose）・Openステートメント ・Closeステートメント ・OpenステートメントとCloseステートメントの使用例
第117回.ファイル操作Ⅱ（Line Input #）・Line Input # ステートメント ・EOF関数 ・Line Input # ステートメント と EOF関数の使用例．
第118回.ファイル操作Ⅱ（Print #）VBAでファイル（CSV等）を扱う時は、最初に、ファイルを開き、その後に、読込み書込みを行い、最後に、ファイルを閉じます。ファイルを書き込む時には、Print#ステートメントを使います。Print#ステートメント シーケンシャル出力モード(OutputまたはAppend)で開いたファイルにデータを書き込むファイル入…
第119回.ファイルシステムオブジェクト（FileSystemObject）・FileSystemObjectオブジェクトの使用方法 ・FileSystemObjectオブジェクトのプロパティとメソッド ・FileSystemObjectオブジェクトのメソッドの戻り値 ・FileSystemObjectオブジェクトの使用例 ・FileSystemObjectオブジェクトの関連記事と実践例
第120回.OnTimeメソッド・OnTimeメソッドの構文 ・OnTimeメソッドの使用例 ・OnTimeメソッドの実践例
第121回.SendKeysメソッドとAppActivateステートメント・SendKeysメソッド ・特殊なキーを表す文字 ・AppActivateステートメント ・SendkeysとAppActivateの使用例 ・Sendkeysでは送信できないキーコード ・NumLockが解除されてしまう問題 ・Sendkeysについて
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。