モジュールレベル変数でも、実行時エラーで停止したような場合は初期化されてしまいます。
特にイベント処置していない場合は処理中にEscキーを押した段階でも初期化されてしまいます。
そうでなくても、一連のマクロ動作が完了した後でのモジュールレベル変数の「保持」は保障されていません。
あまり初心者向けの話ではないのですが、基本的に重要なことなのでここで説明することにします。
ワークブックを開いてから、継続的に何かの状態を変数に保持していたいということがありますが、この「初期化されてしまう」ということへの対処が必要になります。
実際にワークブックを閉じるまで保持させたい変数は、モジュールレベル等の変数ではなく、ワークシート上のセルに収容しておきます。Excelではセルも1つの変数となります。
ユーザーに見せたくない内容であれば、隠しシートを作っておいて、そこに保持するようにします。
但し、これには問題がないわけではありません。
セルの値を変更するわけですから、ブック自体が「非保存」になるという弊害が発生します。
実際にはシートの内容を書き換えていないのに上記の理由で閉じる段階で「○○の変更内容を保存しますか？」というメッセージが表示されてしまいます。
この状態はワークブックの「Savedプロパティ」で制御できるのですが、単に「保存済み(Saved = True)」にすれば良いわけではありません。
ここでの考え方はVBAで必要な制御情報をセルに収容する時、元々のワークブックの「Savedプロパティ」を変更しない、ということです。
手操作であれ、マクロであれ、セルに値を書き込めばそのワークブックは「非保存(Saved = False)」になるので、
そのまま閉じようとすると「○○の変更内容を保存しますか？」のメッセージが表示されるのがExcel本来の仕組みです。
'***************************************************************************************************
' 制御シートのA1セルに値を書き込む
'
' 作成者:井上治 URL:https://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 >>--------------------------------------