宣言もセットも不要!エクセルVBAでワークシートをオブジェクト名で取り扱う方法

★気に入ったらシェアをお願いします!


name

photo credit: jacobchristensen Table card via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAで請求データ一覧から請求書を自動で作成する方法をシリーズでお伝えしています。

前回はこちらの記事でした。

エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法
エクセルVBAで請求データ一覧から請求書を自動で作成するシリーズです。Excel関数でおなじみのSumIfs関数を使って年月の判定をします。DateAdd関数という日付に追加する便利な関数も紹介しますよ。

これで請求書を作るVBAプログラムとしては、まあまあ形になってきたと思いますが、ちょっとコードが長くなってきましたよね。

今回から何回かはちょっと趣向を変えて、今の機能はそのままで書き方をスッキリするテクニックをいくつかお伝えします。

まず注目したいのは、ワークシートです。

実は、ワークシートを取り扱う方法として、これまでは「Worksheet型」のオブジェクト変数を使ってきたのですが、別の方法として「オブジェクト名」で指定する方法というのがあります。

オブジェクト名による方法でワークシートを取り扱うと

  • 変数の宣言やセットが不要
  • プロシージャやモジュールをまたいでオブジェクト名で指定できる

というメリットがあり、結果としてスッキリ書くことができます。

ということで、今回はエクセルVBAでワークシートをオブジェクト名で取り扱う方法です。

では、いってみましょう!

ワークシートをオブジェクト変数で取り扱う方法

エクセルVBAでワークシートを取り扱う場合、これまでお伝えしてきた方法はこのようなものでした。

  1. DimでWorksheet型のオブジェクト変数を宣言
  2. Setでオブジェクト変数にWorksheetオブジェクトをセット

という流れですね。以下記事でも詳しくお伝えしている通りです。

【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
初心者向けVBAの実用的な使い方について、請求データ一覧から請求書を自動で作成するを目標に進めます。初回はシートを取り扱うためのWorksheetオブジェクトの準備とセルの値の操作についてお伝えします。

複数のプロシージャで同じワークシートを取り合う使う方法

複数プロシージャで同じワークシートを取り扱いたいとき、どのようにすれば良いでしょうか?

オブジェクト変数へのセットをする方法をベースとするのであれば、いくつかの選択肢があります。

各プロシージャでワークシートをセットする

まず、最もスタンダードな方法ですが、各プロシージャでワークシートオブジェクトをセットする方法です。

例えば、このような形です。

単純ではありますが、何度も同じことを書くのは無駄がある気がします。

また、シート名が変更になったときなどは、二か所とも変更しないといけないので面倒ですよね。

モジュールレベル変数にワークシートをセットする

次にワークシートを格納するオブジェクト変数をモジュールレベル変数とする方法です。

例えば、このようなプログラムです。

モジュールレベル変数は、そのモジュール内のどのプロシージャからでも、同一のものとして使用することができます。

こうすると、最初にセットしてしまったものは、他のプロシージャに処理が移っても、連続して操作することができるんですね。

以下記事でも説明をしている通りです。

【初心者向けエクセルVBA】モジュールレベル変数の宣言とSubプロシージャの呼び出し
請求データ一覧から請求書を自動で作成するシリーズも第6回目となりました。モジュールレベル変数の宣言、別のSubプロシージャの呼び出しなどによりプログラムをスッキリしつつ、うっかり発見した不具合を直していきます。

しかしこの方法にもデメリットがあります。

上記のような単純なプロジェクトなら良いのですが、たくさんのコード、たくさんのプロシージャから成るような複雑なプロジェクトになると、そのモジュールレベル変数がどんな状態なのかを追いかけるのが大変になってきちゃうのです。

ワークシートを引数として渡す

さて、別の方法としてワークシートを引数として渡すという方法があります。

例えばこのような形です。

プロシージャを呼び出すときに、wsDataを引数として渡します。そして受け取るときに、そのオブジェクトをwsというオブジェクト変数で受け取っているということですね。

ワークシートを渡してのプロシージャ呼び出しが一回なら良さそうです。

ただ、あちこちのプロシージャを呼び出すと考えると、毎回のようにワークシートを引数に入れるのは、スッキリしないですよね。

ワークシートのオブジェクト名とは

そこで登場する別の方法が「オブジェクト名」です。

オブジェクト名はワークシートに定められている定数のようなもので、どのモジュールまたはプロシージャからでもその名称でワークシートにアクセスすることができます。

オブジェクト名を確認する方法

では、試しに「請求データ」シートのオブジェクト名を確認してみましょう。

