カテゴリー: 配列など

'配列データを出力する
Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select
Selection = zz配列

配列データの中でも、セル範囲を取得した場合の出力にはResizeプロパティを使用することで比較的簡単に配列データのデータ数に合わせたセル範囲指定を行うことが出来ます

例コードでは、動きを分かり易くするために一旦選択を行っていますが、実際の使用時は選択は行わず直接データを出力すればいいです

実際の使用例

Dim zz配列 As Variant

zz配列 = Range("A1").CurrentRegion

Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select

Selection = zz配列

このコードを使用して解説を行います
後半2行は例コードをそのまま使用しています

実際の動きを先に確認しておきます
このコードを実行すると、A1セルを含む表範囲を配列として取得して、データをD1セルから同じ範囲サイズに出力します

Dim zz配列 As Variant

最初に変数の宣言を行っています
セル範囲を直接配列データに代入する場合は、Valiant型の変数を宣言します
またこの際、静的配列は指定できませんので例の様に特に配列指定まで行わなくても問題ありません

zz配列 = Range("A1").CurrentRegion

次に、宣言した変数にA1セルを含む表範囲のデータを代入させます
表範囲の取得にはCurrentRegionプロパティを使用すると簡単に取得できます
また、この時点で配列データが作成され要素数も全て自動的に定義されています

Range("D1").Resize(UBound(zz配列), UBound(zz配列, 2)).Select

配列データを出力する場合は、セル範囲が配列データの要素数と一致する必要があります
要素数を超える範囲選択をするとはみ出した部分はエラー値が入力されてしまいます
少ない場合は当然、データが全て出力されません

そこで配列データに合わせたセル範囲選択を行います
その際にResizeプロパティを使用するのが便利です

Resizeプロパティの引数の行数・列数にはUBound関数を使用して要素数の最大値を取得することで判定できます
通常、要素数の最大値は要素数と一致しない場合が多いです
それは下限値の違いによるものです

しかし、セル範囲を代入させた場合は下限値が1から始まるので関数で返される数値をそのまま使用することができます

セル範囲の配列データは2次元配列なので、その2次元目までの要素数を取得させます
2次元目の要素数はUBound関数の引数の2つめに次元数を指定することで取得できます
省略すると1次元が指定されますので、1つ目の関数では省略していますが2つめは省略せず「2」を指定しています
これで行数・列数を配列データと同じサイズのセル範囲を選択できます

Selection = zz配列

選択範囲に配列データを出力します
ここは、上記にも書いたように選択は行わずに出力するようにするほうが処理速度が速いので動きの理解が出来たら、その様に変更してもいいです

このコードでは配列データを加工していないのでセルデータのコピーを行っている形になります
しかし、配列データに書式データは含まれませんので値の貼り付けを行ったような動きになります

ちなみに、この方法はコピーして値の貼り付けをするより処理速度の向上が図れます
クリップボードを使用しないし、データ以外の情報も扱わないためです

なので、値貼り付けを実行したい場合にセル範囲が大きい場合はこのコードで配列としてデータを扱うと良いです

'セルデータの一括取得
Dim セルデータ As Variant
セルデータ = Range("A1:C3")

Valiant型の変数に、Rangeオブジェクトを代入することでそのセル範囲のデータを2次元配列データとして一括代入させることができます

Rangeオブジェクトを代入すると、Rangeオブジェクト自体が代入されるように感じる人もいるかもしれません
しかし、この代入にはLetステートメントが省略されており、本来省略しない形は以下のようになります

Let セルデータ = Range("A1:C3")

このLetステートメントは値の代入として認識されるため、セル範囲を指定してもRangeオブジェクトが代入されることはありません

逆にRangeオブジェクトを代入する場合には、Setステートメントを使用します

話を戻しますが、このように一括代入を行うと配列データに２次元配列としてセルのデータそのものが代入されます

この表のセルデータを一括で代入させます

実際に2次元配列に取得された配列データです

これをそのまま他のセル範囲に一括出力することも出来ます
その場合は、Copyメソッドのほうが分かりやすいのであまり意味はありませんが可能です

ここで取得したデータを別の２次元配列に加工しながら取得させて、最終的にその加工した２次元配列をセル範囲に一括出力します

