Excel VBA Array・配列の使い方(応用編)

今回は配列の使い方の応用編をご説明します。

配列の基礎については「Excel VBA 配列の使い方(基礎編)」をご覧ください。

応用編の以下の内容を記載します。

• シート上のデータと配列の共有
• 配列の要素数を取得する
• 配列の要素を追加・削除する
• 配列の列数(次元数)を取得する
• 配列をループして要素を操作

1.シート上のデータと配列の共有

配列にデータを格納したり出力するには、セル範囲を一括で格納/出力する方法と、ループで1行(列)ずつ格納/出力する方法があります。

順番にご説明します。

シート上のデータを一括で配列に格納する

シート上のデータを配列に一括で格納する方法は、「配列名=データ範囲」で格納出来ます。

サンプルデータとサンプルコードです。

Option Explicit

Sub Sample1()

    Dim myArray     As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol)) '配列に格納

End Sub

ここで注意するのが、配列のインデックスが1から始まっている事です。

基本的に配列のインデックスは0から始まりますが、セル範囲を一括で格納した場合は1から始まります。

また、宣言時に()が省略されていますが、Variant型で指定して、下記コート部分で配列と判定されています。

myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol))

配列の要素をセルに一括で出力する

配列に格納してあるデータを、シート上のセルに一括で出力する方法です。

基本的な書き方は同じですが、セルのデータを一括で格納した配列と、そうではないインデックスが0から始まる配列、それぞれのサンプルコードを掲載します。

インデックス1から始まるセルデータを一括で格納した配列

Sample1で格納した配列を出力するサンプルです。

A1~E11の表を格納した配列をG1～K11に出力します。

Sub Sample2()
    
    Dim myArray     As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol)) '配列に格納
    
    '==========ここから出力コード==========
    '開始列をG列に移動したため列を+7列追加
    '終了列を1列開けた+1列と表の5列で+6列追加
    Range(Cells(1, 7), Cells(MaxRow, MaxCol + 6)) = myArray

End Sub

配列の範囲と出力する範囲が合っていないとエラー値が出力されたり、一部しか出力されないといった原因になりますので、範囲はしっかり把握しましょう。

インデックス0から始まる配列

インデックス0から始まる配列を作るために、「可変長配列」で宣言して、ReDimステートメントで0から始まるように定義して、ループで表を配列に格納しています。

Sub Sample3()
    
    Dim myArray()   As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    Dim i           As Long
    Dim n           As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    ReDim myArray(0 To MaxRow - 1, 0 To MaxCol - 1)'0から始まるため行と列を-1
    
    For i = 1 To MaxRow '行ループ
    
        For n = 1 To MaxCol '列ループ
        
            myArray(i - 1, n - 1) = Cells(i, n) '配列は0から始まるためiとnを-1する
        
        Next n
    
    Next i
    
    '==========ここから出力コード==========
    '開始列をG列に移動したため列を+7列追加
    '終了列を1列開けた+1列と表の5列で+6列追加
    Range(Cells(1, 7), Cells(MaxRow, MaxCol + 6)) = myArray

End Sub

配列が0~10と0~4と定義されています。

結果はSample2と同じです。

2.配列の要素数を取得する

VBAでは配列の要素数を、直接取得する関数がないため、配列の要素数のLBound(最小値)関数とUBound(最大値)関数を使用します。

UBound関数で要素数取れると思いますが、インデックスが1から始まる場合は良いのですが、0から始まる場合は必ずしもUBound=要素数とは限らないため、以下のように算出します。

配列の要素=UBound(配列名)-LBound(配列名)+1

また、次元を指定した要素数を取得する場合は以下のように次元を指定します。

配列の要素=UBound(配列名,次元)-LBound(配列名,次元)+1

3.配列の要素を追加・削除する

要素の追加

要素の追加方法は、「ReDim Preserveステートメント」を使用します。

ReDimステートメントではそのまま使用すると配列の中身が初期化されるためです。

「ReDim Preserveステートメント」を使用する事で、すでに格納されているデータを初期化する事なく、追加する事が出来ます。

ここで、注意が必要です。

「ReDim Preserveステートメント」で要素を追加出来るのは、最終次元のみとなります。

2次元配列の場合、列方向にしか追加出来ないという事になります。

Sample1~Sample3の様に、縦に追加されるような形式で配列に格納すると、縦に要素を追加出来ない事態に陥ります。

ですので、動的に配列を操作する場合は基本的に行と列を入れ替えて格納する事をおすすめします。

