Excel VBA シートWorksheetsを指定するコーディング方法について

※今回の記事は、Excel VBAのコーディングを自力でやってて、変数くらいは分かるレベルの人向けです。

今回はExcel VBAで、ExcelのシートWorksheetsを指定する際の、スマートな書き方を考えてみる。

ここで、with構文の使用は禁止する縛りとする。
with構文を使うと、スマートでない書き方に対してもかなり誤魔化しが効くからね。

かなりスマートさに欠けると思う書き方

割とマジでExcel VBAをやってる人でもこういう感じで、Worksheets(“売上”)というのを複数回繰り返すソースを書いている人がいる。

非常に申し訳ないがこういうのを見ると、「このレベルの人がVBAを続けてて大丈夫かな？」くらいにまで思ってしまう。

電卓で求めた答えをそのまま再利用せず、毎回もう一度電卓を最初から打ち直して、同じ答えを求めて転記してるような、無駄の多いコードになっている。

もしシート名が「売上」から変わってしまったりしたら、多数の箇所を修正しないといけない。
置換するにしてもリスクが伴うだろう。

シート名を指定して変数に格納する書き方

次にこれが、普通に合格のレベルだと思う。
Worksheets(“売上”)という露骨な指定は一度限りにしてさっさと変数に収める。

シートのインデックス番号を指定して変数に格納する書き方

次に、シート名ではなくてシートのインデックス番号(左から何番目にあるか)で指定する方法。

シートが何番目にあるかは、ブックを操作する人がちょっと不注意でも起こせば変わりやすいと思うので、私はあまり使わない。
新規作成したブックのシートを指定する場合などは、このやり方でやることもあるけどね。

ActiveSheetを対象とする書き方

次にこれが、実は私が結構使うやり方。Rangeの書き方も好きではないのでCells記法に変えた。
ActiveSheetって何だよ、それこそ変わりやすいだろという欠点はある。

ただ、私のいつものやり方としては、マクロの起動ボタン(フォームコントロールのボタン)を特定のシートに配置して、そのアクティブシートのボタンからだけマクロを起動する前提で進めるから、このやり方でだいたい問題ない。

シートの内部的な名称を用いる書き方

さて、ここまでは他愛のない雑談レベル。
実はここからが、私の本当に紹介したい方法。別に私も毎回その方法を使ってるわけじゃなくて、状況に応じて使うけどね。

たとえば「売上」「原価」「粗利」「販管費」「営業利益」と、きっちりシート名を振ったExcelファイルがあるとする。
そのシート名について、マクロを書くVBEの画面(Alt+F11で開けるよ)で見てみると、こういう風になる。

VBAできちんとモジュールを使ってる人ほど意識が向いてないポイントだと思うが、Excel上で「売上」「原価」とかシート名を振っていても、VBAで内部的に見るとそれらシートには「Sheet1」「Sheet2」、・・・という名称が連番で割り当てられている。

これは普通に使っていたらExcelが自動設定するものであり、変更したかったらVBEから変更するしかない。
そのためには、VBEでまずシート名のところをクリックし、次にそのプロパティの画面で「オブジェクト名」のところを書き換える。

これで、シートの内部的な名前まで書き換えられるけど、書き換えたからどうだというのか。
これによりVBAで、Worksheetsに対応する変数を改めて設定しなくて良くなるのだ。

上記の例なら「売上」シートの内部的な名前を「uriageWS」と書き換えたわけだが、これにより

といきなり書いてしまって良くなる。
通常ならば

という変数宣言が必要なところを省略できるのだ。

この方法なら、仮に(表面的な)シート名が「売上」から変更されたとしても全く関係なく「uriageWS」という内部的名称の方を使っていける。
もちろんコードの書き換えは不要だ。

まあこの方法にしても、内部的なシート名なんて設定するのに馴染まないケースは多いだろうし、決して万能ではない。
ただ、こういう方法もあるというのは覚えておくと得だろう。