Excel でカスタム関数を作成する

マクロなどのカスタム関数では、Visual Basic for Applications (VBA) プログラミング言語を使用します。 マクロとは 2 つの重要な点で異なります。 最初に、Sub プロシージャの代わりに Function プロシージャを使用します。 つまり、Sub ステートメントではなく Function ステートメントで始まり、End Sub ではなく End Function で終了します。 2 つ目は、アクションを実行する代わりに計算を実行することです。 範囲を選択して書式設定するステートメントなど、特定の種類のステートメントは、カスタム関数から除外されます。 この記事では、カスタム関数を作成して使用する方法について説明します。 関数とマクロを作成するには、Excel とは別の新しいウィンドウで開く Visual Basic エディター (VBE) を使用します。

あなたの会社の製品販売で、注文が 100 個を超える場合、10% の数量割引を行うとします。 以下の段落で、この割引を計算する関数を示します。

次の例は、各品目、数量、価格、割引 (ある場合)、および合計金額の一覧を表示する注文書を示します。

このブックに DISCOUNT カスタム関数を作成するには、次の手順を実行します。

1. Alt キーを押しながら F11 キー (Mac では fn キーと option キーを押しながら F11 キー) を押して Visual Basic Editor を開き、[挿入]、[モジュール] の順にクリックします。 Visual Basic Editor の右側に新しいモジュール ウィンドウが表示されます。

2. 次のコードをコピーして新しいモジュールに貼り付けます。

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

これで、新しい DISCOUNT 関数を使用する準備が整いました。 Visual Basic Editor を閉じて、セル G7 を選択し、次のように入力します。

=DISCOUNT(D7,E7)

Excel は、1 個あたり 47.50 ドルの製品 200 個に対する 10% の割引を計算し、950.00 ドルを返します。

VBA コードの 1 行目の Function DISCOUNT(quantity, price) で、DISCOUNT 関数には quantity と price の 2 つの引数が必要であることを示しました。 ワークシートのセルでこの関数を呼び出す場合は、これら 2 つの引数を含める必要があります。 数式 = DISCOUNT(D7,E7) では、D7 が quantity 引数で、E7 が price 引数です。 これで、G8 から G13 に DISCOUNT 式をコピーすると、以下に示す結果を得ることができます。

Excel がこの Function プロシージャを解釈する方法について考えてみましょう。 Enter キーを押すと、Excel は現在のブックで DISCOUNT という名前を検索し、それが VBA モジュールのカスタム関数だとわかります。 かっこで囲まれた引数名の quantity と price は、割引の計算の基になる値のプレースホルダーです。

次のコード ブロック内の If ステートメントは quantity 引数を調べ、販売された品目の数が 100 以上かどうかを判定します。

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

販売された品目の数が 100 以上の場合、VBA は次のステートメントを実行します。このステートメントは quantity 値に price 値を掛けて、その結果に 0.1 を掛けます。

Discount = quantity * price * 0.1

結果 は変数 Discount として格納されます。 変数に値を格納する VBA ステートメントは、等号の右側にある式を評価し、結果を左側の変数名に代入するため、 代入 ステートメントと呼ばれます。 変数 Discount の名前は関数プロシージャと同じであるため、変数に格納されている値は DISCOUNT 関数を呼び出したワークシートの数式に返されます。

quantity が 100 未満の場合、VBA は次のステートメントを実行します。

Discount = 0

最後に、次のステートメントは、Discount 変数に割り当てた値を小数点以下 2 桁に四捨五入します。

Discount = Application.Round(Discount, 2)

VBA には ROUND 関数がありませんが、Excel にはあります。 そのため、このステートメントで ROUND を使用するには、Application オブジェクト (Excel) 内で Round メソッド (関数) を探すように VBA に指示します。 これには、Round という単語の前に Application という単語を追加します。 VBA モジュールから Excel 関数にアクセスする必要があるときはいつでも、この構文を使用します。

カスタム関数は Function ステートメントで始まり、End Function ステートメントで終わる必要があります。 Function ステートメントは通常、関数名に加えて、1 つまたは複数の引数を指定します。 ただし、引数なしで関数を作成することもできます。 Excel には、引数を使用しない組み込み関数がいくつか含まれています (たとえば、RAND や NOW)。

Function ステートメントに続いて、Function プロシージャには、関数に渡された引数を使用して判定を行い、計算を実行する 1 つまたは複数の VBA ステートメントが含まれています。 最後に、Function プロシージャのどこかに、関数と同じ名前の変数に値を代入するステートメントを含める必要があります。 この値は、関数を呼び出す式に返されます。

