VBA技術解説
VBAの配列まとめ(静的配列、動的配列)

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2018-02-08 最終更新日:2021-04-28

VBAの配列まとめ(静的配列、動的配列)


VBAで配列を必要とするのは、処理速度を上げる為だと言って良いでしょう。
そもそも、エクセルにはセルの2次元配列であるシートがあります。
にもかかわらず、VBAの学習を進めると必ず配列が出てきます。


ではなぜVBAで配列必須になるかと言うと、
セルを使うと処理速度が非常に遅く、これを高速に処理するために配列が必要となるからです。

VBAの配列では、シートのセル範囲を一括で配列に入れたり、
配列を一括でシートに出力することで、高速に動作するVBAを実現することが出来ます。
VBAで配列を使いこなせるようになることは、VBA習得の1つの大きな目標と言えます。
以下の項目に分けて解説します。

配列の概念

配列は、値を格納するために多くの区画を持つ1つの変数です、
集合住宅やアパートにたとえられますが、
要は、変数の箱を複数つなげたものと理解すれば良いでしょう。

1つの箱には、1つの値しか入れられませんが、その箱が複数つながっていますので、
結果として、複数の値をいれられるのが配列という事になります。
通常の変数が、1つの変数に1つの値を格納している点で大きく異なっています。

通常の変数 VBA マクロ 画像

配列    VBA マクロ 画像


配列が格納しているすべての値を参照する場合は、配列全体を参照することもできますし、
配列のインデックス番号(配列内の位置)を指定することで、個々の要素を参照することもできます。

配列のインデックス番号の最小値は、通常は0になります。
これを、
Option Base 1
これをモジュールの宣言セクションに指定することで、
インデックスの最小値を1に変更可能ですが、あまり使用しない方が良いと思っています。

配列の、箱(値を入れられる)の数を固定して変更しない場合と、
VBA内で、必要に応じて箱の数を変更する場合があります。

箱の数を固定するのを、静的配列と呼び、
箱の数を固定せずVBAで動的に変更するのを、動的配列と呼びます。

VBAでより重要でより難しいのが、動的配列になります。
プログラミングをする上で、
実行時点で要素数を決めたい場合や、実行途中で増やしたい場合が多くあります。
実行時点で要素数を増減できる動的配列こそが配列の極みになります。

静的配列

配列の宣言

Dim ステートメント、Private ステートメント、Public ステートメントで宣言します。

Dim MyArray(10) As Long

インデックスの最小値は、0になりますので、
上記の宣言で、11個要素を持つ、1次元の配列を定義したことになります。
値を入れる時は、

MyArray(0) = 1

このように使用します。
以下の例では、配列に0から10を順に数値を入れています。

Sub sample()
  Dim MyArray(10) As Long
  Dim i As Long
  For i = 0 To 10
    MyArray(i) = i
  Next
End Sub

多次元配列

Dim MyArray(10, 5) As String

これは、2次元配列を宣言しています。
ワークシートの、11行、6列のセル範囲を想像してもらえればよいでしょう。

Sub sample()
  Dim MyArray(10, 5) As Long
  Dim i As Long, j As Long
  For i = 0 To 10
    For j = 0 To 5
      MyArray(i, j) = Cells(i + 1, j + 1)
    Next
  Next
End Sub

上記では、セル範囲A1~F11を配列に入れています。

要素の下限の変更

Dim MyArray(1 To 10) As String

このように定義することで、配列の要素は、1~10の10個になります。
特に、ワークシートとデータのやり取りをする場合は、
このようにした方が、理解しやすく、また、記述もしやすいでしょう。

動的配列

ReDimステートメント

動的配列変数に対するメモリ領域の再割り当てを行います。

ReDim [Preserve] varname(subscripts) [As type]

Preserve

省略可能です。
既存の配列に格納されている値を失うことなく、配列の最後の次元の要素数を変更する場合に使用する、キーワードです。

varname

必ず指定します。
宣言する変数の名前です。
変数の標準的な名前付け規則に従って指定します。

subscripts

必ず指定します。
配列変数の次元を指定します。
指定できる次元数の最大値は 60 です。
引数 subscripts の構文は、次のとおりです。
[lower To] upper [,[lower To] upper] . . .
引数 lower を省略した場合、配列の添字の最小値は Option Base ステートメントによって制御されます。
Option Base ステートメントが記述されていなければ、添字の最小値は 0 になります。

type

省略可能です。
変数のデータ型を指定します。


キーワード Preserve を指定した場合、変更できるのは動的配列の最後の次元のサイズに限られます。
つまり、キーワードPreserveを使用した場合、動的配列のサイズを変更するために変えられるのは、添字の上限だけです。
また、次元数は変更できません。
添字の下限を変更しようとすると、エラーが発生します。

変数の初期化時には、数値変数は 0 に、可変長文字列は長さ 0 の文字列 ("") に初期化されて、固定長文字列には 0 が埋められます。
また、バリアント型 (Variant) 変数は Empty 値に初期化されます。

