Excel VBA（マクロ）を使ったツールのメンテナンスをしやすくするためにできること

自分はよく Excel ファイル中で、VBA（マクロ）を使ったツールを作ることがあります。

Excel VBA で作るツールは後々修正や改良をしなければならないことも多いので、メンテナンスがしやすいように作っておいた方が良いです。

自分自身、Excel VBA を使い始めたばかりの頃は、後のことを考えることなどできずに、かなりメンテナンスしにくい状態にしてしまった失敗がいくつもあります。

さて、今日は Excel VBA でツールを作る際に、後々メンテナンスがしやすいように意識していることをいくつかあげてみたいと思います。

1、VBA コードの工夫

VBA コードを書くときに意識していることをお話しします。

プロシージャ名は目的が分かるように付ける

VBA コードは通常、標準モジュールの中に書きます。

VBE（エディタ）を開いて、「Alt → I → M」を押すと、新しい標準モジュールが挿入されます。メモ帳みたいなの ↓

VBA コードは Sub と End Sub で囲われるプロシージャの中に書くわけですが、Sub の直後に書くプロシージャ名は何を目的としているか分かるように付けています。

例えば、Excel シート上の一部のセルを消すための VBA コードを記述したプロシージャであれば、「Clear1」のように名前を付けます。

こうすることで、後でメンテナンスするするときに、「消すために作った」プロシージャなのだ！ とすぐに思い出せます。

作っている最中は「忘れっこないって」と思っていても、1年後2年後に見るとさっぱり分からなくなっているものなので、思い出せる仕組み作りは大事です！

関連するプロシージャは同じ標準モジュールの中に作る

プロシージャは1つのモジュールの中にいくつも作ることができます。

さて、自分がプロシージャを作る際に注意していることとして、

関連するプロシージャは同じ標準モジュールの中に作る

ということがあります。

例えば、Excel シートの「あるセルを消すために作ったプロシージャ」がいくつもある場合、それらは同じ標準モジュールの中に作った方が後々分かりやすいです。

Clear1、Clear2、Clear3、のように、消すセルの場所によってプロシージャをいくつも作るなど。

この標準モジュールの中には消すための VBA コードを書いてたんだな！ ってすぐに思い出せます。

変数名も目的が分かるように付ける・コメントで補足しておく

変数名も目的が分かるように付けた方が良いです。

また、コメントでも補足しておいた方が良いでしょう。

全てそのようにはできないかも知れませんが、できる限りは。

下は Excel ファイルの中に CSV ファイルをインポートする VBA コードの一部です。

変数は Dim を使って定義してあり、以下のようなものになります。

・openFile は「これから開くCSVファイル名」
・code はこれから処理するデータに関係する会社の「会社コード」
・syamei はこれから処理する「会社名」
・nendo は処理の対象となる「年度」
・sei は「従業員の姓」
・mei は「従業員の名」
・seimei は「従業員の名前（姓名）」
・i、i6 は繰り返し処理の For 文の中で使う変数

変数名を目的に合わせておくと、後で見たときに直観的に何のためのものか思い出すことができます。

また、補足のコメントも後で修正・改良するときにはほぼ必ず確認することになります。

1つのプロシージャを長くし過ぎないようにする、分割する

1つのプロシージャはあまり長くし過ぎないようにした方が良いと考えます。

自分の場合、1つのプロシージャの中に全てのコードを書き込める場合でも、目的がはっきりしている部分を敢えて分割して別のプロシージャに記述することがあります。

そして、Call ステートメントを使って呼び出すようにします。

メインである「プロシージャ1」の中で、Call を使って分割してある「プロシージャ2」を呼び出す感じですね。

できるだけ一文がすっきり短くなるようにする

VBA コードの一文をできる限り短くする工夫も必要だと考えています。

例えば、いくつものシートを操作する VBAコードの中で「Test1」という名前のシートの一部の入力を消したい場合、次のように記述したとします。

上記の5つの文のどれにも、ワークシートを指定するために Worksheets(“Test1”) という記述が入っています。

これ必要ないです。

最初に「Test1」シートをアクティブ（操作対象）にしておけば、Worksheets(“Test1”) の記述は省略できます。

↑ 一文一文が短くなりすっきりしてみえませんか？

以下の記事も参考にして頂ければと思います。

コメントをできる限り詳しく残す

先に、変数のところでもお話ししましたが、コード中にコメントを残すことはとても大事です。

後々メンテナンスする可能性を考えて、少なくとも自分には分かるようにしておかなければなりません。

自分の経験から言うと、どれだけ詳しくコメントを書いても十分すぎることはありません。

コメントはを書くときは文の前に「’」を付けてコメントアウトします。そうすれば、VBA コードによる命令にはなりません。

一例として、先にも示したシートの一部の入力を消すプロシージャを出します。

次のように文の一番最後にコメント（緑色の日本語）を付ける場合があります。

