ExcelのVBA。public変数の値が消える

VBAについて。Excelの2003や2007を使っています。標準モジュールで public 変数を定義しました。 ユーザーformを使い、パブリック変数に値を入れたり変更し、標準モジュールに戻ったとき、そのpabulic変数が resetされてしまっている事があります。全く同じマクロで、この現象が起こることと、起こらないことがあります。excelの2003でも2007でも、同じ現象が起こります。簡単なマクロで再現性のある具体的なものを示したいと思い、試しましたが、できませんでした。簡単なマクロでは、きちんとパブリック変数は保持されています。問題のあるマクロはかなり長いものなので、とても示せません。問題がどこにあるのかわかりません。このマクロで不都合があるという具体的なマクロを示すことができない状態での質問で、申し訳ありません。
また、このようなプログラミングをしていて思ったのですが、ユーザーフォームに対してユーザーが任意の引数を渡す方法あるいは、ユーザーフォームから引数を受け取る方法はあるのですか?今は、pubulic変数を用いたり、具体的なセルに値を代入したりしていますが、どう考えてもそれはエレガントではないし、汎用性もないと思います。引数で引き渡すのが一番綺麗でいいとは思うのですが、それが可能かどうかわかりません。

No.4 ベストアンサー 回答者： Wendy02 回答日時： 2011/01/20 09:06

IDは違うけれども、同じ内容の質問のようですね。

http://oshiete.goo.ne.jp/qa/6420530.html

具体的にこんなことをしています、ということをおっしゃれば、話は変わるかとも思います。
今のままでは、いくら説明しても、たぶん納得いかないだろうと思います。

最初に、#3の方のMSのサポートの文章で、
>[VBA] Public 宣言された変数の有効期間
この内容、ご質問者さんは、意味を誤解しているようです。

「ほとんどの場合、プロシージャ終了後も値は保持されますが、
　……Public 変数がアプリケーション終了時まで有効であること
　を期待する VBA マクロの実装は、推奨されません。」

どうして、保持した変数がなくなってしまうか、いろんな理由はあっても、完成したマクロでは、原因はひとつで、End Sub を通っていないままに終了しているからです。

　「ある Office ドキュメントが VBA のマクロを含む場合、…… Public 変数
　　の値が有効である期間は、あるプロシージャの実行を開始
　し、そのプロシージャが "End Sub" で終了するまでの間のみです。」

この文章は間違いです。その話のままだったら、Public 変数など意味がなくなってしまいます。値だって確保しているし、変数は動的です。しかし、実務上、構造化マクロで、きちんと作られたマクロの流れ（ルーチン）が通る間だけだと思ってよいです。

>ユーザーformを使い、パブリック変数に値を入れたり変更し、標準モジュールに戻ったとき、そのpabulic変数が resetされてしまっている事があります。

Public 変数が、リセットされるというのは、マイクロソフトの文章は間違いに近いです。これは、極論すると、不完全なマクロだということです。しかし、本当に完全なコードを提供出来るかというのは、それは誰も自信などありません。だから、そういう方法を避けるわけです。

Public 変数は、標準モジュールを経由して、ローカルのUserForm に供給を受けるけれども、それを戻すということはしないのです。

図式化するとこうなります。

・ローカルで発生した値　→標準モジュールのPublic 変数　→UserFormのローカルのプロシージャ
・標準モジュールのPublic 変数　→　UserFormのローカルのプロシージャ

※ただし、起動時の一回きり、それ以上の持ち回しはしない。逆もしない。

「ユーザーフォームに対してユーザーが任意の引数を渡す方法
　ユーザーフォームから引数を受け取る方法はあるのですか?」
出来ないわけではありませんが、これらは、変数で渡すということはしないということです。

>ただ、ユーザーフォーム　aUS　全体で参照し値を変更できる　変数a　のようなものを、使えたらいいなあ、と思い、

理屈では、一旦、標準モジュールのPublic 変数に送っておいて、そこから他のプロシージャ等に送ればと思いますでしょうが、それはしないということです。ルーチンが別だからです。

