こんにちは！今日もExcel VBAの学びを深めていきましょう。
マクロの自動記録を卒業し、「自分だけの便利な関数を作ってみたい！」と思えるようになったのは、あなたがVBAの基礎をしっかりと理解し、一歩先へ進めた素晴らしい証拠です。
しかし、実務で自作関数——いわゆるUDF（ユーザー定義関数 / User Defined Function）を使い始めると、多くの人が最初にぶつかる大きな壁があります。
「便利だと思って作った関数を大量のセルにコピーしたら、シートの再計算が終わらなくなってExcelがフリーズした…」
実はこれ、VBAの性能の問題というよりも、「ワークシート関数」と「VBA関数」の根本的な役割の違いを知らないことで起きてしまう現象なのです。
今回は、Excel内部の仕組み（再計算エンジンや処理速度の特性）を踏まえながら、「どのような処理を関数化すべきか」、そして「プロが実践するパフォーマンスと再利用性を両立させるUDFの設計指針」をわかりやすく解説します。
ここをクリアできれば、あなたの作るExcelツールは一気にプロクオリティへと進化しますよ！
—
1. ワークシート関数 vs VBA関数（UDF）：決定的違い
まず結論からお伝えします。
> 「ワークシート関数でできることは、絶対にワークシート関数に任せる」
> これが、Excel VBA開発における絶対の黄金律です。
なぜなら、Excelに標準搭載されている `SUM` や `VLOOKUP`、`XLOOKUP` といった関数は、C++という言語で最適化され、マルチスレッドで超高速に動作するように設計されています。
一方、VBAで書くUDFは、単一スレッドかつVBAの実行エンジン（インタプリタ）を介して処理されるため、本質的にワークシート関数よりも低速です。
【処理速度のイメージ】
標準のワークシート関数 : [ C++エンジン (超高速・並列処理) ] ⚡⚡⚡
VBAのユーザー定義関数 : [ VBAエンジン (単一スレッド) ] ──(COMの壁)──> [ シート ] 🐌
VBAからシート上のセルにアクセスするたびに、裏側では「COM（Component Object Model）」と呼ばれる通信のオーバーヘッド（オーバーロード）が発生します。何千・何万行というセルでUDFを呼び出すと、この小さな遅延が積み重なり、シート全体が激重になってしまうのです。
では、いつUDFを使うべきなのか？
UDFを使うべきなのは、「標準関数を何重にもネスト（入れ子）して、数式が読めなくなったとき」や、「独自の複雑な業務ロジック（複雑な文字列解析や特殊な税計算など）をカプセル化したいとき」です。
- 標準関数を使うべき領域：
- データの集計（`SUMIFS`, `COUNTIFS`）
- 単純な検索・結合（`XLOOKUP`, `INDEX` + `MATCH`）
- シンプルな条件分岐（`IF`, `IFS`）
- UDFを作るべき領域：
- 正規表現を使った高度な文字列の抽出し・置換
- 社内独自の複雑な計算規約（段階的な割引計算や複雑な営業日の計算など）
- 数式が長すぎて可読性が著しく低下している処理
—
2. UDFを極小化・高速化するための3つの設計原則
UDFを作成・運用するにあたって、絶対に知っておくべき3つのルールがあります。
ルール①：`Application.Volatile`（自動再計算）は原則使わない！
VBAのコードで `Application.Volatile` と書くと、「シート内のどのセルが更新されても、この関数を再計算しなさい」という命令になります。
日時を返す関数などには必須ですが、通常の計算ロジックでこれを多用すると、関係ないセルを編集しただけで全UDFが再実行され、Excelが激重になります。基本的には書かず、引数として渡されたセルの変更時のみ動くようにするのが鉄則です。
ルール②：関数内で「他のセルの値を勝手に書き換える」ことはできない
UDFは「数式を入力したセルに値を返す」ためのものです。
関数の中から、`Range(“A1”).Value = 100` のように他のセルの値を書き換えたり、文字の色を変えたりすることはExcelの仕様上できません（エラーになるか無視されます）。
ルール③：引数で「Rangeオブジェクト」を受け取り、配列として処理する
大量のセル範囲を関数に渡す場合、1セルずつループで処理すると爆発的に遅くなります。範囲を丸ごとVBAの配列（Variant型）に読み込んでからメモリ上で処理するのが、プロの技です。
—
3. 実践コードで学ぶ！「ダメなUDF」と「プロのUDF」
それでは、具体的なコードで比較してみましょう。
例として、「指定した範囲の中から、特定の文字（例：[確定]）が含まれるセルの値を合計する」というカスタム関数を作成します。
❌ やってはいけない「激重」コード例
‘ 【Bad Example】1セルずつシートにアクセスするため極めて遅い
Function BadSumIfContains(ByVal TargetRange As Range, ByRef Keyword As String) As Double
Dim cell As Range
Dim total As Double
total = 0
‘ TargetRange内の全セルを1つずつループ（毎回COMオーバーヘッドが発生）
For Each cell In TargetRange
If InStr(cell.Value, Keyword) > 0 Then
If IsNumeric(cell.Value) Then
total = total + cell.Value
End If
End If
Next cell
BadSumIfContains = total
End Function
一見問題なさそうに見えますが、`TargetRange` が1万セルあった場合、1万回の「シートへのアクセス」が発生します。これが数千個のセルに入っていたら……恐怖ですね。
—
⭕ プロの設計による「超高速＆安全」コード例
次に、メモリ（配列）を活用し、型チェックとエラーハンドリングまで完備した理想的なUDFです。
Option Explicit
”’
”’
”’ 対象のセル範囲
”’ 検索する文字列
”’
Public Function FastSumIfContains(ByVal TargetRange As Range, ByVal Keyword As String) As Variant
‘ 引数が無効な場合は即座にエラー値を返して処理を抜ける
If TargetRange Is Nothing Or Keyword = “” Then
FastSumIfContains = CVErr(xlErrValue)
Exit Function
End If
‘ セル範囲の値（Value）を一度にVBAのVariant配列に読み込む（超高速化の肝！）
Dim dataArray As Variant
dataArray = TargetRange.Value
Dim total As Double
total = 0
‘ 単一セルの場合は配列にならないため分岐処理
If Not IsArray(dataArray) Then
If InStr(CStr(dataArray), Keyword) > 0 And IsNumeric(dataArray) Then
total = CDbl(dataArray)
End If
FastSumIfContains = total
Exit Function
End If
‘ メモリ上の配列を2重ループで高速走査（シートへのアクセスゼロ）
Dim r As Long, c As Long
Dim cellValue As Variant
For r = 1 To UBound(dataArray, 1)
For c = 1 To UBound(dataArray, 2)
cellValue = dataArray(r, c)
‘ エラー値（#N/Aなど）が含まれるセルを考慮して安全にチェック
If Not IsError(cellValue) Then
If InStr(CStr(cellValue), Keyword) > 0 Then
If IsNumeric(cellValue) Then
total = total + CDbl(cellValue)
End If
End If
End If
Next c
Next r
‘ 正常な計算結果を返す
FastSumIfContains = total
End Function
💡 このコードのポイント（解説）
1. `TargetRange.Value` を配列へ代入：
`dataArray = TargetRange.Value` とするだけで、指定範囲の値がすべてパソコンの高速なメモリ上にコピーされます。シートへのアクセスが最初の1回で済むため、処理速度が数百倍に向上します。
2. `CVErr(xlErrValue)` によるエラー返却：
UDF内で問題が起きた際、安易に `0` や文字列を返すのではなく、Excel本来の `#VALUE!` エラーを返すのが作法です。これにより、数式を使った側も「引数が間違っている」と気づくことができます。
3. `IsError` での安全対策：
参照先のセルに `#N/A` などのエラーが入っていると、VBAは `InStr` や `IsNumeric` で型不一致（Type Mismatch）エラーを起こして停止します。`IsError` であらかじめガードするのが、プロの配慮です。
—
4. UDF活用のフローチャート（迷ったときの判断基準）
「この処理、UDFにしようかな？」と迷ったら、以下のチャートを思い浮かべてみてください。
[ 実現したい処理がある ]
│
▼
【Q1】標準のワークシート関数で実現できるか？
├─ YES ──> 【結論】標準関数を使おう！（最速・保守性高）
└─ NO
│
▼
【Q2】他のセルの書き換えや、装飾（色付けなど）を行いたいか？
├─ YES ──> 【結論】UDFでは不可！「通常のSubマクロ」を作ろう
└─ NO
│
▼
【Q3】数式が長すぎて、後から見た人が理解不能（保守不能）か？
├─ YES ──> 【結論】UDFを作成！ロジックをきれいに隠蔽しよう
└─ NO ──> 【結論】標準関数を組み合わせた数式で頑張ろう
—
まとめ：正しく使えばUDFは最高の武器になる！
今回は、ユーザー定義関数（UDF）の設計におけるパフォーマンスと使い分けについて解説しました。
- 基本は標準関数が主役、UDFは「補佐」
- UDF内ではセルループを避け、範囲を配列（Variant）化して処理する
- エラー処理・型チェックを怠らず、Excelネイティブの挙動に近づける
ここさえクリアできれば、あなたの作成するワークシートは「読みやすく、壊れにくく、そして爆速」になります。VBAの基本を完全に掌握したと言っても過言ではありませんよ！
焦らず、まずは小さな自作関数から試してみてくださいね。応援しています！
