【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し

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


Call

mickyroo / Pixabay

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

請求データ一覧から請求書を自動で作成するシリーズのVBA講座、今回は6回目です。
なかなか完成しませんね~、でも少しずつコツコツとできることが増えていますよ。

前回はこちらの記事。

【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
請求データ一覧から請求書を自動で作成するシリーズのVBA講座も第5回目です。今回は間違いなくお世話になる条件分岐If~Thenの使い方の超基本をマスターしていき特定のデータのみ転記するように改良していきます。

エクセルVBAの基本の中でも超重要なIf~Thenを使って、データの中から必要なものだけを選んでチョイスする方法について解説しました。

しかし不具合を二つ、見つけちゃいました…。

前記事直すという手もあったのですが、実際に勉強してみると不具合が見つかって直すということも当然あるので、そのまま突き進みます。

今回はPublic変数を使ったり、Subプロシージャを分けたりする方法を学びつつ、プログラムを簡潔にかつスッキリとそして不具合も修正といきたいと思います。

スポンサーリンク

前回のプログラムのおさらい

請求データからIf~Thenで5月納品分のデータだけを請求書に貼り付ける、というところまでできました。

請求データはこちらを使います。

請求データに納品日を追加

これらのデータから5月のデータのみを請求書ひな形のシートに転記をしていくというプログラムがこちらです。

不具合を発見!

このプログラムですが5月分やるときはいいんですが、4月分やるとどうなるかということなんです。

上記プログラムの22行目のIf文の条件式ですが

Month(dayData) = 4

に変更して実行してみると・・・

実行結果がおかしい-1

なんかおかしいですね。どう考えても合計が正しくありません。

これは請求書ひな形に5月の転記を行ったときのデータが残ってしまっているのが原因です。プログラムの冒頭で前回のデータは削除しなくてはいけません。

また、この状態でプログラムの22行目のIf文の条件式を

Month(dayData) = 5

に戻して実行しますと

実行結果がおかしい-2

またこれもおかしい…。これも合計が正しくないです。

これは実は転記自体はうまくいっているのですが、本来表示すべき行が隠れたままになってしまうことによります。

これについては、以前の記事で入れておくように自分で言っていたのに忘れちゃってました。てへ。

【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

さて、このように運よく発見できた不具合ですが

  • 請求書ひな形の転記先のエリアは全てクリアする
  • 表示を隠していたのを再表示する

という処理をデータを転記する処理の前に差し込んで修正します。

それで、この差し込む処理を含めてワークシートの準備をする処理たちをシート初期化()という別のプロシージャに分離して、プログラムをスッキリさせてみたいと思います。

小分けにパーツ化するようなイメージですね。

ちょっと今の時点ではわかりづらいかも知れませんが、ひとまずついてきてください。

Public変数を宣言する

本題に入る前に変数の使用範囲について先にお話しします。

Subプロシージャの中で宣言した変数はその中でしか使えないというのが基本ルールとしてあります。

ですが、今回のように複数のプロシージャで構成したいと思ったら、同じ変数を複数のプロシージャでまたがって扱いたいときがあります。

そんな時には、プロシージャの外でPublicの変数宣言をするのです。

書き方は

Public 変数名 As 変数の型

と書きます。通常の変数宣言でDimだったところをPublicに変えればOKですね。

記述する場所はSubプロシージャの外に書きます。標準モジュールの一番上に書けばOKです。

ただ何でもかんでもPublicにするべきではありません。プロシージャ内で完結する変数はDimを使うようにして、どうしてもプロシージャをまたがって使いたいもののみPublic変数にしましょう。

シート初期化プロシージャを作成

それではシートの初期設定部分を別プロシージャとして作成していきたいと思います。

  • Worksheetオブジェクトをオブジェクト変数にセット
  • 「請求データ」シートの行数をカウント
  • 「請求書ひな形」シートの隠れたセルを再表示
  • 「請求書ひな形」シートの転記のセル範囲を全てクリア

といった処理をシート初期化プロシージャとします。プログラム化すると

となりまして、あとはセルのクリアを入れればOKです。

Clearで特定のセル範囲をクリアする

書き方ですが

Worksheetオブジェクト.Range(セル範囲).Clear

と書きます。簡単ですね。

今回のプログラムとしてはセル範囲はA21:C50までが対象ですので

とすればOKです。

Callで他のSubプロシージャを呼び出す

請求書作成()プロシージャ内で、先ほど作成したシート初期化()プロシージャを呼び出したいと思います。

プロシージャの呼び出しを行うときは

Call プロシージャ名

と書きます。

したがって、請求書作成()プロシージャは

とすればOKです。

6行目でSub シート初期化()に処理が飛んで、End Subを迎えたら、請求書作成()プロシージャの7行目に戻って来ます。

かなりスッキリしました。

4月分,5月分ともに実行結果は

実行結果4月分

実行結果5月分

と正しいものが出力されました。

まとめ

  • Public変数を宣言する
  • Clearで特定のセル範囲をクリアする
  • Callで他のSubプロシージャを呼び出す

などを駆使して、不具合を直しつつプログラムをスッキリさせることができました。

ちょっと長いですがプログラム全体はこちらです。

ただ、If文のところ。Month(日付)=5の条件をいちいちVBE開いて修正してしていくのはとてもめんどうですよね。

次回はこちらの記事。

【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
請求データ一覧から請求書を自動で作成するシリーズのVBA講座の7回目です!今回はInputBoxによる入力フォームの表示と月末日の自動算出を活用して実用に耐えうるプログラムの完成を目指していきます。

今度こそ、その課題を解決していきますよ。

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

  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】ファイルのコピーを使って取引先別の請求書を作る


コメント

  1. u より:

    Dim i, j, k As Long
    と変数を宣言するとiとjはVariant型になってしまいますよ。
    正しくはDim i As Long, j As Long, k As Long です。