技術資料/Excelユーザ定義関数

関数定義 †

Variant型引数の判別 †

TypeName()関数を使用することで、Variant型の引数に何が渡されたかを判別することができる。この仕組みを利用して、全ての引数をVariant型で受けて後で判別するように作るのが理想的。
そうすることで、ある引数に対して値もセルも指定可能な柔軟な関数になる。

引数に設定できる値 †

数値／文字列 †

• 概要
  数値は対応した組み込み型もしくはVariant型の引数で受け取る。
  セルが指定された場合は、そのセルの値が入力される。
  

• 使用例
  

  =UDFRef(10.5)
  =UDFRef("abc")
  =UDFRef(B2)

• 定義例
  

  1 2 3 4 5 6

  ' 値をそのまま表示 Public Function UDFRef(ByVal val As Variant) As Variant UDFRef = val End Function

真偽値 †

• 概要
  真偽値はboolean型もしくはVariant型の引数で受け取る。
  条件式をそのまま指定することができる。
  

• 使用例
  

  =UDFIf(1=1, "true", "false")
  =UDFIf(C3>30, "true", "false")
  =UDFIf(FALSE, "true", "false")

• 定義例
  

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  ' 条件分岐 Public Function UDFIf(ByVal bl As Boolean, ByVal tval As Variant, ByVal fval As Variant) As Variant Dim ret As Variant If bl Then ret = tval Else ret = fval End If UDFIf = ret End Function

セル範囲 †

• 概要
  セル範囲はVariang型、Object型、Range型（Ranges型ではないことに注意）のいずれかの型の引数で受け取る。
  
• 使用例
  

  =UDFSum($A1:$C$5)
  =UDFSum('Sheet2'!F2)

• 定義例
  

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  ' セル範囲の合計値 Public Function UDFSum(ByRef rs As range) As Variant Dim ret As Variant Dim r As range ret = 0 For Each r In rs ret = ret + r.Value Next UDFSum = ret End Function

省略可能な引数 †

引数にOptionalキーワードを付与することで、省略可能な引数となる。
ただし、それ以降の全ての引数にもOptionalキーワードを付与する必要がある。

引数が省略されたかどうかは、引数がVariant型であればIsMissing()関数で判定できる。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13

Function UDFArgState1(Optional ByVal arg As Variant) As String
 
    Dim ret As String
 
    If IsMissing(arg) Then
        ret = "省略"
    Else
        ret = CStr(arg)
    End If
    
    UDFArgState1 = ret
 
End Function

引数がVariant型では無い場合は、デフォルト値を設定しておいて判定することになる。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13

Function UDFArgState2(Optional ByVal arg As String = "") As String
 
    Dim ret As String
 
    If arg = "" Then
        ret = "省略"
    Else
        ret = CStr(arg)
    End If
    
    UDFArgState2 = ret
 
End Function

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13

Function UDFArgState3(Optional ByRef arg As Range = Nothing) As String
 
    Dim ret As String
 
    If arg Is Nothing Then
        ret = "省略"
    Else
        ret = arg.Value
    End If
    
    UDFArgState3 = ret
 
End Function

可変長引数 †

引数にParamArrayキーワードを付与することで、可変長引数となる。
ただし、必ずVariant型の配列にする必要がある。
また、ByVal、ByRef、Optionalキーワードと併用はできない。

引数が省略されたかどうかは、引数配列の上限が下限よりも小さいかどうかで判定できる。

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18

Function UDFParamArray(ParamArray args() As Variant) As String
 
    Dim v As Variant
    Dim ret As Long
    
    If UBound(args) < LBound(args) Then
        UDFParamArray = "省略"
        Exit Function
    End If
 
    ret = 0
    For Each v In args
        ret = ret + CLng(v)
    Next
    
    UDFParamArray = CStr(ret)
 
End Function

自動計算対応 †

自動計算　Application.Volatile

エラー表示 †

関数の引数入力中に、Ctrl+Aで出てくるダイアログ

マクロの説明 †

Application.MacroOptions

マクロのカテゴライズ †

引数の入力支援 †

引数入力中にカーソル下に表示されるツールチップ

補足事項 †

引数の変数型 †

引数や戻り値はObject型やVariant型で、実行時に型情報を調べるほうがいい？SUM関数のようにセルや数値のどちらも引数に渡す場合「TypeOf TypeName IsNumeric」で処理を分岐するなど。

boolean型の引数で、IF関数のような条件式（厳密にはその真偽値）を受け取ることもできる。「A1=1」

Functionをワークシートで使用不可にする †

下記の手段によってFunctionプロシージャを関数挿入ダイアログや、入力途中の関数一覧に表示させないことが可能。

• Funtionを「Private」宣言する
  
• モジュールの先頭で「Option Private Module」を記述しておく（そのモジュール全体に影響することに注意）
  

ただし、直接関数名を入力すると使用することはできてしまうので注意。

制限事項 †

• スプレッドシートでのセルの挿入、削除、または書式の設定
• 別のセルの値の変更
• ブックでのシートの移動、名前の変更、削除、または追加
• 計算方法や画面表示など、環境オプションの変更
• ブックへの名前の追加
• プロパティの設定およびほとんどのメソッドの実行
  →つまり、戻り値が自動で対象セルにセットされるということ以外のアウトプット系処理は全滅？
  →ワークシート関数での制限に反するコードは無視される。エラー表示などはされない。
  http://support.microsoft.com/kb/170787/ja