ExcelVBAを実務で使うためのTips

はじめに

最近『ExcelVBA 実践のための技術』という本を読んだのですが、この本があまりにも良書過ぎたので今後役に立ちそうな部分を抜粋しておこうと思います。

何がそんなに画期的か

VBAプログラマだけではなく全てのプログラマのための本

この本が素晴らしいなと思ったのはVBAの深堀り紹介本というわけではなくて、VBAという言語を通してオブジェクト指向の実践と注意点や、コーディングの時に陥りがちな落とし穴、ユーザに快適に利用してもらうためのポイントなどを丁寧に解説している点です。

VBAなんてプログラミング言語として不完全だし将来性と無縁だから触らないよと正直感じている人にこそ読んで欲しい1冊です。

Chap1,2 VBAの基本と構文の理解

この章で自分がVBA改めて再学習の重要性を感じたのはDim A As String, B As Stringの記述をした場合にはBはVariant型になるということを本書で指摘された部分です。自分の場合にはVBAを学習したのが20年前（まさにVB6.0全盛期）だったため入門書が少なく情報のアップデートを怠っていました。。

• 開発効率やメンテナンス性にも考慮して、「動くからいいや」からの脱却を目指す。

• VBはVisualBasic6.0をベースとしているため、Excelの新機能に対応した命令が追加されたとしても基本的な部分というのはもう進化したりはしない。

• 完全なオブジェクト指向ではないため「継承」の概念はないが、その考え方を積極的に取り入れることは重要。

• 変数名の命名規則）システムハンガリアン記法（データ型を変数名の頭につけるような命名法、例えばstrNameのような）は使わない。変数名とデータ型が結合してしまいデータ型を変更した場合に変数名も変更しなくてはならないためやめたほうがいい。

• 命名規則はどんな規則を適用しても構わないが、定数はUSRR_NAMEのようにすべて大文字で記述するのがよい。

• Msg関数などのように()を文末につけたりつけなかったりする関数は、呼び出した時に返り値を使うかどうかで使い分けている。関数をコールしたときの引数渡しは基本的に参照渡し。

• 従来、データ型の宣言はセンシティブに行うべきという理由からVariant型＝ダメというイメージが付いているがExcelVBAを扱う上ではセル範囲の格納や特定の関数の戻り値を取得する上でVariant型でなければならない場合も多く存在する。

• ExcelのVBAの定数はLong型の値。この値の中身はオブジェクトブラウザで確認するのが良い。特に組み込み定数のうちvbから始まるものはVBA全般に共通のもの、xlで始まるものExcel独自の定数となる。

• VBA限った話ではないが、変数の有効期間はできるだけ短く、適用範囲はできるだけ狭くする。

• VBAの制御文の中には他言語では存在するContinue文が存在しない。そのため、特定の条件にマッチした時にスキップするためにはIfで分岐（ただしこの場合にはわざわざこのためにIfのネストが増えてしまう）するか、GoTo分で分岐するかの処理を行う。

配列の扱いは難しい

配列を使う以上は変数名を１つずつ振って操作するシンプルさとは違い、要素を取り出して操作する時に煩雑になってしまうことが多いです。
その場合にはColectionオブジェクトを利用するとよいようです。またどうしても配列はそのインデックスだけ見てもその中身がわからず可読性が下がりがちですが、列挙型を利用することで可読性を高めることができます（この辺はVBAでなくてもそうですが）。

Private Enum Sample
   Name=1
   Color=2
End Enum

'配列の可読性を高める例
User(1,Samle.Color)="Red"

しか一方で配列は要素の追加・削除がしづらいというデメリットがあります。そこでVBAではCollectionオブジェクトという便利なオブジェクトが存在します。Collectionオブジェクトというのはデータとインデックスを併せて格納できる汎用のデータ箱であり、「○○の前、後ろ」みたいな感じで序列を決められるオブジェクトのことです。具体的な使用例は他の文献などを参照してみて下さい。

Chap3,4 Book,Worksheet,Cellの理解

マクロ作成初心者でもこの章はかなり役立ちます。ワークシートの取得のときの注意点や「セル」というオブジェクトの扱い方について。

