VBAによる安定したワークシート用ユーザー定義関数の作り方

今回はVBAで安定したユーザー定義関数を作る方法についてお話します。

f:id:Kotori-ChunChun:20191004221217p:plain

はじめに
関数とは
- 正しい関数の設計
安定したユーザー定義関数の作り方
まとめ

はじめに

皆さんはVBAを使ってワークシートで使うためのユーザー定義関数(UDF)を作ったことはありますか？

作ったは良いけど、動作が安定していなくて使っていないという人はいませんか？

そんな人は、おそらくユーザー定義関数の作り方を間違えています。

上手く行かないと言っている方のソースコードを見ると、そもそも関数と呼べる設計になっていないです。（※私の感想です。）

今日はそんな人たちに、何故ユーザー定義関数が再計算されない時があるのか、説明するために筆を執りました。

長文になりそうでしたので、複数回に分けてシリーズでお送りします。

関数とは

まずは、関数はどうあるべきか。

私の考える「関数と呼べる設計」が何かはっきりさせておきます。

「ASCII.jpデジタル用語辞典の解説」が非常に分かりやすいものでしたので引用します。

kotobank.jp

与えられた文字や数値に対し、定められた処理を行って結果を返す機能のこと。表計算ソフトやデータベースソフト、プログラミング言語などで利用される。関数ごとにさまざまな処理が割り当てられており、たとえば、表計算ソフトのExcelで「SUM」という関数を用いると、指定した範囲の合計が求められる。

重要なのは与えられた文字や数値に対し、定められた処理を行って結果を返すの部分です。

この文章は

入力＝与えられた文字や数値
処理＝定められた処理
出力＝結果を返す

の工程に分割できます。

これはワークシート向けの関数に限った話ではありません。

VBAで関数を作る時に、これらの工程を明確に区別することで、汎用性のある良い関数を作るができるようになります。

正しい関数の設計

まず、繰り返しになりますが、関数が処理に使えるのは与えられた情報のみです。

逆に言えば与えられていない情報を使ってはいけないとも言えます。

たとえば、誰でも知ってそうなEXCEL関数を思い浮かべてみてください。

SUM、MAX、IF、AVERAGE、NOW、VLOOKUP、ROUND、PI、INDEX・・・

与えられていない情報を使っている関数はありましたか？

ありましたね！（ぉぃ

上記で言うと、NOW、PIが与えられていない情報を返してきますね。

しかし、少なくともNOWやPIは、関数名から何が返るか明らかに類推できる範囲に留まっているはずです。

というか、どちらも値を返すだけで何も引数を取っていませんよね。

関数という体は取っていますが、いわゆる環境変数や定数という位置付けで考えるべきでしょう。

また、VLOOKUPは「1列目を検索対象とする」という、引数に含まれていない情報を使用しています。

要は「どこまでの情報を定められた処理に含まれるか？」という問題で、検索列だけなら入出力データの範囲に影響しないためセーフと考え方で良いと思います。

それ以外のものはSUMはもちろん、VLOOKUPやINDEXのような高度な関数も含めて、返す値は全て引数で与えらた範囲の一部またはその計算結果になっていますよね。

これが私が正しい設計のされた関数と呼ぶ大前提です。

安定したユーザー定義関数の作り方

関数とは何かを理解していただいたところで、ようやくワークシート向けの安定したユーザー定義関数の作り方の説明に入ります。

一応有識者のために補足しておきますと、ここで言う「安定した」というのは、バグを誘発しやすい「揮発性関数を使わない。でも必要な時にはちゃんと計算してくれる」という意図で書いています。

実はワークシート上でユーザー定義関数を使う事自体が障害を誘発する機能なので、「障害が起こらない」という意味での「安定化」は想定していません。

あくまで、良く見かける「悪い例」よりは「安定して動く」という事でお願いします。

それでは、先に悪い例を示し、後に良い例を紹介していきます。

テストデータ

本ページでは、なんちゃって個人情報により生成した下記の表を使って説明しています。

	A	B
1	なんちゃって名前	なんちゃってふりがな
2	村山	むらやま
3	比嘉	ひよし
4	小杉	こすぎ
5	森島	もりしま
6	入江	いりえ
7	新垣	にいがき
8	小口	おぐち
9	麻生	あそう
10	矢島	やじま
11	田口	たぐち

良くない関数の実装例

まずは良く見かける「設計ミスってる」関数で試しましょう。

'A2:A11の中で[検索値]と一致した行のB列の値を返す関数
Function VLOOKUP_BAD(検索値 As String)
    Application.Volatile       '揮発性関数化の処理
    Dim i As Long
    For i = 2 To 11
        If Cells(i, "A") = 検索値 Then
            Exit For
        End If
    Next
    VLOOKUP_BAD = Cells(i, "B")
End Function

これは、過去にVLOOKUP関数の引数を減らしてラクラク入力出来るようにした！と自信満々に持ってこれらた関数の再現です。（余談ですが、作者はVLOOKUP関数を知りませんでした。）

実際に白紙のシートにコピペして使ってみると、意図した通りに動くのがご確認頂けるかと思います。

f:id:Kotori-ChunChun:20191004221226g:plain

ところが、ですね。恐らくこの関数を作られた人は、うまく動かなかったがゆえに揮発性関数にしています。（Application.Volatileの文）

「揮発性関数」については次回の記事で説明するつもりですが、一言でいうとどこのセルの値が変更された時にも再計算を行う関数です。

詳しくは　docs.Microsoft.com - Excelの再計算　をご覧ください。

もし、Application.Volatileをコメントアウトするとどうなるかと言うと・・・不安定になります。具体的には元の表を書き換えた時に再計算されません。

'    Application.Volatile       '揮発性関数化の処理

f:id:Kotori-ChunChun:20191004221747g:plain

別に揮発性関数でも動くなら良いじゃん！と思うかも知れませんが、使っちゃダメな理由は後日説明するとして、ここで理解してもらいたいのは関数が適切な設計になっていないから再計算されないのだということです。

実はExcelは引数に含まれているセルが更新された時だけ再計算するという仕様です。

そうしないと、関係のないセルが書き換わるたびに膨大な数式を計算しなおすことになり、激重になってしまうのです。

あと、上記プログラムには多数のバグの原因となりうる箇所があります。

それも後日の記事で説明することを検討しています。

正しい関数の実装例

上記の関数を適切な設計にしたら次のコードになります。

'[指定範囲]の(1列目)で[検索値]と一致した行の(2列目)の値を返す関数
Function VLOOKUP_1To2(検索値, 検索範囲 As Range)
    Dim i As Long
    For i = 1 To 検索範囲.Rows.Count
        If 検索範囲(i, 1) = 検索値 Then
            Exit For
        End If
    Next
    Debug.Print 検索値
    VLOOKUP_1To2 = 検索範囲(i, 2)
End Function