共通作業は別プロシージャに書くサブルーチン化を進めましょう
勉強会でご質問いただいた内容について記事にしていきます。以下のような質問をいただきました。
「グループで共有出来るようなマクロを準備したい」が課題があるので相談したい
との事でした。課題を教えていただく為に会話してみるといくつか疑問点を持たれている様子でした。
- コード内の式を実行した際にエラーを回避してマクロが止まらないようにしたい
- コードはどのモジュールに書いたらよいのか分からない
- そもそも作法の様なものはあるのか
これは結構難しい課題です。初心者さんのレベルを超えていますね。
マクロを使用するグループの環境（レベルや決まり事）も考慮してコードを用意する事が必要になります。
よってどんなケースにも当てはまるような画一的な作法はありません。しかし目指すべき方向性はあります。
回答としてはマクロの質を上げることをおすすめします。以下2つを意識する事でマクロの質が上がります。
- エラーが出てもマクロが止まらない様にする
- エラーが出た時の処理を作っておく（エラーを許容したマクロ）
- 標準モジュール内でサブルーチン化を進める
- 標準モジュールの中で共通部分を切り離して別プロシージャにする
この2つができるとマクロの質が上がります。マクロの質が上げるとマクロ自体のメンテナンス性が上がります。
メンテナンス性が上がるという事はグループで共有しやすくなるという考え方です。
以降具体的な事例を使って説明していきます。
関連記事
後ほど紹介するコード内で使用する値渡し（ByVal）参照渡し（ByRef）を使ったコードを紹介しています。
再帰呼び出しを使っているのでコード自体は難しいです。勉強してみたい人は記事を読んでください。
値渡しと参照渡しを使用【VBA_再帰】複数フォルダ内を検索拡張子を決めて画像を取り出す
同じく値渡し、参照渡しを使用している記事を紹介します。
値渡しと参照渡しを使用VBA｜コードが長いプロシージャを短く切り分ける｜マクロを部品化する
関連書籍
こちらの書籍はサブルーチンついて分かり易い説明が掲載されています。
サブルーチンとは
Wikipediaを引用します。
プログラミングにおけるサブルーチン（英: subroutine）は、プログラム中で意味や内容がまとまっている作業をひとつにまとめたものである。
サブプログラムあるいは副プログラム（英: subprogram）とも呼ばれ、単に「ルーチン」（英: routine）と呼ばれることもある。
プログラミング言語によっては、関数（英: function）やプロシージャあるいは手続き（英: procedure）とも呼ばれる。
Wikipedia：サブルーチンから引用
1つのプログラムをメインとサブに分けた際のサブプログラムの事をサブルーチンと呼びます。
分けたプログラムのどちらをメインにするのかサブにするのか？何を基準に分けるのかはその時の仕様次第です。
以降の記事の中でサブルーチンを用いたコードを用意しています。具体例を使って解説していきます。
事例紹介
ベースとなる事例を紹介します。
私が以前書いた記事からコードを転用し加工しました。参考に記事のリンクを置いておきます。
ワークシート
シートの構成（仕様）は以下の通りです。
シート名：データベース
シート名：抽出シート
1つのDBに対して性質の違うボタンが1つずつという環境です。
- シート名：データベース ・・・ 情報が並んでいるシート
- シート名：抽出シート ・・・ DBから情報を抽出するためのシート
- 抽出シートには「実行」ボタンと「削除」ボタンがある
- 実行ボタン：抽出シートD列の名前がデータベースのA列にあった際は特定の列の情報を取り出す
- 削除ボタン：実行ボタンで取得した情報を削除する為のボタン
コード
コードは全て標準モジュール内に用意されています。
プロシージャは3つです。
抽出用プロシージャ（実行ボタン）、削除用プロシージャ（削除ボタン）が各ボタンに連動しています。
Sub 抽出用()
'ここではオブジェクトとプロパティの値を取得して
'メイン作業のプロシージャに値を引き渡す為の準備をするためのコード
'************************************************************
'変数の定義
Dim clm1 As Integer 'VLOOKUP関数で取得する1つ目の列を指定する変数
Dim clm2 As Integer 'VLOOKUP関数で取得する2つ目の列を指定する変数
Dim sht As Worksheet '実行ボタンのあるワークシートへの参照を収納する変数
'************************************************************
'画面更新の停止
Application.ScreenUpdating = False
'************************************************************
clm1 = 8
clm2 = 10
Set sht = Sheet2
'作業をする為のプロシージャにお願いする
Call メイン作業(clm1, clm2, sht)
'************************************************************
'画面更新停止の解除
Application.ScreenUpdating = True
End SubSub メイン作業(ByRef c1 As Integer, ByRef c2 As Integer, ByRef ws As Worksheet)
'DBから抽出する際の列番号と値を出力する為のワークシートの情報を引数として取得
'もとのコードからの引数は値を書き換えるものではないのでByRefでもByValでも可
'引数c1、c2を使ってVLOOKUP関数を実行し値をオブジェクト変数wsのシートに返す
'最後に引数wsの参照を開放するがその後に作業は無いので実質無でもOK。
'************************************************************
'変数の定義
Dim name_r As Long '抽出シートのD列を周回する為の変数
Dim r As Long 'データベースの最終行の値を格納する為の変数
Dim c As Long 'データベースの最終列を格納する為の変数
'************************************************************
'データベースの最終行と最終列を確認する
With Sheet1
r = .Cells(Rows.Count, 1).End(xlUp).Row
c = .Cells(5, Columns.Count).End(xlToLeft).Column
End With
'************************************************************
'表題を取得しオブジェクト変数wsのシートに記入する
ws.Range("E5") = Sheet1.Cells(5, c1)
ws.Range("F5") = Sheet1.Cells(5, c2)
'************************************************************
'抽出シートの４列目をループする
For name_r = 6 To ws.Cells(Rows.Count, 4).End(xlUp).Row
'オブジェクト変数wsの4列目にいる名前がデータベースに居るのか確認
If WorksheetFunction.CountIf _
(Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(r, 1)), ws.Cells(name_r, 4)) > 0 Then
'居たらVLOOKUPで結果を抽出
ws.Cells(name_r, 5) = WorksheetFunction.VLookup _
(ws.Cells(name_r, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(r, c)), c1, False)
ws.Cells(name_r, 6) = WorksheetFunction.VLookup _
(ws.Cells(name_r, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(r, c)), c2, False)
Else
'居ない時は「該当なし」と表示させる
ws.Cells(name_r, 5) = "該当なし"
ws.Cells(name_r, 6) = "該当なし"
End If
Next
'************************************************************
'オブジェクト変数wsの開放
Set ws = Nothing
End Sub削除ボタンのコードです。
Sub 削除用()
'変数の定義
Dim 行 As Long
Dim 行2 As Long
'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行を認識
行 = Cells(Rows.Count, 5).End(xlUp).Row
行2 = Cells(Rows.Count, 6).End(xlUp).Row
'抽出シートの都道府県とカレーの食べ方が入力されている列の最終行が５より大きかったら・・・
If 行>5 and 行2>5 then
'結果をクリアにする（都道府県とカレー食べ方の抽出結果を消去する）
Range(Cells(6, 5), Cells(行, 6)).ClearContents
End If
End Sub2つのポイントでコードの質を上げる
用意されたコードは2つのポイントが盛り込まれています。1つずつ見ていきましょう。
1_エラーが出てもマクロが止まらない様にする
エラーが出てもマクロが止まらない様にするにはエラーを想定しておくことが必要です。
エラーが出た時の処理を作っておく（エラーを許容したマクロ）という立ち回りをしていきます。
解説
ワークシートでVLOOKUP関数を使った事がある方は「＃N/A」という表記を見たことがありますね。
読み方は諸説あるので割愛します。意味としては1つです。エラーだという事です。
VBAでVLOOKUP関数を使う際対象となる要素が無い時はエラーでマクロが止まってしまいます。
しかし裏を返せばエラーが出るケースはかなり明確になっていますのでピンポイントで対策を用意します。
「メイン作業」プロシージャの中段やや下のFor～Nextステートメント内の処理です。
Ifステートメントを使って処理を分岐しています。
最初にCountIf関数で対象となる要素がVLOOKUP関数にて検索される側のデータ内に存在するのかを確認します。
存在するのであればVLOOKUP関数を実行し存在しないのであれば「該当なし」という文字をセルに代入します。
この様にエラーが出るところに「エラーが出たら○○する」という分岐を用意してあげましょう。
エラーを許容する（受け流す）事ができるとコードの質が上がります。
2_標準モジュール内でサブルーチン化を進める
コードを書く場所はたくさんあり対象となる仕事によってどこにコードを用意するのかは変わります。
よってここでは標準モジュールにコードを用意したケースに特化してコードを紹介します。
そのうえでメンテナンス性を上げる為のコードを用意しました。
解説
用意されたコードはVLOOKUP関数を使ってデータベースの中から一定の値を取り出すという作業です。
今回はデータベースの8列目と10列目を取り出しています。ここで新しい環境を想定してみましょう。
もう1つワークシートを追加して2つのシートからデータベースの情報を取得する事を考えてみます。
2つ目のシートではデータベースの別の列の情報を取得するとしたらまた全部のコードを書かないといけません。
そうなると似たようなコードが2つ存在する事になります。
これを避ける為に以下のような仕様でサブルーチン化（コードを分ける）します。
- VLOOKUP関数で抽出する列を決めるコード・・・「抽出用」プロシージャ
- 残りの作業を担当するためのコード・・・「メイン作業」、「削除用」プロシージャ
「抽出用」でデータベースの列を決めます。ここで決めた値を引数として「メイン作業」に投げます。
「メイン作業」は引数さえ渡せばデータベースの中のどの列の情報でも取り出せるような仕様にしておきます。
このような仕様にする事でシートを増やした際に出来るだけコードが重複しない仕様にする事ができます。
Case-1では1つの標準モジュール内に必要なコードを全て用意しました。
Case-2では2つの標準モジュールを使いました。プロシージャを用意する時の環境でモジュールを分けています。
1つ目の標準モジュールはシート追加と共に用意する必要があるプロシージャをまとめてあります。
2つ目の標準モジュールはシートの増減に関係のないプロシージャをまとめてあります。
Case-1でも-2でもどちらの構造でも構いません。使う人が分かりやすいかどうかで決めてください。
メリットデメリット
プロシージャを分ける事で良いこともあれば悪い事もあります。
- コードの重複が減るのでメンテナンス性が上がる
- 値渡し（ByVal）、参照渡し（ByRef）の勉強が出来る
- クラスモジュールを勉強する際コードの書き方が参考になる
- スコープや値渡し、参照渡しが分かってないとコードが読めない、書けない
- プロシージャを跨いでコードが走るので可読性が落ちて引継ぎが難しくなる
要するにメンテナンス性を上げると可読性が落ちて引き継ぎが難しくなる可能性があります。
メンテナンス性を取るか可読性を取るか
ここは割り切ってメンテナンス性を取りましょう。
一般的にはグループでマクロを共有していても「コードを書くのは限られた人たち」というケースが多いです。
コードを書くメンバーのスキルに差がありサブルーチンが分からない時はメンバーには勉強してもらいましょう。
サブルーチンを覚えた方がグループでコードを開発する事も出来る様になります。
AさんはAAプロシージャ、BさんはBBプロシージャという様に複数人で1つの仕事にアプローチできます。
1人の仕事でもグループでの仕事でもメンテナンス性を求めてコードのサブルーチン化を進めましょう。
参考：クラスモジュール
どのモジュールにコードを書くのかに対する1つの回答です。
記事内では標準モジュールを勧めましたが別の考え方もあります。（より高度な考え方です）
マクロの使用環境次第ですがクラスモジュールを使うと効率良くコードを書く事ができる場合があります。
クラスについて【サンプルコード】VBAでクラスを使ったコードと使わないコードを比較する
まとめ
「グループで共有出来るようなマクロを準備したい」という課題について解説しました。
- エラーが出てもマクロが止まらない様にする
- 標準モジュールにコードを用意しつつサブルーチン化を進める
この2つを意識してコードを用意していただければグループで共有出来るマクロを作る事ができるようになります。
ここで挙げた2つの要素の他にもやる事はありますがまずはこの2つを意識してコードを用意してみてください。
以下リンク先の記事では1つのプロシージャをサブルーチン化する一連の作業のプロセスを見る事ができます。
値渡しや参照渡しなど細かい要素についても解説しています。
