スポンサーリンク

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

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

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

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

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

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

スポンサーリンク

1、VBA コードの工夫

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

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

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

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

Image(8)

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

Image(9)

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

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

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

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

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

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

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

ということがあります。

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

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

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

Image(11)

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

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

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

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

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

Image(12)

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

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

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

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

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

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

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

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

【初心者向け】Excel VBA(マクロ) 他のプロシージャを呼び出す Call ステートメントの使い方
Excel  VBA(マクロ)を使うと、コードを分割したくなることがあります。 特に様々な Excel ツールで共通して使うような部分があると、そこだけは分けておきたいと感じます。 さて、そのようなときに Call ステートメントを使う...

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

Image(13)

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

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

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

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

これ必要ないです。

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

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

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

マクロ(Excel VBA)のコードを書くときのちょっとした工夫、シート名はなるべく変数で指定する、アクティブなシートを切り替える
Excel でシートの数がたくさんあり、複数のシートを処理するようなマクロ(Excel VBA)のコードを記述することがあると思います。 「シート1」の数値を処理して、「シート2」の数値からマイナスして、さらに「シート3」の数値と掛け算し...

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

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

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

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

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

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

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

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

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

セミナー開催致します。
◆4/22 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録

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

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

2、Excel シートの工夫

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

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

●行番号、列番号を変数で置く
●空白の行・列を作っておく

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

マクロ(Excel VBA) を使うときは後の作業を楽にするために行番号と列番号を変数で指定する、Excel シートに空白の行・列を入れておく
Excel VBA でのプログラミングを楽にするために工夫は欠かせません。 今日は、私が普段 VBA を使う前提で Excel シートを作るときに工夫していることを2つ紹介します。 ●行番号と列番号を変数で指定する ●空白の行・列を...

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

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

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

Image(16)

このシートで 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つずつずれます。

Image(17)

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

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

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

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

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

Image(18)

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

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

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

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

Image(19)

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

Image(16)[1]

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

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

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

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

ということです。

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

Image(20)

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

Image(21)

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

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

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

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

3、まとめ

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

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

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

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

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

編集後記

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

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

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

セミナー開催致します。
◆4/22 30代半ばから税理士になった体験談セミナー

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録