HOME > 即効テクニック集 > Excel VBA > その他関連のテクニック > モジュールレベル変数の値が消えるとき
その他関連のテクニック

モジュールレベル変数の値が消えるとき

(Excel 97/2000/2002/2003/2007)
次のマクロをご覧ください。

Private Target As String

Sub Sample1()
  Dim i As Long
  For i = 1 To 100
    If Cells(i, 1) = 99 Then
      Target = Cells(i, 1).Address
    End If
  Next I
End Sub   

Sub Sample2()
  If Target <> "" Then
    MsgBox "見つかったセルは" & Target & "です"
    Range(Target).Select
  End If
End Sub   
プロシージャSample1は、セル範囲A1:A100を調べて「99」が入力されているセルを見つけ出し、そのアドレスをモジュールレベル変数Targetに格納しています。 もうひとつのプロシージャSample2は、モジュールレベル変数Targetに格納されているアドレスをユーザーに示し、そのセルを選択しています。 Sample1とSample2が連続して実行されない場合。つまり、Sample1を実行して、その後なにか別の作業をして、どこかのタイミングであらためてSample2を実行する…そんなケースを想定しています。 Sample1で見つけたアドレスを、いつかSample2が実行されるときまで覚えておきたいわけです。 上で示したように、連続して実行されないプロシージャ間で使用するデータ(値)を、モジュールレベル変数に記憶(保存)しているマクロをときどき見かけます。 しかし、これは危険な行為です。次のようなシンプルな例で解説しましょう。
Private N As Long

Sub Sample3()
  N = N + 1
  MsgBox N
End Sub   
Sample3は、モジュールレベル変数Nの値を1増加させています。Sample3を実行すると、最初は「1」が表示され、続けて実行すると「2」→「3」と増加していきます。 これは、Sample3が終了しても、変数Nの値が保持されていることを表しています。 では、何度かSample3を実行したあとで、新しいモジュールレベル変数を追加してみましょう。
Private N As Long
Private buf As String    ''どんな変数でもいい

Sub Sample3()
  N = N + 1
  MsgBox N
End Sub   
(1)Sample3を実行する(1回目)→結果:1 (2)Sample3を実行する(2回目)→結果:2 (3)Sample3を実行する(3回目)→結果:3 (4)新しいモジュールレベル変数を追加する (5)Sample3を実行する(4回目)→結果:1 変数を追加した後でSample3を実行すると、変数Nの値が消えているのがわかります。 あるいは、何度かSample3を実行したあとで新しいプロシージャを作成しても同じです。 (1)Sample3を実行する(1回目)→結果:1 (2)Sample3を実行する(2回目)→結果:2 (3)Sample3を実行する(3回目)→結果:3 (4)新しいプロシージャSub Sample4を作成する (5)Sample3を実行する(4回目)→結果:1 モジュールレベル変数は、宣言したモジュール内のすべてのプロシージャで使用できる変数です。 このように、ある変数がどこで使用できるかという範囲を変数の"適用範囲"と呼びます。 一方、変数に値が保持されている期間のことを、変数の"有効期間"と呼びます。 プロシージャの内部で宣言するプロシージャレベル変数の有効期間は、そのプロシージャが終了するまでです。 変数の"適用範囲"と"有効期間"は、まったく別の概念です。 モジュールレベル変数は、宣言したモジュール内のすべてのプロシージャで使用できるという"適用範囲"を持っていますが、どんなときでも値が保持され続けるという"有効期間"は持っていません。 上で試したように、モジュールレベル変数は、値がクリアされることもあるのです。 モジュールレベル変数の値がクリアされるタイミングを明確に知るには、VBEの[オプション]ダイアログボックス[全般]タブの[状態の破棄を通知]オプションをオンにします。 このオプションがオンになっていると、上で試したように、別のモジュールレベル変数を追加/削除したり、プロシージャを追加/削除するタイミングで 「このアクションを実行するとプロジェクトがリセットされます。実行しますか?」 という確認メッセージが表示されます。 ただし、確認メッセージが表示されずに変数の値がクリアされることもあります。 たとえば、モジュールレベル変数に何かの値が保持されている状態で、既存の標準モジュールやUserFormを解放(削除)すると、確認メッセージは表示されずにモジュールレベル変数の値がクリアされます。 「モジュールレベル変数が、何かのタイミングでクリアされる」ということは、ヘルプやMicrosoftの公式ドキュメントにも書かれています。 しかし、どんな操作でクリアされるのかは明確にされていません。 昔のExcelでは、新しいワークシートを挿入しただけでも、モジュールレベル変数がクリアされました。 おそらく、ワークシートというDocumentモジュールが追加されたためプロジェクトがリセットされて、変数の値もクリアされたのでしょう。 最近のExcelでは、ワークシートを挿入しただけでモジュールレベル変数の値がクリアされることはなくなりました。 しかし、それは経験則で知っているだけで、Microsoftから公式なアナウンスがあったわけではありません。 つまり、いつ、どんな理由で、仕様が変わるか予測はできないということです。 モジュールレベル変数に値を保持するというマクロは、意外と多いです。 そうしたマクロを作成するときは、クリアされる危険性も考慮に入れてください。