7-1.VBAで使用できる関数

今回は、VBAで使用できる関数について説明します。
VBAで使用できる関数には、ワークシート関数VBA関数ユーザー定義型関数の3種類あります。

ワークシート関数

ワークシート関数は、エクセルで使用できる関数を指します。
VBAでワークシート関数を使用する方法は、WorksheetFunctionプロパティEvaluateメソッド[]による記述の3種類あります。

WorksheetFunctionプロパティ

○構文
Application.WorksheetFunction.関数名(引数)

Applicationは省略可能で、通常は省略します。
関数名と引数は、エクセルで使用する時と同じ関数名と引数を使用します。

○処理
関数の処理結果を戻り値として返します。

Sub WorksheetFunctionプロパティ()
    
    'Sum関数
    Range("B1").Value = WorksheetFunction.Sum(Columns("A").Value)
    
    'Average関数
    Range("B2").Value = WorksheetFunction.Average(Columns("A").Value)
    
End Sub

Evaluateメソッド

○構文
Application.Evaluate(”関数名(引数)”)

Applicationは省略可能で、通常は省略します。
関数名と引数は、エクセルで使用する時と同じ関数名と引数を使用します。

○処理
関数の処理結果を戻り値として返します。

Evaluateメソッドは、()内の文字列式をワークシート関数として処理するメソッドです。
()内に、エクセルで使用する時と同じ形式で、関数名(引数)を文字列として指定します。

なお、文字列だけでなく、プロパティやメソッドで取得できる値や、文字列式を格納した変数を指定することもできます。

Sub Evaluateメソッド()
        
    'Sum関数
    Range("B1").Value = Evaluate("Sum(A:A)")
    
    Range("B1").Value = Evaluate("Sum(" & Columns("A").Address & ")")
    
    'Average関数
    Range("B2").Value = Evaluate("Average(A:A)")
    
    Dim str As String
    str = "Average(" & Columns("A").Address & ")"
    Range("B2").Value = Evaluate(str)
    
End Sub

[]

○構文
[関数名(引数)]

関数名と引数は、エクセルで使用する関数名と引数と同じです。

○処理
関数の処理結果を戻り値として返します。

[]内に、エクセルで使用する時と同じ形式で、関数名(引数)を指定します。
Evaluateメソッドと違い、文字列として指定する必要はありません(””で囲う必要はありません)。

Sub サンプルコード()
    
    'Sum関数
    Range("B1").Value = [Sum(A:A)]
    
    'Average関数
    Range("B2").Value = [Average(A:A)]
    
End Sub

VBA関数

VBA関数とは、VBA特有の関数のことです。

種類が多いのでここでは触りだけご紹介しますが、ワークシート関数と同じ処理を行う関数もあれば、同じ名前で処理が異なる関数もあります。

VBA関数を使用することで、データを処理に使いやすい形式に加工したりすることができます。

ユーザー定義型関数

基本構文

○構文
Function プロシージャ名(第1引数名 as データ型,第2引数名 as データ型, …)

ユーザー定義型関数とは、VBAで作成した開発者独自の関数のことを指し、Functionプロシージャで作成します。

Functionプロシージャは、プロシージャの処理結果を戻り値として取得することができます。
したがって、Functionプロシージャ自体を関数として利用することができます。これをユーザー定義型関数と呼びます。

なお、このFunctionプロシージャが記述されているブックや、Functionプロシージャをアドインとして読み込んでいる場合は、通常の関数として、エクセルシート内で使用することができます。
使用する際は、通常の関数と同様、=プロシージャ名で記述します。

○引数
引数には、プロシージャ内で使用する変数を指定します。
通常の変数の宣言(Dimステートメント)と同様、変数名とデータ型を指定します。

引数には、プロシージャで使用する数値や文字列を格納した変数や、処理対象のセル範囲を格納したRangeオブジェクトなどを指定します。

引数は省略することができます。

Sub ユーザー定義型関数()
    
    'Functionプロシージャの戻り値をセルに入力
    Range("A1").Value = 選択範囲を合計(Selection)
    
End Sub

Function 選択範囲を合計(rng As Range)
    
    Dim v As Long

    '選択範囲の合計を算出
    v = WorksheetFunction.Sum(rng.Value)

    '算出結果を戻り値として格納
    選択範囲を合計 = v
    
    '算出結果を直接戻り値として格納することも可能
    選択範囲を合計 = WorksheetFunction.Sum(rng.Value)
    
End Function

省略可能な引数

処理によっては、宣言した引数を省略したい(引数を使用しない)場合があります。

その場合は、省略したい引数の前にOptionalを付ける必要があります。
Optionalを付けずに引数を省略した場合、エラーが発生します。

Optional以降に宣言した引数は、すべて省略可能な引数として扱われます。
必須で使用する引数は、Optionalを記述する前に宣言しましょう。

○構文
Function プロシージャ名(Optional 第1引数名 as データ型,第2引数名 as データ型, …)

エラー値を返すユーザー定義型関数

ユーザー定義型関数の戻り値は、Functionプロシージャに格納したデータになります。

したがって、関数の結果としてエラー値を返したい場合は、Functionプロシージャにエラー値を格納する必要があります。

if文などの条件分岐処理で、特定の条件の時にFunctionプロシージャにエラー値を格納することで対応できます。

エラー値の内容は、独自のエラー文を指定することもできますが、CVErr関数を使用することで、既定のエラー値を使用指定することもできます。

○構文
CVErr(xlCVError)

○処理
引数xlCVErrorに指定した値に応じて、エラー値を返します。

○引数

xlCVError内容セルの表示
xlErrDiv02007エラー番号 : 2007#DIV/0!
xlErrNA2042エラー番号 : 2042#N/A
xlErrName2029エラー番号 : 2029#NAME?
xlErrNull2000エラー番号 : 2000#NULL!
xlErrNum2036エラー番号 : 2036#NUM!
xlErrRef2023エラー番号 : 2023#REF!
xlErrValue2015エラー番号 : 2015#VALUE!
xlErrSpill2045エラー番号:2045#スピル!

コメント