以下に、Excel VBA UI 部品を“クラス化”して再利用性を最大化した版をまとめます。
すべて そのまま標準モジュール／クラスモジュールとして貼るだけで使える構造です。
クラス化 UI フレームワーク（再利用 MAX 版）
目的：
- どの業務ブックでも 同じコードを使い回せる
- すべてを 単一責務で分離し、大規模開発でも壊れにくくする
- 画面（UserForm）は薄くし、ロジックはクラスに集約
全体構成
/UIFramework
├─ CCalendar ：カレンダー入力
├─ CDateRangePicker ：日付範囲
├─ CProgressBar ：進捗バー
├─ CSearchDialog ：検索ダイアログ
├─ CLogin ：ログイン認証
├─ CListDetail ：一覧 → 詳細
├─ CRowEditor ：行編集（Add/Edit/Delete）
├─ CDropdownFilter ：ドロップダウン絞り込み一覧
├─ CAdvancedSearch ：AND/OR 高機能検索
└─ UI_Common ：共通処理（標準モジュール）
共通処理（標準モジュール: UI_Common）
' フォーム中央配置
Public Sub UI_FormCenter(frm As Object)
frm.Left = (Application.UsableWidth - frm.Width) / 2
frm.Top = (Application.UsableHeight - frm.Height) / 2
End Sub
' ComboBox に配列セット
Public Sub UI_SetComboList(cmb As MSForms.ComboBox, arr)
cmb.Clear
cmb.List = arr
End Sub
' ListBox に Range セット
Public Sub UI_SetListBoxFromSheet(lst As MSForms.ListBox, src As Range)
lst.ColumnCount = src.Columns.Count
lst.List = src.Value
End Sub
CCalendar（カレンダー入力）
クラスモジュール名：CCalendar
Option Explicit
Private WithEvents frm As frmCalendar
Private ret As Date
Public Function GetDate() As Date
Set frm = New frmCalendar
Call UI_FormCenter(frm)
frm.Show
GetDate = frm.SelectedDate
End Function
使い方
Dim cal As New CCalendar
Range("B1") = cal.GetDate
CDateRangePicker（日付範囲）
クラスモジュール名：CDateRangePicker
Option Explicit
Private WithEvents frm As frmDateRange
Public Type DateRange
FromDate As Variant
ToDate As Variant
End Type
Public Function GetRange() As DateRange
Dim r As DateRange
Set frm = New frmDateRange
Call UI_FormCenter(frm)
frm.Show
r.FromDate = frm.DateFrom
r.ToDate = frm.DateTo
GetRange = r
End Function
使い方
Dim dr As DateRange
dr = (New CDateRangePicker).GetRange
CProgressBar（進捗バー）
クラスモジュール名：CProgressBar
Option Explicit
Private WithEvents frm As frmProgress
Public Sub Start(msg As String)
Set frm = New frmProgress
frm.lblMsg.Caption = msg
frm.lblBar.Width = 0
frm.Show vbModeless
DoEvents
End Sub
Public Sub SetPercent(p As Double)
frm.lblBar.Width = frm.InsideWidth * p
DoEvents
End Sub
Public Sub Finish()
Unload frm
End Sub
使い方
Dim pb As New CProgressBar
pb.Start "処理中…"
For i = 1 To 2000
pb.SetPercent i / 2000
Next i
pb.Finish
CSearchDialog（検索ダイアログ）
クラスモジュール名：CSearchDialog
Option Explicit
Private WithEvents frm As frmFind
Private src As Range
Public Sub Init(target As Range)
Set src = target
End Sub
Public Sub Show()
Set frm = New frmFind
Call UI_FormCenter(frm)
frm.SetSource src
frm.Show
End Sub
※ frmFind 側に以下を追加
Public SearchSource As Range
Public Sub SetSource(r As Range)
Set SearchSource = r
End Sub
CLogin（ログイン認証）
クラスモジュール名：CLogin
Option Explicit
Private WithEvents frm As frmLogin
Private userid As String
Private pass As String
Public Sub SetCredential(u As String, p As String)
userid = u: pass = p
End Sub
Public Function Authenticate() As Boolean
Set frm = New frmLogin
Call UI_FormCenter(frm)
frm.Show
If frm.txtUser = userid And frm.txtPass = pass Then
Authenticate = True
Else
Authenticate = False
End If
End Function
使い方
Dim login As New CLogin
login.SetCredential "admin", "1234"
If login.Authenticate Then MsgBox "OK"
CListDetail（一覧 → 詳細）
クラスモジュール名：CListDetail
Option Explicit
Private src As Range
Public Sub Init(table As Range)
Set src = table
End Sub
Public Sub Show()
frmList.SetSource src
frmList.Show
End Sub
※ frmList 側に
Private src As Range
Public Sub SetSource(r As Range)
Set src = r
UI_SetListBoxFromSheet Me.lst, src
End Sub
CRowEditor（Add / Edit / Delete）
クラスモジュール名：CRowEditor
Option Explicit
Private sheet As Worksheet
Public Sub Init(ws As Worksheet)
Set sheet = ws
End Sub
Public Sub AddRow(id, name, qty)
Dim r As Long
r = sheet.Cells(sheet.Rows.Count, "A").End(xlUp).Row + 1
sheet.Cells(r, 1) = id
sheet.Cells(r, 2) = name
sheet.Cells(r, 3) = qty
End Sub
Public Sub EditRow(id, name, qty)
Dim f As Range
Set f = sheet.Columns(1).Find(id)
If Not f Is Nothing Then
f.Offset(0, 1) = name
f.Offset(0, 2) = qty
End If
End Sub
Public Sub DeleteRow(id)
Dim f As Range
Set f = sheet.Columns(1).Find(id)
If Not f Is Nothing Then f.EntireRow.Delete
End Sub
CDropdownFilter（ドロップダウン絞り込み）
クラスモジュール名：CDropdownFilter
Option Explicit
Private src As Range
Public Sub Init(table As Range)
Set src = table
End Sub
Public Function Filter(col As Long, key As String) As Variant
Dim buf As Collection: Set buf = New Collection
Dim r As Range
For Each r In src.Rows
If key = "" Or r.Cells(1, col) = key Then buf.Add r
Next
Dim ar(), i As Long
ReDim ar(1 To buf.Count, 1 To src.Columns.Count)
i = 1
For Each r In buf
Dim c As Long
For c = 1 To src.Columns.Count
ar(i, c) = r.Cells(1, c)
Next
i = i + 1
Next
Filter = ar
End Function
CAdvancedSearch（AND/OR 検索）
クラスモジュール名：CAdvancedSearch
Option Explicit
Private src As Range
Public Sub Init(table As Range)
Set src = table
End Sub
Public Function Search(key1, key2, ANDmode As Boolean) As Variant
Dim buf As Collection: Set buf = New Collection
Dim r As Range: Dim v As String
For Each r In src.Rows
v = r.Cells(1, 1).Value
Dim ok As Boolean
If ANDmode Then
ok = (InStr(v, key1) > 0 And InStr(v, key2) > 0)
Else
ok = (InStr(v, key1) > 0 Or InStr(v, key2) > 0)
End If
If ok Then buf.Add r
Next
Dim ar(), i As Long, c As Long
ReDim ar(1 To buf.Count, 1 To src.Columns.Count)
i = 1
For Each r In buf
For c = 1 To src.Columns.Count
ar(i, c) = r.Cells(1, c)
Next
i = i + 1
Next
Search = ar
End Function
ここまでの利点まとめ
- 全処理が クラス化されているため、コピペで再利用可能
- フォームや UI はすべて “薄いコード” となり保守性が高い
- 業務アプリとして必要な
- カレンダー
- 日付範囲
- 検索
- フィルタ
- 行編集
- プログレス
を 統一的な API で呼び出せる
