VBAで配列に値を代入する方法｜ Excel作業をVBAで効率化

データをまとめて扱うには配列を扱う

VBAを使っていると、データをまとめて扱いたい場面が多々あります。その代表的な方法が配列の利用です。

VBAプログラミングにおいて、配列は複数のデータを効率的に管理するための重要な機能です。

配列は複数の値を一つの変数に格納できる入れ物であり、繰り返し処理やデータの一括処理に欠かせません。しかし、配列への値の代入は基本的な操作でありながら、様々な方法と注意点があります。

この記事では、特に「配列への代入」に焦点を当て、基礎から応用まで具体的に解説します。

1. VBAにおける配列とは

配列とは、Integerなどの数値型や文字列のString型など、同じデータ型の値をまとめて扱う仕組みです。Variant型であれば違うデータ型の値を持つことも可能ですが、通常はこのような使い方をすることはほとんどありません。

配列には大きく分けて以下の2種類があります。

静的配列：あらかじめ要素数を固定する
動的配列：処理の途中でサイズを変更できる

代入方法やコピーの扱い方は、この2種類で少し異なります。

2. 静的配列への代入

静的配列は宣言時に要素数を決めてしまう方法です。

「Dim nums(4)」の4の部分が要素数を表しますが、配列自体の要素は4＋1＝5個のデータが格納できます。これで配列にいくつデータを入れるかを指します。

Sub StaticArraySample()
    Dim nums(4) As Long  ' 添字は0～4の合計5要素
    nums(0) = 10
    nums(1) = 20
    nums(2) = 30
    nums(3) = 40
    nums(4) = 50
    
    MsgBox nums(2)  ' 30が表示される
End Sub

Sub StaticArraySample()

Dim nums(4) As Long ' 添字は0～4の合計5要素

nums(0) = 10

nums(1) = 20

nums(2) = 30

nums(3) = 40

nums(4) = 50

MsgBox nums(2) ' 30が表示される

End Sub

静的配列では個別に 配列名(添字) = 値 という形で代入します。

ループを使えば効率的に代入できます。

Sub LoopAssign()
    Dim i As Long
    Dim nums(4) As Long
    
    For i = 0 To 4
        nums(i) = (i + 1) * 100
    Next i
    
    ' nums(0)=100, nums(1)=200 ... nums(4)=500
End Sub

Sub LoopAssign()

Dim i As Long

Dim nums(4) As Long

For i = 0 To 4

nums(i) = (i + 1) * 100

Next i

' nums(0)=100, nums(1)=200 ... nums(4)=500

End Sub

3. 動的配列への代入

動的配列は、最初はサイズを決めずに宣言し、ReDimで後からサイズを指定します。

「Dim nums()」で括弧の中に数字を入れていないことが動的配列を意味します。

Sub DynamicArraySample()
    Dim nums() As Long
    Dim i As Long
    
    ReDim nums(4)  ' 添字0～4
    
    For i = 0 To 4
        nums(i) = (i + 1) * 10
    Next i
    
    MsgBox nums(4)  ' 50が表示される
End Sub

Sub DynamicArraySample()

Dim nums() As Long

Dim i As Long

ReDim nums(4) ' 添字0～4

For i = 0 To 4

nums(i) = (i + 1) * 10

Next i

MsgBox nums(4) ' 50が表示される

End Sub

さらに ReDim Preserve を使うと、既存の値を保持したまま配列サイズを変更可能です。元の配列サイズより増やせば、それより前の添え字の配列の値は保持されますが、Redim Preserveで元の配列サイズより小さい要素数にすると、縮小対象になった配列位置の値は配列自体が無くなるため値も無くなります。

Sub ResizeArray()
    Dim nums() As Long
    Dim i As Long
    
    ReDim nums(2)
    nums(0) = 100
    nums(1) = 200
    nums(2) = 300
    
    ReDim Preserve nums(4)  ' 添字0～4になる
    nums(3) = 400
    nums(4) = 500
    
    MsgBox nums(4)  ' 500
End Sub

Sub ResizeArray()

Dim nums() As Long

Dim i As Long

ReDim nums(2)

nums(0) = 100

nums(1) = 200

nums(2) = 300

ReDim Preserve nums(4) ' 添字0～4になる

nums(3) = 400

nums(4) = 500

MsgBox nums(4) ' 500

End Sub

4. 配列を一括代入する方法

個別代入だけでなく、まとめて代入する方法もあります。

4-1. Array関数を使う

Array 関数を使えば、Variant型にまとめて代入可能です。

Sub ArrayFunction()
    Dim arr As Variant
    arr = Array("A", "B", "C", "D")
    
    MsgBox arr(2)  ' "C"（添字は0始まり）
End Sub

Sub ArrayFunction()

Dim arr As Variant

arr = Array("A", "B", "C", "D")

MsgBox arr(2) ' "C"（添字は0始まり）

End Sub

4-2. ワークシート範囲を配列に代入

セル範囲をそのまま配列に代入できます。

Sub RangeToArray()
    Dim arr As Variant
    arr = Range("A1:C3").Value  ' 3行×3列の2次元配列
    
    MsgBox arr(2, 3)  '// 2行目の3列目であるC2の値が表示される
End Sub

Sub RangeToArray()

Dim arr As Variant

arr = Range("A1:C3").Value ' 3行×3列の2次元配列

MsgBox arr(2, 3) '// 2行目の3列目であるC2の値が表示される

End Sub

A1:C3のセル範囲には9個のセルがありますが、行＋列の二次元配列で格納され、arr(1, 2)であれば、1行目の2列目を指します。