Sub Sample4()
    
    Dim myArray()   As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    Dim i           As Long
    Dim n           As Long
    
    '最終次元に要素を追加できるように行と列を入れ替えて配列に格納
    ReDim myArray(0 To 4, 0 To 10)
    
    For i = 1 To 11 '行ループ
    
        For n = 1 To 5 '列ループ
        
            myArray(n - 1, i - 1) = Cells(i, n) '配列は0から始まるためiとnを-1する
        
        Next n
    
    Next i
    
    '==========ここから要素を追加==========
    'ReDim Preserveステートメントで配列の領域を追加
    ReDim Preserve myArray(0 To 4, 0 To 11)
    
    For n = 1 To 5 '列ループ
    
        myArray(n - 1, 11) = Cells(12, n) '追加された12行目を追加
    
    Next n

End Sub

4.要素の削除

配列の要素を削除する場合は、配列に要素を削除する関数がないため、単純にループで上詰めしていき、最終的に要素を差し引いた再定義をすることになります。

特定の要素を削除する場合も、配列の領域を変更する場合は最終次元のみ可能ですので、行列を入れ替えた配列を用意しました。

5番目の要素を削除します。

ループで要素を削除

Sub Sample5()

    Dim myArray()   As Variant
    Dim i           As Long
    Dim n           As Long
    
    '最終次元の要素を変更できるように行と列を入れ替えて配列に格納
    ReDim myArray(0 To 4, 0 To 10)
    
    For i = 1 To 11 '行ループ
    
        For n = 1 To 5 '列ループ
        
            myArray(n - 1, i - 1) = Cells(i, n) '配列は0から始まるためiとnを-1する
        
        Next n
    
    Next i
    
    '5番目の要素を削除します。
    For n = 0 To 4
    
        For i = 5 To 9
        
            myArray(n, i) = myArray(n, i + 1)
        
        Next i
    
    Next n
    
    ReDim Preserve myArray(0 To 4, 0 To 9)
    
End Sub

配列の要素数が0 to 10から0 to 9に削除されていて、「2019/01/05」が削除されています。

別の配列に入れ替える

これは上記のループは同じですが、削除する要素の条件が複雑になったり、削除する要素数が多くなった時に、上記のような方法よりも、別の配列に入れ替えた方が楽だと思います。

あくまで個人的ですが、自分はこちらの方法を使う方が多いです。

Sub Sample6()

    Dim myArray()   As Variant
    Dim NewArray()  As Variant'新しい配列を用意する
    Dim i           As Long
    Dim n           As Long
    Dim myInt       As Long
    
    '最終次元の要素を変更できるように行と列を入れ替えて配列に格納
    ReDim myArray(0 To 4, 0 To 10)
    
    For i = 1 To 11 '行ループ
    
        For n = 1 To 5 '列ループ
        
            myArray(n - 1, i - 1) = Cells(i, n) '配列は0から始まるためiとnを-1する
        
        Next n
    
    Next i
    
    'インデックスが偶数の要素を削除します。
    
    myInt = 0 '追加するインデックスを格納
    
    For i = 0 To 10
        
        If i Mod 2 = 0 Then 'インデックスが偶数の判定
        
            For n = 0 To 4 'インデックスが偶数の時のみ配列に格納
            
                ReDim Preserve NewArray(0 To 4, 0 To myInt) '最終次元をmyIntで変更(追加)する
        
                NewArray(n, myInt) = myArray(n, i)
                
            Next n
            
            myInt = myInt + 1 '1つ追加したらインデックスを+1する
        
        End If
    Next i
    
End Sub

myArrayの(0 to 4, 0 to 10)の偶数のみをNewArrayに追加しています。

NewArray(0 to 4 , 0 to 5)となり、偶数日のみが格納されました。

5.列数(次元数)を取得する

配列の要素はUBound関数とLBound関数を使用して取得出来ましたが、列数(次元数)は取得する関数がありません。

本来は列数(次元数)が範囲を超えてループしようとしたとき、エラーが発生します。

これを利用する事でエラーとなる列数(次元数)を取得する事で、配列の列数を取得できます。

ここまでで説明した通り、可変長配列を操作する上で列数(次元数)を取得する必要が発生する事が多々あります。

そのため、 列数(次元数)を取得するコードは重宝します。

