ワークシート関数を自作する【SUM編】

ワークシート関数をFunctionプロシージャで自作してみる遊びです。

今回は【SUM関数】を自作してみました。

ソースコード

' SUM関数 和風スパ版
Function spSUM(ParamArray 数値()) As Double
    
    If IsMissing(数値) Then spSUM = 0: Exit Function ' 本家と違い引数なしを許す
    
    ' 各引数をループ
    Dim 各引数 As Variant
    For Each 各引数 In 数値
    
        ' Rangeの場合
        If TypeName(各引数) = "Range" Then
            spSUM = spSUM + Getセル範囲の合計値(各引数)
        
        ' 配列の場合
        ElseIf IsArray(各引数) Then
            spSUM = spSUM + Get配列の合計値(各引数)
        
        ' ブール値をはじく
        ElseIf 各引数 = True Then
        
        ' 数値の場合
        ElseIf IsNumeric(各引数) Then
            spSUM = spSUM + 各引数
            
        End If

    Next
    
End Function

' Rangeの合計値
Private Function Getセル範囲の合計値(ByVal セル範囲 As Range) As Double
    Dim セル As Range
    For Each セル In セル範囲.Cells
        
        ' 数値のみを合計しそれ以外はスキップ
        ' 本家と同様文字列は無視、さらに本家と違い日付も無視する
        If IsNumeric(セル.Value) And (セル.Value <> True) Then
            Getセル範囲の合計値 = Getセル範囲の合計値 + セル.Value
        End If
        
    Next
End Function

' 配列の合計値
Private Function Get配列の合計値(ByVal 配列) As Double
    
    Dim 要素 As Range
    For Each 要素 In 配列
    
        ' 本家と違いジャグ配列(配列の中に配列)にも対応
        If IsArray(要素) = False Then
            Get配列の合計値 = Get配列の合計値 + Get配列の合計値(要素)
        
        ' 数値のみを合計しそれ以外はスキップ
        ' 本家と同様文字列は無視、さらに本家と違い日付も無視する
        ElseIf IsNumeric(要素) And (要素 <> True) Then
            Get配列の合計値 = Get配列の合計値 + 要素
        End If
        
    Next
End Function

解説

ソースコードを見ればわかる通り、
引数を愚直にFor Each文で足していくストレートなコードです。

本家のSUM関数同様、文字列が渡されてもスキップするため、
基本的にエラーにはならず、例外はすべて0として計算しています。

本関数を作る上で最大の注意点は、
IsArray関数がRangeに対してもTrueを返してしまう点です。

このため、配列かセル範囲かを判定するには、
先にTypeNameがRangeであるかを判定する必要があります。

詳しくはこちらの記事を参照ください。

その他気を付けなければいけない点として、
IsNumeric関数は「ブール値(True/False)」をTrueと判定します。

このため、IsNumericによる判定の前に、

If 各引数 <> True Then

この判定を入れておかないと、Trueが-1として加算(減算)されてしまいます。

SUM関数を自作する場合は、この仕様に注意してください。

本家との違い（改良点）

文字列型の数値を合計する

本家のSUM関数は、文字列を無視するため、
"1"を渡しても0として計算されていました。

対して四則演算はこれを計算するため、

数式	値
"1" + "1"	2
SUM("1","1")	0

という違いがあり、SUM関数の罠として知られています。

今回の自作関数はIsNumericで判定しているため、
数値と判定できる文字列も加算しています。

よって

数式	値
spSUM("1","1")	2

として動いてくれます。

もちろん、配列やセル値に"1"があっても同様に動きます。

日付への対応

本家のSUM関数は日付も加算します。

が、それで嬉しいことはあまりないので、
今回の自作関数では日付は無視しました。

こちらも単純にIsNumeric関数で判定することで実装できます。

この「日付を数値とみるか」についてはVBAとワークシートの違いのようで、
シート関数「ISNUMBER」と、IsNumericも同じ違いを持ちます。

詳しくはこちらの記事をどうぞ。

www.limecode.jp

ジャグ配列への対応

本家のSUM関数はジャグ配列に対応していません。

対して、今回の自作関数は再帰させることでジャグ配列に対応しています。

余談になりますが、

Get配列の合計値 = Get配列の合計値 + Get配列の合計値(要素)

こんなに綺麗にFunction名が3つ並ぶことが初めてで感動しました。

以上で自作SUM関数の解説を終わります。

実用的かというと全くそんなことはありませんが、
遊びながら楽しくVBAを学べるいい題材だと思います。

Functionの作り方や、ForEach文の書き方を練習したい人は、
是非とも挑戦してみてください。