Excel VBAでの配列はこんな時に使います

「VBAを独学していますが、配列ってどんな場面で使うのかな？様々なサイトでも解説してありますが、使い方のイメージが出来ません。」

プログラミングの基本をマスターすると、次の壁となるのは配列です。

配列を使わなくてもVBAプログラミングは出来ますが、習得できるメリットが大きいので是非おさえておきたい内容です。

この記事を書いている私は、約10年前からVBAを使ってきましたが、配列の便利さを理解出来なかったため、積極的に使ってきませんでした。

しかし、最近VBAの学習をし直したところ、とても便利だということに気付かされましたので、忘れないようにまとめておくことにしました。

VBAを扱えるけど配列を使えない、という方には是非とも参考にしてほしいです。

配列と変数の違いは何?

まずは、配列の正体を知っておかないと使いこなすことができません。

配列の正体を知るためには、変数との違いを把握することが一番分かり易いです。

一つの変数には一つのデータしか入れることはできません。

Sub Variable_Ex()
    Dim HinSt As String  '変数の宣言
    '変数の格納
    HinSt = "りんご"
    HinSt = "みかん"
    HinSt = "メロン"
    Debug.Print HinSt   '"メロン"のみが出力される
End Sub

上の文を実行してみると、りんごやみかんが無視されて、一番最後のデータであるメロンのみが出力されてしまいます。

一方、配列には一度に複数のデータを格納することができます。

Sub Array_Ex1()
    Dim i As Integer
    Dim HinSt(2) As String  'String型配列の宣言
    '配列の格納
    HinSt(0) = "りんご"
    HinSt(1) = "みかん"
    HinSt(2) = "メロン"
    '全ての格納された値を出力
    For i = LBound(HinSt) To UBound(HinSt)
        Debug.Print HinSt(i)
    Next
End Sub

上のコード文を実行してみると、全てのデータを出力することが出来ました。

変数とString型配列の違いは、宣言をする時にデータを入れる数（要素数）を設定する必要があります。要素数は3つですが、宣言する時は2を設定します。

Dim HinSt As String  '変数の宣言
Dim HinSt(2) As String  'String型配列の宣言(要素数は3)

また、Variant型の配列では一括でデータを格納することが出来ます。String型のように要素数を設定する必要がありません。

Sub Array_Ex2()
    Dim i As Integer
    Dim HinSt As Variant  'Variant型配列の宣言
    '配列の格納
    HinSt = Array("りんご", "みかん", "メロン")
    '全ての格納された値を出力
    For i = LBound(HinSt) To UBound(HinSt)
        Debug.Print HinSt(i)
    Next
End Sub

配列は一旦データを格納してしまえば一括出力できます。

Lbound関数とUbound関数を使い、For～Next文で繰り返すだけです。　

'全ての格納された値を出力
For i = LBound(HinSt) To UBound(HinSt)
    Debug.Print HinSt(i)
Next

なぜ配列を使えた方がいいのか

変数と配列の違いを知ったとしても、このように感じる方もいるのでは無いでしょうか？

「要素数ではなくて、変数を必要なだけ作ってあげればいいのではないか」

もちろん、変数を増やせば配列と同じ動作を再現できます。

しかし、実務で活用するときには、

• 後で見返した時に何を書いているか分かる
• プログラムの修正がしやすい

ということもVBAで自動化するための大事な要因になってきます。

2〜3個程度ならばそんなに負担に感じませんが、5個以上になると後で手直しする時に見づらくなります。

コード文を書いた当日であれば内容も覚えているのですが、1か月も経てば何を書いたか覚えていないというのが、VBAプログラマーのあるあるです。

もう一度どんな動きになっているかを把握し直さないと、どこを修正したらよいかも分かりません。

把握しやすくするためにも、日頃から複雑すぎないコードを書く習慣を身に付けておく必要があります。

配列を使うとコード文を解読しやすくなるので、項目の追加など修正が必要になった場合でも手直しのにかける手間を大きく減らすことが出来ます。

配列の使用例1-決まったリストを配列に入れて活用する

配列を使えた方がいいことはわかりますが、どうやって使えばいいのでしょうか？

「そんなことは自分で考えなさい。」と言われてもなかなか難しいですよね？

配列を使えば、特定のリストを何かに一括で出力できることを知っておきましょう。

一例として、配列のリストで新しいフォルダを作ってみます。

Sub MakeDiretory1()
    Dim i As Integer
    Dim HinSt As Variant
    HinSt = Array("りんご", "みかん", "メロン")
    For i = LBound(HinSt) To UBound(HinSt)
        'カレントパスにフォルダ作成
        MkDir ThisWorkbook.Path & "\" & HinSt(i)
    Next
