関数は自分で作る

「学問のすすめ」みないな。

Excelの標準モジュールに作った関数は、セルの中で使える。

ユーザー定義関数（自分で作る関数）を作るためには以下の知識が必要です。

1. プロシージャが書ける
2. 引数が使え、型を知っている
3. セルの処理、Range,Cells、Offsetが使える
4. WorksheetFunction関数、VBA関数がある程度使える
5. 条件分岐、繰り返し処理ができる。
6. 条件式が書ける

SUMIFやVLOOKUPなどのワークシート関数（Excel/エクセルに組み込まれている関数）は、何かやりたいことがあって使うのだが、自分が作ったわけではないので処理内容を確認しながら進める。以下の手順でワークシート関数を使う。

1. 使えそうなワークシート関数を探す
2. 使い方をチェック、期待する処理ができるか確認する。調査能力がアップするだけでプログラム力では無い。

一方、自分で関数を作ることができれば以下の手順で関数を作る。

1. 入力と出力を決める。入力は引数でセルか値を渡す。出力はFunctionプロシージャの戻り値。
2. 処理手順を標準モジュールに書いてFunctionプロシージャを作る

「？？？ができるワークシート関数はどれかな？」って探すのではなく、自分で作るので自作関数とかカスタム関数、ユーザー定義関数とか言う。

（プログラムする）利点

1. セルの中に長い式を書かなくていい。最重要！
2. 処理内容がコードにとして見える
3. VBA学習のスタート課題として使える。これで力を付ける。
4. VLOOKUP関数など複雑なワークシート関数の使い方を覚えなくていいし、ユーザー定義関数／自作関数の作成はスキルアップになる。作れば作るほど技能がアップする。

（プログラムする）欠点としては、プログラムやVBA関数を知らない人には読めない、修正、引き継ぎが出来ない点。「他の人が修正できないが！」というクレーム。

再計算の対象にするためユーザー定義関数内にApplication.Volatileを挿入する必要がある。一般的に動作が遅くなると言われるが更新されないクレーム回避策になる。

一般的なExcel使いの人の意見でスキルを止めてはいけない。使いやすい関数を提供してあげえればいい。

一般的に、INDEXやVLOOKUPなどを組み合わせるような式は読みにくい。メンテナンスする点からも、「引き継ぎ」に関して大抵は引き継げるほど簡素では無いはずだ。Excelのセルの中のコードを読むのって難しい。じゃない？

比較的簡単でセルの中を見れば処理内容が分かる程度ならExcelのワークシート関数を使う。どちらを使うかの判断は、「どちらが読みやすいか」だが、練習の場合は単純でもユーザー定義関数を作ればいい。

SUMというワークシート関数があるが、あえて自作のmySUM関数を作る。

セルの範囲を引数で受け取る場合のユーザー定義関数の基本的な書き方は以下のようになる。

Function 関数名(引数名 As Range)
    Dim v
    For Each v In 引数名   '引数に複数のセルが入っていることを前提にしている。
         処理コード
    Next
End Function

ワークシート関数にSum関数がある。これと同じような関数をユーザー定義関数で書く。

Function mySUM(範囲 As Range)
    Dim v As Variant      ’As Variantは省略しても同じ意味
    For Each v In 範囲
        mySUM = mySUM + v
    Next
End Function

範囲のセルの値を変えたらmySUMの値も変わることを確認する。次のコードは合計数と最大値を文字列にして返す。

Function mySUM2(範囲 As Range)
    Dim s As String, i As Integer
    ReDim ar(範囲.Count) As Double 'ワークシート関数のMaxを使うための配列
    For i = 1 To 範囲.Count
        mySUM2 = mySUM2 + 範囲(i)
        ar(i - 1) = 範囲(i) '配列にも入れる
    Next
    s = "合計は" & mySUM2 & "です。最大値は" & WorksheetFunction.Max(ar) & "です。"
    mySUM2 = s
End Function

練習：myKake関数を作る。掛け算をする関数。ただし、セルの中の””は計算しないが０が入っている場合は計算する。

まず、下記のようなコードを思い付くと思う。myKakeの初期値がEmptyというバリアント型特有の値でたいていは０として扱われ永遠に０の結果を返す関数になる。

Function myKake(r As Range)
    Dim v
    For Each v In r
        myKake = myKake * v
    Next
End Function

対応が必要なのはセルが空白文字や文字列、myKakeがEmptyの時。ElseIfまで必要になる。

Function myKake(r As Range)
    Dim v
    For Each v In r
        If "" = v Then  '""の空白は何もしない
        ElseIf VBA.IsNumeric(v) Then '数値の場合
            If VBA.IsEmpty(myKake) Then 'まだmyKakeがEmptyの場合は１で掛ける
                myKake = 1 * v
            Else
                myKake = myKake * v '通常の掛け算
            End If
        End If
    Next
End Function

条件分岐が複数になると試行錯誤、条件が５，６個程度ならコードを先に書いて確認する方が早い。

令和6年9月22日追記です。

CharGPTで作ってみた。