通常の配列と異なり、0開始ではなくセル範囲のRangeの考え方の1開始になり、セルの座標を扱う感覚で直感的に配列を利用できます。

Cellsプロパティを使って、「Cells(1, 1)」のように「Cells(行, 列)」でセル指定するコードを書くことがありますが、それと同じように座標指定します。

5. 配列同士の代入・コピー

5-1. 動的配列やVariant配列は代入可能

動的配列の場合や、Variant配列は=で直接代入が可能です。

Sub CopyArray()
    Dim arr1 As Variant, arr2 As Variant
    arr1 = Array(10, 20, 30)
    
    arr2 = arr1
    MsgBox arr2(1)  ' 20
End Sub

Sub CopyArray()

Dim arr1 As Variant, arr2 As Variant

arr1 = Array(10, 20, 30)

arr2 = arr1

MsgBox arr2(1) ' 20

End Sub

5-2. 静的配列同士は代入不可

静的配列の場合は=での直接代入することはできません。エラーになります。

Sub StaticCopyError()
    Dim a(2) As Long
    Dim b(2) As Long
    
    a(0) = 100
    ' b = a  ← エラーになる
End Sub

Sub StaticCopyError()

Dim a(2) As Long

Dim b(2) As Long

a(0) = 100

' b = a ← エラーになる

End Sub

静的配列へ代入するにはループでコピーする必要があります。

Sub CopyStaticArray()
    Dim a(2) As Long
    Dim b(2) As Long
    Dim i As Long
    
    a(0) = 100: a(1) = 200: a(2) = 300
    
    For i = 0 To 2
        b(i) = a(i)
    Next i
End Sub

Sub CopyStaticArray()

Dim a(2) As Long

Dim b(2) As Long

Dim i As Long

a(0) = 100: a(1) = 200: a(2) = 300

For i = 0 To 2

b(i) = a(i)

Next i

End Sub

配列のコピーについては「VBAで配列を別の配列にコピーする方法」をご参照ください。

6. 配列とSplit関数、Join関数

6-1. 文字列を配列に分割（Split関数）

Split関数を使うと文字列を分割してVariant型の配列に格納されます。

Split関数の詳細は「VBA関数：指定文字で分割して配列にする（Split）」をご参照ください。

Sub SplitExample()
    Dim txt As String
    Dim arr As Variant
    txt = "apple,orange,banana"
    
    arr = Split(txt, ",")
    MsgBox arr(1)  ' "orange"
End Sub

Sub SplitExample()

Dim txt As String

Dim arr As Variant

txt = "apple,orange,banana"

arr = Split(txt, ",")

MsgBox arr(1) ' "orange"

End Sub

6-2. 配列を文字列に結合（Join関数）

Join関数を使うと配列の全要素を連結して１つの文字列にすることができます。

Join関数の詳細は「VBAで配列の全要素を連結して文字列にする（Join）」をご参照ください。

Sub JoinExample()
    Dim arr As Variant
    Dim txt As String
    
    arr = Array("A", "B", "C")
    txt = Join(arr, "-")  ' "A-B-C"
    
    MsgBox txt
End Sub

Sub JoinExample()

Dim arr As Variant

Dim txt As String

arr = Array("A", "B", "C")

txt = Join(arr, "-") ' "A-B-C"

MsgBox txt

End Sub

7. 配列を扱う際の注意点

配列を扱う際にはいくつかの注意点があります。

添字の開始位置
Dim nums(4) なら 0～4 の5要素になります。
2.型の指定
Variant配列は柔軟ですがメモリ効率は悪くなります。高速性を求めないのであればVariantで問題ないのですが、処理速度を考えるなら型を指定するのが望ましいです。
セル範囲→配列代入の効率
ループで1セルずつ処理するより、一括代入→配列処理→一括出力の方が圧倒的に速いです。
インデックス範囲外エラーの回避
インデックス範囲外のエラーは頻発するエラーの１つです。回避方法は「エラー9対応方法（インデックスが有効範囲にありません）」をご参照ください。
データ型の一致
配列に代入する値は、宣言時に指定したデータ型と一致させる必要があります。

まとめ

VBAにおける配列への代入はとても重要な技術です。

基本的な要素ごとの代入から、一括代入、Excel範囲との連携まで、様々な方法を適切に使い分けることで、より効果的なVBAプログラムを作成できます。

配列を使用する際は、インデックス範囲やデータ型に注意を払う必要があります。どんなプログラミング言語にも当てはまりますが、配列はバグが混入しやすい箇所です。また、エラーが起きやすい仕組みです。

どんなに注意を払っても、熟練プログラマーでも配列を扱うコードを書くときにデータ型不正やインデックス範囲ずれなどのエラーに遭遇した経験を持ちます。

バグやエラーが起きても、慌てずに「あー、やらかしたー」ぐらいの落ち着いた気持ちで、適切に対応してください。

データをまとめて扱うには配列を扱う

1. VBAにおける配列とは

2. 静的配列への代入

3. 動的配列への代入

4. 配列を一括代入する方法

4-1. Array関数を使う

4-2. ワークシート範囲を配列に代入

5. 配列同士の代入・コピー

5-1. 動的配列やVariant配列は代入可能

5-2. 静的配列同士は代入不可

6. 配列とSplit関数、Join関数

6-1. 文字列を配列に分割（Split関数）

6-2. 配列を文字列に結合（Join関数）

7. 配列を扱う際の注意点

まとめ

関連記事