bizvba_160Excelには、関数が存在するが、「ユーザー定義関数」として自分で関数を作ることができる

ユーザー定義関数の作成は、とても簡単です。

VBE を開いて(Excel で Alt + F11)、標準モジュールに、例として次のコードを入力してみてください。


Function 損益分岐点売上高(ByVal 固定費 As Long, ByVal 限界利益率 As Double)

    損益分岐点売上高 = Int(固定費 / 限界利益率)
    
End Function


Function 目標達成売上高(ByVal 目標利益 As Long, ByVal 固定費 As Long, ByVal 限界利益率 As Double)
    
    目標達成売上高 = Int((固定費 + 目標利益) / 限界利益率)
    
End Function

 ↓ こんな感じです。

vbe

通常、Sub ~ End Sub の間にプログラムを書きますが、ユーザー定義関数を作る時は、
Function ~ End Function の間に書きます。

Function の後ろのプロシージャ名が関数名になります。上記例では、「損益分岐点売上高」と「目標達成売上高」が関数名になります。

関数なので、プロシージャには引数が必要になります。引数で値をユーザーから受け取って、プログラムで計算して、計算結果を返します。
上記「損益分岐点売上高」のプロシージャでは、引数として「固定費」と「限界利益率」を受け取って計算しています。

プログラムした計算結果が、最終的にプロシージャ名に代入されるようにします。「=」で、プロシージャ名に代入しています。

これで、作成完了です。


では、Excel側でユーザー定義関数を使ってみましょう。

Excelに戻って、入力したいセルを選択してから、数式バーの 「fx」マークを押して「関数の挿入」ダイアログボックスを表示して、関数の分類を「ユーザー定義関数」にします。(下図)

dialog1

すると、関数名に、損益分岐点売上高と、目標達成売上高が表示されます。

まずは、損益分岐点売上高を選択してみます。
「関数の引数」ダイアログボックスが表示されるので、固定費に「13,600」を、限界利益率に「0.12」と入力してみます。結果は、113,333となります。(下図)

dialog2

セルには、「=損益分岐点売上高(13600,0.12)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。



次は、目標達成売上高を選択した場合です。
「関数の引数」ダイアログボックスが表示されるので、目標利益に「10,000」、固定費に「13,600」を、限界利益率に「0.18」と入力してみます。結果は、131,111となります。(下図)

dialog3

セルには、「=目標達成売上高(10000,13600,0.18)」と入力されます。
ダイアログボックスを使わずに、直接セルに入力しても大丈夫です。


ユーザー定義関数を作ることで簡単に計算できるようになり、また、セルに入力する算式も短くなるメリットがあります。VBAで、その他いろいろな関数を作ってみてください。