Mutable_Yunの業務改善ブログ

業務改善や生産性向上のブログです。自動化の手段として、VBAやRPAの勉強に役立つ解説をしています。

VBA 動的二次元配列を実務で使う :行方向に要素を追加したいときは、最後に縦横変換する

配列は有効に使いこなせていますか。
以前、 VBAで作ったマクロの高速化① 配列を使うで、セルへの書き込み時間短縮のためには配列を利用するのが効果的である、と言う話をしました。

今回は転記したい範囲が予めわからない場合の対応について考えていきたいと思います。


この記事は中級~上級です。
レベルについてはExcel VBAの実力(レベル)を定義してみる 初心者~三段をご参照ください。


 

目次

動的二次元配列とは

 

動的二次元配列と言うと名前が難しそうですが、名前ほど難しくありません。
配列の要素数が動的に変わることを前提とした二次元の配列と言うだけです。

マクロの高速化①では、シートの範囲を直接Variant型の変数に代入すると、エクセルが勝手に二次元配列に置き換えてくれると言う裏技(?)的な使い方をしています。


本来は、配列は下記の様に定義します。

Sub 静的2次元配列()
    Dim サンプルarr(3, 5) As Variant
End Sub

これは静的二次元配列の宣言です。実際のデータがどのように格納されているかはさておき、イメージとしてはワークシートのイメージで良いです。

( )の中に一次元、二次元の最終の要素番号を記述します。要素のインデックス番号は0から始まるので、要素数を指定しているわけではない点に注意です。

なお、静的とは、一度配列の形、つまりそれぞれの要素数を決めたら後から変更しないと言うことです。

確かめます。

Sub 静的2次元配列()

    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ステートメントを記述して、マクロを中断します。

結果
f:id:mutable_yun:20190917191847p:plain
UBoundは配列の上限のインデックス番号を取得します。
LBoundは下限です。

二つ目の引数である1と2は次元数です。1次元目の上限が3下限が0、2次元目の上限が5、下限が0である事が確認できました。

せっかくStopで止めたので、配列もローカルウィンドウで確認します。

f:id:mutable_yun:20190917192221p:plain
ローカルウィンドウで確認。

下限が0から始まっているので、1次元と2次元はそれぞれ4つ、6つの要素が存在していることが確認できました。

しかし、静的に配列を宣言すると後から配列の形を変更することはできません。
実務では後から配列の要素数を変更できると便利な場合があるので今回はその方法と実例を見ていきます。

 

動的二次元配列の宣言と要素数の変更

動的二次元配列の宣言は、このように記述します

Sub 動的2次元配列()
    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

 結果
f:id:mutable_yun:20190917193313p:plain

「こんにちは」が一回しか表示されませんでした。
これは、一回目のDebug.Printです。
ちなみに、2回目のRedimでRedim(3, 5)と全く同じ形の変数の形を宣言しても
中身は消えてしまいます。

Redimの互換の通りRe(再度)新しく定義と思えば納得です。

これを防ぐためには、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

f:id:mutable_yun:20190917200203p:plain
「こんにちは」が2回表示された。
 
ここで注意は、2次元目の方の値しか変更できないと言う事です。
3次元以上にも対応できる言い方をすると、VBAでは最終次元の値しか変更できません。

ネット上の質問で、配列をワークシート的に使いたい人が、最終次元しか大きさを変更できないと知って諦める所を見かけたことがありますが、ここではその解決まで具体的に見ていきます。

サンプルで実務的な内容の実装

それでは実際に実装していきます。百聞は一見にしかず、です。
今回の題材は下記のようなモノです。

 

f:id:mutable_yun:20190917201438p:plain
今回のサンプルデータ:0以上50未満の数がA列に1,000行並んでいる


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で割り切れるときだけ、jをカウントアップする
        End If

    Next i
    
    Stop
    
End Sub

いつものように最後の直前でStopを記述しましたので、イミディエイトウィンドウで確認します。

f:id:mutable_yun:20190917203943p:plain
配列の中身

狙い通り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

実行結果は下記の通りです。

f:id:mutable_yun:20190917204101p:plain
動的2次元配列を使って3の倍数のみ別シートに抽出できた。

ポイントは、下記の部分です

    Dim 縦横変換用arr() As Variant
    ReDim 縦横変換用arr(UBound(サンプル3arr, 2), 0)

縦横変換用arrは1次元の最終インデックス番号が分かっているので、
静的に宣言しようとして Dim 縦横変換用arr(UBound(サンプル3arr, 2), 0) とやることはできません。

静的配列なのに可変な変数や式を入れることは矛盾しているからです。よって、
動的に宣言してすぐにRedimで配列の形を決めています。

For~Nextの部分は1次元と2次元の要素番号を入れ替えているだけです。

これで実務的なコードができました。
配列をマスターすると、マクロの実行が高速化できるので、このように表で行方向にデータを追加していきたい時は、最後に縦横変換をするようにしましょう。