こうすることで、セル上で加工を行わない分、処理の高速化が図れますし、元のセルデータを改変してしまうこともありません

使用時に注意するべきこと

注意点として、この一括代入した場合には配列の開始要素数が1から始まってしまいます

上記の画像の青色になっている行を確認してください
要素数が「1 to 3, 1 to 3」になっています
3行3列なので、１～３までの配列要素数で定義されています

通常、配列は0から始まるため
このセル範囲配列から通常の配列に代入させる場合には注意が必要です

どうしても0から始めたい場合は、新たに要素数を定義した動的配列に代入し直す必要がありますが、それはそれで面倒な処理なので、要素数を合わせるほうが楽です

また、セル自体がValiant型のように様々なデータが入っているので、Valiant型で宣言します
というか、この配列代入自体Valiant型でしか実行できません

型を限定したい場合は、この配列を加工して代入させる配列を特定の型指定を行えば可能ですが、ほぼ利点は無いと思います

'2次元配列の宣言
Dim 多次元配列(3, 5) As String

多次元配列とは、通常の配列は1次元で表現されるのに対し、2次元以上の配列が多次元配列となります

この次元というものは、現実世界でいう次元とはイメージが違います
次元というのはデータの集合体であり、そのグループ分けされたものが別次元です

もはや、訳が分かりませんね
実際に、コードによって生成された変数を確認すると少し分かり易いです

ローカルウィンドウの画像です
例コードのある処理を実行した際に、2次元の配列が作成されます

青の行は変数名なので、これ以下のものが実際のデータ範囲です

すぐ下に「多次元配列(0)」があります、その中にはさらに6つの変数が存在しています
ようするに、次元数とは階層数と同じようにイメージしてもらうと画像と一致します

また、型の列を確認すると、青の行では「String(0 to 3,0 to 5)」となっています

これは要素のインデックスの下限と上限が表示されています
つまり、1次元は「0,1,2,3」の4要素、2次元は「0,1,2,3,4,5」の6要素が作成されていることが分かります

下限は変更可能ですが、常に「0」で考えるようにしてください
これは、この配列データの個数を数える場合に影響があることや、標準の設定をわざわざ変更することによるコードの可読性低下などが理由です

これに関しては変更するコードを書かなければ、常に「0」になるので、あまり意識しなくてもいいと思います

そして、1次元の「3」や2次元の「5」というインデックス最大値を取得するのがUbound関数です、これを利用して配列データの個数は取得します

この次元数自体は、どんどん増やしていくことが可能です

Dim 多次元配列(3, 5, 8) As String

この様に、引数の数値を増やしていくだけです
これで、3次元配列が作成されます

ただ、次元数が増えすぎると管理が大変になるため、実用性で考えると2次元で十分かな
3次元ぐらいまでなら、なんとか管理できるけど、4次元以上になるとかなり複雑な配列になるのでコードも組むの大変でバグめっちゃ出そうやので、自分はしません

なにより、3次元以上はセルへの一括出力ができません
ワークシートは縦と横の2次元で表現されているためです

2次元配列は、セルへの一括出力が可能です

また、この多次元は静的、動的配列のどちらにも使用可能で使用方法もそれぞれの宣言に引数の数値をコードの様に記載するだけです

再定義時の注意点

多次元配列使用時は、Preserveキーワードでの再定義が行えません
最後の次元数は変更可能ですが、それ以前の次元数の再定義は行えません

ReDim 動的配列(5, 5, 5)

まず、最初にこのコードで3次元のそれぞれに要素数を定義します
その後、再定義を行う際に注意する必要が出てきます

ReDim Preserve 動的配列(5, 5, 6)

→→→　実行可能

この様に最後の次元数の変更は可能です
当然、キーワードを使用しているので既存のデータは保持されています

ReDim Preserve 動的配列(5, 6, 5)
ReDim Preserve 動的配列(6, 5, 5)

→→→　実行不可

こちらのように、最後の次元数以外の変更は出来ません
実行時エラーが発生します

このキーワードを使用せずに次元数を再定義することは可能ですが、既存のデータは初期化されてしまいます

多次元配列の動的使用時は、どの次元数がどの時点で確定するかをしっかり見極めたうえで使用する必要があります

