VBAの配列は有効に使いこなせていますか？セルへの書き込みによるマクロの実行時間を削減するには、配列を利用するのが効果的です。詳しくは VBAで作ったマクロの高速化① 配列を使うで解説しています。
今回は転記したい範囲が予めわからない場合の対応方法を解説します。具体的には動的配列を使う方法を説明します。
目次
動的二次元配列を行方向に要素を追加したいときの解決策
動的二次元配列とは
動的二次元配列と言うと名前が難しそうですが、名前ほど難しくありません。配列の要素数が動的に変わることを前提とした二次元の配列と言うだけです。動的というのは後から配列の形が変わるという意味です。配列自体が初めての方は、
VBA 配列を徹底的に解説する (全5回) ①イメージをつかむからの5回の連載を、2次元配列が分からない方は、VBA 配列を徹底的に解説する (全5回) ④多次元配列をご参照頂ければ理解を深めることができます。
マクロの高速化①では、シートの範囲を直接Variant型の変数に代入すると、エクセルが勝手に二次元配列に置き換えてくれると言う裏技(？)的な使い方をしています。
本来は、配列は下記の様に定義します。
Sub 静的2次元配列() Dim サンプルarr(3, 5) As Variant End Sub
これは静的二次元配列の宣言です。実際のデータがどのように格納されているかはさておき、イメージとしてはワークシートのイメージで良いです。
( )の中に一次元、二次元の最終の要素番号を記述します。要素のインデックス番号は0から始まるので、要素数を指定しているわけではない点に注意です。なお、静的とは、一度配列の形、つまりそれぞれの要素数を決めたら後から変更しないと言うことです。
確かめます。
Sub 静的二次元配列() Dim サンプルarr(3, 5) As Variant Debug.Print UBound(サンプルarr, 1) Debug.Print LBound(サンプルarr, 1) Debug.Print UBound(サンプルarr, 2) Debug.Print LBound(サンプルarr, 2) Stop End Sub
Debug.Printはイミディエイトウィンドウにその中身を表示するために使います。イミディエイトウィンドウは 表示⇒イミディエイトウィンドウ で表示できます。マクロの実行が終わる前にローカルウィンドウで配列の状況を確認したいので、End Subの直前にStopステートメントを記述して、マクロを中断します。
結果は下のキャプチャの通りです。
UBoundは配列の上限のインデックス番号を取得します。LBoundは下限です。二つ目の引数である1と2は次元数です。1次元目の上限が3下限が0、2次元目の上限が5、下限が0である事が確認できました。
せっかくStopで止めたので、配列もローカルウィンドウで確認します。
下限が0から始まっているので、1次元と2次元はそれぞれ4つ、6つの要素が存在していることが確認できました。
しかし、静的に配列を宣言すると後から配列の形を変更することはできません。実務では後から配列の要素数を変更できると便利な場合があるので今回はその方法と実例を見ていきます。
動的二次元配列の宣言と要素数の変更
動的二次元配列の宣言は、以下のように記述します。
Sub 動的二次元配列() Dim サンプルarr() As Variant End Sub
Variant型の変数の宣言時に、( )の中の記述を省略するだけです。
( )をつけることによってこのVariant型の変数が配列である事を宣言しつつ、配列の形はまだ分からないので、( )の中は空白にしてある、という感じです。
要素数が決まった時や変更するときは下のように記述します。
ReDim サンプル2arr(3, 5)
簡単ですね。インデックスの上限が3，5などと決まった時点でRedimで配列の大きさを与えればOKです。ここで分かっていてもつい忘れがちなのが要素数を変更すると中身が初期化されてしまう、と言う事です。
Sub 動的2次元配列() Dim サンプル2arr() As Variant ReDim サンプル2arr(3, 5) サンプル2arr(1, 1) = "こんにちは" Debug.Print サンプル2arr(1, 1) ReDim サンプル2arr(4, 6) Debug.Print サンプル2arr(1, 1) End Sub
結果はこの通りです。
「こんにちは」が一回しか表示されませんでした。これは、一回目のDebug.Printです。ちなみに、2回目のRedimでRedim(3, 5)と全く同じ形の変数の形を宣言しても中身は消えてしまいます。Redimの互換の通りRe（再度）新しく定義と思えば納得できるのではないでしょうか。
このようにRedimで配列の形を変更したときに初期化されて配列に格納された値が決めてしまうことを防ぐためには、Redimの後にPreserveをつけて下記の様にします。
Sub 動的2次元配列() Dim サンプル2arr() As Variant ReDim サンプル2arr(3, 5) サンプル2arr(1, 1) = "こんにちは" Debug.Print サンプル2arr(1, 1) ReDim Preserve サンプル2arr(3, 6) Debug.Print サンプル2arr(1, 1) End Sub
ここで注意は、2次元目の方の大きさしか変更できないと言う事です。3次元以上にも対応できる言い方をすると、VBAでは最終次元の要素数しか変更できません。
そして、ネット上の質問で、配列をワークシート的に使いたい人が、最終次元しか大きさを変更できないと知って諦める所を見かけたことがあります。しかし、工夫によって解決する事ができます。次項からその解決を具体的に解説します。
サンプルで実務的な内容の実装
一通り文法の解説が済んだところで、実際に実装していきます。今回の題材は下記のようなものです。
A列に0以上50未満のランダム整数が1,000行並んでいます。A列の値に対して、3で割り切れる場合のみ、配列に格納し、別シートにペタッと貼り付けます。どの行が3で割り切れるか分からないのと、最終的に何行を転記する事になるのか分からないので、動的配列を使う事にします。
Sub 動的2次元配列を使って転記() Dim サンプル3arr() As Variant Dim 最終行rw As Long 最終行rw = Cells(Rows.Count, 1).End(xlUp).Row ReDim サンプル3arr(0, 0) As Variant Dim i As Long '元データの行のカウントアップ用 Dim j As Long '配列の2次元目のインデックスのカウントアップ用 j = 0 '明示 For i = 1 To 最終行rw 'Mod演算子は割り算の余りを返す。割り算の時の「/」の代わりに「Mod」と記述する If Cells(i, 1) Mod 3 = 0 Then ReDim Preserve サンプル3arr(0, j) ’最終次元の大きさしか変更できないので、列方向を大きくする サンプル3arr(0, j) = Cells(i, 1) j = j + 1 'jは3で割り切れるときだけ、ｊをカウントアップする End If Next i Stop End Sub
最後の直前でStopを記述しましたので、イミディエイトウィンドウで確認します。
狙い通り3の倍数のみ配列に格納されています。問題はここからです。このままシートに戻すと横にズラーッと並んでしまいます。どうすれば良いかというと、配列の縦と横を入れ替えれば良いです。
配列を縦横変換する
先ほどのコードのStopを消して、そこに下記を追記します。
'~~省略~~ Dim k As Long Dim 縦横変換用arr() As Variant ReDim 縦横変換用arr(UBound(サンプル3arr, 2), 0) 'このようにする。Dim 縦横変換用arr(UBound(サンプル3arr, 2), 0) は不可 For k = 0 To UBound(サンプル3arr, 2) 縦横変換用arr(k, 0) = サンプル3arr(0, k) Next k Sheets(2).Activate Range(Cells(1, 1), Cells(UBound(サンプル3arr, 2) + 1, 1)) = 縦横変換用arr] End Sub
実行結果は下記の通りです。
ポイントは、下記の部分です
Dim 縦横変換用arr() As Variant ReDim 縦横変換用arr(UBound(サンプル3arr, 2), 0)
縦横変換用arrは1次元の最終インデックス番号が分かっているので、静的に宣言しようとして Dim 縦横変換用arr(UBound(サンプル3arr, 2), 0) とやることはできません。
静的配列なのに可変な変数や式を入れることは矛盾しているからです。よって、動的に宣言してすぐにRedimで配列の形を決めています。
For～Nextの部分は1次元と2次元の要素番号を入れ替えているだけです。
これで実務的なコードができました。配列をマスターすると、マクロの実行が高速化できます。このように表で行方向にデータを追加していきたい時は、列方向に要素を追加していって、最後に縦横変換をするようにしましょう。
VBA動的2次元配列で行方向に洋装を追加したい時の方法まとめ
- 動的二次元配列とは2次元目の要素数が変わる事を前提として宣言された二次元配列のこと
- ReDimで配列の形を変えると配列の要素が初期化されてしまう
- 配列の要素の値を保ったまま形をへんこうするにはPreserveを使う
- 動的多次元配列では最大次元の要素数しか変更できない
- 二次元配列では列方向の要素数を変更し、縦横変換を行うことによって配列を行方向に増やしたのと同じ事が実現できる
- 二次元配列の縦横変換を行うにはFor分を使って行と列の値を入れ替える
最大次元の要素数しか変更できないと聞いて、配列を行方向に要素を増やすのを諦めてしまうのではなく、どうやったらできるのか、と考える事によって解決方法を見いだすことができます。頑張って行きましょう！
＜関連記事＞
＜ゆんの電子書籍＞ゆんの電子書籍はすべてkindle unlimitedで読み放題です!
Kindle Unlimitedは読み放題！
Kindle Unlimitedは月額定額制で漫画や小説、雑誌が読み放題のサービスです。私の著書もすべてKindle Unlimitedで読み放題です。電子書籍は購入すると著者の取り分が低下の３５％の書籍は９９円以上、著者の取り分が７０％なら２５０円以上の値段をつける必要があります。そう考えると２５０円の本なら４冊読めば元が取れる計算です。
私自身Kindle Unlimitedのヘビーユーザーです。私のKindle Unlimitedの使い方は個人が出している読み放題対象の本はどうせ読み放題なのでパンフレット的にざっと目を通して概要を把握するというものです。そして概要が分かってさらに深く知る必要があるという場合には紙の本を買うという具合です。
どうせ読み放題なので立ち読み感覚で目を通せるのが良いところです。
会員をやめるのも簡単なので使わない月は解除するのもアリです。私も会員になったりやめたりメリハリをつけて使っています。
Kindle Unlimitedで読み放題は下のリンクから簡単に可能です。
