エクセルVBAを上手に使うための心構え

はじめに

私のいいたいことはこの記事の最終節にまとまっているのですが、それを説明するにはどうしても具体例が必要なので、最初に長々とノウハウ的な解説をやります。
VBAマスターな方にとっては稚拙な点だらけで辟易するかもしれませんが、最終節の主張をどうしても伝えたいがための説明なので大目に見てやってくださいませ。

状況の説明

一例として、お客様から（あるいは提携部署から）Excelのデータが送られてきたとしましょう。
ある業務に関する数値などのデータが色々入っているヤツです。
これを、あなたの職場では、値の入力する位置などが決められたあなたの職場用のフォーマットに直して保管あるいは提出しなければなりません。

例えばこんな感じの送られてきたデータを

こうしないといけない感じですね。

まあ、こんな面倒な状況になってしまっている時点で連携が上手くいってないのは確かですが、残念ながらよくあることだと思います。

さて、この保管用のファイルを作るにあたってマクロを組んでみましょう。

組みがちなマクロ

以下のようなマクロをたまに見ます。

VBA

Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
Set wb = Workbooks.Open("元データ.xls")
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet1")

ws1.Range("B1").Copy
ws2.Range("C4").Paste
ws1.Range("B2").Copy
ws2.Range("C7").Paste
'以下これが延々と続く

これは、「マクロの記録」からVBAを学んだ人に多い書き方ではないかと思います。
細かいツッコミ所は色々とあって、

・A.Copy : B.Paste と言う記法は冗長で、A.Copy B といったスタイルに書けること
・ws1かws2のどっちかをWithブロックで代用できること

あたりを鑑みれば、以下のようなコードに書けるでしょう。

VBA

Dim ws As Worksheet
Set ws = Workbooks.Open("元データ.xls").Worksheets("Sheet1")

With ThisWorkbook.Worksheets("Sheet1")
    ws.Range("B1").Copy .Range("C4")
    ws.Range("B2").Copy .Range("C7")
    '以下これが延々と続く
End With

まあこの方が読みやすくわかりやすいと思います。
が！！！
実は、これでも褒められた実装ではないのです。

シートでできることはシートでやれ

今回の課題に対する実装は、本来は至極簡単なはずです。

まず、Sheet1に元データを全コピーして貼ります。
次に、Sheet2の適切なセルに数式を入力し、Sheet1から値を参照してきます。

これだけで済む話です。VBAとか必要ありません。
次回以降は、送られてきたデータをSheet1に貼りなおせば、Sheet2には勝手に保管用データができるでしょう。

「しかし、余計なSheet1が残ると良くないし、セルの中身が数式になっているのも困る」と言う方がいるかもしれません。

ならば、それこそVBAを使えばいいのです。

VBA

Application.DisplayAlerts = False 'シート削除時の確認ダイアログをスキップできる
With ThisWorkbook
    With .Worksheets("Sheet2").Range("A1:D13")
        .Value = .Value '領域を値貼り付けしている
    End With
    .Worksheets("Sheet1").Delete
End With
Application.DisplayAlerts = True

これで数式を全部潰し（値に変換し）、なおかつ余計なSheet1を削除することができます。

最終版

以上を踏まえて、今回の課題に対応するExcelツールを作るとすれば、以下のようになるでしょう。

まずSheet1を以下のようにします。

ユーザーが入力する箇所は、この黄色い２箇所のセルだけです。
元データのファイルパスと、保管用ファイルを格納したいフォルダパスを指定します。
ちなみにファイルパスを入力するのはそう面倒ではありません。対象のファイルを「Shift＋右クリック」して「パスとしてコピー」すればパスの文字列がクリップボードに得られます。

次にSheet2は元データ貼り付け用として空のシートにしておきます。
Sheet3は保管用フォーマットを作って、数式でSheet2から参照を引いておきます。

最後にVBAで以下のようなコードを書きます。

VBA

With ThisWorkbook
    Workbooks.Open(.Worksheets("Sheet1").Range("A2")).Worksheets("Sheet1")_
     .Cells.Copy .Worksheets("Sheet2").Range("A1") '元データを開いてSheet2に貼りつけ
    With .Worksheets("Sheet3")
        .Range("A1:IV256").Value = .Range("A1:IV256").Value '値貼り付け、範囲広めにとっておく
        .Copy '空のブックにシートをコピー
    End With
    ActiveWorkbook.SaveAs .Worksheets("Sheet1").Range("A5") & "\保管用.xls" 'コピーしたブックを保管先に保存
    .Close SaveChanges := false 'このブックを保存せずに閉じる
End With

これで何が起きたかと言うと、パスで指定した元データから値をSheet2に貼り、それを数式参照でSheet3に持ってきて、そのままSheet3だけを新しい保管用ファイルとして保管先に格納できたわけです。
ということなので、このツール自体は保存せずに閉じてしまって構いませんね。
というより保存してしまったらSheet3の数式が潰れるので、次回以降使えません。
ですからうっかり保存しないよう、閉じるところまでVBAで指示してあります。

ユーザーは２箇所のパスを指定したら、マクロを実行するだけです（コマンドボタンにしておくといいですね）。これを毎回するだけ。とっても使いやすいツールになりました。
おまけに、保守も楽々です。送られてくる元データの形式や保管用のフォーマットがある時変更になってしまった場合は、Sheet3をいじれば良いのです。VBAを手直しする必要は、全くありません。

まとめ：VBAもオブジェクト指向で！

この記事で私が言いたかったことはこのセクション名の通りです。
VBAは一般的にオブジェクト指向型言語ではないですし、オブジェクト指向寄りに書くのも難しい仕様だと思います。
けれど、それがエクセルにくっついているというのは、大きな武器です。
ブックが、シートが、セルが、オブジェクトになります。

今回の完成例を見てもらえればわかるように、
・Sheet1は入出力先を管理
・Sheet2は貼り付け先
・Sheet3はフォーマット調整
・VBAは入出力の実行とSheet3の切り離し
というようにそれぞれ役割がはっきりと分離しています。（VBAは役割が二つあるので、できればプロシージャを分割してそれぞれ単一の役割にしたい）
さらにSheet3内では、それぞれのセルが数式を持つことでセルごとにオブジェクトとしての役割を果たしているといえるでしょう。

こうすることで、書き手にとっては書きやすく、読み手にとっては意図がとりやすく、引継ぎ相手にとっては保守しやすいという三拍子揃ったツールになることでしょう。

また、今回敢えて多めに登場させたWithブロックも、オブジェクト指向の大きな武器となります。
Withブロックの活用を意識することで自然とオブジェクト単位の操作になり、それらがインデントですっきりまとまる。これにより、意味のまとまりが一目でわかるようになるのです。
後で読む人間にいかに説明なしで意味をわかってもらうかというのがオブジェクト指向の真髄だと思います。
そういう意味では、日々の業務で多用するエクセルVBAだからこそ、オブジェクト指向が真の力を発揮するのでは無いでしょうか。