'配列の最大数を取得
Debug.Print UBound(配列変数)

配列変数のインデックスの最大値を取得するには「UBound」関数を使用します

引数「arrayname」は、配列変数名を指定します
引数「dimension」は、取得する次元数を数値で指定します。省略時は1次元が指定されます。多次元配列を使用する際に、指定する引数です

関数の解説

基本的には、配列データの個数を取得するために使用しますが、この関数の戻り値がそのまま個数にはならない点には注意が必要です

と、いうのも配列データの下限値は任意に変更することが出来ます
下限値を3に設定してUbound関数で5が取得された時、要素数は3,4,5の3つとなります
通常は0から始まるため、Ubound関数の戻り値に1を加算すれば個数になります

こういったことからも、下限値はあまり変更しない方がいいと思います
当サイトは下限値の設定は行わず、常に0であると想定して解説しています

主には動的配列使用時に、要素を新規追加したりループの回数指定に使用したりします
要素のインデックス最大値なので、データの個数はそれに＋１したものになります

また下限値を取得するには「LBound」関数を使用しますが、基本的に下限値を「0」以外に設定する処理は解説もしていませんので記事としての解説は割愛しておきます
使い方は全く同じ使い方をします

Dim zz配列 As Variant
'アクティブセルのある行のデータ範囲を配列に代入
zz配列 = Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow).Formula

'配列データの加工
zz配列(1, 3) = #5/15/2020#
zz配列(1, 4) = 600
zz配列(1, 6) = #5/22/2020#
zz配列(1, 7) = 900

'配列データの出力
Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow) = zz配列
'配列データの初期化
Erase zz配列

配列データをセルに出力する場合、そのセル範囲は連続している必要があります
連続していない場合は、その途切れた範囲ごとにデータの1個目から入力されていきます

例えば、以下のような表があるとします

ピンクに色付けされた所に数値を入力する処理を作成します
上記にもあるように連続していないセル範囲で飛ばして配列データを出力することはできません
配列データはピンク以外の列のデータも含んでおく必要があります

この表に出力したい配列数が5個のデータを入力したいセルだけを選択状態にして出力するとします
なお、配列データは「100,200,300,400,1000」だと想定します

連続していないセル範囲に配列データを出力すると、画像の様に選択範囲のそれぞれからデータが入力されていくため、思い通りの数値が指定セルに入力されていないのが分かります
AB列は要素0（100）と1（200）が代入され、DE列も要素0と1が代入され、G列は要素0が代入されている状態になっています

本来入力したい形は、客先B_1には「300」、客先B_2には「400」、合計には「1000」を入力したかったわけです
それが実行できていません

また、客先ごとに備考列が必要なため削除して連続セルにすることはできません
この例では客先数が2つですが、これが10や50となってくるとすさまじく面倒な入力になります

これは、どうにもなりませんでした
調べても一切出てこない内容で、なかなか悩みましたが、とりあえず解決方法として考えたのが、「入力しないセルも配列データに含める」ということでした

追記：調べても出てこない、というよりそもそもセル範囲を先に取得しておけば事足りるため、その程度のことがあえて解説されていなかったのかもしれません

例コードの前提と動き

コード解説の前にまずは前提条件の説明と動きを確認します

今回の前提として、A列は年月の日付データが入っています
この表は、各月ごとを1行として、客先数分の列データが追加されていくような表データです

B列はその月の合計金額が表示されます
今回は数が少ないので簡単にSUM関数で入力金額を合計しているだけです
これが数十件以上あるような場合はSUMIF関数にする形になるでしょうね

C・D列が客先1つ目の日付と金額データです
E列はなんらかの備考情報です
この3列が客先数分作成される表となります
今回は2つしか作成していません

この画像で、実際の動きを確認してください
今回は5月分のデータを入力します

配列が作成され、それを加工して出力するところまでの動きをなんとなく追ってみてください

コード解説

Dim zz配列 As Variant

最初に配列データを代入する変数宣言を行います
今回はセル範囲を先に取得するので、静的配列は使用できません
動的配列を指定する必要があります、ですがセル範囲代入の場合は自動的に動的配列として認識されるので変数名だけで問題ありません

また、セル自体がどんなデータも受付するのでそれを取得するためにはValiant型で宣言しておきます

