ユーザー定義関数は便利な割にあまり使われていない気がします。名前のせいでしょうか。「関数型マクロ」と言い換えるとわかりやすいでしょうか。
ユーザー定義関数の特徴
関数的なところ
「=ユーザー定義関数名(引数)」の形式で数式と同様に入力して利用する
マクロのような「実行」のアクションは不要。自動で実行され結果が返される
他のセルの値やマクロで取得した値などから算出した結果を表示する
他のセルの値を変えたりシートの操作したりすることはできない
マクロ的なところ
VBAのFunctionとしてコーディングする。構造や記述方法は通常のFunctionプロシージャと同じ
マクロが実行できる環境が必要。セキュリティ設定などでマクロ実行不可な環境では機能しない
独自の名前で作成できる（日本語可）
関数が多重にネストしたり長文になったりすると、自作でもメンテナンス時に解読に難渋します。それに比べるとVBAコードのほうが処理を整理しやすいのです。また留意点などをコメントで併記しておけるのも安心です。
ユーザー定義関数の例
表示中の値だけを対象に平均値を表示するものです。フィルターで表示を絞り込むと値が変化します。
E1に入力する数式
=uf_可視セルの平均(B2:B12)
ユーザー定義関数のVBAコード
Function uf_可視セルの平均(rng As Range)
Dim i As Long, j As Long
Dim buf As Long
For i = 1 To rng.Rows.Count
If rng.Rows(i).Hidden = False Then
buf = buf + rng.Rows(i).Cells(1).Value
j = j + 1
End If
Next i
uf_可視セルの平均 = Round(buf / j, 1)
End Function=ROUND(SUBTOTAL(1,B2:B12),1)
SUBTOTAL関数が非表示の値を除外することと集計方法の「1」がAVERAGEであることが難なく読み解ける人には、関数のほうがこの場合はお手軽かもしれません。ユーザー定義関数の保存先
通常は関数を使うブックファイルに保存します。
次のように数式を記述すれば、他のブックファイルに保存したユーザー定義関数をすることもできます。=PERSONAL.XLSB!uf_可視セルの平均(B2:B12)
ただし、ファイルの扱いについては外部ファイルの参照と同様に注意が必要です。
ユーザー定義関数の応用例
セルに入力するほかにも利用方法が
数式と同様に、セルに入力する以外の使い方もできます。
例えば、条件付き書式で「=ユーザー定義関数名(対象セル)<100」のように指定できます。
チェック結果をわかりやすく
数値でなくメッセージテキストを返す使い方ができます。
例えば、各列をチェックして、どの列にどのような問題があるかを具体的に示すメッセージを表示するといったことができます。
引数を使わないユーザー定義関数
次の例は、ブックの保存場所を表示します。呼び出す数式は「
=uf_WBPath()」と指定します。=TODAY() などと同様です。Function uf_WBPath()
uf_WBPath = ActiveWorkbook.Path
End Function自セルを表すThisCell
ユーザー定義関数を呼び出す数式があるセルは、Application.ThisCell で表せます。このため、引数で渡す必要はありません。
次の例は、数式があるセルの左のセルの値を取得します。
val = Application.ThisCell.Offset(0, -1).Value
