VBAで標準モジュールにPublic変数を宣言、またはPrivate変数を宣言してPublicプロシージャで値を書き換える処理を構築した場合、予期せず、変数に格納したはずの値がクリアされることがあります。

実際にACCESSで、汎用的に使えるようにと私もそのような方法で書いたのですが、上記のような事象が発生したので今回調べてみました。

答えはマイクロソフトのサポートページにありました。
どうやら標準モジュールでPublic宣言した変数であっても、意図しないタイミングで変数の値が破棄されることがあるようで、そのようなプログラミング手法は推奨されていないようです。

解決法としては、保存したい値をテーブルに格納するという方法が現実的かと思います。

ポイントは「元に戻す」です。

以下、マイクロソフトサポートページからの引用：

ある Office ドキュメントが VBA のマクロを含む場合、その中のモジュールに記述された Public 変数の値が有効である期間は、あるプロシージャの実行を開始し、そのプロシージャが “End Sub” で終了するまでの間のみです。

ほとんどの場合、プロシージャ終了後も値は保持されますが、意図しないタイミングで保持されていた Public 変数の値が破棄され、使えていた変数の値が突然使えなくなる場合があります。 そのため、Public 変数がアプリケーション終了時まで有効であることを期待する VBA マクロの実装は、推奨されません。

なお、モジュールの編集、プロジェクトの構造の変更、コンパイルエラーの発生、参照設定の変更、デザインモードへの切り替え、コントロールを削除して [元に戻す] を実行するなどのタイミングで変数が破棄される場合があります。 これは、そのタイミングでメモリ上に確保されている VBA の情報が破棄され、初期化を行ってから、その後の処理が実行されるためです。

補足 : この文書で説明する Public 変数とは、すべてのモジュール レベル変数および静的変数を指します。