WorksheetFunction使用時のパフォーマンスへの影響について
レーベンシュタイン距離を求めるVBA（スピル対応）の作成において、3つの値の最小値を求める必要がありました。
当初は、「WorksheetFunction.Min」を使用したのですが、テストデータを増やしていくと、極端に処理時間がかかるようになってしまいました。
対策としては、「WorksheetFunction.Min」を使わずに独自Functionを作成して対応しました。
そこで、この二つの方法で、実際にどの程度処理速度の差があるのかを検証したときの記録です。
検証に使った、レーベンシュタイン距離を求めるVBA
Public Function LevenshteinFunction(ByVal s1 As String, ByVal s2 As String, opt) As Long
Dim i1 As Long, i2 As Long
Dim lenS1 As Long, lenS2 As Long
Dim arr1() As String, arr2() As String
Dim d() As Long ' 動的計画法テーブル
lenS1 = Len(s1)
lenS2 = Len(s2)
' 特殊なケース: どちらか、または両方が空文字列
If lenS1 = 0 Then
LevenshteinFunction = lenS2
Exit Function
End If
If lenS2 = 0 Then
LevenshteinFunction = lenS1
Exit Function
End If
' 文字列を1文字ごとの配列に変換：Mid関数の繰り返し呼び出しを避ける
ReDim arr1(1 To lenS1)
ReDim arr2(1 To lenS2)
For i1 = 1 To lenS1
arr1(i1) = Mid$(s1, i1, 1)
Next
For i2 = 1 To lenS2
arr2(i2) = Mid$(s2, i2, 1)
Next
' 動的計画法のためのテーブルを確保 (サイズ: (lenS1+1) x (lenS2+1))
ReDim d(0 To lenS1, 0 To lenS2)
' 1行目と1列目を初期化 (削除/挿入の初期コスト)
For i1 = 0 To lenS1
d(i1, 0) = i1
Next
For i2 = 0 To lenS2
d(0, i2) = i2
Next
' メインの距離計算ループ
Dim cost As Long, costDel As Long, costIns As Long, costSub As Long
For i1 = 1 To lenS1
For i2 = 1 To lenS2
' コストの決定: 文字が異なれば1、同じなら0
cost = -(arr1(i1) <> arr2(i2)) ' True(-1)なら1、False(0)なら0
' 3つの操作(削除/挿入/置換)のコストを計算
costDel = d(i1 - 1, i2) + 1 ' 削除 (上から)
costIns = d(i1, i2 - 1) + 1 ' 挿入 (左から)
costSub = d(i1 - 1, i2 - 1) + cost ' 置換/一致 (左上から)
' 最小コストを選択し、テーブルに記録
' ※以下の2通りの方法での処理速度の違いの検証です。
If opt = 1 Then
d(i1, i2) = Min3(costDel, costIns, costSub)
Else
d(i1, i2) = WorksheetFunction.Min(costDel, costIns, costSub)
End If
Next
Next
' 最終的な距離はテーブルの右下隅の値
LevenshteinFunction = d(lenS1, lenS2)
End Function
Private Function Min3(ByVal val1 As Long, ByVal val2 As Long, ByVal val3 As Long) As Long
' 最初の2つの最小値を求める
If val1 < val2 Then
Min3 = val1
Else
Min3 = val2
End If
' 3つ目の値と比較し、最小値を更新
If val3 < Min3 Then Min3 = val3
End Function
上記VBAは、以下に掲載したVBAから、レーベンシュタイン距離を求める本体部分を抜き出したものです。
レーベンシュタイン距離を求めるVBA（スピル対応）とセル数式
処理速度を計測するVBA
Sub test()
Dim t As Single, i As Long, j As Long
Dim ary(1 To 10, 1 To 2) As Double
Application.Calculation = xlCalculationManual
For i = 1 To 10
t = Timer
For j = 2 To 11
Cells(j, 3).Value = LevenshteinFunction(Cells(j, 1).Value, Cells(j, 2).Value, 1)
Next
ary(i, 1) = Timer - t
DoEvents
t = Timer
For j = 2 To 11
Cells(j, 3).Value = LevenshteinFunction(Cells(j, 1).Value, Cells(j, 2).Value, 2)
Next
ary(i, 2) = Timer - t
DoEvents
Next
Range("F2").Resize(UBound(ary, 1), UBound(ary, 2)).Value = ary
End Sub
上記VBAで処理速度を検証しました。
以下のステップのみ取り替えての速度比較です。
d(i1, i2) = Min3(costDel, costIns, costSub)
d(i1, i2) = WorksheetFunction.Min(costDel, costIns, costSub)
| Min3 | WorksheetFunction | |
| 1回目 | 0.078125 | 1.472656 |
| 2回目 | 0.089844 | 1.464844 |
| 3回目 | 0.062500 | 1.453125 |
| 4回目 | 0.074219 | 1.468750 |
| 5回目 | 0.066406 | 1.437500 |
| 6回目 | 0.085938 | 1.464844 |
| 7回目 | 0.062500 | 1.468750 |
| 8回目 | 0.066406 | 1.468750 |
| 9回目 | 0.066406 | 1.453125 |
| 10回目 | 0.058594 | 1.468750 |
差は説明するまでもなく歴然です。
この結果は、予想以上の差があると感じられたのではないでしょうか。
技術的な解説：VBAとワークシート関数の「境界線コスト」
- WorksheetFunction.Minの動作（遅い原因）
VBAコード内でWorksheetFunction.Minを呼び出すとき、内部で以下の処理が発生します。- 環境の切り替え（オーバーヘッド）: VBAの実行スレッドが中断され、Excelのワークシート計算エンジンに制御が移ります。この環境の切り替え自体に時間がかかります。
- 型変換: VBAの変数が、ワークシート関数が認識できるデータ型に変換されます。
- 計算の実行: ワークシートエンジンが最小値の計算を行います。
- 環境の復帰: 計算結果がVBAのデータ型に再変換され、VBA実行スレッドに制御が戻されます。
このため、呼び出しのたびに発生するわずかな「環境切り替えのコスト」が積み重なり、処理時間が著しく増大します。 - Min3関数の動作（速い理由）
一方、Min3関数は、VBAモジュール内で定義された純粋なVBA関数です。- VBA内処理の完結: Min3関数内で行われるのは、VBAの実行スレッド内でのシンプルなIf文による比較操作のみです。
- 境界線コストの回避: 外部のワークシートエンジンへの切り替えや、それに伴う型変換などのオーバーヘッドが一切発生しません。
- 効率的な直接処理: 繰り返される最小値の計算が、最も効率的かつ直接的なVBAのロジックで実行されるため、非常に高速に処理が完了します。
- 総括
今回の検証結果から分かるのは、VBAにおける処理速度の差は、
アルゴリズムそのものよりも「どの実行環境で処理を行うか」によって大きく左右されるという点です。WorksheetFunction を呼び出すたびに、VBAランタイムとExcel計算エンジンの間でデータが受け渡され、その都度、環境切り替え（境界線コスト）と型変換が発生します。
単発であれば微小な遅延でも、ループ内で数十万回繰り返されると、そのコストが累積して大きな速度差となります。一方で、Min3 のような純粋なVBA関数では、すべての演算が同一スレッド内で完結します。
外部エンジンへの切り替えやデータ変換が不要なため、境界線コストを完全に回避でき、繰り返し処理において圧倒的に有利です。このことから、VBAではループや再帰を含む高頻度処理では、WorksheetFunction の使用を避け、処理をVBA内で完結させる設計がパフォーマンス最適化の基本方針であることが確認できます。
同じテーマ「マクロVBA技術解説」の記事
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較
スピルって速いの?スピルの速度について
1次元配列の下限インデックスを高速に変更する関数
レーベンシュタイン距離を求めるVBA（スピル対応）とセル数式
WorksheetFunction使用時のパフォーマンスへの影響について
Dirは限界！FSOは遅い！VBAファイル検索をWindows APIで爆速化
新着記事NEW ・・・新着記事一覧を見る
最長連続出現数（ランレングス）の算出｜エクセル練習問題(2025-11-15)
SQL基礎問題11：連続期間の開始月と終了月を抽出｜SQL入門(2025-11-14)
セル数式における「再帰」の必要性｜エクセル雑感(2025-11-10)
掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算｜エクセル関数応用(2025-11-10)
配列を自在に回転させる数式｜エクセル関数応用(2025-11-09)
非正規化（カンマ区切り）の結合と集計：最適な手法は？｜エクセル雑感(2025-11-06)
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計｜SQL入門(2025-11-06)
SQL基礎問題9：特定商品購入者の平均購入金額｜SQL入門(2025-11-04)
SQL基礎問題8：バスケット分析・ペア商品の出現回数｜SQL入門(2025-11-04)
SQL基礎問題7：成績表から各教科の最高点と最低点を抽出｜SQL入門(2025-11-02)
アクセスランキング ・・・ ランキング一覧を見る
1.生成AIパスポート試験 練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- WorksheetFunction使用時のパフォーマンスへの影響について
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.