HOME > 即効テクニック > Excel VBA > 関数関連のテクニック > ユーザー定義関数を作る

ユーザー定義関数を作る|Excel VBA

関数関連のテクニック

ユーザー定義関数を作る

(Excel 97/2000/2002/2003/2007/2010/2013/2016)

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

■引数の数が不定のとき(ParamArray)

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関数で判定できる引数はバリアント型でなければいけません。

なお、ユーザー定義関数では、次のような動作を行うことはできません。

  • セルの挿入/削除
  • 他セルの値の変更
  • アクティブセルや選択範囲の変更
  • シートの挿入/削除や名前の変更
  • Excelの設定の変更