• シートの選択はシート名称ではなく「オブジェクト名」を利用する。シート名はユーザによって任意に変更される恐れがある。「ユーザによって気をつけてもらう」はヒューマンエラーの根本的な解決にならない。
• ExcelのオブジェクトモデルはこのようになっているWorkSheets(1)やWorkSheet("sample")のような書き方をするとワークシートの順序や名前をユーザに変更するとマクロが停止してしまう原因になってしまうのでそういったときにはオブジェクト名を利用する。
• セルを値の変更やコピー＆貼り付けに関しては一度ガバッとVariant型の配列に格納してから行う。（このあたりは「最近のコンピュータはスペック高いから大した差がないよ～」と仰る方もいらっしゃるようですが間違いなく配列に一度格納したほうが速いです。）
• また、上の方法だとVlaueプロパティを配列に格納しているので、もし数式セルに同様の処理をしたいときには注意が必要。
• Cellsプロパティは親にRangeの参照を返すだけ。なのでCells(1,1)と書くと値を取得できることから.Valueがデフォルトのプロパティだと思いがちだが、あくまでもCells.[_Default](1,1)が正しい。混乱した人はこちらを参照。Cells(1,1)とするとインテリセンスが切れるのはCellsの戻り値のRangeのデフォルトプロパティがVariant型であるためにオブジェクト対象がはっきりせずインテリセンスが切れます。
• Rangeはセルの範囲を格納するオブジェクト、数を数えるにはAreaプロパティを使う（よくこれを知らずにフィルタをかけた表の内容を取得するのに苦労します。。）。同様に行全体取得はEntireColumnプロパティ。
• 重い処理を行うときにはExcelを同一のプロセス上で走らせるのではなく、定期的にWindowsに制御を戻すか別プロセスでExcelを起動して処理を行う工夫をする。

Chap5 VBA関数

• そもそもVBA関数とワークシート関数は別物。
• VBA関数と呼ばれるものは結局**クラスモジュールのFunctionから始まるもの（=Propertyではないので戻り値を取得しなくてもOK）**である。
• 似た名前のVBA関数でもワークシート関数の仕様を踏襲したつもりで利用すると思わぬバグに遭遇することがある（値の切り上げ、切り捨てなど）。

その他書籍中では、VBA関数を使わなくても正規表現を使えば済むシーンや同名のFuctionが作れることを利用してオーバーライドもどきをする方法などに触れています。

Chap6 図形とグラフの扱い

• 図形もグラフもShapeオブジェクトとして扱われる。さらにグラフについてはChartObjectsコレクションのメンバでもある。この二重の親を持つ構成になっていることに注意。グラフはChartObjectコレクションのChartObjectオブジェクトのChartオブジェクト、元々計算とグラフ描画が別ソフトであったことに起因してオブジェクトの構成が煩雑。
• ActiveSheet.DrawingObject.Deleteのように下位互換のためにレガシーなオブジェクトも存在する。
• 図形は特に数が増えがちな割に操作が煩雑になりがちなので、シートに追加時点で適切なオブジェクト名を与えておく。

Chap7 印刷とページ設定

• この章で言われている「単に印刷するだけでいいのか」という「印刷設定こそマクロでやるべきという考え方」は目から鱗。言われてみるとたしかに非常に合理的。
• PageSetUpオブジェクトで印刷設定をするとプロパティを設定するたびに通信するので遅いというイメージがあるが（実際自分もそうでした）、Excel2010以降に登場した通信を制御するApplicationクラスのPrintCommunicationプロパティを利用するとプリンタ側との通信をまとめてやりとりすることができるので処理速度が上がる。
• 改ページプレビューを設定して保存しておくと、ファイルの読み込みのたびにプリンタと通信するため余計に時間がかかる可能性がある。
• 帳票の保存は改ざんの恐れのあるエクセルファイルで保存するのは好ましくない。データはきちんと表に記入し、その表から帳票にデータを転記すべき。
• でも未だに帳票にダイレクトにデータを入力してデータと見栄えを分離できないのはVBAをきちんと使えるエンジニアが少ないから。関数だけでは限界がある。

Chap8 Accessとの連携

• Accessのデータベースソフトと連携するためにはDAO(Data Access Object)もしくはADO(Active Data Object)を利用して連携するが、これをごちゃまぜにして利用すると間違いの元。

Chap9 クラスモジュールの活用

この章こそこの書籍の肝！20年間VBAマクロの書籍を読み漁ってきましたが、こんなにクラスモジュールを詳細に解説している書籍を他に知りません。この章だけでもこの本を買う価値があります。クラスモジュールを具体的にどういうふうに書くのかはこちらをチラ見してください。

