VBAコードにSub、Functionを導入して可読性・保守性を改善する

VBAコードの可読性・保守性向上に不可欠なサブルーチン化、ユーザ定義関数(Function)の基本的な使い方をご紹介します。また、サブルーチン、ユーザ定義関数を使う上で大切な引数の値渡しと参照渡しについても分かりやすく解説します。

ある程度の工程数の処理を経る集計作業になると、それなりのVBAコードを書かなくてはなりませんが、VBAを始めたての頃などは1つのプロシージャにすべてのVBAコードを書いてしまいがちですよね。

そのような1つのプロシージャに、延々と長いVBAコードが書かれている場合、初めて見る人にとっては、コード全体の流れを把握するのにかなりの時間を要してしまします。

また、他人でなくても自分で書いたコードでさえ時間が経つと、どのような処理をするコードなのか、すぐにその全体像を把握できない場合があります。

本記事では、そのような1つのプロシージャをSubやFunctionを使う事で適切に分割し、VBAコード全体の処理の流れや、各集計処理の工程がどこのVBAコードで行われているかなどを早く把握できる方法についてご紹介します。

サンプルシートと2つの集計処理

サンプルシート

本記事で使用するサンプルシートは、「SampleSheet01」を使いますので、宜しければダウンロードしてご利用ください。

なお今回は、上図右側にある「仕入リスト」シートは、「SampleSheet01」をシートコピーして名前を変更し、2行目以降のレコードを削除して作成してください。

作成後の「仕入リスト」シートは、青枠の項目欄だけが残った状態になります。

2つの処理について

本記事でサンプルとして取り上げる2つの処理内容についてご紹介しますね。

処理1：在庫数30個未満の場合、レコードのセル色を黄色にする

まず1つめは、在庫数が30個未満のレコードのセル色を黄色に着色します。処理後の様子は下図のようになります。

処理2：仕入リストに在庫30個未満のレコードだけを転記する

2つ目の処理は、1つ目で着色された在庫30個未満のレコードだけを別リストに転記する集計処理です。VBAコード実行後の状態は下図のようになります。

本記事では、前述した2つの集計処理を実行させるいくつかのVBAサンプルコードをご紹介しますが、 『「値渡し」と「参照渡し」の違いを知るサンプルコード 』以外は、いずれのコードの実行結果もここでご紹介した処理結果と同じになります。

1つのプロシージャにVBAコードを書いたサンプルコード

はじめにベースとなるVBAサンプルコードについてご紹介します。前項でご紹介した2つの処理を、1つのプロシージャで実行させるサンプルコードです。

1つのプロシージャにVBAコードを書き続けるデメリット

ご紹介した単一プロシージャで書かれたVBAコードには、先に説明した2つの集計処理が書かれているだけですが、コメントも適宜入れているとはいえ、このまま追加の集計処理を書き続けていく場合、コードがどんどん下方に伸びていき、そのうち可読性やメンテナンス性が悪くなるだろうことが想像つきますよね。

例えば、今回のベースコードに対し、追加の集計処理のためのコードを追加しようとすると、下図の緑の範囲以降にVBAコードを追加していくことになります。

このように主処理のコードを追加していく場合、それに伴い下図に示す変数や初期処理に関するコードも追加になる場合があります。図の赤字は処理1に関するコードで、青字は処理2に関するコードですが、主処理のコードを増やそうとすると、必要な変数や初期処理のためのコードも増えることが多いです。

実際の実務においては、いくつもの集計処理を、VBAコードで順次行わせることが多いため、1つのプロシージャにそれらの集計処理を追加し続けると以下にあげたデメリットも増えていきます。

• プログラム全体の処理の流れを把握しづらい
• 機能改善の場合、適切なVBAコードの改善位置を把握しづらい
• 機能削除の場合、不要になったコード・変数などの削除・コメントアウトに時間を要したり、間違いやすくなる

つまり可読性やメンテナンス性が悪くなるため、必然的にデバッグを含めた開発効率も悪くなるということです。さらにプロシージャが長くなることで、スクロールしてお目当てのVBAコードを探す時間も無視できなくなります。

VBAコードをサブルーチン化（Sub～End Subに分ける）

1つのプロシージャにVBAコードを書き続けることによるデメリットを避けるために、処理をサブルーチン化（あるまとまった処理を1つの単位として、複数のプロシージャに分ける）してコードを管理する方法があります。

