【VBAの配列】値の一括代入と高速処理

2024-07-21

通常の変数では格納できるのは１つです。

同じ種類の値を複数扱いたい場合、扱いたい値の数の変数を使うと大変です。

このような場合に配列を使うと一気に変数を格納できます。

また処理速度が速くなります。

配列の基本

例えば次の3つの値を格納する場合、3つの要素を持つ1つの配列を宣言します。

りんご
みかん
すいか

（1）配列変数「str」の配列の宣言します。

Dim str(3) As String

（2）配列変数に値を格納します。

str(0) = "りんご"
str(1) = "みかん"
str(2) = "すいか"

配列の要素は「0」から始まります。

格納した要素をセルに書き出す

セルA1～A3に書き出す場合の例です。

配列名のかっこ（）の中にインデックスの数を指定します。

Dim str(1 To 3) As String

Sub sample1()
    Dim str(3) As String
    Dim i As Long
    
    str(0) = "りんご"
    str(1) = "みかん"
    str(2) = "すいか"
    
    Range("A1").Value = str(0)
    Range("A2").Value = str(1)
    Range("A3").Value = str(2)
End Sub

書き出す値が多い場合は上記の方法だと大変です。次の For～Nextステートメントを使うと手間が省けます。

Sub sample1_2()
    Dim str(3) As String
    Dim i As Long
    
    str(0) = "りんご"
    str(1) = "みかん"
    str(2) = "すいか"
    
    For i = 1 To 3
        Cells(i, 1).Value = str(i - 1)
    Next i
End Sub

要素は0から始まるので、For～Nextステートメントを使いシートに書き出す処理の場合、「i-1」にします。

シートの行の開始1行目（変数i）と合わせたい場合は、次の2つの方法があります。

Option Baseステートメント

宣言セクションに「Option Base 1」と記述すると、配列の下限値を「1」に設定できます。

何も設定しない場合：0
指定できる値：0または1

Option Base 1
Sub sample1_3()
    Dim str(3) As String
    Dim i As Long
    
    str(1) = "りんご"
    str(2) = "みかん"
    str(3) = "すいか"
    
    For i = 1 To 3
        Cells(i, 1).Value = str(i)
    Next i
End Sub

配列の宣言で下限値を設定

配列名のかっこ（）の中にインデックスの下限値と上限値を指定します。

Dim str(1 To 3) As String

Sub sample1_4()
    Dim str(1 To 3) As String
    Dim i As Long
    
    str(1) = "りんご"
    str(2) = "みかん"
    str(3) = "すいか"
    
    For i = 1 To 3
        Cells(i, 1).Value = str(i)
    Next i
End Sub

格納した要素をメッセージで表示

格納した要素をメッセージで表示する場合の例です。

Sub sample2()
    Dim str(3) As String
    
    str(0) = "りんご"
    str(1) = "みかん"
    str(2) = "すいか"
    
    MsgBox str(0)
    MsgBox str(1)
    MsgBox str(2)   
End Sub

For～Nextステートメントを使う例です。

Sub sample2_2
    Dim str(3) As String
    Dim i As Long
    
    str(0) = "りんご"
    str(1) = "みかん"
    str(2) = "すいか"
    
    For i = 1 To 3
        MsgBox str(i - 1)
    Next i
End Sub

配列の応用・配列の宣言を簡単にする

基本の方法の場合、配列にひとつづつ要素を代入していくので手間がかかります。

Array関数・LBound関数・UBound関数

3つの関数を使うことで、要素の代入と出力が簡単にできます。

Array関数

配列名のかっこ（）の中にインデックスを指定しません。
変数はVariant型で宣言します。

Dim str() As Variant
str = Array("りんご", "みかん", "すいか")

LBound関数・UBound関数

要素の出力時に配列のインデックスの最小値と最大値を指定する関数です。

LBound関数：最小値
UBound関数：最大値

  
Sub sample3()
  Dim str() As Variant
  str = Array("りんご", "みかん", "すいか")
  Debug.Print "LBound:" & LBound(str)
  Debug.Print "UBound:" & UBound(str)
End Sub

上記の実行結果です。

最小値：0
最大値：1

For～Nextステートメントを使う例です。

Option Base 1
Sub sample3_2()
    Dim str() As Variant
    Dim i As Long
    
    str = Array("りんご", "みかん", "すいか")
    
    For i = LBound(str) To UBound(str)
        Cells(i, 1).Value = str(i)
    Next i
End Sub

シートの値をまとめて代入

Array関数を使う方法でも、データ数が多い場合は大変です。シートに入力している値を代入する例です。

配列名のかっこ（）の中にインデックスを指定しません。
変数はVariant型で宣言します。
Range(“A1”).CurrentRegionで一気に要素を代入します。

Sub sample4()
    Dim str() As Variant    
    str = Range("A1").CurrentRegion
End Sub

上記を実行した結果です。

シートと同じく2次元で代入されることがわかります。

一番最初の要素「りんご」は０ではなく、str(1,1)です。シートの行番号と配列のインディックスが一致するのでわかりやすいです。

出力処理も含めた例です。B列に出力します。

Sub sample4_2()
    Dim str() As Variant
    Dim i As Long
    
    str = Range("A1").CurrentRegion

    For i = LBound(str) To UBound(str)
        Cells(i, 2).Value = str(i, 1)
    Next i
End Sub