Sub Sample7()

    Dim myArray()   As Variant
    Dim ErrInt      As Variant
    Dim i           As Long
    
    myArray = Range(Cells(1, 1), Cells(11, 5)) '配列に格納

    On Error Resume Next 'エラーを無視する
    
    Do While Err.Number = 0 'エラーが発生するまでループ
    
        i = i + 1 '次元数を加算していく
        
        ErrInt = UBound(myArray, i)
        
    Loop
    
    On Error GoTo 0
    
    MsgBox "配列の次元数は" & ErrInt
    
End Sub

6.配列をループで操作する

配列を操作する場合、要素のインデックスを指定して操作します。

一つの特定の要素を操作する場合は、その要素のインデックスを指定するだけで良いですが、

多数の要素を扱う場合はループ処理を行います。

ループ処理は基本的にセルをループする書き方と同じです。

ループがよくわからない場合は「ExcelVBA 「For~Next」「Do~Loop」 ループの使い方」をご覧ください。

配列をループする場合、その配列の行と列の関係性で、要素数の取得方法が異なります。

詳しくは目次の「2.配列の要素数を取得する」と「5.列数(次元数)を取得する」をご覧下さい。

For~Nextで操作する

LBoundとUBoundを使用したループ

すでにサンプルで使用してますが、下記データを配列に格納してループします。

下記データのA店売上を合計します。

Sub Sample8()
    
    Dim myArray     As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    Dim i           As Long
    Dim mySum       As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol)) '配列に格納
    
    '================ここからFor～Nextループ==================
    'A店売上を合計します。
    mySum = 0
    
    For i = LBound(myArray) To UBound(myArray) '配列の最小値から最大値までループ
    
        If IsNumeric(myArray(i, 2)) = True Then '項目を加算するとエラーとなるため数字かどうか判定
    
            mySum = mySum + myArray(i, 2) 'ループしながら数字あれば加算
            
        End If
    
    Next i
    
    MsgBox "A店の売上の合計は" & mySum & "円です。"

End Sub

列(次元数)を取得してループ

列に長いデータを使用してループします。

B店売上の合計を計算します。

Sub Sample9()
    
    Dim myArray     As Variant
    Dim ErrInt      As Long
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    Dim i           As Long
    Dim mySum       As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol)) '配列に格納
    
    '================ここから列数を取得==================
    
    On Error Resume Next 'エラーを無視する
    
    ErrInt = 0
    
    Do While Err.Number = 0 'エラーが発生するまでループ
    
        i = i + 1 '次元数を加算していく
        
        ErrInt = UBound(myArray, i)
        
    Loop
    
    On Error GoTo 0
    
    '================ここからFor～Nextループ==================
    'A店売上を合計します。
    mySum = 0
    
    For i = 1 To ErrInt '配列の最小値から最大値までループ
    
        If IsNumeric(myArray(3, i)) = True Then '項目を加算するとエラーとなるため数字かどうか判定
    
            mySum = mySum + myArray(3, i) 'ループしながら数字あれば加算
            
        End If
    
    Next i
    
    MsgBox "B店の売上の合計は" & mySum & "円です。"

End Sub

For Each In Nextで操作する

For Each In Nextは配列を操作する場合は、配列全てを操作することになります。

列行ともに条件を指定するなど分岐処理などで制御することになります。

今回は複雑なことをせず全店の売り上げを合計します。

赤線はFor Each In Nextの処理される順番です。

Sub Sample10()
    
    Dim myArray     As Variant
    Dim Arr         As Variant
    Dim MaxRow      As Long
    Dim MaxCol      As Long
    Dim i           As Long
    Dim mySum       As Long
    
    MaxRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行を取得
    MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得
    
    myArray = Range(Cells(1, 1), Cells(MaxRow, MaxCol)) '配列に格納
    
    '================ここからFor Each In Nextループ==================
    '全店の売上を合計します。
    mySum = 0
    
    For Each Arr In myArray '配列全てループ
    
        If IsNumeric(Arr) = True Then '項目を加算するとエラーとなるため数字かどうか判定
    
            mySum = mySum + Arr 'ループしながら数字あれば加算
            
        End If
    
    Next
    
    MsgBox "A店の売上の合計は" & mySum & "円です。"

End Sub

7.まとめ

基本編と応用編と長く説明してきましたが、配列は複雑で慣れるまで扱うのが難しいと思います。

ですが、一度慣れてしまうと非常に便利です。

VBAの処理速度のボトルネックが、セルへのインプット、アウトプットです。

配列を使うことで、IOの回数を極力減らすことで、処理を高速にする事も可能です。

今回の2記事でも全てではないので、徐々に追加掲載していきます。