カスタム関数で使用できる VBA キーワードの数は、マクロで使用できる数より少なくなっています。 カスタム関数では、ワークシート内の式、または別の VBA マクロや関数内で使用されている式に値を返す以外のことは許可されていません。 たとえば、カスタム関数は、ウィンドウのサイズ変更、セル内の式の編集、セル内のテキストのフォント、色、パターンの各オプションの変更を行うことができません。 Function プロシージャにこの種の “アクション“ コードを含めると、#VALUE! エラーが返されます。

Function プロシージャが実行できる (計算の実行とは別の) 1 つのアクションは、ダイアログ ボックスの表示です。 関数を実行しているユーザーから入力を取得する手段として、カスタム関数内で InputBox ステートメントを使用することができます。 ユーザーに情報を伝える手段として、MsgBox ステートメントを使用することができます。 また、カスタム ダイアログ ボックスの UserForms を使用することもできますが、この概要の範囲を超えた話題です。

単純なマクロとカスタム関数でも読みにくい可能性があります。 コメントの形式で説明文を入力して、理解しやすくすることができます。 コメントを追加するには、説明文の前にアポストロフィを付けます。 たとえば、次の例では、コメント付きの DISCOUNT 関数を示しています。 このようなコメントを追加すると、作成者または他のユーザーが将来 VBA コードを保守しやすくなります。 今後コードを変更する必要がある場合、元の処理を容易に理解できます。

アポストロフィは、同じ行の右にあるすべてを無視するように Excel に通知するので、行に単独でまたは VBA コードを含む行の右側にコメントを作成することができます。 まず、比較的長いコード ブロックに全体的な目的を説明するコメントを追加し、次に文書の個々のステートメントにインライン コメントを使用することができます。

マクロとカスタム関数を文書化する別の方法は、わかりやすい名前を付けることです。 たとえば、マクロに Labels という名前を付けるのではなく、MonthLabels という名前を付けると、マクロが果たす目的をより具体的に説明することができます。 マクロとカスタム関数にわかりやすい名前を付けると、特に多数のプロシージャを作成した場合、または目的は似ているが同一ではないプロシージャを作成する場合に役立ちます。

マクロとカスタム関数をどのように文書化するかは、個人の好みの問題です。 重要なのは、ある文書化の方法を採用し、一貫して使用することです。

カスタム関数を使用するには、作成した関数が入っているモジュールを含むブックを開く必要があります。 そのブックが開いていない場合、その関数を使用しようとすると、#NAME? エラーが発生します。 別のブックで関数を参照する場合は、関数名の前に、その関数が属するブックの名前を付ける必要があります。 たとえば、Personal.xlsb というブックに DISCOUNT という関数を作成し、その関数を別のブックから呼び出す場合は、単に =discount() と入力するのではなく、=personal.xlsb!discount() と入力する必要があります。

[関数の挿入] ダイアログ ボックスからカスタム関数を選択すると、いくつかのキー操作を省略できます (また、入力ミスが減る可能性があります)。 カスタム関数は、[ユーザー定義] カテゴリに表示されます。

カスタム関数をいつでも利用できるようにするより簡単な方法は、それらを別のブックに格納し、そのブックをアドインとして保存することです。 その後、Excel を実行するたびに、アドインを利用可能にすることができます。 その手順は次のとおりです。

これらの手順を実行すると、Excel を実行するたびに、カスタム関数が利用可能になります。 関数ライブラリに追加する場合は、Visual Basic Editor に戻ります。 Visual Basic Editor のプロジェクト エクスプローラーの VBAProject 見出しの下に、作成したアドイン ファイルに基づいて名前が付けられたモジュールが表示されます。 アドインには、拡張子 .xlam が付けられます。

Project エクスプローラーでそのモジュールをダブルクリックすると、Visual Basic エディターに関数コードが表示されます。 新しい関数を追加するには、[コード] ウィンドウで最後の関数を終了する End Function ステートメントの後に挿入ポイントを配置し、入力を開始します。 この方法で必要な数の関数を作成でき、[ 関数の挿入 ] ダイアログ ボックスの [ユーザー定義] カテゴリで常に使用できます。

このコンテンツはもともと、書籍 Microsoft Office Excel 2007 Inside Out の一部として Mark Dodge と Craig Stinson によって作成されました。 以降、新しいバージョンの Excel にも適用されるように更新されています。