サイト内検索

オブジェクト管理 基本

VBAコードにワークシート名を使う複数ワークシート管理法

複数のワークシートをVBAで扱う1つの方法として、ワークシート名を使って複数のワークシートを管理する方法があります。集計業務では、複数のワークシートを取り扱うことがほとんどですよね。

わたしは、取りまとめ担当をしていましたので、各部署のExcelシートを1つのExcelファイルへ集めて、1枚のシートに合算集計する業務をしていました。

もちろん、VBAを使った集計業務でも、複数のワークシートを使って一瞬で1枚のシートに合算集計することが得意です。ですが、そのためにはちゃんと各シートに名前を付けて管理しなくてはいけません。

そうでないと、各シートには「B1」とか「E3」などの同じセルがありますよね。例えば、コード上でRange("B1")と書いても、複数のシートにRange("B1")が存在します。

その複数あるRange("B1")に対し、どのシートのRange("B1")かをコード上で明確にしないと、正しい集計結果は出ないですよね。

VBAでは、その問題回避のために個々のワークシートに名前を付けることができます。Excelワークシート名のつけ方のルールの1つにもありますように、同じExcelファイル上では、同じワークシート名はつけることができませんよね。

このルールはVBA上でワークシートを管理する上でも実は重要だったんですね。それこそ、同じ名前を付けることが出来たら、いくらVBA上でワークシート名で管理しようと思っても、正しい集計結果が出ませんから。

さて前置きが長くなりましたが、今回は下記のファイルを使ってお話しをすすめたいと思います。

サンプルファイル「HowToUseModule2」は、前回の記事で使用した「HowToUseModule」に対し、これも前回作成した標準モジュール「A部B部週報用.bas」を一緒に保存したものです。

では、今回のコードですが、実は2つのプロシージャそれぞれに手を加えて修正していますので、比べてみて以下コードと同じになるように修正してみてくださいね。

ワークシート名を使う小計行削除のVBAサンプルコード
A部用プロシージャ修正箇所
ワークシート名を使う棚卸資産列削除のVBAサンプルコード
B部用プロシージャ修正箇所

今回のVBAコードのロジックは、前回と全く同じです。修正内容は、A部とB部の各ワークシートをオブジェクト変数に設定したことです。

ここでVBAコードの内容について、先にお話しします。今回は両プロシージャの共通点を取り出しながらお話ししますね。話の中心はワークシートオブジェクト変数の「wstADep」と「wstBDep」になります。

<変数宣言>
Dim wstADep As Worksheet

Dim wstBDep As Worksheet
「 A部」、「B部」の各ワークシートを変数で扱うために、それぞれ「wstADep」、「wstBDep」と変数名を設定します。Depは英語「Department」(日本語で「部、部門」など)から拝借しました。

<ワークシートオブジェクト設定>
Set wstADep = Worksheets("A部")

Set wstBDep = Worksheets("B部")
ワークシートを変数に設定するVBAコードになります。Rangeオブジェクトなどのオブジェクトを、オブジェクト変数に設定するときは、「Set」を使うのでしたね。

VBAコード集計技の必須3大オブジェクト

ここは次図でお話ししたいと思います。

ワークシート名をExcelワークシート上で確認
ワークシートを変数で取り扱う

VBAコードの右辺は、Worksheets()の中に、ワークシート名を文字列として入れるだけです。「Worksheets」は複数形であることに注意してくださいね。

これで以降のコードの中で、各ワークシートを変数「wstADep」、「wstBDep」として扱えるようになります。

<最終行列取得>
lngERow = wstADep.Range("B" & wstADep.Rows.Count).End(xlUp).Row
lngECol = wstBDep.Range("IV1").End(xlToLeft).Column

これも長くなり、ちょっと複雑そうなので、別図でお話をしますね。

最終行列取得のVBAサンプルコードをやさしく説明
最終行列取得コード

ワークシート変数名を宣言・設定したことで、VBAコード内でワークシートを変数で指定することができるようになります。

「どのワークシートのオブジェクト(Range)」とか、「どのワークシートのプロパティ(Rows)」かを指定するためには、まず属するオブジェクト/プロパティの前に「.」(ドット)を書きます。

そして、「.」(ドット)の前に対象のワークシート変数名を書きます。

一方、修正前のような「Range」オブジェクトや、「Rows」プロパティの前に、ワークシートを指定しない場合、VBAはアクティブなワークシートを処理の対象にします。

<If文条件式>
If wstADep.Cells(r, 2) Like "?市場" Then
If wstBDep.Cells(1, c) = "棚卸資産" Then

同様に、Cellsプロパティの前に「.」、さらに「.」の前にワークシートを指定することで、明確にどのワークシートのセルなのかを指定しています。

<行列削除コード(If Then ~ End If内)
wstADep.Cells(r, 2).EntireRow.Delete
wstBDep.Cells(1, c).EntireColumn.Delete

これも考え方は同様です。それぞれ、「A部」のCells(r, 2)、「B部」のCells(1, c)を指定しています。

では、お待たせしました。これからコードの実行をしてみたいと思います。ここで前回の実行手順を思い出してください。前回は、それぞれのプロシージャを実行する前に、処理対象のワークシートを選択しましたよね。

しかも、実行するまでにいろいろと手順が多かったですよね。今回は、実行手順は簡単に済みます。Excel側のアクティブなシートはどこでも構いませんので、ただ2つのプロシージャを続けて実行するだけです。

今回は開発タブから下のように続けて実行してみましょうか。

開発タブからVBAコードを実行
開発タブから実行

どうですか?前回と同じになりましたでしょうか。

今回は、VBAコード上で対象のワークシートを指定しましたので、前回のようにいちいち実行対象のワークシートをアクティブにしなくても実行されます。

言い方を変えますと、前回のVBAコードのように「どのワークシートなのか?」を指定しない場合は、VBA側で自動的にActiveなワークシートを処理対象としてVBAを実行してしまいます。

まとめ

今回はVBAコード上で、複数のワークシートを取り扱う際の方法についてお話ししました。

単一のシートに対する集計処理であれば、VBAコード上でワークシートを明記しなくても問題ありません。

しかし、複数のワークシートを同時に処理する場合は、必ずどのワークシートを対象にした処理かを、VBAコード上で明確にする必要があります。この場合、今回ご紹介した考え方が役に立ちますよ。

[スポンサーリンク]



サイト内検索

-オブジェクト管理, 基本
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.