本書では表のレコードNo.を管理するCollectionオブジェクトと、検索用の連想配列（Dictionary型）を用いてExcelの表におけるレコードを一つのクラスオブジェクトとして操作するサンプルが示されています。

• クラスモジュールに作成できるPropertyプロシージャはプロパティを作成できるプロシージャ。Letキーワードを使うのが値を設定する、Getキーワードを使うのが値を取得するためのプロシージャ。これらの変数自体はモジュールレベルの変数である必要がある。
• 対象がオブジェクトの場合にはLetの代わりにSetを使うことになる。

また書籍内で詳しく触れられているわけではありませんが、最近はPCのリソースが豊富にあることからNothingでインスタンスを解放しない散布rコードを良く見かけます。
でも実際はきちんとコンストラクタとデコンストラクタを作成し、インスタンスは利用し終わったら解放する癖をきちんとつけておくべきでしょう。

Chap10,11 イベント処理とユーザーインターフェース

• よくブックを閉じるときや開く時、セルの編集時を指定してイベント処理を定義しておくことは多いですが、意外にイベント処理の種類は豊富です。
• 個人的にはユーザーフォームを作り込むようなプログラムは外注すべきと思っているのであまりこの章については詳しく触れません。しかし、いわゆるポカヨケ（ユーザの入力忘れや操作ミスを防止するための工夫）にイベントを利用するのはとても良いなと思いました。（例：入力ミスをチェックしてメッセージボックスで追加したり、シートを開くと必ず入力すべきセルがアクティブになるなど）
• ただ「ウィンドウサイズが変更されたら常に最大化」などのイベント処理は一見親切心で作ったつもりが、不便なシーンも意外に多いことがありそうなので、最大公約数のひとがたちが使えるようになるべく標準を心がけながら、そっと寄り添うくらいの強制力に留めないと思わぬソフトエラーになりそうです。

Chap12 例外処理

もちろんありとあらゆるシーンを想定してプログラムを作っておくことが究極の理想論ですが、多くの実務の場では不要な作業です。無駄に工数に工数がかかってプログラムを作ることのほうが目的となってしまい本末転倒です（実際多くの開発現場では処理したい部分よりもUIや例外処理のコードの方がとても膨らむ事がほとんどです）。

本書ではエラーオブジェクトをうまく活用して（例えばシート名の命名チェックを一つ一つ行うのではなく、一度そのシート名で作成できるか実際に走らせてエラーならばシート名が無効である旨を返すなど）例外処理を書く方法が示されています。

最後に

本書の優れているのはプログラミング入門書が毎月山のようになかなか出版されている現在において、
なかなか見落としがちな初心者から中級者に移行する「より実践的」なすぐ業務で使える知識を、
開発に対する姿勢もところどころに交えながらストーリー形式で伝えるかたちになっているところです。

これにより、読み手は本書を読みすすめることで単にプログラミング技術だけではなく
開発時のお作法の初歩の部分を学ぶことができます。
（ただその寄り道がいきあたりばったりで各章の内容がぼやけているのも
確かなのでこのまとめ記事を書きました）

本書の途中で確かにVBAで多くの作業が自動化できるが手作業でもちょっとできることをなぜわざわざプログラムにするのかという問いかけがありますが、PCのスキルは人によって千差万別だからこそ簡単な繰り返しをマクロにすることでアルバイトや不慣れな人でも確実に素早く正確に得たい結果が得られることを指摘しています。

そもそもVBAでコーディングできる人は比較的PCスキルの高い人のはずですが、使う人は往々にしてPCスキルの低い人が多く、その感覚の違いが使い勝手を悪くしている原因となっていることもとても多いです。ただそれをすべてのシーンに対応できる万能マクロとして開発するのであれば社外内製でやるよりも外部のプロに任せてしまったほうがいいわけで、多くの会社の場合はそこまでは求めていないはずです。

その時にコーディングするプログラマと利用者を代表した依頼者がスムーズに意思疎通するには、
まずこの書籍程度の知識は依頼側にもざっくり把握しておいてもらえるといいと言えるでしょう。

そういったマクロに対する向き合い方も丁寧にかかれているので、可能であればノンプログラマーでエンジニアと打ち合わせるような方にもつまみ読みしてほしいなあと思いました。

良いマクロライフを。