zz配列 = Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow).Formula

宣言した変数にセル範囲のデータを代入します
この時にセルオブジェクトの代入をしてしまわないように、Setステートメントは使用しないように注意してください
この代入は値の代入になるので、省略またはLetステートメントを使用する必要があります

代入元のセル範囲の指定ですが、まず表範囲全体を取得するCurrentRegionプロパティを使用して、さらにIntersectメソッドを使用してその範囲とアクティブセルのある行全体の重複する部分を取得します

つまり、表内のアクティブセル行のデータ範囲を取得します

そして、今回ここが一番重要な部分といってもいいかもしれませんが、取得する際のセル範囲のプロパティには注意が必要です

今回はFormulaプロパティを使用しています
これはセルデータのうちの数式を取得するプロパティです

今回の表データにはSUM関数が入力されていますので、通常のValueプロパティで取得を行うと、SUM関数の結果の数値が取得されてしまい
後で出力する際に、値に振り替えられ関数がなくなってしまいます

そのため、取得するセル範囲に数式が含まれている場合はFormulaプロパティを使用するようにしてください
なお、このプロパティは数式ではない場合は値を取得するので基本的にはどのデータを取得する際も問題はありません

ただ問題があるとすれば空白セルが「””」と取得される点と日付データは実データが文字列で取得されている点です
Valueプロパティで取得した場合、空白はEmpty値という特別な値になりますが、こちらでは「””」という文字列として取得されます
この辺りを処理上、判定が必要な場合は注意してください

ただ、便宜上取得して、それを出力するだけならセルが自動的に判定してくれるので問題はありません

配列データに取得させる場合のセルのプロパティには要注意です

zz配列(1, 3) = #5/15/2020#
zz配列(1, 4) = 600
zz配列(1, 6) = #5/22/2020#
zz配列(1, 7) = 900

ここは、取得した配列データの加工の部分です
今回は決め打ちの値を代入していますが、実際の処理時はここで色々な計算を行ったり、取得したデータを代入させます

この時、配列はメモリ上に展開されますので画像の様にローカルウィンドウを表示して、その配列データを展開しておくと分かりやすいです

またセル範囲の配列データは2次元配列になります
2次元目が列数になるので、今回は3・4・6・7行のデータを入力するので、その要素数の配列に代入しておきます

Intersect(Range("A1").CurrentRegion, ActiveCell.EntireRow) = zz配列

加工した配列データの出力です
出力は基本的に取得した範囲をそのまま使用するので、取得時の左辺と右辺を入れ替えるだけで処理可能です
そのため、おおよその処理ではセル範囲も変数に代入しておくと処理が実行しやすくなります

Erase zz配列

最後に配列データを初期化します
配列データはデータが大量に入っているため、1つ1つに初期値の代入を行ったりはしません
Eraseステートメントを使用して初期化します
なおこのステートメントでは、値だけでなくオブジェクトを参照していたとしても問題ありませんので、配列データの初期化にはこのステートメントを使用します

'配列データをセル範囲に一括出力
Range("A2:C3") = bh配列データ

配列データを一括で出力するには、指定セル範囲に配列変数名を代入するだけで可能です

配列データはメモリ上での操作で取得・加工を行うため、その部分は少し難しい内容になりますが、出力は非常に簡単にできますし、セルの更新自体も1回で済むので処理が高速になります

実際の使用例

Dim bh配列データ(99) As Long, bhインデックス番号 As Long

For bhインデックス番号 = 0 To 99
bh配列データ(bhインデックス番号) = Range("A1").Offset(0, bhインデックス番号) * 1.1
Next bhインデックス番号

Range(Range("A2"), Range("A2").Offset(0, 99)) = bh配列データ

配列データを計算をしてから代入していき、計算が終わってから一括で出力します

こうすることで、1セルずつ処理を実行していくよりも遥かに早く出来ます
例コードでは、1行目の100セルに10%上乗せした数値を計算して出力しています
ようするに、税抜き金額から税込み金額を計算して出力している形です

まずは、コード実行前と後の結果画像を確認します

コード実行前の状態です
100セルを表示することはできませんので、SUM関数で合計金額を表示します
D3には、計算結果が正しいかを分かり易くするために、答えを先に表示しています