サブルーチン化のイメージ

先のベースコードをサブルーチン化したサンプルコードをご紹介する前に、まずは各処理をサブルーチン化して分けた概略図を用意しましたのでご覧ください。

上図は、前項でご紹介した単一サブルーチンのVBAコードに対して、サブルーチン化を取り入れた後のイメージ図です。

サブルーチン化によるメリット

単一サブルーチンでは、メインコードにすべての処理を書きましたが、サブルーチン化したメインコード（太字部分）では、2つの処理を「Call」ステートメントと呼ばれるプロシージャを呼び出すための文に置き替えています。

そして、各処理はメインコードの後に続く、それぞれの「Private Sub～End Sub」の中で詳細に記載されています。（Subの前に記載されている「Private」については、とりあえずここでは無視してください。）

このようにすることで、一番初めに目にするメインコードを見れば、コード全体の処理の流れを大まかに把握できるようになります。

また機能追加や削除する場合でも、どこのサブルーチンを改修（あるいは削除）すれば良いか、あるいはどこに新しいサブルーチンを追加して新機能を持たせるかなどの判断を効率よく行うことができます。

サブルーチン化によるVBAコードの実行順序

VBAコードが実行される順番についてはこれまで通り、記述された順番になります。よって、メインコードの上から順に実行され、「Call 在庫不足チェック」のところで、「在庫不足チェック」プロシージャ（赤字部分）内のVBAコードが実行されます。

それが終わると、次の「Call 仕入リスト作成」文により、今度は「仕入リスト作成」プロシージャ（青字部分）内のVBAコードが実行され、その後再びメインコードに戻り、残りのコードが実行されます。

処理別にSubを使ったVBAサンプルコード

それではベースコードをサブルーチン化したVBAサンプルコードをご紹介しますね。

上記サンプルコードでは、3つのプロシージャがあります。先のイメージで説明したメインコードと、2つの処理を記述したプロシージャの計3つですね。

それでは、これらのVBAコードについて詳細にお話ししていきますが、主役はサブルーチン化のお話しですので、既出のVBAコードについての詳しい説明は割愛させて頂きますね。

メインコードとモジュール変数宣言

モジュール変数の宣言部とメインコード部の解説です。

モジュール変数宣言部

モジュール変数は、同一モジュール内で有効な変数です。別の言い方をすると、同一モジュール内のすべてのプロシージャに共通して使える変数です。

今回の場合、「SampleSheet01」のwstSelfとレコード最終行番号の「lngERow」をモジュール変数として宣言しました。

理由は、処理全体として中心となるのはレコードのある「SampleSheet01」シートであり、そのレコードの最終行番号は多くのプロシージャ（今回の場合は2つのだけですが。。。）で利用されるからです。

なお、図の緑文字は2つのサブルーチンに共通して利用されるモジュール変数を意味しています。

メインコード部

初期設定で各プロシージャに共有されるモジュール変数に関する処理をしています。

続く主処理の中で、2つの処理をCallステートメントにより呼び出して実行させています。ここでCallの後に続く「在庫不足チェック」や「仕入リスト作成」はプロシージャ名であり、今回は処理の中身と流れが理解しやすいように日本語でネーミングしています。

「在庫不足チェック」プロシージャ（サブルーチン）

1つ目の処理をサブルーチン化したコードになります。ここでは各レコードの在庫数が30個未満かどうかをみて、30個未満の場合は、レコードを黄色セルで着色します。

このサブルーチンでは、主な処理が2つあります。1つは、項目名を含むレコード範囲全体のセルを無色にしてリセット（初期化）します。

もう1つは後半赤枠部分で、すべてのレコードに対する繰り返し処理の中で、各レコードの在庫数が30個未満のレコードを探し、条件に合う場合はセルを黄色に着色しています。

「仕入リスト作成」プロシージャ（サブルーチン）

2つ目の処理をサブルーチン化したコードになります。ここでは、「SampleSheet01」上の黄色に着色されたレコード（=在庫数30個未満のレコード）を抽出し、別シート「仕入リスト」上に転記しています。

上図は大まかな処理を前半と後半で分けたものです。前半部では、変数宣言と初期化を行い、後半部では「SampleSheet01」上のすべてのレコードから1列めのセルが着色されたレコードを探し、「仕入リスト」シート上へ転記しています。

