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

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2018-02-08

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


VBAで配列を必要とするのは、処理速度を上げる為だと言えます、
そもそも、エクセルにはセルの2次元配列であるシートがあります。


にもかかわらず、VBAの学習を進めると必ず配列が出てきます、

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

VBAの配列では、シートのセル範囲を一括で配列に入れたり、
配列を一括でシートに出力することで、高速なVBAを実現することが出来ます。

VBAで配列を使いこなせるようになることは、VBA習得の1つの大きな目標と言えます。


配列の概念

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

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

通常の変数 VBA マクロ 画像

配列    VBA マクロ 画像


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

配列のインデックス番号の最小値は、通常は0になります。
これを、
Option Base 1
のように指定することで変更可能ですが、あまり使用しない方が良いでしょう。

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

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

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



静的配列

配列の宣言
Dim ステートメント、またはPrivate ステートメント、Public ステートメントで宣言します。

Dim MyArray(10) As Long

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

値を入れる時は、

MyArray(0) = 1

このように使用します。

以下の例では、配列に1から順に数値を入れています。

Sub sample()
  Dim MyArray(10) As Long
  Dim i As Long
  For i = 0 To 10
    MyArray(i) = i
  Next i
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 i
  Next i
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次元で済むはずです。
それ以上の次元は、むやみに複雑化させるだけです。



セル範囲⇔配列の基本

セル範囲を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 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

単に、100000行の掛け算をしています。

セル範囲とやり取りする配列は、必ず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 が指定されたものと見なされます。

使用例.
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
  For j = LBound(MyArray, 2) To UBound(MyArray, 2)
    MyArray(i, j) = 0
  Next j
Next i

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

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

Array(arglist)

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

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

使用例.
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回.静的配列
配列は、値を格納するために多くの区画を持つ1つの変数です、集合住宅やアパートにたとえられますが、要は、変数の箱を複数つなげたものと理解すれば良いでしょう。1つの箱には、1つの値しか入れられませんが、その箱が複数つながっていますので、結果として、複数の値をいれられるのが配列という事になります。
第112回.動的配列(ReDim)
静的配列では配列の要素数は宣言時点で決められていましたしかしプログラミングをする上で実行時点で要素数を決めたい場合や実行途中で増やしたい場合が多くあります。実行時点で要素数を増減できるのが動的配列になります。ReDimステートメント 動的配列変数に対するメモリ領域の再割り当てを行います。
第113回.配列に関連する関数
配列を使う上で必要となるVBA関数がいくつかありますより便利に配列を活用するために必須となるVBA関数について解説します。LBound関数 配列の指定された次元で使用できる最小の添字を長整数型(Long)の値で返します。LBound(arrayname[,dimension]) arrayname 必ず指定します。
第114回.セル範囲⇔配列
セル範囲をVariant型変数に入れる事で、配列を作成することができます、また、配列をセル範囲にまとめて入れる事も出来ます。これは、VBAを高速処理したい時の必須テクニックになります、マクロVBAの処理が遅い場合は、このテクニックが使えないか検討してください。

以下は、配列の実践例になります。

最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。顧客管理は幅が広く、業種によっても様々です。まずは、ごく一般的なものを作成していきます。出来れば、「お客様第一」顧客管理システムに近い物ができれば良いと思います。
WorksheetFunction.Matchで配列を指定した場合の制限について
WorksheetFunctionでMatchを使いデータ検索する事は良くあります。この時他の部分の記述との関係で配列を指定してMatchを行う事があります。以下のようなVBAコードになります。Subsample1() DimiAsLong DimaryAsVariant ary=Range(_A1:A10000_) ・・・i=WorksheetFunc…
マクロ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大量データからのVLOOKUPを大量行に設定すると再計算がなかなか終わらなくなります…そんな経験した…

1次元配列の並べ替え(バブルソート,クイックソート)
配列(1次元)の並べ替え方法について、バブルソートとクイックソートのサンプルになります。元来エクセルには、ワークシートの並べ替え機能があります。ワークシートにデータを書き出して、ワークシートの並べ替え機能を使えるのですが、どうしても、配列をワークシートに途中で書き出すと言うのは面倒なものです。
2次元配列の並べ替え(バブルソート,クイックソート)
配列(2次元)の並べ替え方法についてバブルソートとクイックソートのサンプルになります。2次元配列の並べ替えと言えばまさにワークシートの並べ替え機能になります。本来はワークシートにデータを書き出してワークシートの並べ替え機能を使えば良いのですがしかしどうしても配列をワークシートに処理途中で書き出すと言うのは面倒なものです。
DIR関数で全サブフォルダの全ファイルを取得




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

配列の使い方について
VBAの配列まとめ(静的配列、動的配列)
最終行の判定、Rangeオブジェクトと配列、高速化の為に
記述による処理速度の違い
速度比較決定版【Range,Cells,Do,For,ForEach】
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(SumIf)


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

IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)
Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)
VBA+SeleniumBasicで検索順位チェッカー作成|VBA技術解説(5月18日)
テーブル操作のVBAコード(ListObject)|VBA入門(5月12日)
テーブル操作の概要(ListObject)|VBA入門(5月12日)
VBAのスクレイピングを簡単楽にしてくれるSelenium|VBA技術解説(5月6日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.ひらがな⇔カタカナの変換|エクセル基本操作
7.繰り返し処理(For Next)|ExcelマクロVBA入門
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄


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

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


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






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