ユーザー定義関数の作成

ユーザー定義関数とは？

エクセルには、式に利用できる関数として、SUM() のように計算に関するものや、IF() のように論理条件を制御するものなど、様々なワークシート関数が用意されています。
しかし、既存のワークシート関数で要望を満たすものがない場合、または、要望と機能が少し異なるような場合、VBA(Visual Basic for Applications)を使用して、独自の関数を作成することができます。
作成した、このユーザー独自の関数を「ユーザー定義関数」と呼びます。

ここでは、簡単な例として「ヘロンの公式（３辺の長さから三角形の面積を求める）」を作成します。
もちろん、ワークシート上で数式としても設定できますが、かなり長い式となります。
式が複数回登場するような場合は、関数として作成した方が簡単で便利です。

三辺の長さから三角形の面積を求める

a,b,c を三角形の３辺とするとき、

面積Tは、

で求められます。

この三角形の三辺の長さを入力するだけで、その面積を求めるユーザー定義関数Helon を作成します。

まずはユーザー定義関数を作成する方法を確認します。

ユーザー定義関数の作成自体は簡単で、VBA の標準モジュールに Function関数を記述するだけですぐに利用できます。以下、手順

１．開発タブの表示

マクロ（VBAコード）を記述するには、まず、開発タブを表示しておく必要があります。

開発タブを表示しておかなければ、マクロの記録、マクロの実行、マクロの編集が出来ません。

開発タブの表示方法

２．標準モジュールの表示（追加）

マクロの編集、VBAコードを入力するために、標準モジュールを表示しておきます。

標準モジュールの追加方法

３．コードの記述

以下の例は、三辺の長さ（A , B , C）を入力パラメータとして与えると、
その三辺で作られる三角形の面積を返す関数です。

Function Heron(A As Single, B As Single, C As Single) As Single
'
' ヘロンの公式
'
' 3辺の長さから面積を求めます
    
    Dim s As Single
    
    s = (A + B + C) / 2
    Heron = Sqr(s * (s - A) * (s - B) * (s - C))

End Function

アドインなどで複数ブックからの利用を想定している場合は Public を先頭に追加します。

４．戻り値

Function 関数名() As 戻り値のデータ型
　関数名 = 戻り値
End Function

変数の定義のように関数名と戻り値のデータ型を記述します。

Public Function Heron(A As Single, B As Single, C As Single) As Single

処理の結果得られる値を関数名に代入すると戻り値になります。

  Heron = Sqr(s * (s - A) * (s - B) * (s - C))

５．コメント

ちなみに、アポストロフィー（apostrophe）「'」は、コメント化する記号です。
「'」以降、行末までの１行がコメント（VBAコードとしては認識されず、
単なる文字列として無視される）として扱われます。

複数行のコメントを記述したい場合は「/*　～ */」で囲みます（途中改行可）。

このような VBA コードを追加した状態でシート上でセルに関数名を入力すると、すぐに利用できます。

以上で、ユーザー定義関数の作成は（ほぼ）完了です。

自分でのみ使用する場合は、前項で十分ですが、人に使用してもらう場合は、もう少し工夫が必要です。

このままの状態では、Excel に標準で用意されているワークシート関数で表示される
利用方法の説明、入力支援やヘルプファイルのような便利な機能が使用できません。

以下、使用するにあたって、それらが使えるように機能を追加します。

関数の情報を登録する

関数を任意のカテゴリに追加したり、リボンメニューの [数式] タブ → [関数の挿入] で確認できる関数の説明やヘルプをユーザー定義関数にも表示させるには、Application.MacroOptions メソッドで登録処理を実行します。
登録処理は Workbook_Open イベント（ブックが開かれるときに呼び出される）などに実装できます。

記述例

Sub RegisterMyFunction()
    Application.MacroOptions Macro:="Heron", _
    Description:="３辺の長さから三角形の面積を求めます", _
    Category:="数学/三角", _
    ArgumentDescriptions:=Array("辺Aの長さ", "辺Bの長さ", "辺Cの長さ"), _
    HelpFile:="http://mt-soft.sakura.ne.jp/kyozai/excel_mid/150_macro/50_userfunction.htm"
End Sub

行末のアンダースコア（ _ ）は、行が継続（改行を無視）されることを示します。
C言語やJAVAなどのフリーフォーマット言語は、任意の位置に改行を置けますが、VBは行単位の記述なので、
１文が長過ぎる場合は、「 _ 」を入れて改行します。（「 _ 」は継続行を意味する）
「 _ 」が無いと、エラーが発生するので注意。

上記の定義登録により、作成した関数が分類に登録され、説明が表示されます。（下図）

パラメータ説明

以下、上記で記述した関数登録処理の説明。

関数名(Macro:)

本例では「Helon」とします。

関数の説明(Description:)

Description: の後に関数の説明を入力します。
数式タブの関数一覧にも関数とその説明が表示されます。

関数の説明の中で改行したい場合は、改行コード「Chr（13）」を文字列として連結します。
下記のように記述すると、

Description:="３辺の長さから" & Chr(13) & "三角形の面積を求めます"

