VBA 関数 ー 呼び出し、戻り値、およびパラメータ

このチュートリアルでは、VBAでパラメータのある関数とない関数を作成し、使用する方法を学びます。

VBAには多くの組み込み関数がありますが、自分で書くことも可能です。 VBAでコードを書くときは、Subプロシージャ、またはFunctionプロシージャで書くことができます。Functionプロシージャは、コードに値を返すことができます。 これは、VBAにあるタスクを実行させて結果を返したい場合に非常に便利です。VBAの関数は、Excelの通常の組み込み関数と同じように、Excel内部から呼び出すこともできます。

引数なしの関数を作成する

関数を作成するには、関数に名前を付けて定義する必要があります。次に、関数が返すデータの種類を示すデータ型として定義することができます。 呼び出されるたびに静的な値を返すような、いわば定数のような関数を作りたい場合もあるでしょう。

Function GetValue() As Integer
   GetValue = 50
End Function

この関数を実行すると、常に50という値が返されます。

VBAではオブジェクトを参照する関数も作成できますが、関数から値を返すにはSetキーワードを使用する必要があります。

Function GetRange() as Range
  Set GetRange = Range("A1:G4")
End Function

上記の関数をVBAのコードで使用すると、どのシートでも常にセルA1からG4までの範囲を返すようになります。

Subプロシージャから関数を呼び出す

一度関数を作れば、Subプロシージャを使ってコードのどこからでも関数を呼び出すことができます。

この場合、常に50という値が返されることになります。

また、SubプロシージャからGetRange関数を呼び出すこともできます。

上記の例では、SubプロシージャからGetRange関数が呼び出され、Rangeオブジェクト内のセルが太字になっています。

関数の作成

単一引数

また、関数に1つまたは複数のパラメータを割り当てることができます。 これらのパラメータは引数と呼ばれることがあります。

Function ConvertKilosToPounds (dblKilo as Double) as Double
   ConvertKilosToPounds = dblKilo*2.2
End Function

上記の関数をSubプロシージャから呼び出すと、何キロが何ポンドになるかを計算することができます。

必要に応じてVBAのコード内の複数のプロシージャから関数を呼び出すことができます。 これは、同じコードを何度も書く必要がなくなるという点で非常に便利です。 また、長いプロシージャを管理しやすい小さな関数に分割することもできます。

上の例では、2つのプロシージャがあります。どちらのプロシージャも、dblKilo引数で渡されたキログラムのポンド値を計算するために関数を使用しています。

複数の引数

複数の引数を持つFunctionを作成し、その値をSubプロシージャを介してFunctionに渡すことができます。

Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double
   CalculateDayDiff = Date2-Date1
End Function

この関数を呼び出せば、2つの日付の間の日数を計算することができます。

オプションの引数

関数には、オプションの引数を渡すこともできます。 言い換えれば、その関数をどのようなコードで使うかによって、その引数が必要な場合と不要な場合があるということです。

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date) as Double
'2番目の日付をチェックし、もしなければ、Date2に今日の日付を代入する
   If Date2=0 then Date2 = Date
'差分を計算する
   CalculateDayDiff = Date2-Date1 
End Function

引数のデフォルト値

また、関数を作成する際にOptional引数のデフォルト値を設定しておくと、ユーザーが引数を省略した場合に、デフォルトとして設定した値が代わりに使用されるようになります。

Function CalculateDayDiff(Date1 as Date, Optional Date2 as Date="06/02/2020") as Double 
'差分を計算する 
   CalculateDayDiff = Date2-Date1 
End Function

ByValとByRef

関数に値を渡すとき、ByValまたはByRefキーワードを使用することができます。 どちらかを省略した場合は、ByRefがデフォルトとして使用されます。

ByValは変数のコピーを関数に渡すことを意味し、ByRefはその変数の元の値を参照することを意味します。 変数のコピーを渡す場合（ByVal）、変数の元の値は変更されませんが、変数を参照する場合、変数の元の値は関数によって変更されます。

