エクセルには、セルに数式を入力するだけで使える様々なワークシート関数が用意されています。
複数のセルの値を合算するSUM関数や、表から目的の値を検索して対象行の必要な列の値を取得するVLOOKUP関数などを使ったことがある方も多いでしょう。
しかし、予め用意された関数を使うだけでは欲しい結果が得られない場合もあります。
このようなときにはVBAで記述した関数をワークシート関数として使用することができます。
例題
実際に遭遇する場面は想像できないかもしれませんが、以下のように16進数が入力された範囲があり、それらの合計値を10進数で求めたい状況を考えます。
合計ということで、SUM関数を用いて「1」から「4」の範囲を選択すれば求めたい値が得られそうな気がするかもしれませんが、実際に試してみるとこのSUM関数の示す値は25になります。これは選択した範囲の中で「F」と「CA」を除いた「1,20,4」を10進数として合計しただけであり、期待した結果とは異なります。
単純な解決方法としては以下のようなやり方が考えられます
ここではHEX2DEC関数を使い、16進数が入力されているセルの下に「 =HEX2DEC(R[-1]C) 」という数式を入力して10進数に変換したうえで、SUM関数を使って合計を計算しています。
結果は254(10進数)となり「1,20,F,CA,4」をそれぞれ16進数として合計した結果が正しく得られています。もしこの254を更に16進数に変換したい場合にはDEC2HEX関数を使えばFE(16進数)を得ることができます。
しかし、この方法だと計算用に10進数で表示するための行が必要になってしまっています。
ユーザー定義関数の作成
例題の解決方法として、自分で関数を定義することもできます。
標準モジュールを作成し、以下のコードを入力します。
Function SUMHEX(selectedRange As Range) As Long'16進数配列の和を求める関数
Dim result As Long
result = 0
On Error GoTo ConvertError
For Each num16 In selectedRange
If num16.Value2 <> "" Then
result = result + CInt("&H" & num16.Value2) '10進数に変換して加算
End If
Next
SUMHEX = result '戻り値をセット
Exit Function
ConvertError:
MsgBox "10進数に変換できない値が含まれています"
SUMHEX = Error 'エラー(数値以外をセット)
End Function
これで選択範囲の16進数を10進数に変換し、10進数(Long型)で合計値を求めるSUMHEX関数の定義ができました。選択範囲内の空欄は無視しますが、16進数以外の値が含まれる場合にはエラーとしておきます。
それではワークシートに戻ってこの関数を使ってみましょう。
「=su」まで入力した時点で関数リストにSUMHEXが表示されています。=SUMHEX( に続けて16進数が入力された範囲を選択します。
自分で作成したSUMHEX関数を使って正しい計算結果を得ることができました。
例題ではこの関数を使えば、シート上で16進数を10進数に変換してそれらを合計するという回り道をする必要がなくなり、ワークシートがごちゃごちゃすることを防ぐことができます。
エクセルのワークシート関数は、セルの値の変更がすぐに反映されるため便利な機能ですが、複雑な計算をしようとすると数式がひどく複雑になったり、計算用に参照するためのセルを大量に確保したことにより見やすさが低下しがちです。
予め用意された関数では使い勝手が悪いような場合、自分でワークシート関数を作ってみることも検討してみる価値はあります。
ユーザー定義（ワークシート）関数はシート上で使用する関数なので引数はセルの範囲を指定できるRange型になるのが自然ですが、他の引数を持つことも可能です。
例えば上の関数の1行目を「Function SUMHEX(selectedRange As Range , Optional param1 As Long = 0) As Long」と記述すれば、SUMHEX関数は省略可能なLong型の引数「param1」を受け取ることができるようになります。
ユーザー定義関数使用時の注意
標準モジュールに記述された（publicな）関数はワークシート関数として扱うことができます。
つまり、VBAのプログラム内で使用するつもりで作成した関数でも呼び出すことができるわけですが、プログラムで使うつもりで作成した関数をワークシート上で使用すれば、思わぬ不具合が発生する恐れがあります。
また、予め組み込まれたワークシート関数と同じ名前で関数を定義してしまうと、以下の画像のように混乱を生じます。
このような事態を避けるため、純粋にプログラムで使用する関数を作成する際には「private」キーワードを付ける習慣をつけるようにするべきです。
ただし、そもそも一般にエクセルを使用する人から見れば、関数リストに表示されるものがユーザー定義関数なのか、もともとエクセルに組み込まれたワークシート関数なのかの区別はつきづらいため、安全性の観点からすれば自作のユーザー定義関数を組み込んだシートを安易に配布するようなことは避けたほうがよいかもしれません。