ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化 について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

VBA 動的二次元配列を実務で使う :行方向に要素を追加したい時の解決方法

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

結果は下のキャプチャの通りです。
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 動的二次元配列()
    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で配列の形を変更したときに初期化されて配列に格納された値が決めてしまうことを防ぐためには、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次元の要素番号を入れ替えているだけです。

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

VBA動的2次元配列で行方向に洋装を追加したい時の方法まとめ

  • 動的二次元配列とは2次元目の要素数が変わる事を前提として宣言された二次元配列のこと
  • ReDimで配列の形を変えると配列の要素が初期化されてしまう
  • 配列の要素の値を保ったまま形をへんこうするにはPreserveを使う
  • 動的多次元配列では最大次元の要素数しか変更できない
  • 二次元配列では列方向の要素数を変更し、縦横変換を行うことによって配列を行方向に増やしたのと同じ事が実現できる
  • 二次元配列の縦横変換を行うにはFor分を使って行と列の値を入れ替える

最大次元の要素数しか変更できないと聞いて、配列を行方向に要素を増やすのを諦めてしまうのではなく、どうやったらできるのか、と考える事によって解決方法を見いだすことができます。頑張って行きましょう!

<関連記事>