Function GetValue(ByRef intA As Integer) As Integer
   intA = intA * 4
   GetValue = intA
End Function

上の関数では、ByRefを省略しても、同じように動作します。

Function GetValue(intA As Integer) As Integer
   intA = intA * 4
   GetValue = intA
End Function

この関数を呼び出すには、サブプロシージャを実行すればよい。

Sub TestValues()
   Dim intVal As Integer
'変数に値10を代入する
   intVal = 10
'GetValue関数を実行し、その値をイミディエイトウィンドウに表示する
   Debug.Print GetValue(intVal)
'変数intValの値をイミディエイトウィンドウに表示する 
   Debug.Print intVal
End Sub

デバッグウィンドウには、2回とも値40が表示されていることに注意してください。 変数IntValを関数に渡すと、10という値が関数に渡され、4倍されます。ByRefキーワードを使用すると（または完全に省略すると）、変数IntValの値が変更されます。 これは、最初に関数の結果をイミディエイトウィンドウに表示し（40）、次にIntVal変数の値をデバッグウィンドウに表示するとわかります（同じく40）。 もし、元の変数の値を変えたくない場合は、関数内で ByVal を使う必要があります。

Fcuntion GetValue(ByVal intA As Integer) As Integer
   intA = intA * 4
   GetValue = intA
End Function

ここで、サブプロシジャからこの関数を呼び出すと、変数IntValの値は10のままです。

終了関数

ある条件を満たす関数を作成し、その条件が真であることがわかったら、その関数から値を返したい場合、その関数内のすべてのコードを実行する前に関数を終了するために、関数内にExit Functionステートメントを追加する必要があるかもしれません。

Function FindNumber(strSearch As String) As Integer
   Dim i As Integer
'文字列の各文字をループする
   For i = 1 To Len(strSearch)
   '文字が数値の場合、その値を関数に返す
      If IsNumeric(Mid(strSearch, i, 1)) Then
         FindNumber= Mid(strSearch, i, 1)
   'その後、この関数を終了する
         Exit Function
      End If
   Next
   FindNumber= 0
End Function

上の関数は、指定された文字列をループして数字を見つけ、その数字を文字列から返します。 この関数は、文字列の中の最初の数字だけを探して終了（Exit）します。

上の関数は、以下のようなSubルーチンで呼び出すことができます。

Sub CheckForNumber()
   Dim NumIs as Integer
'数値の検索関数にテキスト文字列を渡す
   NumIs = FindNumber("Upper Floor, 8 Oak Lane, Texas")
'結果をイミディエイトウィンドウに表示する
   Debug.Print NumIs
End Sub

Excelシートの中から関数を使用する

VBAコードからSubプロシージャを使用して関数を呼び出すだけでなく、Excelシート内から関数を呼び出すこともできます。 作成した関数は、デフォルトでは関数リストのユーザー定義セクションに表示されるはずです。

fxをクリックすると、「関数の挿入」ダイアログボックスが表示されます。

カテゴリ一覧から「ユーザー定義」を選択します。

利用可能なユーザー定義関数（UDF）の中から必要な機能を選択します。

Excelで関数を書き始めると、関数のドロップダウンリストにその関数が表示されるはずです。

Excelシート内で関数を利用させたくない場合は、VBAコードで関数を作成する際に、Functionの前にPrivateを付ける必要があります。

Private Function CalculateDayDiff(Date1 as Date, Date2 as Date) as Double 
   CalculateDayDiff = Date2-Date1 
End Function

これで、利用可能なExcel関数を示すドロップダウンリストには表示されなくなりました。

しかし、面白いことに、この関数はまだ使うことができます – 関数を探すときにリストに表示されないだけです。

第2引数をOptionalと宣言しておけば、Excelシート内でもVBAコード内でも省略することができます。

また、作成した関数を引数なしでExcelシートで使用することもできます。