私の基本的な設計の考え方を示しておくと、UserForm自身とか、その中にあるものは、OLEオブジェクトのインスタンスです。つまり、モノ(オブジェクト）がある限りは、値は確保しているのです。だから、UserFormが残っている以上は、変数ではなくて、モノから、値を取得するのです。そのためには、UserForm はModalモードを、Offにしておくのが良いわけです。

例えば、UserForm1.TextBox1.Value とかで取れるわけです。

これは、システム設計の話で、それを見れば分かっていただけます。非表示のワークシートのセルに書くような話は、それは状況（渡す変数の数）によります。ただ、わざわざワークシートのセルに代入する必要はないということです。そのような方法はシステム設計では全体にリスクを高くするので賛成出来ないです。

私の失敗の経験から、Public 変数については、ひとつのルーチン以上に用いないことです。また、動的には用いないことです。一回きりの静的な変数として使うべきです。静的といっても、Static 変数の意味ではなく、文字通り変数を変化させないということです。ただし、時間の間隔やカウントなどの単純なものは別です。

Public 変数が空かどうかチェックする方法も考えられますが、意味がありませんから、それなら、参照渡しにすればよいのです。

この回答への補足

詳しい説明をありがとうございました。とっても役に立ちました。

＞私の失敗の経験から、Public 変数については、ひとつのルーチン以上に用いないことです。また、動的には用いないことです。一回きりの静的な変数として使うべきです。静的といっても、Static 変数の意味ではなく、文字通り変数を変化させないということです。ただし、時間の間隔やカウントなどの単純なものは別です。

これからは、これを心がけたいと思います。

＞理屈では、一旦、標準モジュールのPublic 変数に送っておいて、そこから他のプロシージャ等に送ればと思いますでしょうが、それはしないということです。ルーチンが別だからです。

この部分もとっても参考になります。

こちらも見ました。
http://oshiete.goo.ne.jp/qa/6420530.html
endの部分の違い(コメントかそうでないか)により結果の違いはとても勉強にないました。

ありがとうございました。

補足日時：2011/01/24 20:49

No.5 回答者： yorozu_ya 回答日時： 2011/01/22 00:32

たぶん、質問者さんには理解して頂けたと思うのですが、

念のため解説しときます。

Office の VBA はドキュメントに付随しています。
Excel の場合ですと、ワークシートにプログラムが付随しています。
このワークシートというのは、手動でもプログラムからでも追加／削除ができます。
つまり、プログラム自体が変更される可能性があるということです。
プログラムが変更されたらリセットして再コンパイルする必要がありますが、
プログラム実行中にそんなことされたら、堪りませんよね。
それで、プロシージャ実行中は保証します、という仕様になっています。

プロシージャ実行中にプログラムが変更されたと判断されたら、
終了後にリセットする、ということです。

この回答への補足

度々ありがとうございます。
＞Excel の場合ですと、ワークシートにプログラムが付随しています。
確かにいわれてみれば当たり前ですが、今まであまり、今回のような件で意識したことがありませんでした。こういう視点が大切だと気付かされました。
ありがとうございます。

補足日時：2011/01/24 20:54

No.3 回答者： yorozu_ya 回答日時： 2011/01/19 21:26

> > そのpabulic変数が resetされてしまっている事があります。

> Excel の仕様です。どうしようもありません。
>
> > 具体的なセルに値を代入したりしていますが
> それが確実な方法です。

情報源です。

http://support.microsoft.com/kb/408871/ja

この回答への補足

情報源見ました。

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

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

本当にビックリしました。public変数のスコープがプロシージャのend　sub　までだけだなんて。それならばとても「public」とは言えませんよね。またそれに対して、の対処法が

「Excel の場合 非表示にしたワークシートに値を記述します。」

というのも拍子抜けです。それはすでにやっています。

本当にありがとうございました。自分の技術や知識のなさのために起こっていたと思っていた現象が、excelの仕様だなんて。しょうがないので、これから「非表示にしたワークシートに値を記述」する形に変えます。
せめて早く、public変数の仕様を変え、少なくとも「Public 変数がアプリケーション終了時まで有効」にして欲しいですね。

補足日時：2011/01/19 21:51

この回答へのお礼

ありがとうございました。本当に助かりました。

お礼日時：2011/01/24 20:58

No.2 回答者： imogasi 回答日時： 2011/01/19 10:05

このようにコードも掲示できない（しない）場合に、このコーナーに質問しても無駄だと思いませんか。

読者は質問者のパソコンのコードも実行したり、多少変えて実行したりも出きずに、エラー原因の究明などできるわけが無い。
よほど典型的なエラーコードが出ている場合をのぞいて、このコーナーはデバッグの質問には適さない。
それなのに、エラーの原因の質問が出る。自分で究明するほか無いでしょう。この面では他人を当てにしないこと。
１つの提案はＭｓｇｂｏｘ　（Ｐｕｂｌｉｃ変数名）を要所要所に入れて実行してみて、何処で予想外に変化するか、見極めることは出来るのでは無いですか？
こんなこともやってみましたか。
パブリック変数にもスコープ的なな考えは必要と思うので
http://pc.nikkeibp.co.jp/pc21/special/2007_gosa/ …
などをよく読んで考えてみてください。
＞ユーザーフォームに対してユーザーが任意の引数を渡す方法あるいは、ユーザーフォーム・・
は、こんな抽象的な書き方でなく、具体的にどういう場合に、どういうことをしたいのか書いて質問すべきだ。
http://oshiete.goo.ne.jp/qa/5160575.html
の質問などあるが、ユーザーフォームに引数を渡す場合と言えるのかどうか？
ーー
私は不勉強で、回答の＞pabulic変数が resetされてしまっている事があります。ーー＞Excel の仕様です。の具体例は想像できません。
ーー
＞pabulicはＰｕｂｌｉｃのミスタイプです。プログラマーとしては、コード内に、こういう誤りは無いですか。

この回答への補足

以下のマクロでも、時にはpublic変数がresetされてしまいます。しかし、resetされないこともあります。　一度resetされると、ずーっとresetとされます。また、立ち上げ直したりして、resetされないと、これもまたずーっとresetされずに正常に動きます。どういうときにうまく動き、どういうときにうまく動かないのか、分かりません。resetされるタイミングはtest1というフォームから戻ったときです。（ミスタイプは本当に申し訳ありません。publicと打ったりパブリックと打ったりして、急いで打ったためゴチャゴチャになってしまいました。）



標準モジュールにーーーーーーーーーーーーーーーーー
Option Explicit
Public a As String
Sub ini()
MsgBox "初期化します"
a = "5"
End Sub

Sub testMain()
If a = "" Then ini
MsgBox a
test1.Show
End Sub

test1　というフォームのモジュールにーーーーーーーーーーーーーー
(このフォームに　コマンドボタンがあります。)
Private Sub CommandButton1_Click()
a = "10"
Unload Me
End Sub


シートのモジュールにーーーーーーーーーーーーーーーーー
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
testMain
End Sub

補足日時：2011/01/19 21:15

No.1 回答者： yorozu_ya 回答日時： 2011/01/19 00:06

> そのpabulic変数が resetされてしまっている事があります。

Excel の仕様です。どうしようもありません。

> 具体的なセルに値を代入したりしていますが
それが確実な方法です。

> 引数で引き渡すのが一番綺麗でいいとは思うのですが
ケースバイケースです。

> ユーザーが引数を渡す
意味が分かりかねます。

この回答への補足

ありがとうございます。excelの仕様なのですか。ショックです。その場合どうしようもできないのですね。
public変数の意味がないような気がします。ただ、回答の2の方によると、そうでもないのようにも感じます。ますます分からなくなってしまいました。

後半の部分の質問が、下手な説明で申し訳ありません。
例えば、ユーザーフォーム　aUS　の中で、変数　a　を使うとします。しかしその同じ変数を　sub　bSB　でも　function　cFC　でも使うとします。その場合、sub　や　function　では　bSB(a)　や　cFC(a) とか　a=　cFC　などの形で　引数の引き渡しができます。しかし、ユーザーフォームでは、textboxとかcheckboxとか具体的なコントロールが配置され、例えばそれぞれがクリックされたときの処理とかをVBAで書くことができます。その時に例えば標準モジュールでpublic変数のようにあちこちで参照できるようなことができないかと考えたのです。感覚的にいえば、ユーザーフォーム　aUS(a)　とaを引数とするような感覚です。もちろん、aUS(a)とはできませんよね。ただ、ユーザーフォーム　aUS　全体で参照し値を変更できる　変数a　のようなものを、使えたらいいなあ、と思い、そしてそのaを標準モジュールのsubやfunctionの間でやりとりができないのかなあ、と考えたのです。つまりユーザーフォームaUSに対し　引数a　を引き渡す感じのことができないか、と考えたのです。そして、このようなことが私の知識ではできなそうなので、public変数を使いました。しかし、それがうまくいかなかった。また、public変数の数は少ない方が見通しのいい、ミスの少ないプログラムになると感じたので。また、変数はどこでどう使うかを限定した方がいいと思うので。どうなのでしょうか。

補足日時：2011/01/19 21:20