End Sub

カレントブックが置かれているファルダの中です。他のフォルダは無い状態です。

プログラムを実行すると、配列で設定した名前のフォルダが作られます。

お使いの環境がOneDriveの場合、ThisWorkbook.Pathで指定するとエラーを発生することがあります。そんな時は相対パスでなく絶対パスで指定してあげると正しく作動します。

フォルダを作ること以外にも、

• Excelブックの名前を変えて複製する
• テキストファイルやCSVファイルを複数出力する

日頃の業務で何気なく行っている、小さなルーティン作業を自動化することが出来ます。

他にも、システムのデータを取り込む時、特定のテーブルを指定する時にも配列を使うことが出来ます。

配列の使用例2-システム内のデータを活用

基幹システムなどに溜め込んだデータを取り込む時にも、配列をフル活用出来ます。

この記事では、配列のイメージをつかむのが目的なので、データベースを使わずにExcelを元データとしています。

以下は変数で隣に貼り付けた場合のコードです。

Sub GetVariable1()
    Dim i As Integer, j As Integer
    '商品マスタのデータを順番に貼付(2重ループ)
    For i = 1 To Cells(1, 1).CurrentRegion.Rows.Count
        For j = 1 To Cells(1, 1).CurrentRegion.Columns.Count
            Cells(i, Cells(1, 1).CurrentRegion.Columns.Count + j + 1).Value = _
            Cells(i, j).Value
        Next j
    Next i
End Sub

一方、1行を一度に貼付けるために配列を使ったコードです。

Sub UseSplit1()
    Dim i As Integer, Ary1 As Variant
    '商品マスタを配列に格納
    Ary1 = Cells(1, 1).CurrentRegion.Value
    '1行ずつデータを反映する
    For i = LBound(Ary1) To UBound(Ary1)
        'Split関数で1行まるごと出力
        Cells(i, Cells(1, 1).CurrentRegion.Columns.Count + 2).Resize(1, 5) = _
        Split(Ary1(i, 1) ＆ "," ＆ Ary1(i, 2) ＆ "," ＆ Ary1(i, 3) ＆ "," ＆ _
        Ary1(i, 4) ＆ "," ＆ Ary1(i, 5), ",")
    Next i
    '区切文字の修正
    Columns(Cells(1, 1).CurrentRegion.Columns.Count*2+1).TextToColumns _
    FieldInfo:=Array(1, 1)
End Sub

多少コード文は長くなりますが、一旦全範囲を配列Ary1に格納し1行ずつ貼り付けます。

貼り付け元のデータテーブルは5列の8レコードですので、40回の貼付け作業を繰り返しています。

配列を使った場合は繰り返し回数が8回で、2重ループの5分の1で済んでいます。

実務では、更に多くのデータ件数や項目数を取り込みますので、配列を活用するメリットは大きいです。
また、取り込む必要が無い項目名がデータテーブルの中に含まれることも実務ではよく起きます。

セルの出力する時にSplit関数を使用していますが、使いたい項目名と順番を指定できます。

'Split関数で1行まるごと出力
Cells(i, Cells(1, 1).CurrentRegion.Columns.Count + 2).Resize(1, 5) = _
Split(Ary1(i, 1) ＆ "," ＆ Ary1(i, 2) ＆ "," ＆ Ary1(i, 3) ＆ "," ＆ _
Ary1(i, 4) ＆ "," ＆ Ary1(i, 5), ",")

必要な項目名のみを指定してあげるとよいでしょう。

Split関数を活用した場合は、数値も文字列で貼り付けられますので計算することができません。

計算区切り位置の設定を出力後に行うことで数値に変換できますので、忘れずに行いましょう。

'区切文字の修正
Columns(Cells(1, 1).CurrentRegion.Columns.Count*2+1).TextToColumns _
FieldInfo:=Array(1, 1)

ここでは、データタグの「区切り位置」のマクロ記録を実行し、不要な部分を削除しています。

AccessやSQL SERVERからデータを取り込む場合、ADOを使ってデータを抜き出し、配列を使って1行ずつデータを反映させることが出来ます。

まとめ

今回は配列の使い方についてまとめてみました。

配列を使えるようになると、コード文の量を減らして可読性を上げることが出来ます。

変数のみでも配列と同じような動作は再現できますが、後で見返す手間を考慮すると配列を積極的に使う価値は大きいです。

ここでは一例として、リストの出力やシステムからのデータ取込を挙げましたが、他にもいろいろな使い方が見つかると思います。

配列はAccessでもよく使いますので、現在Accessを学習している方には特に必須です。

本日も最後までありがとうございました。