まず、プロジェクトエクスプローラーで「Sheet1(請求データ)」を選択した状態で、VBEメニューから「表示」→「プロパティウィンドウ」を選択します。または F4 キーでもOKです。

VBEでプロパティウィンドウを開く

すると、「請求データ」シートのプロパティウィンドウが開きます。

ウィンドウ内には「(オブジェクト名)」という項目があり、これがまさにオブジェクト名です。

この例では「Sheet1」と設定されていますね。

VBEでワークシートのオブジェクト名を確認

実は、プロパティウィンドウを開かなくても、オブジェクト名を確認できます。

というのも、プロジェクトエクスプローラーに「Sheet1(請求データ)」と記載されていますね。

まさに、ここでの表示が

オブジェクト名(シート名)

となっているので、こちらで確認するほうが簡単です。

のあ、オブジェクト名は、シート名つまりWorksheetオブジェクトのNameプロパティとは全く別モノですので注意して下さいね。

オブジェクト名を使ったプログラム

では、実際にオブジェクト名を使って操作をしてみましょう。

冒頭で紹介したプログラムと同様のことを、オブジェクト名による取り扱いとすると、こんなプログラムになります。

…たったこれだけです…!

オブジェクト名は、それこそオブジェクト変数と同じような使い方となります。

そして変数ではないので、宣言もセットも不要なのです。

これは便利ですよね!

オブジェクト名を変更する

さて、オブジェクト名ですがデフォルトでは「Sheet1」とか「Sheet2」といった無味乾燥な名称となっていますが、任意の名称に変更をすることができます。

オブジェクト名を変更するのは簡単で、プロパティウィンドウで「オブジェクト名」の欄にカーソルを当てて編集をするだけです。

VBEでオブジェクト名を編集

これで変数を用いるときと同様に、意味のあるオブジェクト名でシートを取り扱うことができますね。

オブジェクト名を使用した請求書作成プログラム

では、シリーズでお伝えしてきた請求書作成プログラムを、オブジェクト名を使用して書き換えてみましょう。

オブジェクト名を使用することで

  • ワークシート用のオブジェクト変数をPublicで宣言
  • ワークシートオブジェクトのセット
  • シートの初期化をするSubプロシージャ

を削除して書いてみました。このほうが、全体としてスッキリ書けているかと思います。

まとめ

以上、エクセルVBAでワークシートをオブジェクト名で取り扱う方法についてお伝えしました。

シートをオブジェクト名で取り扱うことで

  • シートをプロジェクト全体で定数のように一意で指定できる
  • 宣言やセットなどのステートメントを省ける

といったメリットがあります。

ワークシートを使用する際は、ぜひこの選択肢も利用できるようにしておいてくださいね。

次回は列番号を管理するのにもの凄く便利な「列挙体」についてお伝えします!

エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方
エクセルVBAで請求データ一覧から請求書を自動で作成する方法のシリーズ。今回は、Enumステートメントで定義する「列挙体」というものを使って、シートの列の挿入も簡単に対応する方法をお伝えします。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  3. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  4. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  5. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  6. 【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し
  7. 【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
  8. 【初心者向けエクセルVBA】Worksheetのコピーを活用して複数の請求書を作る
  9. 【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る
  10. エクセルVBAでDateAdd関数を使って年月を条件としてSumIfs関数を使う方法
  11. 宣言もセットも不要!エクセルVBAでワークシートをオブジェクト名で取り扱う方法
  12. エクセルVBAでシートの列の挿入も簡単に対応できちゃう列挙体の使い方

コメント

  1. SGO より:

    とても素敵です!
    ありがとうございます!!

  2. おっさんSE より:

    仕事で急遽Excelマクロが必要になり、半分パニックの中
    (1)から(11)までためさせてもらいました。
    一通り感じがつかめました。とてもわかりやすかったです。
    ありがとうございました!

  3. よぶびえ より:

    職場でのマクロ作成にとても参考にさせて頂いております!
    ひとつお伺いですが、オブジェクト名での指定とシート名での指定とどちらがおすすめでしょうか?…というとケースバイケースだと思いますが、どういう場合にどちらを使うかとか決めていらっしゃいますでしょうか?
    シート名を変更しても良いという点やこちらの頁の通り記述の少なさという点では、オブジェクト名での指定が良いと思いますが、一方で、オブジェクト名という別の管理項目が必要となってしまうこと、また、初級者にはわかりづらいかなと思っており悩んでおります。良いお知恵があれば教えて頂けると幸いです。