ユーザー定義関数を作る|Excel VBA |
VBAには、値を返さないSubプロシージャと、値を返すFunctionプロシージャがあります。
Sub Sample1()
Dim buf As Long
buf = 123
MsgBox Func1(buf)
End Sub
Function Func1(arg As Long)
Func1 = arg * 2
End Function
Func1のような、値を返すFunctionプロシージャは、ワークシート上でも使用できます。
ワークシート上で使用することを目的としたFunctionプロシージャを、ユーザー定義関数と呼びます。
たとえば、上記のFunc1は、セルに「=Func1(123)」のように入力することで、SUM関数やVLOOKUP関数などと同じように使うことができます。
引数として数字を受け取るユーザー定義関数は、上記Func1の「(arg As Long)」のように引数の型に数値型を指定しておきます。
引数にセルまたはセル範囲を受け取るときは、次のようにRange型の引数を使います。
次のユーザー定義関数は、引数に指定されたセルの文字列を連結して返します。
Function Func2(arg As Range)
Dim c As Range, ans As String
For Each c In arg
ans = ans & c.Text
Next c
Func2 = ans
End Function
CHOOSE関数のように、引数がいくつ指定されるかわからない場合には、ParamArrayというキーワードを付けた引数を宣言します。
次のユーザー定義関数は、CHOOSE関数と同じ働きをします。
Function Func3(arg1 As Long, ParamArray arg2())
Func3 = arg2(arg1 - 1)
End Function
ParamArrayを付けた省略可能なリストは、引数の最後に指定しなければなりません。
VLOOKUP関数の第4引数は省略可能です。省略された場合はTrueが指定されたものとします。
このように省略可能な引数には、Optionalキーワードを付けます。
Optionalキーワードを付けて引数を宣言した場合、その後ろで宣言するすべての引数もOptionalキーワードを付けて省略可能にしなければなりません。
引数が省略されたときの対応には、2通りあります。
1つは、省略されたときの初期値をあらかじめ設定しておく方法です。
Function Func4(arg1 As Range, Optional arg2 As Long = 2)
Func4 = arg1.Value / arg2
End Function
上記のユーザー定義関数は、arg1のセルを、arg2の数値で割った結果を返します。
arg2が省略された場合は初期値として設定した2で除算します。
もう1つは、省略されたかどうかを判定する方法です。
Optionalで省略可能にした引数が省略されたかどうかは、IsMissing関数で判定します。省略されていると、Trueを返します。
Function Func5(arg1 As Range, Optional arg2 As Variant)
Dim buf As Long
If IsMissing(arg2) Then
buf = 2
Else
buf = arg2
End If
Func5 = arg1.Value / buf
End Function
上記のユーザー定義関数では、arg2が省略されたかどうかを判定し、省略されていたら明示的に値を設定しています。
ただし、IsMissing関数で判定できる引数はバリアント型でなければいけません。
なお、ユーザー定義関数では、次のような動作を行うことはできません。