以上のように、3つに分かれたプロシージャについて解説してきました。

このようにメインコードの処理部分をサブルーチン化して別プロシージャにすることで、処理の流れを把握しやすくなり、また探している処理がどこのVBAコードで実行されているのかを見つけやすくなったと思います。

確かに、今回のサンプルではたった2つの処理をサブルーチン化しましたので、全体的な行数が増えたことにより、かえってごちゃついた感じにも見て取れます。

しかしながら、多くの処理を実行する中規模以上のVBAコードになった場合、処理をサブルーチン化せずに単一のプロシージャ上で書き続けたり、無秩序にサブルーチン化するだけでは、その管理に限界が来てしまいます。

一方で、サブルーチン化により構造化されたVBAコードは、可読性・メンテナンス性に優れるため、コードの再利用や他人へ引き継ぐ場合にも有効ですので、みなさんもぜひこの方法（構造化プログラミング）に慣れて頂ければと思います。

VBAコードの値渡しと参照渡し

前項で取り上げたサブルーチン化では、処理を別プロシージャに分けてVBAコードを記述しました。具体的には、2つの処理をさせるプロシージャをそれぞれ「Sub～End Sub」として別に作り、それらをメインコードの中で「Call」ステートメントで呼んで処理させるのでした。

本項では、プロシージャを呼び出す際に、引数と呼ばれる値（数値、文字列、オブジェクト、配列など）を渡して処理をさせる方法についてご紹介したいと思います。

なお、引数を使うメリットですが、筆者の場合モジュール変数の使用を抑えるのと、サブルーチン同士のデータの受け渡しが理解しやすいからだと考えています。

メインコード内で呼ぶプロシージャに引数を渡すVBAサンプルコード

プロシージャに引数を渡すイメージ

まずは、プロシージャに引数を渡す場合のメインコードと各プロシージャとの関係を、前項で使用した概略図に加えてみましたのでご覧ください。

上図のように、引数をプロシージャに渡す場合、まずはCallステートメントに続くプロシージャ名の後ろに()をつけ、その()の中に渡したい値を引数として記述します。

そして、呼び出されたプロシージャ側でも、プロシージャ名の後ろの()の中に受け取る引数を記述します。これにより、そのコード内で引数を利用できるようになります。

実際のコードの記述方法については、このあとに紹介するサンプルコード内で詳しく解説しますので、ここではサブルーチン化された処理の流れの中で、引数がどのように受け渡されるかのイメージを掴んで頂ければと思います。

メインコード内で呼び出す2つのプロシージャに引数を渡すサンプルコード

それでは、プロシージャに引数を渡した場合のサンプルコードをご紹介します。

続いてVBAコードの解説になりますが、ここでも話題の中心は「引数」ですので、詳しいコードの解説は割愛しますね。

メインコードとモジュール変数宣言

下図はメインコードとモジュール変数の宣言箇所になります。

まずは、前項でモジュール変数として宣言した変数「lngERow」をローカル変数としてメインコードの中で宣言しました。理由は、引数を利用した場合のサンプルをご紹介する便宜もあったのですが、過去記事でもご紹介したようにモジュール変数の使用は必要最低限に抑えた方が良いと考えているからです。

メインコードで呼ばれている2つのプロシージャには、引数としてその変数「lngERow」を渡しています。記述の仕方としては、Callステートメントの後に、プロシージャ名を書き、続く()内に引数を記述します。

「在庫不足チェック」プロシージャ

次は、メインコードで呼ばれている「在庫不足チェック」プロシージャです。

メインコードで呼び出されたプロシージャ側になります。今回は引数を受け取りますので、冒頭プロシージャ開始文の緑色文字の箇所が、引数無しの記述に対して追加されます。

プロシージャで引数を受け取る場合は、()内に引数（例では変数vRow）を記述しますが、その前後にも記述する項目があります。

まず引数の前に記述されている「ByVal」ですが、これは引数を「値渡し」という形式で受け取ることを意味します。この「値渡し」についてですが、後ほど詳しく解説しますので、とりあえずは受け取る引数の前に、受け取る形式を記述しなければならないことだけ覚えておいてください。

一方、受け取る引数の後ろに記述されている「As Long」ですが、これは受け取る引数のデータ型を宣言しています。これにより、呼び出し側のメインコードで渡される引数の データ型 とも一致しないとエラーになりますので注意が必要になります。