下図のように表示されます。

分類(Category:)

Category パラメーターの分類一覧

整数値  分類

 1      財務
 2      日付/時刻
 3      数学/三角
 4      統計
 5      検索/行列
 6      データベース
 7      文字列操作
 8      論理
 9      情報
10      コマンド
11      ユーザー設定
12      マクロ制御
13      DDE/外部
14      ユーザー定義

入力パラメータ(ArgumentDescriptions:)

ArgumentDescriptions: の項目に、カンマ区切りで入力パラメータ名を記述します。
関数ダイアログボックスを開くと、それぞれのパラメータの説明が表示されます。

ヘルプファイル(HelpFile:)

ヘルプへのURLを指定します。

Application.MacroOptions メソッドにて、HelpFile パラメーターで、ユーザー定義関数にヘルプ情報をリンクすることもできます。
本例では、このウェブページをヘルプのURLとしてしてあります。

ヘルプファイルをリンクすると、関数の一覧で「この関数のヘルプ」をクリックしたときに、ヘルプページが表示されます。

注意事項

ユーザー定義関数の情報登録は、Workbook.Openの中で行っていますので、
登録情報を変更しただけでは反映されません。

ブックを閉じて、再度、開く必要があります。

入力パラメータについて

関数に渡す入力パラメータは、日本語（漢字）が良いかもしれません。
一般的なプログラム記述では、英数で全て記述しますが、ユーザー定義関数については日本語の入力変数名の方が分かりやすいかもしれません。
本例では、A、B、Cとしていますが、　

辺Aの長さ、辺Bの長さ、辺Cの長さ

のようにすると、下図のように表示され、分り易くなります。

ただし、VBAコードの記述自体は、少々、面倒になります。
内部的には、

　Dim A,B,C
　A=辺Aの長さ
　B=辺Bの長さ
　C=辺Cの長さ

のように、入力パラメータを簡単な英数文字に置き換えることが必要になるでしょう。

制限事項

インテリセンス不可

なお、残念ながら以下のようなセル入力時の関数一覧のツールチップや、関数入力時のインテリセンスは表示することはできません。

下図 SUM　の場合のようなインテリセンスは、ユーザ定義関数では表示不可
ユーザー定義関数として作成した Helon との表示相違

自動計算について

通常（自動再役さんスイッチがオンの場合）、セルの値を変更すると、そのセルを参照しているセルの計算式は、全て再計算されます。
しかし、参照しているセルの値が変化していない場合は、ユーザー定義関数は再計算されません。
本例のような簡単な関数の場合は問題ありませんが、内部で Now() のように、呼び出すたびに異なる値を返す関数などを使用している場合は、少々、不都合が生じます。

「シート再計算（F9）」を実行しても、変更されたセルを参照していないセルの計算式は再計算されず、常に同じ値となります。

そのために用意されているメソッドが、Application.Volatile です。

書式：　Application.Volatile( Volatile )

Volatile：True を指定すると、その関数は自動再計算関数になります。

参照していないセルでも、値が」変化すると、全てのセルの式が再計算されます。
False を指定すると、非自動再計算関数となります。既定値は True です。

下記のように関数処理の先頭行に追加します。

Sub RegisterMyFunction()
    Application.Volatile True '強制再計算
    Application.MacroOptions Macro:="Heron", _
    Description:="３辺の長さから三角形の面積を求めます", _
    Category:="数学/三角", _
    ArgumentDescriptions:=Array("辺Aの長さ", "辺Bの長さ", "辺Cの長さ"), _
    HelpFile:="../../../../kyozai/excel_mid/150_macro/50_userfunction.htm"
End Sub

ただし、この関数は常に再計算されるので、ワークシートを開いたりセルの値を変更する場合、VBAの内容によっては、表示速度が遅くなることがありますのでご注意！

マクロ登録-ブックを開くときに登録

Private Sub Workbook_Open()
    Call RegisterMyFunction   'ユーザー関数登録
End Sub

マクロ情報登録

Option Explicit

Sub RegisterMyFunction()
    Application.MacroOptions Macro:="Heron", Description:="３辺の長さから三角形の面積を求めます", _
    Category:="数学/三角", ArgumentDescriptions:=Array("辺Aの長さ", "辺Bの長さ", "辺Cの長さ"), _
    HelpFile:="help-heronsFomula.html"
End Sub

関数本体

Function Heron(A As Single, B As Single, C As Single) As Single
'
' ヘロンの公式
'
' 3辺の長さから面積を求めます
    
    Dim s As Single
    
    s = (A + B + C) / 2
    Heron = Sqr(s * (s - A) * (s - B) * (s - C))

End Function

書式　Heront(A , B , C)

　正規表現で文字列中から指定の文字列を検索し、見つかったら、その文字列を返す

パラメータ

A	辺Aの長さ
B	辺Bの長さ
C	辺Cの長さ

戻り値

見つかった文字列、見つからなかった場合は空白。

ユーザー定義関数の作り方

ユーザー定義関数とは？

作成するユーザー定義関数例