excel/vba/public変数

excel VBAで下記のようなコードを書きました。
他のプロシージャでも共通の変数を使用したいと思っています。
その為、public変数を宣言して使用したいと設定しましたが。
うまくいきません。
testmainを実行し、iに何も入っていないことを確認しました。
その後『1』を代入し、testmainは終了します。
testsubをその後実行し、iに先ほど代入された値を確認しようとしても何も代入されていない状態です。
全プロシージャでその変数を使用したいと思っているのですが、
何か方法、もしくはこのような使用方法ではないのでしょうか？
public変数を使用してもプロシージャが終了すれば変数はクリアされるのでしょうか？
構造化の方法の問題上参照渡しや、戻り値など、指定する方が複雑になっていきます。
ご指導よろしくおねがいします。

Public i As Variant

Sub testmain()
MsgBox test
i = 1
End Sub

Sub testsub()

MsgBox i

End Sub

No.6 ベストアンサー 回答者： Wendy02 回答日時： 2011/01/02 23:58

質問者さんの話から少し離れて、専門的になってしまうのですが、

#5さんの引用先の
Microsoft サポートの中「 Public 宣言された変数の有効期間」の

「モジュールの編集、プロジェクトの構造の変更、コンパイルエラーの発生、参照設定の変更、デザインモードへの切り替え、コントロールを削除して [元に戻す] を実行するなどのタイミングで変数が破棄される場合があります。」

これ話自体はごもっともというか、実務的にはあまり意味がありません。一般的に問題になるのは、コンパイルエラーじゃなくて、たぶん、Runtime Error やEndメソッドです。
Rumtime Error は、On Error Goto ErrHandler 等のエラートラップで回避できますが、しかし、End メソッドは使えません。

要するに、Sub ---> End Sub や Function ---> End Function という括りを終えないと、モジュールレベル変数やPublic 変数が飛んでしまうわけです。だから、End メソッドが使えません。

以下は、二回以上繰り返します。End をコメント・ブロックしたりしなかったりして、違いを比べてみるとよいです。

Dim i As Variant
Sub Main2R()
　If i = "" Then
　　SubTest2
　End If
　MsgBox i
　 End '←End メソッドがあると、変数が確保できません。
End Sub
Sub SubTest2()
　 i = 1
　 MsgBox "Let " & i
End Sub

変数というものは、代入された値であって、その都度変化するものですから、ひとつのルーチンの中で確保されればよいはずです。それ以上のスパンを考えないほうが良いのかもしれません。参照渡してもよいのですが、例えば、プロシージャからUserForm に渡す場合などは、Public変数のほうが楽なのです。

定数なら、Public Const にすればよいのであって、わざわざ、変数に置く必要はないです。変化するもので固定的に置きたいなら、格好は悪いですが、私は、CustomPropertiesに置いたり、iniファイルに置いたり、秘匿性のものなら、レジストリに置いたりします。まだ、一般の人には分からない場所もあります。人によっては、PERSOANAL.XLS(b)の非表示のシートに置いているようですが、PERSONAL.XLS(b)は、脆弱性があるのでお勧めしません。MSでも、アドインの中のシートひとつを書き込みはしないものの値を置いているものもあるようです。（書き込みはしないはずです。書き込みをすると、デジタル署名が壊れるはずです）なお、私は、シートや非表示シートを使うことは滅多にしません。あくまでも、VBAはVBAの領域の中で処理しようとします。Excelは、他のOfficeと違って、標準モジュールを使い、そのモジュールとそのプロシージャは、分離して使うことがあるから、グローバル変数を多用するのだと思います。

No.5 回答者： bonaron 回答日時： 2011/01/02 15:05

これは、かなり有名な問題だったと思います。

[VBA] Public 宣言された変数の有効期間
http://support.microsoft.com/kb/408871/ja

Access では、普通に対策しておけば、あまりお目にかかりませんが
Excel では頻繁に遭遇してしまいます。

対策としては、ブックを開いている間有効にしたい変数は
ワーク用の非表示シートを作成し、そこに書き込むようにしています。

No.4 回答者： kmetu 回答日時： 2011/01/02 14:51

ちなみにpublic変数 を利用せずに値をどこかのシートの目立たないセルに書き込んでおいて利用すると言う手もあります。

No.3 回答者： kmetu 回答日時： 2011/01/02 14:12

testmain()を実行した後に該当ファイルを保存したりすると変数の値はリフレッシュされますがいかがでしょうか

ところで
MsgBox test
は何を表示しようとしたのでしょうか？

No.2 回答者： Wendy02 回答日時： 2011/01/02 14:09

>public変数を使用してもプロシージャが終了すれば変数はクリアされるのでしょうか？

>構造化の方法の問題上参照渡しや、戻り値など、指定する方が複雑になっていきます。

Public ステートメントは基礎レベルですから、ここでつまずいてしまうと、その先の組み立ては難しいかと思います。

サンプルコードとしては、VBAの試験では以下のような内容が出てきます。
標準モジュールで、Public ステートメントは、主に明示的に入れるわけです。

Dim i As Variant
Sub Main1()
　MsgBox i　'(1)
　SubTest1
　MsgBox i　'(2)
End Sub
Sub SubTest1()
　i = 1
End Sub

'では、上のコードを具体的に使えるようなコードにする場合は、

'すでに変数を宣言されているとします。
Sub Main2()
　If i = "" Then
　　SubTest2
　End If
　MsgBox i
End Sub
Sub SubTest2()
　 i = 1
End Sub

>public変数を使用してもプロシージャが終了すれば変数はクリアされるのでしょうか？
そういうことはないけれども、エラーなどが発生したりすると、モジュールレベルやグローバルの変数等は抜けることがあるので、実際のコードでは、上記のMain2()のように常に抜けを確認しなくては使い物にはなりません。

意外に厄介なコードになります。これを確実にするなら、Class を利用することになりますが、VBA等では、読みにくくなるかもしれません。

参照渡しのほうが確実ですが、それは、内容にもよります。

No.1 回答者： okormazd 回答日時： 2011/01/02 13:08

testsubのmsgboxで「1」と表示されます。

testmainで、i=5とすれば、
testsubのmsgboxで「5」と表示されます。
これでいいのではないですか。
もし、うまくいかないとすれば、
このコードのせいではなさそうです。