マクロはどのモジュールに書けば良いのか
勉強会でご質問いただいた内容について記事にしていきます。
以下のような質問をいただきました。
「グループで共有出来るようなマクロを準備したい」が課題があるので相談したい
との事でした。
課題を教えていただく為に会話してみるといくつか疑問点を持たれている様子でした。
- コード内の式を実行した際エラーが出てもマクロが止まらないようにしたい
- コードはどのモジュールに書いたらよいのか分からない
- そもそも作法の様なものはあるのか
これは結構難しい課題です。初心者さんのレベルを超えていますね。
この課題は少し勉強が進んだ方だからこそ考える案件です。
例えばJIS規格でVBAの書き方は・・・なんてことは無い（あったらごめんなさい）です。
ただしある程度慣例というか「よく見るコードはこんな形だよね」というものはあります。
自分の中でも「仕組み上コードは〇〇の様に書いた方が都合が良いよね」という考えは持っています。
一度考えを整理する為にも記事を書いてみます。
コードの書き方を考える事でコードを書く場所やエラーへの対策も紹介していきます。
私見を多く含みますので必ずしも正解ではないのですが「大外れ」でもないと思っています。
結論
結論を先に提示しておきます。
「出来るだけ共通部分は切り離して別モジュールに書く」という事をおすすめします。
これが出来てくると色んな事が解決に近付きます。
事例紹介
具体的に事例を使って考えていきましょう。まずはベースにするコードを紹介します。
私が以前書いた記事からコードを転用します。参考に記事のリンクを置いておきます。
簡単に説明すると以下の通りです。
シート名：データベース
シート名：抽出シート
- シート名：データベース ・・・ 情報が並んでいるシート
- シート名：抽出シート ・・・ DBから情報を抽出するためのシート
- 抽出シートには「実行」ボタンと「削除」ボタンがある
- 実行ボタン：抽出シートD列の名前がデータベースのA列にあった際は特定の列の情報を取り出す
- 削除ボタン：実行ボタンで取得した情報を削除する為のボタン
1つのDBに対して性質の違うボタンが1つずつという環境です。コードは2つのプロシージャとなります。
今回はプロシージャ名を抽出用（実行ボタンのコード）、削除用（削除ボタンのコード）にしています。
Sub 抽出用()
'変数の定義
Dim 行 As Long
Dim 列 As Long
Dim 抽出行 As Long
'画面更新の停止
Application.ScreenUpdating = False
'データベースの最終行と最終列を確認する
With Sheet1
行 = .Cells(Rows.Count, 1).End(xlUp).Row
列 = .Cells(5, Columns.Count).End(xlToLeft).Column
End With
'抽出シートの４列目をループする
For 抽出行 = 6 To Cells(Rows.Count, 4).End(xlUp).Row
'抽出シートの名前がデータベースに居るのか確認。
If WorksheetFunction.CountIf _
(Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 1)), Sheet2.Cells(抽出行, 4)) > 0 Then
'居たらVLOOKUPで結果を抽出
Cells(抽出行, 5) = WorksheetFunction.VLookup _
(Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), 8, False)
Cells(抽出行, 6) = WorksheetFunction.VLookup _
(Sheet2.Cells(抽出行, 4), Sheet1.Range(Sheet1.Cells(6, 1), Sheet1.Cells(行, 列)), 10, False)
Else
'居ない時は「該当なし」と表示させる
Cells(抽出行, 5) = "該当なし"
Cells(抽出行, 6) = "該当なし"
End If
Next
'画面更新停止の解除
Application.ScreenUpdating = True
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 Sub回答
コードを書く場所とエラー対策について回答していきます。
コードを書く場所
2つのプロシージャはシートモジュールか標準モジュールにまとめて書きます。
以下の図のようなイメージです。
ActiveXでボタンを設定するならばシートモジュールに寄せます。
オートシェイプやフォームコントロールであれば標準モジュールでも良いです。
どちらを使おうか決めかねる時はオートシェイプ×標準モジュールで用意しましょう。
理由はActiveXの理解が必要なためです。ユーザーフォームを勉強する時に覚えれば良いです。
エラー対策
VLOOKUP関数を実行した際のエラー（#N/A等)の対策としてはIF文を使っています。
詳細はVBA-14の記事をご確認ください。CountIf関数でエラー回避をしているコードを見る事が出来ます。
一旦まとめる
・・・終わってしまいました。ちょっとこれでは寂しいですよね。
しかしエラー対策については本当にこれで終了なんですよ。
エラーのみを誘導する分岐を作りエラーを回避させるというのが回答です。
少し物足りないのでコードを書く場所についてはもう少し考えてみましょう。
事例に要素を追加してみる
事例を用意します。DBから情報を抽出するシートが2枚になったとしましょう。
各シートが異なる情報を抽出するとなった場合どうするべきか考えてみましょう。
- DB用のシート
- 抽出用のシートA（DBの8列目と10列目を取得）
- 抽出用のシートB（DBの2列目と3列目を取得） ←左記シートの追加を想定します
シートモジュールにコードを書いている場合
シートモジュールにコードを書いている場合はシートをコピーしたらコードもコピーされます。
シートをコピーしてコードの一部を書き直せば作業完了です。
抽出用ボタンのコードは最初のシートと比較して異なる情報を抽出する事になるります。
よってVLOOKUP関数は第3引数の列を変える事になります。
DBの2列目の情報が欲しければ引数を2にする等メンテナンスが必要です。
シートの情報も取り直す必要があります
コピーしたシートは抽出用のシートの原本をコピーしている為です。
元のコードが書いてあるシートがsheet2だとします。
今回コピーで追加したシートに書いてあるコードのsheet2という記載がある場所を変える必要があります。
今回追加したシートの番号に書き換えれば全ての作業は終了です。
抽出結果を削除するボタンのコードは1つのシートの中で収まる作業です。
もとから積極的にシートを指定していません。
シートをコピーしたらコピーした先のシートで必要なところだけ削除してくれる様にコードを書いています。
よって変更を加える必要は無いです。
標準モジュールにコードを書いている場合
一方で標準モジュールにコードを書く場合はコピーしたシート用のコードを新たに用意する必要があります。
(コードの書き方次第で1つにも出来ますが初心者では難しいでしょう）
この場合でもほとんど同じコードを同じモジュール内に2つ書く事になるので効率が悪いです。
抽出結果を削除するボタンのコードは複数シートで共用出来ます。
よって標準モジュールに書いたコードを複数のシートで使い回す事にします。
課題
標準モジュール、シートモジュールどちらにコードを書いたとしても課題が残ります。
Workbook内にほぼ同じコードが複数存在する事になります。
内容次第ですが1つのコードに訂正が入ったら対になるもう1つのコードも訂正する可能性大です。
2つのコードならまだなんとか・・・と考えがちですが修正箇所が増える事もあるでしょう。
発展してシートがさらに増えていくとなったら修正が入った際は訂正箇所はどんどん膨らみます。
ミスが起きるのは時間の問題だと思います。
ここで最初に提示した「出来るだけ共通部分は切り離して別モジュールに書く」に立ち返ってみましょう。
結論を深堀してみる
まずは概念図とコードを見てください。
上の概念図の中にある「シートA 抽出用 引数の準備」にあたるコードです。
このコードがボタンに紐付きます。シートBは引数の指定が少し違うだけなので省略します。
Private Sub CommandButton1_Click()
'ここではオブジェクトとプロパティの値を取得して
'メイン作業のプロシージャに値を引き渡す為の準備をするためのコード
'************************************************************
'変数の定義
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 Sub上の概念図の中にある「シートA、B 抽出用 メインの作業」にあたるコードです。
実際にデータを取ってくるメインの作業は全てここに書きます。
Public Sub メイン作業(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最初に書いたボタンに紐付くプロシージャでは必要な情報を揃える事に特化します。
VLOOKUP関数を使った作業等メインとなる作業は別プロシージャに分けています。
こうしておくと別プロシージャに切り出したコードに修正が入った際作業が楽です。
1つのプロシージャだけ修正出来れば関連する作業への修正が完了します。
ボタンに紐付くプロシージャでは必要な情報を整理するだけです。
以降シートを増やした際に準備するコードは引数を準備するプロシージャだけで済みます。
メリットデメリット
このようにコードを分解して書ける様になると分かる事があります。
「どのモジュールにコードを書くべきなのか」が分かってきます。
この考え方のメリット、デメリットは以下の通りです。
- コードの重複が減るので訂正に強い
- スコープの勉強が出来る（Public、Private等）
- 値渡し（ByVal）、参照渡し（ByRef）の勉強が出来る
- クラスを勉強する際コードの書き方が参考になる
- スコープや値渡し、参照渡しが分かってないとコードが読めない、書けない
- プロシージャを跨いでコードが走るので可読性が落ちて引継ぎが難しくなる
要するに良し悪しなわけですよ。
メンテナンス性を上げると可読性が落ちて引き継げなくなるというジレンマに陥ります。
メンテナンスを取るか可読性を取るか
初心者のうちは割り切って考えましょう。
引継ぎはコードが読める人にしか出来ません。
自分しか勉強しない環境に居る人は元から引き継げないと考えるしかないです。
可読性が落ちるというデメリットは考えなくて良いです。
勉強する人が居て引継ぎが出来る環境では引き継ぐ人が勝手に勉強してコードを読みます。
心配する必要は無いです。（けっこうな暴論ですよ。分かってます。）
メリットのメンテナンス性だけ見て前向きな矢印だけを頼りに共通部分だけ切り離してみましょう。
応用
上記コードには書いていませんがエラー対策する際もこの考え方で対応出来ます。
どのコードでも確認するような内容は別プロシージャに切り出しておけば良いです。
「入力必須の欄が空欄になってないか？」、「全角、半角のチェック」等々。
切り出したコードをどのモジュールに書くのかはWorkbookとしてどの様にデータを持つかによります。
スコープや値渡し、参照渡しの考え方さえ分かっておけばどこに書いてもコードを繋ぐことは可能です。
スコープや値渡し、参照渡しについては別途記事を書いてみようと思います。
（いつか書きます）ほかにも案がありますので提示しておきます。
他の事例も紹介
2つ事例を紹介します。
全部標準モジュールにコードを書く
-2も引数を用意する為のプロシージャはシート毎に用意します。
全てのプロシージャはとにかく標準モジュールに書くという例です。初心者のうちはこれで十分です。
用途で標準モジュールを分けてコードを書く
次は-3です。こちらも標準モジュールへコードを用意するのですが標準モジュールを2つ使っています。
用途毎に標準モジュールを用意して別々に格納しておくという方法です。
シートを追加した際は引数を準備する為のプロシージャを用意し標準モジュールに格納します。
メインの作業と削除用のプロシージャだけもう1つ標準モジュールを用意して格納します。
シートが増えた際は上の標準モジュールに各シートのボタンに連動したプロシージャを用意するだけにします。
主要な作業とデータを削除する作業は変更される事が少ないので切り離しておくという仕様です。
私はActiveXでシートモジュールに関連付くボタンをあまり使いません。よって-2か-3を使います。
理由はワンクリックで動作するボタンしか用意しないのとボタンのデザインをカスタマイズしたい為です。
オートシェイプで照り感のあるボタンにしたり3Dの画像をインプットしてボタンにしたいので標準モジュールにコードを書く事が多い為です。
まとめ
-1を提示したのはおそらく考え方としてはこれが一番正解に近いと思っているからです。
これから勉強を重ねるとユーザーフォームやクラス、他の言語について勉強する事になります。
その際は-1の考え方が役に立ちます。
ここでは緩い説明に留めます。
勉強する時になったら「そういえば・・・」みたいな感じで記事を見に来てください。お疲れ様でした。
関連記事です。マクロはどのモジュールに書くのかを表や画像を使って表現してみました。