ClearContents は消すためのメソッドです。それは分かっているので何を消しているのかを「日付、タイトル、科目、金額、摘要」という具合にコメントを残しているのです。

また、次のようにプロシージャ名のすぐ下に、プロシージャ内で何をやっているか書くような場合もあります。

コメントはできるだけ多く、詳しく書いた方が良いというのが自分の経験から言えることです。

コメントが残っていないと、後で見たときにさっぱり分からないので、メンテナンスのための生命線と言っても過言ではないかも知れません。

2、Excel シートの工夫

さて、次に Excel シートの工夫についてお話しします。

なるべく、以下の2つを意識するようにしています。

これらについては以下の記事で詳しく書きました ↓

ざっと説明させて頂きます。

行番号、列番号を変数で置く

次のように4～3月まで、総支給額、社会保険料、源泉所得税、住民税、差引支給額を並べた表があったとします。

このシートで VBA コードを記述する際に

変数 Soushikyu に総支給額
変数 Syaho に社会保険料
変数 Gensen に源泉所得税
変数 Jumin に住民税

を入れるものとします。

例えば、4～3月の指定は For 文を使って指定するため行番号は変数 i を使うとして、列番号を次のように記述するとします。

Soushikyu = Cells(i + 1, 2).Value
Syaho = Cells(i + 1, 3).Value
Gensen = Cells(i + 1, 4).Value
Jumin = Cells(i + 1, 5).Value

さて、これを変数「S_Line」を使って、以下のように書き換えることができます。

S_Line = 2
Soushikyu = Cells(i + 1, S_Line).Value
Syaho = Cells(i + 1, S_Line + 1).Value
Gensen = Cells(i + 1, S_Line + 2).Value
Jumin = Cells(i + 1, S_Line + 3).Value 

後でシート上で列が挿入された場合などに、VBA コードの列番号がずれてしまい変更しなければならないことがあります。

例えば新しく B列が挿入されると、総支給額、社会保険料、源泉所得税、住民税、差引支給額の列番号は1つずつずれます。

このような場合に、先に示したように列番号を変数で指定しておけば修正箇所は少なくて済みます。

最初の「S_Line = 2」の部分を「S_Linee = 3」に置き換えればいいだけです。

空白の行・列を作っておく

シートを作る際には、後々のことを考えて空白の行・列を作っておいた方が良いと考えます。

これ ↓ すごく、行幅・列幅が小さいので見づらいのですが、1～10行、A～J列を空白にしています。

後で、何か行または列を追加したくなったときに、VBAコードを修正することなしに追加できるからです。

上の表の4月の総支給額は VBA で表すと Cells(12,12)、社会保険料は Cells(12,13) となります。

総支給額、社会保険料の列番号が変わると VBA コードも修正が必要になってきます。

例えば、「支給日」の列を追加したい場合、もともと K列に「月」を書いていたのを J列にずらして K列を「支給日」にすれば VBAコード内の総支給額、社会保険料の列番号を変更しなくて済みます。

元のシートを A列からぎっしり詰めて余白を作らないと、後で行・列を追加したときに VBA コードの修正が必要になってしまうので、それを防ぐための工夫です。

3、メンテナンスの際に該当する個所を見つけ出す方法

さて、メンテナンスする際に探している箇所を見つけやすくする方法を紹介いたします。

メンテナンスのとき以外でも、最初に VBAコードを組むときにも使えます。

「Ctrl + F」を使って検索しましょう！

ということです。

VBE 上で「Ctrl + F」を押すと、以下のウインドウが出てきます。

「カレントプロジェクト」を選択して、「探したいキーワード」を入力しましょう！

「カレントプロジェクト」を選択することで、現在開いているモジュールの中以外も、ファイルの中のすべてのモジュールを検索できるということですね。

変数やコメントを探し出すのにとても便利な機能です。

先に、コメントはできるだけ詳しく書いた方が良いと言ったのは、後でメンテナンスする必要が出てきたときに検索しやすくするためでもあります。

目視で見ながら修正や改良が必要な個所を探し出すのは容易ではないので、この「Ctrl + F」による検索を活用するようにしましょう！

3、まとめ

Excel VBA（マクロ）を使ったツールのメンテナンスをしやすくするためにできることについてお話ししました。

学習し出した頃は、とにかくコードを書くのに必死で後のことは考えれませんでした。

VBA を使い始めた当時のツールを改良しようと思うと、当時の自分が後のことを何も考えずに作っているので、めちゃくちゃメンテナンスしにくいです。。

今では、将来の自分を助ける意味でもメンテナンスしやすいように工夫しているつもりです。

是非参考にして頂ければと思います。

編集後記

週末は土曜日（9日）が従弟の結婚式に出るために東京へ。すばらしい式＆披露宴でした。

そして、日曜日（10日）は息子の空手の昇段試験でした。

無事昇段することができて良かったです ^^