またこの例「As Long」は、Long型(長整数型)で引数を受け取るという意味になります。

この「在庫不足チェック」プロシージャでは、引数を「vRow」という変数で受け取りますが、この変数「vRow」にはメインコード側の変数「lngERow」の値が入ります。

なお、受取り側のプロシージャの引数（例ではvRow）は、呼び出し側の引数として渡された変数名 （例ではlngERow) と同じでなくても構いません。

以上の説明から「在庫不足チェック」プロシージャ側でも、VBAコード「For r=2 to vRow」の中で、最終行番号を格納した変数として扱えます。

「仕入リスト作成」プロシージャ

引数を受け取るもう1つのプロシージャについての説明になります。

「在庫不足チェック」プロシージャと同様、引数受取に伴いプロシージャ先頭の記述が変わります。受取る引数「vRow」の前の「ByVal」は、「値渡し」で引数を受け取る際に記述するものですが、「値渡し」の意味については後述します。

また引数「vRow」の後ろ「As Long」は、受け取る引数の型を指定するものです。

ここでも受け取った引数を変数「vRow」としてVBAコードの中で扱っており、メインコード側の「lngERow」と同値ですので、「在庫不足チェック」プロシージャ同様、VBAコード「For r=2 to vRow」の中で最終行番号として利用しています。

引数の値渡しと参照渡しの違いについて

前項で触れました引数を受け取る形式について、ここで詳しくお話ししたいと思います。

まず引数を受け取る形式には、サンプルコードでご紹介した「値渡し」以外に、「参照渡し」というもう一つの形式があります。書き方は、値渡しの「ByVal」に対して、参照渡しでは「ByRef」と記述します。

これらの違いを一言で説明しますと、「値渡し」は引数のコピーを受け渡すのに対して、「参照渡し」は引数の参照先を受け渡します。

「値渡し」における値のコピーとは

サンプルコードでは、メインコードの変数「lngERow」を引数として、2つのプロシージャーに値渡し（ByVal）で渡しました。

この時、引数を渡されたプロシージャー側では、値のコピーを渡されたので、それぞれのプロシージャーで値を変更しても、その影響はメインコードの「lngERow」（コピー元）には及びません。

「参照渡し」における値の参照先を渡すとは

一方の「参照渡し」ですが、メインコードから渡す引数の「参照先」、つまりメモリのアドレス情報を渡します。よって、この形式で渡された引数は、渡された先のプロシージャで変更されると、メインコード側の値にも影響を与えます。

「値渡し」と「参照渡し」の違いを知るサンプルコード

引数の「値渡し」と「参照渡し」の違いについて、簡単なサンプルコードで確認してみますね。

まず上のサンプルコードは、引数の受け渡しを「値渡し（ByVal）」で実行するものです。メインコード側「Test」プロシージャを実行すると、実行手順に沿って途中の引数の値を①→②→③→④の順で表示します。ぜひ実際に実行して確認してくださいね。

次に、このサンプルコードの「test2」プロシージャの引数の受け渡し形式に関する記述「ByVal（値渡し）」を「ByRef（参照渡し）」に書き換えてから再度実行してみてください。（変更箇所は下図朱書き部の1か所です）

実行結果の違いは、メインコード上の④の処理結果に現れます（下図）。④は、直前のプロシージャー「test2」実行後、メインコードの最後で変数「lngERow」の値を表示させますが、参照渡しの方では「test2」プロシージャーの実行結果が、メインコード側でも反映されます。

以上が「値渡し」と「参照渡し」の引数の渡し方による違いになりますが、「値渡し」と「参照渡し」の違いは、下図のようなメールに添付ファイルをつけるか、参照先となるパスやURL情報を添付するかの違いと似ている気がします。

図は、AさんからBさんへメールでExcelファイルを渡す例を示したものです。