この状態でコードを実行すると、2行目に計算結果金額が出力されます

コード実行後の状態です
100セル全ての計算を行い、セルへの入力は1回のみなので瞬時に終わります
金額が答えのセルと一致していることも確認してください

ループ処理により1セルずつ行っていった場合、自分の環境では10倍速度が違いました
セルの個数が増えれば、その幅はどんどん大きくなっていきます

このように、ループ処理で計算を行っていけば出来るような処理でも配列を使用すると非常に高速に処理できることがわかります

コードの解説

Dim bh配列データ(99) As Long, bhインデックス番号 As Long

まず配列データの変数宣言です
ここでは静的配列として宣言しています、個数が確定している場合は静的配列の方が便利です

For bhインデックス番号 = 0 To 99

Forループの開始コードです
要素数分ループを実行します

bh配列データ(bhインデックス番号) = Range("A1").Offset(0, bhインデックス番号) * 1.1

左辺は、配列データのインデックス番号をForループの変数値で指定しています
右辺は、A1セルからForループの変数値分Offsetで移動しながら、そのセル数値に「1.1」を乗算した数値を代入しています

ここで、実際の計算が行われていますが、セルへの出力は行われていないため
このループ処理自体は非常に高速に終了します

Next bhインデックス番号

Forループの終了コードです、次のインデックス番号に移動します

Range(Range("A2"), Range("A2").Offset(0, 99)) = bh配列データ

ここで一括出力を行っています

配列で一括代入する場合は、要素数と同じセル範囲を指定する必要があります
なので、ここではA2セルからOffsetを使用して99セル右に移動したところまでを範囲指定しています

ここで、要素数未満であった場合はその選択範囲分データが出力され
超えていた場合は「＃N/A」が出力されます

後は、配列変数名だけでデータ全ての指定が可能です

'配列を代入して選択範囲に一括出力
Dim 配列 As Variant
配列 = Array(1, "+", 2, "=", 3)
Selection = 配列

Array関数とは、配列を作成する関数です

引数「arglist」にはデータを指定します、例の様に要素を「,」で区切って入力していくことで要素数を増やすことが出来ます

配列について

この配列というのは変数での使用が主になります
オブジェクト変数と同じくらい理解が難しい内容なので、この記事はある程度VBAの理解が出来てからで大丈夫です

配列というのは複数の要素からなるデータ群のことです

Array関数で作成する配列は1次元の配列になります
この次元というのは、1次元が一方向へのデータの集合体となりますので、2次元は2方向へのデータの集合体という認識になります

2次元の配列というのは、要はExcelのワークシートと同じイメージです
Excelのワークシートは縦と横にデータを入力していくことができるデータ群です
なのでArray関数で作るデータというのは、Excelのワークシートの行や列方向へのデータの作成ということになります

この画像は配列を代入させて、それを選択範囲に一括で出力しています
コード最後の「Selection = 配列」で一括出力ができます
ここでの配列は5要素を持ち、整数型と文字列型の混在した配列データとなっています
なおArray関数で代入する場合は、要素が全て文字列型で統一されていたとしても代入する変数はVariant型にする必要があります
（その辺の解説はこちらの記事を見てください）

また、上記にもあるようにArray関数で作成するのは1次元配列なので選択範囲が3行あっても1行ごとの内容はすべて同じになっています
そして、配列の要素数以上のセルには「#N/A」が入力されます

そしてこの配列が扱うデータというのは、数値や文字列だけでなくオブジェクトも扱いますので、シートやセルなども配列の要素にすることができます
特にシートの複数選択の際にはArray関数を使用してシートの指定を行います
これに関してはマクロの記録でワークシートの複数選択を行った際に、自動的にArray関数が作成されていることで確認できます
なおこれはシート名の文字列型を配列データとして扱っており、シート自体を配列として取得しているわけではありません

配列を使う理由について

この配列という考え方は、正直なくても処理の作成は全く問題なく行うことができます

シートの複数選択にしても、シートごとに1枚ずつ処理をしていけばいいだけなので配列を使用しなくても処理を作ることは可能です

ですがこの配列というものをあえて使う目的は、配列のデータをメモリ上で操作することが可能になる事です

