ExcelVBAでSubプロシージャを書けるようになると、次に「セルの中で=関数名()のように自分だけのオリジナル関数を使いたい」と思う方も多いのではないでしょうか。ExcelにはSUMやVLOOKUPなど便利な関数が数多く用意されていますが、自社独自の計算ルールなどは標準の関数だけでは対応できないことがあります。
この記事では、セルに直接入力して使えるユーザー定義関数（UDF：User Defined Function）の作り方を、基本から引数の工夫、注意点まで解説します。
ユーザー定義関数（UDF）とは
ユーザー定義関数とは、Functionプロシージャとして作成し、ワークシートのセルに数式として入力できるようにしたオリジナル関数のことです。SUMやIFと同じように、セルに=関数名(引数)と入力するだけで呼び出せます。
SubプロシージャがVBA内で処理を実行するのに対し、Functionプロシージャは「値を返す」ことができ、その戻り値をセルに表示できる点が特徴です。
基本の作り方
まずは、税抜価格から税込価格を計算するシンプルなUDFを作ってみます。標準モジュールに以下のコードを記述してください。
Function TaxIncluded(price As Double) As Double
TaxIncluded = price * 1.1
End Function
Function 関数名(引数) As 戻り値の型で開始し、End Functionで終了します- 関数名と同じ名前の変数（ここでは
TaxIncluded）に値を代入することで、その値が戻り値になります
作成後、セルに以下のように入力すると、関数として呼び出せます。
=TaxIncluded(A1)
A1セルの値に1.1を掛けた結果が表示されます。SUMやAVERAGEと同じ感覚で、セルや数値を引数として渡せます。
複数の引数を受け取るUDF
引数はカンマ区切りで複数指定できます。以下は、単価と数量から合計金額を計算する例です。
Function TotalPrice(unitPrice As Double, quantity As Long) As Double
TotalPrice = unitPrice * quantity
End Function
=TotalPrice(B2, C2)
B2セルの単価とC2セルの数量を掛け合わせた金額が返されます。複数のセルを組み合わせた独自の計算を、通常の関数と同じように再利用できるのがUDFの強みです。
Optional引数で省略可能なパラメータを作る
Optionalキーワードを使うと、省略可能な引数を持つUDFも作れます。以下は、税率を省略した場合は10%として計算する例です。
Function TaxIncludedRate(price As Double, Optional taxRate As Double = 0.1) As Double
TaxIncludedRate = price * (1 + taxRate)
End Function
=TaxIncludedRate(A1) ' 税率省略時は10%で計算
=TaxIncludedRate(A1, 0.08) ' 軽減税率8%を指定
税率を省略すればデフォルトの10%で、指定すればその税率で計算されるため、1つの関数で複数のケースに対応できます。
条件によって結果を変えるUDF
Functionプロシージャの中でも、If文やSelect Case文を使った条件分岐が可能です。以下は、点数に応じて評価ランクを返すUDFの例です。
Function GetRank(score As Long) As String
Select Case score
Case Is >= 90
GetRank = "S"
Case Is >= 70
GetRank = "A"
Case Is >= 50
GetRank = "B"
Case Else
GetRank = "C"
End Select
End Function
=GetRank(D2)
D2セルの点数に応じて、”S”〜”C”の評価ランクが表示されます。VLOOKUPで判定表を別途用意する必要がなく、ロジックをそのまま関数内に書けるのがUDFのメリットです。
複数セル範囲を引数に受け取るUDF
引数にはRange型を指定することもでき、セル範囲全体を受け取って独自の集計を行うUDFも作成できます。以下は、範囲内の正の数だけを合計する例です。
Function SumPositiveOnly(targetRange As Range) As Double
Dim cell As Range
Dim total As Double
total = 0
For Each cell In targetRange
If IsNumeric(cell.Value) Then
If cell.Value > 0 Then
total = total + cell.Value
End If
End If
Next cell
SumPositiveOnly = total
End Function
=SumPositiveOnly(A1:A10)
A1:A10のうち、正の数だけを合計した結果が返されます。SUMIFでも近い処理はできますが、複雑な条件を組み込みたい場合はUDFのほうが自由度が高くなります。
Application.Volatileで再計算のタイミングを制御する
UDFは、既定では引数として参照しているセルが変更されたときだけ再計算されます。TODAY関数のように、引数に関係なく常に最新の状態で再計算させたい場合は、Application.Volatileを使います。
Function CurrentUserAndTime() As String
Application.Volatile
CurrentUserAndTime = Environ("USERNAME") & " / " & Format(Now, "yyyy/mm/dd hh:nn")
End Function
Application.Volatileを関数の先頭に記述すると、シートが再計算されるたびにこの関数も再計算されるようになります。ただし多用するとブック全体の計算速度が低下するため、本当に必要な場合のみ使用してください。
使用時の注意点
ユーザー定義関数を作る際は、以下の点に注意してください。
- 標準モジュールに書く：UDFはシートモジュールやThisWorkbookモジュールに書いても、セルの数式からは呼び出せません。必ず標準モジュール（Module1など）に記述してください。
- セルの値を直接変更できない：UDFはあくまで「値を返す」関数であり、内部でセルに値を代入したりMsgBoxを表示したりする処理は基本的に行えません（実行時エラーやフリーズの原因になります）。値の変更が必要な場合はSubプロシージャ（マクロ）を使いましょう。
- 関数名の重複に注意：既存のExcel関数と同じ名前を付けると、意図せず標準関数を上書きしたような挙動になる場合があります。分かりやすいオリジナルの名前を付けましょう。
- ブックの保存形式：UDFを使ったブックは、マクロを保存できる形式（.xlsmなど）で保存する必要があります。
まとめ
Functionプロシージャを使うと、SUMやVLOOKUPのような感覚でセルに直接入力できるオリジナル関数を作成できます。
Function 関数名(引数) As 戻り値の型〜End Functionで作成し、標準モジュールに記述する- 関数名と同じ変数に値を代入することで戻り値になる
- Optional引数や条件分岐、Range型の引数を使うことで柔軟な独自関数を作れる
- 常に再計算させたい場合はApplication.Volatileを使うが、多用は避ける
繰り返し使う独自の計算ロジックがある場合は、ぜひユーザー定義関数として整備してみてください。
コメント