Excel

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

2021-06-30

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

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

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

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

配列の基本

例えば次の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    
    

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

    ローカルウィンドウ

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

    一番最初の要素「りんご」は0ではなく、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
    

    参考

    この他に2次元配列がわかると処理の幅が広がります。

    -Excel

    © 2021 オフィスのQ&A