方法としては、 メール本文の中にパスかURLのリンク先を添付して送る(図の上側の方法）か、 メールで直接Excelファイルを添付して渡す（図の下側）かが一般的ですよね。

前者の方法で渡せば、Bさんは直接元ファイルを編集することが可能ですので、その結果はAさんにも影響します。一方、後者であればBさんは受け取ったExcelファイルのコピーを編集しますが、その影響はAさんの所有する元ファイルには影響を及ぼしません。

VBAコードのユーザ定義関数（Function）の基本的な使い方

単一のVBAコードを整理する中で、処理の内容を関数としてまとめたい場合もあります。一般的に関数といえば、ある値を入力して処理された結果の値が出力されるものですが、VBAではユーザ自身が作る関数（ユーザ定義関数）は、引数を受取り（入力）、処理した結果を戻り値（出力）として返します。

前項でお話しした引数を受取るプロシージャ（Subプロシージャ）は、引数を受取るだけで、戻り値は返すことができないのですが、今回ご紹介するユーザ定義関数では、引数を受取り処理した後に戻り値を返すプロシージャ(Functionプロシージャ）です。

ということで、本項ではFunctionプロシージャの使い方についてお話ししたいと思います。

Functionプロシージャを使用した際の処理の流れ（概略）

では、実際のサンプルコードをご紹介する前に、サンプルコードにFunctionプロシージャを利用した場合の処理の流れを説明した概略図をご覧ください。

mainプロシージャ

Functionプロシージャからは、戻り値が返ってきますので、その受取用の変数n1, n2を冒頭で宣言しています。また、その戻り値の受け方ですが、「受取用変数 = プロシージャ名（引数）」のように記述します。

Functionプロシージャ

まず冒頭と最後の記述がSubとは異なり、「Private Function ～ End Function」のように記述します。また、冒頭の()内に引数を記述した後、続けて戻り値のデータ型を記述します。

そして、Functionプロシージャ内で処理された結果を、mainプロシージャへ返すために、最後のVBAコードのところで「（Functionプロシージャ名) = (処理結果)」の形式で記述します。

ユーザ定義関数(Function)を使ったVBAサンプルコード

ではこれまでの2つのサブルーチンをFunctionプロシージャに変更したVBAサンプルコードをご紹介しますね。

では、サンプルコードの内容を処理の流れの解説を中心にお話しします。

メインコード(mainプロシージャ)

まずはメインコードの解説になります。今回はさらに前項のモジュール変数「wstSelf」をローカル変数にして、各2つの関数へ第一引数として渡しています。

また、2つの関数の戻り値をそれぞれ代入するための変数「n1」、「n2」を宣言し、主処理で各関数の戻り値を受取ります。

そして、受取った2つの戻り値が等しければ（各処理で集計した在庫数30個未満の商品点数が等しければ）、そのまま処理を終了し、2つの戻り値が等しくなければ（各処理で集計した在庫数30個未満の商品点数が等しくなければ）、「処理にエラーがあります」を表示します。

在庫不足チェック関数

では1つ目のユーザ定義関数のVBAコードの解説になります。

Subプロシージャに対する変更箇所を朱書きしてみました。冒頭の定義の部分がSubからFunctionへ変わっています。また、冒頭最後の記述に「As Long」とありますが、これは関数の戻り値のデータ型を記述します。

VBAコードの最終行では、処理結果の変数「n」を戻り値として返すために「在庫不足チェック = n」のように、（関数名） = (戻り値）、すなわち戻り値を関数名に代入しています。

そして、Functionプロシージャの締めの記述として「End Function」が記述されますが、これは冒頭文をFunctionに変更すると自動的に変わります。

また受け取る引数の数が今回は2つ「vSht」、「vRow」になりますが、これら引数の記述される順番は、呼び出し側で引数を渡す際に記述される順番と揃える必要がありますので注意してくださいね。

仕入リスト作成関数

つぎに2つ目の関数について解説しますが、処理の流れとしては1つめの「在庫不足チェック」プロシージャと同じですので、VBAコードの抜粋と文字色を変えた図を掲載するだけにしておきます。

まとめ

今回はVBAコードを処理単位ごとにサブルーチンやユーザ定義関数にまとめる方法と、その際に必要となる引数の「値渡し」と「参照渡し」、およびユーザ定義関数における戻り値の基本的な使い方についてお話ししました。

実務で見られる集計業務やEUCツールなどは、いくつもの処理に分けて結果を求めることがほとんどです。その一連の処理を単一のVBAコードで書いてしまうのは、あとあとのコードの改修や、コード自体を引き継ぎ長く使う上でもさまざまなリスクを伴います。

ぜひここでご紹介したVBAコードを処理単位でまとめる方法を取り入れて、可読性や保守性の優れた集計ツールを作成してくださいね。