パーマネントに保持しておく値はセルに収容する。

前項で「変数」について説明しましたが、メニューボタン等を用意しての処理を作成したりイベント処理を行なう場合、いつがプログラムの開始/終了なのかが「あやふや」な状態になります。そのワークブックを閉じるまで内容が保持されているのかというとそうでもありません。

モジュールレベル変数でも、実行時エラーで停止したような場合は初期化されてしまいます。
特にイベント処置していない場合は処理中にEscキーを押した段階でも初期化されてしまいます。

あまり初心者向けの話ではないのですが、基本的に重要なことなのでここで説明することにします。
ワークブックを開いてから、継続的に何かの状態を変数に保持していたいということがありますが、この「初期化されてしまう」ということへの対処が必要になります。 実際にワークブックを閉じるまで保持させたい変数は、モジュールレベル等の変数ではなく、ワークシート上のセルに収容しておきます。Excelではセルも1つの変数となります。
ユーザーに見せたくない内容であれば、隠しシートを作っておいて、そこに保持するようにします。

但し、これには問題がないわけではありません。
セルの値を変更するわけですから、ブック自体が「非保存」になるという弊害が発生します。 実際にはシートの内容を書き換えていないのに上記の理由で閉じる段階で「○○の変更内容を保存しますか?」というメッセージが表示されてしまいます。

この状態はワークブックの「Savedプロパティ」で制御できるのですが、単に「保存済み(Saved = True)」にすれば良いわけではありません。

ここでの考え方はVBAで必要な制御情報をセルに収容する時、元々のワークブックの「Savedプロパティ」を変更しない、ということです。 手操作であれ、マクロであれ、セルに値を書き込めばそのワークブックは「非保存(Saved = False)」になるので、 そのまま閉じようとすると「○○の変更内容を保存しますか?」のメッセージが表示されるのがExcel本来の仕組みです。


'***************************************************************************************************
'   制御シートのA1セルに値を書き込む
'
'   作成者:井上治  URL:http://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   制御シートのA1セルに値を書き込む
'***************************************************************************************************
Sub GP_SetSavedValue(intValue As Integer)
    '-----------------------------------------------------------------------------------------------
    Dim blnSaved As Boolean                                         ' 保存済状態
    ' 直前の保存済状態を退避
    blnSaved = ThisWorkbook.Saved
    ' 制御シートのA1セルに値を書き込む
    ThisWorkbook.Worksheets("制御").Range("$A$1").Value = intValue
    ' 保存済状態を戻す
    ThisWorkbook.Saved = blnSaved
End Sub

'------------------------------------------<< End of Source >>--------------------------------------

例えば、このようなプロシージャを用意すれば上記のことに対処できると思います。