要素数の変更について

ReDim MyArray(10, 10)

ReDim MyArray(10)

ReDim MyArray(10, 10)

このように、Redimで次元も要素数も変更できます。
ただし、Redimにより、それまでに配列に入っていた値は失われます。

値を保持したまま、ReDimするには、
Preserveを指定します。

ReDim MyArray(10, 10)

ReDim Preserve MyArray(10, 11)

ReDim Preserve MyArray(11, 11)

Preserveを指定することで、値がそのまま残ります。
しかし、制限があります。
Preserveを指定した場合は、動的配列の最後の次元のサイズのみ変更可能で、
それより上位の次元は変更できません、エラーとなります。


次元数の最大値は60とはなっていますが、
通常は、2次元までにしましょう。
ワークシートが2次元なのですから、大抵の処理は2次元で済むはずです。
それ以上の次元は、むやみにVBAを複雑化させるだけです。

セル範囲⇔配列の基本

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

Dim MyArray

MyArray = Range("A1:B100")

・・・処理・・・
Range("A1:B100") = MyArray


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
  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
  Range("C1:C100000") = MyArray2
End Sub

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

セル範囲とやり取りする配列は、必ず2次元で定義します。
上記で、
ReDim MyArray2(1 To 100000, 1 To 1)

これは、C列に入れる為の配列ですが、2次元で定義しています。
1次元目が行、2次元目が列に相当します。
LBoundは、0でも問題ありませんが、1にした方が理解しやすいと思います。

配列で必要となるVBA関数とステートメント

配列を使う上で、必要となるVBA関数がいくつかあります、

LBound関数

配列の指定された次元で使用できる最小の添字を、長整数型 (Long)の値で返します。

LBound(arrayname[, dimension])

arrayname 必ず指定します。
配列変数の名前です。
変数の標準的な名前付け規則に従って指定します。
dimension 省略可能です。
バリアント型 (内部処理形式 Long の Variant) の値を指定します。
添字の最小値を調べる対象となる配列の次元を示す整数を指定します。
最初の次元なら 1、2 番目の次元なら 2、というように指定します。
引数 dimension を省略すると、1 が指定されたものと見なされます。

UBound関数

配列の指定された次元で使用できる添字の最大値を、長整数型 (Long) の値で返します。

UBound(arrayname[, dimension])

arrayname 必ず指定します。
配列変数の名前です。
変数の標準的な名前付け規則に従って指定します。
dimension 省略可能です。
バリアント型 (内部処理形式 Long の Variant) の値を指定します。
添字の最小値を調べる対象となる配列の次元を示す整数を指定します。
最初の次元なら 1、2 番目の次元なら 2、というように指定します。
引数 dimension を省略すると、1 が指定されたものと見なされます。

LBound関数、UBound関数の使用例

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
  For j = LBound(MyArray, 2) To UBound(MyArray, 2)
    MyArray(i, j) = 0
  Next
Next i

上記では、2次元配列MyArrayの全次元の全要素に0を入れています。

Array関数

配列が格納されたバリアント型 (Variant) の値を返します。

Array(arglist)

引数 arglist は、必ず指定します。
引数 arglist には、値のリストをカンマ (,) で区切って指定します。
引数 arglist を指定しない場合は、長さ 0 の配列が作成されます。

指定した値は、バリアント型 (Variant) に格納されている配列の要素に代入されます。

Array関数の使用例
Dim MyArray
MyArray = Array(10, 20, 30)

この結果、MyArrayは、

LBound(MyArray)は0、UBound(MyArray)は2となります。

IsArray関数

変数が配列であるかどうかを調べ、結果をブール型 (Boolean) で返します。

IsArray(varname)

引数 varname は必ず指定します。引数 varname には、変数の識別子を指定します。

IsArray 関数は、指定した変数が配列の場合は、真 (True) を返します。

それ以外の場合は、偽 (False) を返します。
IsArray 関数は、特に配列を含むバリアント型 (Variant) の式 に有効です。

Join関数

配列に含まれる各要素の内部文字列を結合して作成される文字列を返します。

Join(sourcearray [, delimiter])

