Mutable_Yunの業務改善ブログ

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

VBA 配列を徹底的に解説する (全5回) ⑤動的配列

VBA 配列を徹底的に解説するシリーズもいよいよ最終回です。本当は一つのブログで完結するのがいいとは思いますが、徹底的に解説する以上、10個くらいに分けざるを得ませんでした。ユーザビリティ・・・

<これまでの配列の解説>
VBA 配列を徹底的に解説する (全10回) ①イメージをつかむ
VBA 配列を徹底的に解説する (全10回) ②静的配列
VBA 配列を徹底的に解説する (全10回) ③静的配列の初期化と実務的なサンプル
VBA 配列を徹底的に解説する (全10回) ④多次元配列

目次

動的配列とは

これまで見てきたのはすべて静的配列でした。静的配列は初めに決めたインデックスが変更できないものでした。動的とはこれが変えられる、ということです。

ワークシートに存在するデータの行数がわからないので、最終行を取得するのに変数を使うように、配列も動的にできます。

VBA 配列を徹底的に解説する (全10回) ③静的配列の初期化と実務的なサンプルでは、ワークシートのデータをいきなり配列に格納する裏技を解説しました。これがわかっていれば強力な武器になるので、もはや動的配列は不要な気がしませんか。

でも、必要なのです。ある条件を満たした行のみ別シートに転記する、というような場合です。条件を満たすかどうかはあらかじめ分からないため、動的配列を用意して、条件を満たす行が現れた場合のみ動的配列を一つ大きくして格納していく、という作業が必要です。

動的配列を作成する

それではさっそく動的配列を作成していきましょう。

Sub 初めての動的配列()

    Dim 動的arr() As Variant

End Sub

簡単ですね。Variant型の変数にカッコを付けるのは静的配列と同じです。違うのは、カッコの中が空欄であることです。

動的配列は配列の大きさがあらかじめ分からないので、配列の宣言の証拠であるカッコはつけるものの、配列のサイズは宣言時に指定しません。

動的配列は配列のサイズが決まり次第、再定義します。上のコードにRedimを追記します。

Sub 初めての動的配列2()

    Dim 動的arr() As Variant
    ReDim 動的arr(2, 4) As Variant

End Sub

いつものようにF8で一行ずつステップイン実行していくと、Redimのところで配列の形が決まる様子をローカルウィンドウで確認できます。

f:id:mutable_yun:20190923071452p:plain
Redimで配列の形が決まることが確認できた。

動的配列の形を変更する

Redimの使いどころは、初めて配列の形を決める時に限りません。
形を変えたいときはいつでもRedimで変更することができます。

先ほどのコードの下にさらに一行追加します。

Sub 初めての動的配列3()

    Dim 動的arr() As Variant
    ReDim 動的arr(2, 4) As Variant
    ReDim 動的arr(2, 5) As Variant
    
End Sub

いかがでしょうか。こちらもローカルウィンドウをにらみながらF8でステップイン実行してみてください。VBA 配列を徹底的に解説する (全10回) ④多次元配列では、2次元配列はイメージ的にインデックスが、ワークシートの行列の番号にそっくりである旨を言及しました。

それでは行方向のインデックス数を追加してみましょう。

2次元配列で1次元(ワークシートのイメージの行方向)の最大インデックスを変えたい場合

今回のサンプルはこれです。

支店リスト。動的配列に格納する。
支店リストサンプル

やりたいこととしては、大阪支店の行だけ別シートに転記したいというもの。
動的配列など使わなくてもオートフィルタを使ってコピペする方法や普通に転記する方法も考えられます。

今回は7行しかないデータから2行転記するだけなので、どんな方法でもよいですが、何十万行から何万行も転記する実務レベルの作業ではセルに書き込むのは時間がかかりますし、オートフィルタを使う方法は後で元に戻さなくてはなりません。

シートには手を付けず、配列の中で処理を済ませてしまいましょう。

Sub 動的配列の実務サンプル()
    
    Const 列数 As String = 4
    
    Dim 大阪支店を格納するarr() As Variant
    ReDim 大阪支店を格納するarr(0, 列数) As Variant
    
End Sub

はい、これで動的配列が準備できました。まず大きさを指定しないカッコ付きのVariant型の変数を宣言して、直後に列数をして大きさを決めています。

値を保存するPreserveと動的配列の初期化

静的配列では要素を初期化するためにはそれぞれのインデックスにEmptyを格納する必要がありました。(覚えていますか?)

動的配列ではRedimで配列の形を変更するとその瞬間に中身が消えます。簡単に初期化が可能です。

