はじめに
皆さんこんにちは！
約1年ぶりの投稿です。
VBAのプログラムをいくつか作っていて、Excelの別シートに記載した情報を可変長マトリックス（2次元配列）として取得する関数が結構便利だなーっと思ったので、その関数についてまとめてみました。
また、サンプルコードとしてその関数を使用したプログラムも書いてみたので、関数の使い方について参考にしてみてください。
仕様
- 指定された引数の情報に基づいて、Excelのシートに記載された情報をマトリックス（2次元配列）情報として取得する。
ソースコード
今回の関数のソースコードを下に記載します。
mdlFunction
Option Explicit
'---------------------------------------------------
'概要：引数で指定した開始列、終了列までの情報をマトリックスで返す関数
'マトリックスの開始列のうち、値が入力されている最下行までを対象範囲とする
'引数1：対象シート名
'引数2：取得したいマトリックスの開始列
'引数3：取得したいマトリックスの終了列
'戻り値：シート情報のマトリックス
'---------------------------------------------------
Public Function getMatrixInfo(sheetName As String, startCol As Long, endCol As Long) As Variant
With Worksheets(sheetName)
'指定開始列の最下行を取得する
Dim endRow As Long 'データ最終行
endRow = .Cells(.Rows.Count, startCol).End(xlUp).Row
'1行目からデータ最終行まで、マトリックス開始列から終了列までのマトリックス情報を返す
getMatrixInfo = .Range(.Cells(1, startCol), .Cells(endRow, endCol))
End With
End Function
サンプルプログラム
上のソースコードを用いたサンプルプログラムを作成してみました。
-
サンプルプログラムの仕様
- [Main]シートに入力した「対象テーマパーク」、「年」を元に、対象テーマパークのその年における来場者数・来場者の平均年齢を出力するプログラム。
- [Main]シートにある「検索」ボタンを押すことで[ParkInfo]シートからマトリックス情報（2次元配列情報）を取得し、[Main]シートの入力情報に合致するものだけをメッセージに出力する。
- [ParkInfo]シートにはテーマパークA、Bの情報が記載されている。
Excelの画面
[Main]シート
[ParkInfo]シート
- サンプルプログラムのソースコード
shtMain
Option Explicit
'---------------------------------------------------
'入力したテーマパーク､年の情報から､
'来場者数と来場者平均年齢を取得するプログラム
'---------------------------------------------------
Private Sub cmdSearch_Click()
'エラーハンドリング処理
On Error GoTo sys_Error_Proc
'テーマパークがAかB以外であればエラー
If Cells(3, 3) <> "A" And Cells(3, 3) <> "B" Then
GoTo input_Error_Proc
'年が2016以上の整数でなければエラー
ElseIf Not IsNumeric(Cells(4, 3)) Then
GoTo input_Error_Proc
ElseIf Cells(4, 3) <> Int(Cells(4, 3)) Or CLng(Cells(4, 3)) < 2016 Then
GoTo input_Error_Proc
End If
'わかりやすいように入力情報を変数に格納
Dim inputPark As String '入力したテーマパーク
Dim inputYear As Long '入力した年
inputPark = Cells(3, 3)
inputYear = Cells(4, 3)
'************************************************
'********** 本題のマトリックス取得処理 **********
'************************************************
'[ParkInfo]シートからマトリックス情報を取得する。
'ここでは、別のモジュールで定義した定数、関数を用いることとする。
Dim parkInfo As Variant 'パーク情報を格納する変数
'テーマパークAである場合の情報取得
If inputPark = "A" Then
parkInfo = getMatrixInfo("ParkInfo", enParkInfo.A_YearCol, enParkInfo.A_Ave_Age_Col)
'テーマパークBである場合の情報取得
Else
parkInfo = getMatrixInfo("ParkInfo", enParkInfo.B_YearCol, enParkInfo.B_AveAgeCol)
End If
'************************************************
'******************* ここまで *******************
'************************************************
'得られたマトリックスの情報を下の処理にて使用してみる
'ここでは入力した年に合致する情報を検索する
Dim i As Long
Dim targetRow As Long '入力年に合致したマトリックスの行
Dim searchResult As Boolean '検索結果
For i = dataStartRow To UBound(parkInfo, 1) 'データ開始行からマトリックス最下行まで
If parkInfo(i, 1) = inputYear Then 'マトリックス情報のうち、1列目が検索情報（年）であるため、数値「1」を引数に指定
targetRow = i
searchResult = True
GoTo break_Search
End If
Next
break_Search:
'もし一致する年が存在しない場合、メッセージを出力する
If searchResult = False Then
MsgBox ("入力した年に一致する情報が[ParkInfo]シートに存在しません")
Exit Sub
End If
'来場者数と来場者の平均年齢を出力する
'2列目に来場者数、3列目に来場者平均年齢が格納されている
MsgBox ("来場者数：" & parkInfo(targetRow, 2) & "人" & vbCrLf & "来場者平均年齢：" & parkInfo(targetRow, 3) & "歳")
Exit Sub
input_Error_Proc:
MsgBox ("適切な文字/値を入力してください")
Exit Sub
sys_Error_Proc:
MsgBox ("処理中にエラーが発生しました")
End Sub
mdlConst
Option Explicit
'***************************************************
'定数情報
'***************************************************
'[ParkInfo]シートの情報
Enum enParkInfo
titleRow = 2 'タイトル行
dataStartRow 'データ開始行
A_YearCol = 1 'テーマパークAの年の列番号
A_PeopleNum_Col 'テーマパークAの来場者数の列番号
A_Ave_Age_Col 'テーマパークAの来場者の平均年齢の列番号
B_YearCol = 5 'テーマパークBの年の列番号
B_PeopleNumCol 'テーマパークBの来場者数の列番号
B_AveAgeCol 'テーマパークBの来場者の平均年齢の列番号
End Enum
- 処理成功時の挙動
来場者数、来場者の平均年齢が出力される。
最後に
今回紹介した関数はシンプルでありながら汎用的で色々と応用が利くと思うので、良ければ使ってみてください！