オリジナル関数(ユーザー定義関数)
オリジナル関数とは、自分で作成する関数のことです。
Excelは実に多くのワークシート関数を提供しており、基本的な計算から日付計算、文字列操作、財務計算、統計、データベース処理を簡単な記述で実現できます。一般的な表計算の処理を行う限りでは、標準的なワークシート関数で十分に用が足りるはずです。
しかし、Excelを駆使してさまざまな表計算を行っていく中では、当然のことながら、なかなか提供されている関数だけでは十分に用を足さないこともあるのではないでしょうか。そのような場合に、複数の関数を組み合わせて、複雑な関数式を書いているかもしれません。あるいは、一時的にワークシートやセルに計算の途中結果を保存しておき、それを使ってほしい値を求めるなど、苦労した経験があると思います。しかし、関数式が多重関数を使うなどして長くなれば、当然、記述ミスや論理ミスが発生する可能性が高くなります。
そこで、わかりやすいVBAコードを使って自前の関数を作成することで、数式バーの中で複雑な式を考えるより、実行ミスが少なく、安心して作業に打ち込むことができます。
オリジナル関数は、Functionプロシジャを使用して、Subプロシジャと同じように、「Function〜End Function」という基本の枠組みを持ったプロシジャですが、実行した結果を「呼び出し元に値を返す」という点が大きな特徴です。Subプロシジャや別のFunctionプロシジャから呼び出すことのできますし、もちろんワークシートのセルからもオリジナルな関数（ユーザー定義関数）を呼び出して利用できます。
Functionプロシジャの基本的な枠組みとして、Functionステートメントの後ろに関数名(プロシジャ名)を記述するのは同じですが、そのあとに呼び出し元に返す値の型を定義しているところが異なります。また、プロシジャ内で関数名に値をセットすることで、その値が呼び出し元に返る仕組みになっています。
【構文】
- Function 関数名(引数、引数、・・・・・・) As 処理結果の値の型
- 関数名 = 処理結果の値
- End Function
オリジナル関数の作成
では、実際に標準モジュールにオリジナル関数を作成してみましょう。
まず、オリジナル関数を作成する前に新しい標準モジュールシートを作成しておきましょう。
「メニューバー」 → 「挿入」 → 「標準モジュール」の手順で作成します。
【例題12】 ある点数が100点以上なら判定を「◎」、80点以上なら判定を「○」、60点以上なら判定を「□」、40点以上なら判定を「△」、それ以外は「×」を設定します。ワークシート上で判定処理にIF関数のネスティングを使うとミスがおきやすくなります。そこで見やすいコードで書きなおしてオリジナル関数を作成します。
＊ワークシート関数
=IF(A1>=100,”◎”,IF(A1>=80,”○”,IF(A1>=60,”□”,IF(A１>=40,”△”,”×”))))
＊オリジナル関数コード
ここではSelect Caseステートメントを使用した「判定」関数を作成します。
- Function 判定(x as Integer) as String
- Select Case x
- Case Is >= 100: 判定 = "◎"
- Case Is >= 80: 判定 = "○"
- Case Is >= 60: 判定 = "□"
- Case Is >= 40: 判定 = "△"
- Case Else: 判定 = "×"
- End Select
- End Function
(注) 「Select Case x 〜 End Select」文は変数xの値によっていろいろな処理をするものです。
例題
【例題13】 選択されたセル範囲を1セル飛ばしで合計を計算しましょう。、いろいろな方法で結果を求めることができます。一般的（？）には配列数式を利用しますが、ここでは飛び合計関数を自前で作成して見ましょう。ここでの引数xは飛ぶセルの数です。1は連続セルの合計、2は1つ飛びの合計、3は2つとびの合計を示しています。
- Function とび合計(セル範囲 As Range, x As Integer) As Long
- Dim r As Range
- Dim i As Integer
- とび合計 = 0
- i = 0
- x = x + 1
- For Each r In セル範囲
- i = i + 1 '選択されたセルに順番についた架空の番号
- If i Mod x = 0 Then 'Modは除算の余りを求める演算子です
- とび合計 = とび合計 + r.Value
- End If
- Next r
- End Function
(注1) 「Long」の扱える数の範囲： データ型のLongは＋１２億くらいまで数を取り扱えます。ちなみに、Integerは＋−32000くらいまで取り扱えます。
(注2) 「Mod」の使い方： 変数名 = 被除数 Mod 除数
【例題14】 Subプロシジャ内でFunction関数を呼び出す方法を書いておきます。
例題13で作成したFunction「とび合計」を呼び出してみましょう。
事前にアクティブワークシートのセルA3:D8に適当に数値を入れておいてください。
- Sub 例題14()
- Dim s as Long
- s = とび合計(Range("A3:D8"),2)
- MsgBox "とび合計は、" & s & "です。"
- End Sub
【ワンポイント1】
関数とは何か。簡単に言うと、何か値を与えると、計算などの処理をして目的の値を返すという魔法の箱です。そのため箱の中身がわからなくてもだれでも簡単に利用することができます。
【ワンポイント2】
プログラムのよいところは「繰り返し処理がある」ことです。これは処理の幅を無限にします。