Sub 動的配列の初期化()
    
    Const 列数 As String = 4
    
    Dim arr() As Variant
    ReDim arr(0, 列数) As Variant
    
    arr(0, 2) = "Hello"
    
    ReDim arr(1, 列数) As Variant
    
End Sub

動的配列の形を変更すると、中身が消えてしまう
中身のHelloが消えている

今回のサンプルでは大阪支店をどんどん追記していきたいので、中身が消えてしまうと困ります。その時にPreserveステートメントを追記します。

この時、最大の難関が待ち受けます。先ほどは行方向(一次元目)のインデックスを0から1に変更しましたが、Preserveをつけると、なんと最終次元しか変更できなくなります。

Sub 動的配列の初期化()
    
    Const 列数 As String = 4
    
    Dim arr() As Variant
    ReDim arr(0, 列数) As Variant
    
    arr(0, 2) = "Hello"
    
    ReDim Preserve arr(1, 列数) As Variant 'ここでエラーになる
    
End Sub

Redimは初期化するというより、新たに配列を作り直す概念です。なので、形は自由に作り直せるのですが、元の値を維持しようとすると新たに配列を作り直すのではなく、変更、という概念になります。

2次元配列はワークシートの行列のイメージで良い、と言いましたがこれを理解するには実際の値がどのように格納されているかを少し勉強する必要があります。結論だけ覚えていても構いませんが、少し触れます。

再掲:二次元配列のイメージ

2次元配列のイメージはワークシートの行列で構わない
2次元配列のイメージ

これがVBA 配列を徹底的に解説する (全10回) ④多次元配列で解説した二次元配列のイメージでした。

しかし実際はこのようになっています。こん

本当は多次元配列はこうなっている
本当のイメージ

一次元目の数値は左から何番目の部屋か。二次元目の数値はその中の何番目か、ということです。

たとえばこの例でいえば、「h」は左から3番目の部屋にいるので、インデックスは0からはじまるので一次元目のインデックスは2です。「h」はそのインデックス2の(g,h,i)の中の2番目なのでインデックス1です。よって、hはゆん荘(2,1)です。

一次元目の数は実は、全体の配列の大きさを決めているのでした。Redimは中身を保持する時は各部屋に格納する大きさは自由にしていいから、全体の配列の大きさを変えるのは勘弁してくれ、ということです。

少し長くなりました。本題に戻ります。行方向が追加できない、ということは列方向にデータを追記していくことになります。

ということは、最後に縦横変換するということ。つまりワークシートでは列方向に延びているヘッダは、配列内では行方向に伸ばしていく必要があります。これを踏まえて実装していきましょう。

Sub 動的配列の実務サンプル()
    
    Const 列数 As Long = 3 '配列内では行的に扱う
    Dim 最終行rw As Long  'ワークシートの売上データの行数
    Const データ開始行rw As Long = 2 'ループの最初の行
    Dim i As Long 'ワークシートのループ内のカウントアップ用

    
    Dim 大阪支店を格納するarr() As Variant
    ReDim 大阪支店を格納するarr(列数, 0) As Variant
    
    最終行rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    '>大阪支店を格納する
    For i = データ開始行rw To 最終行rw
        If Cells(i, 1) = "大阪支店" Then
            大阪支店を格納するarr(0, UBound(大阪支店を格納するarr, 2)) = Cells(i, 1)
            大阪支店を格納するarr(1, UBound(大阪支店を格納するarr, 2)) = Cells(i, 2)
            大阪支店を格納するarr(2, UBound(大阪支店を格納するarr, 2)) = Cells(i, 3)
            大阪支店を格納するarr(3, UBound(大阪支店を格納するarr, 2)) = Cells(i, 4)
            
            ReDim Preserve 大阪支店を格納するarr(列数, UBound(大阪支店を格納するarr, 2) + 1)
        End If
    Next i
    '<大阪支店を格納する
    
    Dim 縦横変換arr() As Variant
    Dim j As Long
    ReDim 縦横変換arr(UBound(大阪支店を格納するarr, 2), UBound(大阪支店を格納するarr, 1))
    '>縦横変換をする
    For i = 0 To UBound(大阪支店を格納するarr, 1)
        For j = 0 To UBound(大阪支店を格納するarr, 2)
            縦横変換arr(j, i) = 大阪支店を格納するarr(i, j)
        Next j
    Next i
    '<縦横変換をする
    Stop
    
End Sub

結果

f:id:mutable_yun:20190923084046p:plain
縦横変換することで、結果的に行方向に要素を加えることができた


VBAでは行方向に配列のインデックスを追加できないからと言ってあきらめるのではなく、縦横変換すればできる、ということに着目することが重要です。

直接できなくても、工夫すれば解決方法はあるものです。頑張っていきましょう。