エクセルにはSUMやVLOOKUP、SUBTOTALといった便利な関数が多数存在しています。ある程度複雑なプログラムになってくると、自分独自の関数を作ると便利な場面が出てきます。コードをすっきりとさせ分かりやすくする効果があります。今回は自分独自の関数を作るFunctionプロシジャについて解説します。
目次
VBA Functionプロシジャの使い方と使い所
自分独自の関数とはどういうことか
VBAには自分だけの独自の関数を作る機能があり、それをFunctionプロシジャと呼びます。自分だけの独自の関数のイメージがわかないかもしれないので、以下のような場合を考えます。
下はある商店の商品リストです。
サンプルのデータは6行しかありませんが、実際は10,000行くらいあると思って下さい。複数の仕入れ先がある場合は「/」で区切られています。
次に下のような仕入れ先一覧があります。
今回の作業は、それぞれの商品に対して、仕入れ先の本社の所在地に、自社の支店があるかどうかを調べたいというものです。そのため、自社の支店があるところだけピックアップするような変則VLOOKUPのような関数を作りたいとします。これが今回やりたいことであり、自分独自の関数を作ると言うことです。
Functionプロシジャの作り方
それでは自社の支店がある所をピックアップする「支店ありなし判定」という関数を実際に作ります。
支店ありなし判定()
この支店ありなし判定()という関数は、商品リスト一覧の「仕入れ先」の列の値を入れると、仕入れ先一覧から探して本社所在地を確認し、大阪か福岡のいずれかを含む仕入れ先があれば、「支店あり」なければ「支店なし」という値を返すという、自分で考えた関数です。
まず、本体部分を作ります。本体部分というのは、関数を呼び出して使う部分です。
Sub 支店の有無を確認する() Dim i As Long 'カウントアップ用 Dim rw As Long '最終行を取得する Dim 仕入れ先str As String 'E列の仕入れ先の文字列を入れる Const データの最初の行rw As Long = 3 Const 仕入れ先の列clm As Long = 5 Const 結果を書き込む列clm As Long = 6 rw = Cells(Rows.Count, 1).End(xlUp).Row For i = データの最初の行rw To rw 仕入れ先str = Cells(i, 仕入れ先の列clm) Cells(i, 結果を書き込む列clm) = 支店ありなし判定(仕入れ先str) Next i End Sub
プログラムの冒頭で変数と定数をまとめて宣言しています。こうすることによって将来、変数や定数に関する改修が必要になった時にプログラム全体を見て変数が宣言してある場所を探さなくて済むようにしています。For~Next文の部分で今回使う支店有り無し判定の関数を使っています。処理が分かりやすい様に一旦セルの値を仕入れ先strという変数に入れました。そして今回使う仕入れ有り無し判定に引数として仕入れ先strを渡しています。関数なので戻り値を受け取る必要があります。今回はそのままセルに書き込んでいます。
次に独自の関数を作ります。
「支店の有無を確認する」プロシジャのEnd Subの下にFunction 支店ありなし判定と書いてエンターを押すと自動で()とEnd Functionが挿入されるので、その()の中に引数として受け取る為の変数名を適当につけます。こうしてできたFunctionプロシジャこそが今回作る独自の関数となります。
Function 支店ありなし判定(仕入れ先コードstr) End Function
サブプロシジャで「仕入れ先str」という名前をつけて渡した引数を、「仕入れ先コードstr」という変数で受け取る準備ができました。
関数の戻り値も受け取る引数もString型なので、型名をFunctionプロシジャ名の後に追記しておきます。
Function 支店ありなし判定(仕入れ先コードstr As String) As String End Function
これでFunctionプロシジャを作成する準備が完了しましたので、中身を作っていきます。
方針としては、仕入れ先コードstrは「/」で区切られた文字列なので、「/」で分けて配列に格納し、配列の要素を順番に仕入れ先コードの検索にかけます。配列のループの中に仕入れ先コードを検索するループがある入れ子の形となります。
Function 支店ありなし判定(仕入れ先コードstr As String) As String Const 仕入れ先一覧のデータの最初の行rw As Long = 3 '作業を開始する行 Const 最終行rw As Long = 7 '最終行 Dim 仕入れ先arr As Variant '「/」で分けた仕入れ先コードを要素とする配列 仕入れ先arr = Split(仕入れ先コードstr, "/") Dim i As Long, j As Long Const 仕入れ先コードの列clm As Long = 1 Const 本社所在地の列clm As Long = 3 Dim 判定結果 As Boolean For i = 0 To UBound(仕入れ先arr) For j = 仕入れ先一覧のデータの最初の行rw To 最終行rw If 仕入れ先arr(i) = Sheets("仕入れ先一覧").Cells(j, 仕入れ先コードの列clm) Then If Sheets("仕入れ先一覧").Cells(j, 本社所在地の列clm) = "大阪" Or _ Sheets("仕入れ先一覧").Cells(j, 本社所在地の列clm) = "福岡" Then 判定結果 = True Exit For End If End If Next j Next i If 判定結果 Then 支店ありなし判定 = "支店あり" Else 支店ありなし判定 = "支店なし" End If Next i If 判定結果 Then 支店ありなし判定 = "支店あり" Else 支店ありなし判定 = "支店なし" End If End Function
VBAではFunctionプロシジャ名に戻り値として返す値を代入する事によって、このFunctionの呼び出し元に戻り値を返します。
処理自体はシンプルですが、結構な長いプログラムになりました。この長さのプログラムをSubプロシジャでは「支店ありなし判定」という関数たったの一行で呼び出せます。この中身をSubプロシジャに書いてしまうとさらにループの中に入れることになるので、分かりづらくなります。
また、一度作ってしまえば何度でも関数として使えるところがFunction プロシジャのメリットです。仕様が変わったときもFunctionプロシジャの中だけを修正すれば良いため、メンテナンス性も増します。
Functionプロシジャの使い所
ここまででFunctionプロシジャの作り方が分かりました。しかし、Subプロシジャの中に処理の内容を書けばいいではないか、と思いませんでしたか?確かにそれでもプログラムは作成出来ます。しかし、プログラムの流れが分かりやすくなります。何かの処理をして値を求める部分をFunctionプロシジャにして関数として切り出すことで、何をしているのかが分かりやすくなるのです。今回のサンプルのFunctionプロシジャをSubプロシジャの中に記述すると処理の記述が長くなりすぎてしまい、何をやっているのかが非常に分かりにくくなります。
一方、Functionプロシジャとして計算の中身を外に出し、しかもそのFunctionプロシジャ名に支店ありなし判定という分かりやすい名前をつけたため、Subプロシジャを見ただけで、「ああ、最初の行から最後の行まで支店があるかどうかを判定して結果の列に書き込んでいるのだな」と分かります。
このようにある程度の処理を行って値を返すところは全てFunctionプロシジャの使い所です。使い所というとここ一番で使うべき場面があるように感じてしまったかもしれません。しかし、ある程度以上の処利をすべき所は全てです。ある程度とは特定の意味を持つひとかたまり、と言う意味です。カウントアップのためのi = i + 1の為にFunctionプロシジャを使う必要はありません。
Functionが使える様になる事の本当の意義
Functionプロシジャのメリットが分かっても、なかなか自分で採用する気になれないかも知れません。それでも一度、気合いで使ってみましょう。慣れてくると何かの処理をして値を求めるときは基本的にFunctionプロシジャを作成すると言うのが当たり前になってきます。この当たり前の感覚が磨かれてくると、Subプロシジャはプログラムの処理の流れをコントロールするもので、実際の処理の中身はFunctionプロシジャにやらせる、と言う感覚が身についてきます。実はSubプロシジャの本質的な意味合いはこの、プログラムの処理の流れをコントロールし、必要な機能を呼び出すと言う事にあります。
これは将来的にオブジェクト指向というとても便利なプログラミングの考え方を採用するときに必要な考え方です。オブジェクト指向をVBAのプログラミングに取り入れるとプログラミングの実力が一皮むけます。Functionプロシジャが使える様になる事の本当の意義はここにあります。
Functionプロシジャの作り方と使い所まとめ
Functionプロシジャの作り方と使い所をまとめると下記の通りです。
- 自分独自の関数とは自分で入力と出力を決められる関数
- 自分独自の関数はFunctionプロシジャという
- FunctionプロシジャはSubプロシジャから呼び出して使う
- Functionプロシジャを使う事でSubプロシジャがすっきりとして読みやすいコードになる
- Functionプロシジャを使える用になる事の本当の意義はオブジェクト指向につながる事
<関連記事>