sourcearray 必ず指定します。
結合する文字列を含む 1 次元配列を指定します。
delimiter 省略可能です。戻り値となる文字列を区切るのに使用する文字を指定します。
省略すると、スペース (" ") が使用されます。
引数 delimiter が長さ 0 の文字列 (") である場合は、リスト内のすべての項目が区切り文字なしで連結されます。

Filter関数

指定されたフィルタ条件に基づいた文字列配列のサブセットを含むゼロ ベースの配列を返します。

Filter(sourcesrray, match[, include[, compare]])

sourcearray

必ず指定します。
検索先の 1 次元配列の文字列を指定します。

match

必ず指定します。
検索する文字列を指定します。

include

省略可能です。
引数 sourcearray に指定した配列の各要素の文字列の中に、引数 include が含まれるかどうかを表すブール値を指定します。
引数 include が真 (True) の場合、Filter 関数は、配列の各要素の文字列の中で、引数 match が含まれる配列のサブセットを返します。
引数 include が偽 (False) の場合、Filter 関数は、配列の各要素の文字列の中で、引数 match が含まれない配列のサブセットを返します。

compare

省略可能です。
文字列式を評価するときに使用する文字列比較のモードを表す数値を指定します。
設定する値については、次の「設定値」を参照してください。
Option Compare : ステートメントの設定を使用して比較を行います。
vbBinaryCompare : バイナリ モードの比較を行います。
vbTextCompare : テキスト モードの比較を行います。
※バイナリ モードでは、全角半角、大文字小文字が区別されます。
  テキストモードでは、全角半角、大文字小文字が区別されません。


引数 sourcearray 内で引数 match に一致する文字列がなかった場合は、Filter 関数は空の配列を返します。
引数 sourcearray が Null 値であるか、1 次元配列でない場合は、エラーになります。
Filter 関数が返す配列は、一致した項目数分だけの要素が含まれています。

Eraseステートメント

固定サイズの配列の場合は要素を再初期化し、動的配列の場合は割り当てたメモリを解放します。

Erase arraylist

静的数値配列 要素はすべて 0 に設定されます。
静的文字列配列 (可変長) 要素はすべて長さ 0 の文字列 (") に設定されます。
静的文字列配列 (固定長) 要素はすべて 0 に設定されます。
静的バリアント型 (Variant) 配列 要素はすべて Empty 値に設定されます。
ユーザー定義型配列 各要素は、別個の変数として設定されます。
オブジェクト配列 要素はすべて特別な値 Nothing に設定されます。

配列に関する記事の一覧

配列に関する記事は多数あります。
第111回.静的配列
・配列とは ・静的配列と動的配列 ・配列の宣言 ・多次元配列 ・要素の下限の変更 ・配列について
第112回.動的配列(ReDim)
・ReDimステートメント ・要素数の変更について ・配列について
第113回.配列に関連する関数
・LBound関数とUBound 関数 ・Array関数 ・IsArray 関数 ・Join関数 ・Filter関数 ・Eraseステートメント
第114回.セル範囲⇔配列
・セル範囲⇔配列の基本VBA ・使用例 ・配列およびマクロVBAの高速化に関するページ

以下は、配列の実践例になります。
最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。以前に、モジュール「顧客一覧へ登録」において、.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが、
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 以下の表で検証します。

1次元配列の並べ替え(バブルソート,クイックソート)
・検証方法 ・バブルソート ・挿入ソート ・クイックソート ・最後に
2次元配列の並べ替え(バブルソート,クイックソート)
・検証方法 ・バブルソート ・クイックソート ・複数キーでの並べ替えについて ・ワークシートを使って並べ替え・・・番外編
DIR関数で全サブフォルダの全ファイルを取得
・Dir関数でサブフォルダも含むファイル一覧を取得するVBA ・ファイル一覧を取得するVBAの使用例と解説 ・Dir関数の関連記事




同じテーマ「マクロVBA技術解説」の記事

配列の使い方について

・配列とは ・1次元の配列 ・2次元の配列 ・3次元以上の配列 ・動的配列 ・動的配列 ・動的配列の要素数の取得 ・配列使用時の注意
VBAの配列まとめ(静的配列、動的配列)
最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。以前に、モジュール「顧客一覧へ登録」において、.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが、
記述による処理速度の違い
・1.変数の型指定 ・2.罫線の引き方 ・3.行高の変更 ・4.配列を使用した処理 ・処理速度を早くする為には
速度比較決定版【Range,Cells,Do,For,ForEach】
何度も言っているのですが、RangeとCellsでどっちが早いか、とか、DoとForとFor Eachでどれが早いか とか、そもそも、その議論がナンセンスなんです。以下のコードと結果を見て、各自で判断して下さい。巷の議論が、いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。
エクセルVBAのパフォーマンス・処理速度に関するレポート
ExcelのマクロVBAは遅い・重いと良く言われることが多いようですが、マクロVBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。
VBAのFindメソッドの使い方には注意が必要です
・1.処理速度が遅い ・2.指定オプションがシート操作とリンクしている ・「値」で検索した場合は、表示形式に依存した検索になる ・最後に
マクロVBAの高速化・速度対策の具体的手順と検証
マクロVBAが遅い・重いという相談が非常に多いので、遅い・重いマクロVBAを高速化・速度対策する場合の具体的な手順をここに解説・検証します。マクロVBAの速度に関する記事は既にいくつか書いています。特に、以下はぜひお読みください。
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。2次元配列の場合、ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが、
大量データで処理時間がかかる関数の対処方法(SumIf)
・大量データで処理時間がかかるサンプルデータ ・普通にマクロVBAコ-ドを書いた場合 ・指定範囲を絞ってみる ・配列を使って書いてみる ・アルゴリズムを考えてみる ・Dictionary(連想配列)を使う ・大量データで処理時間がかかる関数の対処方法の最後に
大量データにおける処理方法の速度王決定戦
VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