Excelで普通に使えるSUM関数のような関数以外に自分で独自の関数を作る解説を簡単にします。
一番シンプルな方法
まず、エディタをAlt+F11のショートカットキーで開きます。
そしてブックにModuleを追加します。このModuleに作っていきます。
今回は指定したセルに対して、+1した値を出すPlusOneという関数を作成します。
「Function 関数名(関数の要素)」を入力しEnterします。End Functionが出てくるので、ここの間に動作を書いていきます。
関数名はPlusOne、引数はrngとしてVBAに取り込みます。
最終的な答えは、関数名に入りますので、次のような記述になります。
これで完成です。
実際にExcelのどこかのセルに次の計算式を入力します。
「=PlusOne(A1)」
セルA1に対する1足された数字が求められます。
自動計算されないケース
10/5 6:25訂正：
自動計算されないケースはエディションによるちがいではないかと言及しましたが間違いでした。
Excelの再計算の負荷が考えられていて、関数は、引数に含まれるセルを変更した時は自動再計算され、含まないセルを変更した時は再計算されません。
つまり、何かの理由で引数に入れないセル範囲がある場合、その範囲を変更しても再計算されないのです。
セルA1からA5までの合計を常に求める関数として、A1A5SUMというユーザー定義関数を次のように作成したとします。
Function A1A5SUM()
A1A5SUM=Application.Sum(Range("A1:A5"))
End Function
この場合、A1からA5のセル範囲を変更しても変更になりません。
引数に対象範囲がないからです。
正しくは次のように作成しなければなりません。
Function A1A5SUM(rng) A1A5SUM=Application.Sum(rng) End Function
これできちんと計算の元に使うのはどこなのかを指定することで、再計算の対象になります。
しかし、常にそういうケースだけとは限りません。
今はあまり想像できませんが、中には、必ずそこを使うんだから引数には指定しないというケースもあるかもしれません。
そのような時に使うのが、Application.Volatileです。
これをすればその関数はどんな場合でも関係ないセルを変更しても自動で再計算を行います。
しかし、それは同時にExcelに負荷をかけることにもなります。なるべくなら使わない方がいい処理です。
ユーザー定義関数の再計算は初心者が陥りやすいポイントです。
詳しくは@KotorinChunChunさんがわかりやすい記事を書いてくださっています。
このように自作した関数はどこかのセルを変更した時に自動で起きる再計算が起きない場合があります。どうやらExcelのエディションが関係しているようです。
そこで、関数の最初に強制的に再計算関数だと宣言します。これで再計算する関数に変わります。
ちゃんとした方法（絶対に推奨）
Functionにも変数の型があります。
例えば文字を求める関数であればString型、数字を求める関数であればInteger型、金額などの大きな数字ならLong型、小数点を含む数字ならDouble型のような指定をしなければなりません。
これは、()の引数一つ一つに対しても同様です。
これをしないと、Excelが勝手にその関数の型をいいように想定します。これでは私たちが思っている動作と違くなるかもしれません。どうなるかわからない、それではコンピュータの使い方としては正しくないのできちんと指定しましょう。
Functionの変数の型はFuntionの1行目の最後に「 As 関数の型」、引数はそれぞれの引数の後に「 As 変数の型」の形で設定します。
指定しても指定しなくてもいい引数
Excelの関数には、引数の個数が決まっていない場合、指定を省略できる引数があります。
SUM関数は,で区切って数多くの範囲を指定できますね。また、VLOOKUP関数の一致条件は省略するとTRUEの近似値一致になります。
そのような引数を指定する場合は次のように、Optionalと初期値を指定します。
Optional 変数名As Variant = 初期値例えば、上記で作成したPlusOne関数で足す数を指定しなければ1、指定してあれば指定した値とすることができます。
ワークシートで使える関数はVBAのサブルーチン
上記のように作成したワークシート関数ですが、実はワークシート関数を作ったつもりだったのですが、VBAとして見ると、サブルーチンのように動きます。
他のマクロから、次のように今回作ったFuntionを呼び出し、値としてふるまわせることができます。
実際のところはわかりませんが、むしろこの方法の方が本来の使い方なのかもしれないですね。
実例
いくつかの例を紹介します。
SUM関数同等の関数
指定した範囲をループしてどんどん足していく動作です。関数名は漢字でもできます（英語版のExcelで動くかどうかは保証できません）
Function 合計(rng As Range) As Integer
Dim rng1 As Variant
For Each rng1 In rng
合計 = 合計 + rng1
Next
End Function
CONCAT関数にIF条件で指定する機能を追加した関数
TEXTJOIN関数のような区切り文字も設定できるようにしました。設定しなくてもできるようにOptionalの設定もしています。
Function CCONCATIF(rng1 As Range, Criteria As Variant, rng2 As Range, Optional delimiter As String = "") As String
Dim i As Integer
For i = 1 To rng1.Count
If rng1.Item(i) = Criteria Then
CCONCATIF = CCONCATIF & delimiter & rng2.Item(i)
End If
Next
CCONCATIF = Mid(CCONCATIF, Len(delimiter) + 1, 10000)
End Function
TEXTJOINの逆で、文字列を指定の文字で分離する関数
この場合、分割したあと、何個目の値を参照するかも必要ですので整数型のnumber変数も設定します。
Function SSplit(StrData As String, delimiter As String, number As Integer) As Variant Dim StrDatas As Variant StrDatas = Split(StrData, delimiter) SSplit = StrDatas(number - 1) End Function
まとめ
Excelの関数にはSUM関数などの便利な関数があります。
しかし、それでも複数の関数を組み合わせないとできない場合もあります。それどころかその機能を持つ計算式を関数の組み合わせではどうしてもできない場合もあります。
そんな時にはVBAでオリジナルの関数を作るとよいでしょう。
コメント