スポンサーリンク

マクロ(Excel VBA)のコードを書くときのちょっとした工夫、シート名はなるべく変数で指定する、アクティブなシートを切り替える


Excel でシートの数がたくさんあり、複数のシートを処理するようなマクロ(Excel VBA)のコードを記述することがあると思います。

「シート1」の数値を処理して、「シート2」の数値からマイナスして、さらに「シート3」の数値と掛け算して・・・
といった具合に、いくつものシートをまたいで処理をするとシートの指定を何回も行わなければなりません。

また、シートの指定を行うために一行一行のコードが長くなってしまいます。

今日は、Excel VBA で複数のシートの処理を行う場合におけるちょっとした工夫についてお話しします。

●シート名はなるべく変数で指定する
●アクティブなシートを切り替える

スポンサーリンク

1、複数のシートの処理を行うコードの一例

シート設計

さて、一例として2つのシート「操作画面」と「顧問先情報」がある場合を考えてみます。

以下の「操作画面」シートのクリーム色のセルに顧問先コードを入力して、青色の実行ボタンを押せばマクロが実行します。

マクロは「顧問先情報」から次の情報を読み込んで「操作画面」シートに書き出します。

・顧問先名
・住所
・代表取締役
・電話番号
・売上高
・売上原価
・原価率
・利益率

※原価率、利益率は Excel VBA で計算させます。

「顧問先情報」シートです↓

コード

標準モジュールに記述したコードを以下に示します。

マクロ名は「Test」です。このマクロが上記の「操作画面」シートの実行ボタンに組み込まれています。

実行結果

試しに「操作画面」シートで顧問先コード「1005」を入力して実行してみます。

すると顧問先の情報が出力されます。

ちゃんと出てますね。

この情報は「顧問先情報」シートから該当するデータを読み込んだものです。

以下については、リンク先よりお願い致します。----------------------------------------------------------------
◆メルマガ登録
◆Youtube:税理士 松井元のチャンネル

2、後でシート名を変更したい場合があるのでシート名はなるべく変数で指定しておく

さて、個々のコード全ての詳細な説明は割愛させて頂きますが、今日お伝えしたいところの1つ目について。

上記のコードでは、シート名をいちいち名前で指定しています。

このように↓

Name = Worksheets(“顧問先情報”).Cells(i + 1, 2)

Worksheets(“操作画面”).Cells(8, 5) = Name

その場合、後でシート名を変更したくなったときに1つ1つシート名を変更しなければなりません

シート名を全て書き換えるのは面倒です(コピペでも)。

そのような場合に備えて、以下のコードのようにシート名はなるべく変数で指定するようにしています。

※やっていることは上記コードと同じです。

最初にシート名を表す変数

「Sheet_Name_1」
「Sheet_Name_2」

を定義しています。

Dim Sheet_Name_1
Dim Sheet_Name_2

そしてすぐにシート名を決めています。

Sheet_Name_1 = “操作画面”
Sheet_Name_2 = “顧問先情報”

その後のシートの指定は、全て変数を使って行っています。

このようにです↓

Name = Worksheets(Sheet_Name_1).Cells(i + 1, 2)

Worksheets(Sheet_Name_2).Cells(8, 5) = Name

こうしておけば、後でシート名を変更したくなったときには、変数に入力する名前を変えるだけで対応できるので楽です。

3、アクティブなシートを切り替えてコードを短くする

また、上記のシートを変数を使って指定する方法に加えて、アクティブなシートを切り替えるのも有効です。

※アクティブというのは操作対象になっていることを意味します。

分かりやすく言えばそのシートを開いている状態のことです。

Worksheets(Sheet_Name_1).Activate

と記述すれば、「Sheet_Name_1」シートがアクティブになり

Worksheets(Sheet_Name_2).Activate

と記述すれば、「Sheet_Name_2」シートがアクティブになります。

シートをアクティブにした後に、そのシート内の数値を読み込むときにはシート名の指定は不要になります。

Name = Worksheets(Sheet_Name_2).Cells(i + 1, 2)

と記述していた部分は

Worksheets(Sheet_Name_2).Activate

としてSheet_Name_2」シートをアクティブにした後に

Name = Cells(i + 1, 2)

とワークシートを指定せずに記述すればよくなります。

アクティブなシートを切り替えたコードを以下に示します。

※やっていることは上記コードと同じです。

セルを指定する際にワークシートの指定は不要になるのでコードが短くなりスッキリします

4、まとめ

Excel VBA のコードはできる限りスッキリと書きたいものです。

また、変更があったときの作業の負担も抑えたいです。

上記のようなやり方も、そのための一例として参考にして頂ければと思います。

以下については、リンク先よりお願い致します。----------------------------------------------------------------
◆メルマガ登録
◆Youtube:税理士 松井元のチャンネル