【VBA】動的配列を使って汎用的なプログラミングを書く
融通のきかないプログラム言語と言えば『VBA』
こんな台詞を何度耳にしたことでしょうか？
しかし、現場の実情を見ると業務管理システムとして重宝しまくっているのもVBAなんですよね。
今回は、動的配列を使って汎用的に書くVBAコーディングを紹介していきます。
何を配列にすれば便利なのか？
VBAを書いていく上で、配列にすれば便利になるオブジェクトはたくさんあります。
例えば「エクセルシート」もその一つ。
3つのシートにレコードセットを出力するコードを書く場合、シートを1つずつ指定しなければなりません。
Set xlSheet(0) = xlBook.WorkSheets("sheet_a")
Set xlSheet(1) = xlBook.WorkSheets("sheet_b")
Set xlSheet(2) = xlBook.WorkSheets("sheet_c")これを配列にすれば、同じファイルの中にあるシートなんだってイメージも付きやすいですよね。
これ以外にも、テーブルやクエリ等も配列として認識させることができます。
配列のポイントは、同じプログラム内で使われるパッケージのようなものを見つけること。
この考え方が、開発を進める上で便利になってくることもあります。
まずは固定長配列からおさらい
配列をググると固定長配列が目に付きます。
Dim str(2) As String
str(0) = "a"
str(1) = "b"
str(2) = "c"正直、この配列の形って使い道ないと思いませんか？
最初から変数が固定されているのであれば、配列にする意味って何だろう？と感じますよね。
1つ固定的な配列で便利な点をあげるとするならば、Forでぐるぐる回せる部分。
Dim str(2) As String
Dim var As Variant
Dim msg As String
msg = ""
str(0) = "a"
str(1) = "b"
str(2) = "c"
For Each var In str
msg = msg & var
Next var
Msgbox msg
'//abcこんな感じに、配列に代入した値をForで順番に回すことができます。
でもこれは最初から配列数が分かっている場合のみ…
配列がころころ変わるケースでは全く使えません。
動的に配列を使いたい場面
例えば、エクセルシートの数が日によって変わる場合はどうでしょうか？
3つの配列で固定してしまうと、毎回システム改修をしないといけなくなります。
動的配列は、そんなケースでも水のように柔軟な対応が可能になるので汎用性の高いシステムを構築したい場合にはとっても便利。
では、ここから動的配列のコーディング例を紹介していきましょう。
動的配列の元データをクエリで組む
ここでは例題として、エクセルシートの数がころころ変わる場面を想定したコードを紹介します。
出力するタイミングによってシートの数が1つになったり、6つになったりと統一性がない運用ってよくありますよね。
多くの場合は、最大のシート数をあらかじめ用意しておく運用にしていることでしょう。
しかし動的配列を使えば、必要な分だけスマートに出力することが可能になりますよ。
まずは宣言から
Dim xlApp As Object 'Excelアプリケーション'
Dim xlBook As Object 'Excelブック'
Dim xlSheet() As Object 'Excelシート' 動的配列は宣言の段階で配列数が決まっていないので、カッコの中身は空白です。
さて、ここからがポイントです。
動的配列の配列数をどのデータから引っ張ってくるのか？
DBの設計段階からの話になりますが「何を基準にシート分けするのか？」が肝になってきます。
今回は、集計元テーブルの「categoryフィールド」を基準にシート作成するようにしましょう。
例えば、こんな顧客データがあったとします。
categoryの種類は日によって増減し、固定的ではなかったとしましょう。
この顧客データをcategory毎に分けて、エクセルシートへする場合に動的配列が生きてきます。
まずは「categoryフィールド」に何種類の値が入っているのか？をクエリで抽出します。（テーブル名：t_顧客）
Select category From t_顧客 Group By category;グループ化したcategoryデータのレコード数を配列数として格納する流れです。
事前にクエリを組んでおけば、categoryが何種類になろうとも対応可能！
では、このレコードセットを使った想定でコーディングしていきましょう。
動的配列を使ったコーディング例
Dim rs(1) As DAO.Recordset 'クエリのレコードセット'
Dim myFields As Field
Dim strSQL(1) As String 'SQL'
Dim xlApp As Object 'Excelアプリケーション'
Dim xlBook As Object 'Excelブック'
Dim xlSheet() As Object 'Excelシート'
Dim iArray(1) As Integer '配列数'
Dim i
Dim strCategory As String
Dim iRow, iCol(1) '行と列'
strSQL(0) = "Select category From t_顧客 Group By category;"
Set rs(0) = CurrentDB.OpenRecordset(strSQL(0))
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True '可視化'
Set xlBook = xlApp.Workbooks.Open("C:¥test.xlsx")
With rs(0)
.MoveLast '一旦最後まで行って'
iArray(0) = .RecordCount - 1 '配列数取得/0始まりなので−１'
.MoveFirst '最初に戻す'
With xlBook
.WorkSheets.Add after:=.WorkSheets(.WorkSheets.Count), _
Count:=iArray(0) 'シート追加'
End With
ReDim xlSheet(iArray(0)) 'ここで配列決定して再定義'
i = -1
Do Until .EOF
i = i + 1
Set xlSheet(i) = xlBook.WorkSheets(i + 1)
strCategory = !category.Value
strSQL(1) = "Select * From t_顧客 Where category =""" & _
strCategory & """"
Set rs(1) = CurrentDB.OpenRecordset(strSQL(1))
With rs(1)
iArray(1) = .Fields.Count - 1
iRow = 2
iCol(0) = 1
For Each myFields In .Fields'カラムを出力'
xlSheet(i).Cells(1, iCol(0)).Value = myFields.Name
iCol(0) = iCol(0) + 1
Next myFields
Do Until .EOF'データを出力'
For iCol(1) = 0 To iArray(1)
xlSheet(i).Cells(iRow,iCol(1) + 1).Value = .Fields(iCol(1)).Value
Next iCol(1)
.MoveNext
iRow = iRow + 1
Loop
.Close
End With
Set rs(1) = Nothing
xlSheet(i).Name = "Sheet_" & strCategory'シート名変更'
.MoveNext
Loop
.Close
End With
xlBook.Close True’保存して閉じる’
xlApp.Visible = False
‘解放’
Erase iCol
Erase iArray
Erase rs
Erase strSQL
Erase xlSheet
Set xlBook = Nothing
Set xlApp = Nothing少し長くなりましたが、SQLでcategoryを抽出し動的配列へ格納したコード例です。
格納した配列数とシート数が同じになり、各シートへクエリの結果が出力される仕組みになります。
注意する点が、配列とエクセルオブジェクトのインデックスを同じにしないこと。
例えば、配列が0始まりなのに対して「xlBook.WorkSheets(Index)」のインデックスは1始まりです。
ここを押さえておかないと、たちまちエラーになってしまうので特に注意しておきましょう。
エクセルへの出力コードは、こちらの記事でも詳しく紹介されています。
ぜひ参考にしてみてください。
この記事が気に入ったらサポートをしてみませんか？