ここでは 即実務で使える VBA マクロテンプレ集 を作ります。
特徴は以下の通りです：
- ログ機能：処理履歴を自動記録
- エラー処理：マクロが途中で止まらない、安全設計
- 共通関数・Sub：再利用可能な汎用機能
- フォルダ・ファイル・シート・配列・Outlook・ADO対応
1. 基本構成
- Module1 : 共通関数・Sub
- Module2 : データ処理用サンプル
- Module3 : ログ & エラー処理
- Module4 : 外部連携（Outlook, ADO, CSV）
2. Module1：共通関数・Sub
文字列・数値・配列処理
'===========================
' 数値の丸め
'===========================
Function Round2(val As Double, Optional digits As Long = 2) As Double
Round2 = WorksheetFunction.Round(val, digits)
End Function
'===========================
' 配列をカンマ区切り文字列に変換
'===========================
Function JoinArray(arr As Variant) As String
JoinArray = Join(arr, ", ")
End Function
'===========================
' 空白セルチェック
'===========================
Function IsEmptyCell(rng As Range) As Boolean
IsEmptyCell = Trim(rng.Value) = ""
End Function
3. Module3：ログ & エラー処理
ログ記録 Sub
Sub LogWrite(msg As String)
On Error Resume Next
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ログ")
Dim nextRow As Long
nextRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row + 1
sh.Cells(nextRow, 1).Value = Now
sh.Cells(nextRow, 2).Value = msg
End Sub
エラー処理用テンプレ
Sub SafeRun(target As String)
On Error GoTo ErrHandler
Application.ScreenUpdating = False
CallByName Me, target, VbMethod ' 引数なし Sub を動的実行
ExitPoint:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
LogWrite "Error in " & target & ": " & Err.Number & " - " & Err.Description
Resume ExitPoint
End Sub
4. Module2：データ処理用サンプル
CSV 読み込み（配列で高速）
Function ReadCsvToArray(filePath As String) As Variant
Dim f As Integer, line As String
Dim data() As String, tmp() As Variant
Dim count As Long
If Dir(filePath) = "" Then Exit Function
f = FreeFile
Open filePath For Input As #f
count = 0
Do Until EOF(f)
Line Input #f, line
data = Split(line, ",")
ReDim Preserve tmp(0 To count)
tmp(count) = data
count = count + 1
Loop
Close #f
ReadCsvToArray = tmp
End Function
データ書き込み Sub（シートに配列）
Sub WriteArrayToSheet(arr As Variant, ws As Worksheet, Optional startRow As Long = 1, Optional startCol As Long = 1)
Dim r As Long, c As Long
For r = LBound(arr) To UBound(arr)
For c = LBound(arr(r)) To UBound(arr(r))
ws.Cells(startRow + r, startCol + c).Value = arr(r)(c)
Next c
Next r
End Sub
5. Module4：外部連携
Outlook メール送信
Sub SendMail(toAddr As String, subject As String, bodyText As String, Optional attachPath As String)
On Error GoTo ErrHandler
Dim olApp As Object
Dim olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
With olMail
.To = toAddr
.Subject = subject
.Body = bodyText
If attachPath <> "" Then .Attachments.Add attachPath
.Send
End With
LogWrite "Mail sent to " & toAddr
Exit Sub
ErrHandler:
LogWrite "Error sending mail: " & Err.Description
End Sub
ADO で Access / SQL 接続
Function GetDataFromDB(connStr As String, sql As String) As Variant
On Error GoTo ErrHandler
Dim cn As Object, rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open connStr
rs.Open sql, cn
If Not rs.EOF Then
GetDataFromDB = rs.GetRows() ' 配列で取得
End If
rs.Close
cn.Close
Exit Function
ErrHandler:
LogWrite "DB Error: " & Err.Description
End Function
6. 運用ポイント
- ログ用シートを事前に作成
- 列A：日時
- 列B：メッセージ
- SafeRun を使った安全実行
Sub 実務処理()
SafeRun "ProcessData"
End Sub
ProcessDataは任意の処理 Sub
- 共通関数で処理簡略化
- 配列操作、日付チェック、文字列操作、数値丸めなどはすべて Module1 に集約
- エラー発生時もログに残る
- 実務でのトラブル対応が容易
💡 このテンプレ集を使えば：
- 複数プロジェクト間で再利用可能
- ログ + エラー + 共通関数付きで堅牢
- Outlook / DB / CSV / 配列 / シート処理に対応
