はじめに

本記事は，VBAマクロを書いたことがある人向けです。
ユーザ定義型の変数を動的に拡張 (ReDim) して配列に格納するサンプルコードを共有します。
本コードは雛形として使えるので，ユーザ変数など必要な変数に置き換え活用してください。

本コードが役に立つ人

• 配列を使わず for ループでコピー＆ペースト をしている人
• Item(100) as String のように固定長配列しか使わない人
• 可変長配列をスマートに関数を使って導入したい人
• 自分

ソースコード

'ユーザ型変数の宣言---------------------------------------------
Type USER
    age As Integer
    name As String
End Type
'変数を格納していくための箱---------------------------------------------
Type Box
    Item() As USER
    count As Integer
End Type
'main()---------------------------------------------
Sub macro()
  Dim DB As Box
  Dim ユーザ1 As USER
  '直接代入
　With ユーザ1
    　.age = 10
    　.name = "太郎"
　End With
   Dim ユーザ2 As USER
　'セルから値取得
　With ユーザ2
    　.age = Sheets(1).cells(1,1).value
    　.name =Sheets(1).cells(1,2).value
　End With
'配列を使った操作サンプル----------------------------------------
　Add DB, ユーザ1　
   MsgBox (DB.Item(1).name & "の年齢は" & DB.Item(1).age & "です")
   'シートに出力する例
   Add DB, ユーザ2
   Sheets(2).cells(1,1) = DB.Item(2).age
   Sheets(2).cells(1,2) = DB.Item(2).name
　'配列初期化
　Clr DB 
End Sub
'Boxに要素を動的に追加する関数---------------------------------------------
Private Sub Add(Box As Box, Item As USER)
    With Box
        .count = .count + 1
        ReDim Preserve .Item(.count)
        .Item(.count) = Item
    End With
End Sub
'Boxの初期化を行う関数---------------------------------------------
Private Sub Clr(Box As Box)
    With Box
        Erase .Item
        .count = 0
    End With
End Sub

説明

本ソースコードの肝の部分

• Type Box でBox型の変数を宣言。Box型には，要素を格納する配列 Item() と要素数を操作するためのcountを定義
• BoxのItem()の配列要素数を増やしながら ReDim ,変数を格納するAdd関数を実装
• BoxのItem()とcountを初期化するClr関数を実装

あとは，各々がBoxに格納するためのユーザ定義変数を別途定義してAdd関数やClr関数を使用