例えばシートの複数選択に関して言えばシートごとに処理を行う場合はシートを切り替えながら処理をしていく必要があります
それを複数選択で、一括処理を行えばシートの切り替えが必要なくなり処理速度の向上を図れます

画面更新の抑止や再計算を停止する方法での処理速度の向上の方法がありますが、メモリ上でデータを更新し一括でデータを出力することで、これらの処理速度向上の処理を行わずとも圧倒的に速く処理を行うことができるようになります

この圧倒的に速くという言葉通り、二つの方法を使用できる技量があれば選択の余地がないほど処理速度に差があります
記事中程の画像の動きでもそうですが、セル1つ1つに入力するより一括入力を行う方が速いです

VBAの初心者を脱した辺りで、処理速度に興味がいった時点で考えるのがセル選択を減らすことです、セル選択はそれだけで重たい処理になります
その次にこの出力回数を減らすことを考えてみてください
そのためによく使用するのがこのArray関数です

'Array関数を使用して、配列を一括代入する
Dim 動的配列() As Variant
動的配列 = Array(1, 2, "あ", "か", ActiveCell)

配列変数は同じ役割の変数を複数使用するために使いますが、代入する際に大量にある要素を1つずつ代入するのももったいない場合があります

そんな場合に一括で代入する方法として「Array」関数を使用します
Array関数は、配列を作成して返す関数です
ただし、この方法を使用する場合は動的配列であり、なおかつ型をvariant型で宣言する必要があります

実際のコードの動き

関数で配列を作成し、それを一括で代入します
また使用することは無さそうですが、画像の様にvariant型なので型の違うデータやオブジェクト型もまとめて代入させることもできます

要素0と1はInteger型、2と3はString型、4はRange型となっています

なお、Array関数での代入に関しては要素数宣言が必要ありません
画像の様にRedimを使用していないのに、要素数が自動的に追加されて代入されます、便利っすねぇ

'配列変数を初期化する
Erase 配列変数

配列変数を初期化するには「Erase」を使用します

配列変数名のみ指定しますので、「()」や要素数の指定は必要ありません

このコードを使用すると、配列の宣言時点に戻ります
なので、静的配列はそのまま宣言時の要素数を使用できます
しかし、動的配列は要素未定状態に戻ってしまうので、そのまま再利用することが出来ませんので「Redim」による要素数宣言が必要です
（動的配列の要素数宣言方法はこちら）

なお、Object型は「Nothing」に戻してくれるので、Object型のメモリ解放を個別に行う必要はありません

'動的配列の宣言
Dim 動的配列() As Long

'動的配列要素数の宣言
ReDim 動的配列(5)

'動的配列の要素数の宣言（代入済みデータ保持）
ReDim Preserve 動的配列(8)

配列とは、同じ変数名を複数同時に宣言することができる変数です
動的配列は、要素数が宣言時点では未定の状態で処理中に可変させることの出来る配列変数です

通常の変数の宣言方法とほぼ同じで、変数名の後に「()」を追加するだけです
この時に、なにも「()」の中には入れないことで動的配列となります

ただ、このままでは要素が無い状態なので使用はできません
「ReDim」を使用して要素数の宣言をすることで使用可能になります

画像の動きを見てください。
1回目の「ReDim 動的配列(2)」で、配列変数が使用可能になっています
そして2つの要素に値を代入させていますが、2回目の「ReDim 動的配列(5)」では代入させた値がクリアされてしまっています

「ReDim」で要素数の宣言を行うと、それまでに代入したデータは初期化されます

既存のデータはそのままに、あらたに要素数を追加してデータを追加させたい場合には「ReDim Preserve」を使用します

3回目の要素数宣言の「ReDim Preserve 動的配列(8)」の時点ではそれまでのデータはクリアされていません

処理が始まらないと要素数が分からない場合に、動的配列は使用します
ですが、要素数の確定するタイミング次第でこの要素数宣言の使い分けを行います

要素数の最大値がすぐに取得できる場合は、「ReDim」を使用します
要素数をループなどで随時追加していく場合には、「ReDim Preserve」を使用します

なお、「ReDim」でデータをクリアできますが、その目的では使用しないようにしてください、専用のコードがちゃんとあります
（配列変数のデータを初期化する方法）