再度,ExcelVBA,public変数が消える

質問

大変申し訳ありません。一度、この件で質問し、その回答を締め切ったばかりなのですが、やはりもう少し知りたくて質問させていただきます。
ある方の回答への補足で、以下のマクロを具体例としてあげました。ただし、以下のマクロは、時にはpublic変数が消えてしまいますが、しかし、消えないこともあります。

消える理由として、
回答していただいたものから考えて、
１．End Sub を通っていないままに終了しているから
２．不完全なマクロ
３．きちんと作られたマクロの流れ（ルーチン）ではない流れがある
４．「Public 変数は、標準モジュールを経由して、ローカルのUserForm に供給、しかし、それを戻すということはしない。つまり、a.ローカルで発生した値　→標準モジュールのPublic 変数　→UserFormのローカルのプロシージャ　b.標準モジュールのPublic 変数　→　UserFormのローカルのプロシージャ
　　※ただし、起動時の一回きり、それ以上の持ち回しはしない。逆もしない。」この件に関して、以下のマクロに問題がある。

この様なことを考えました。

４．の場合、testMainからtest1.showを呼び出し、a=10とするが、このaの値は、testMainには戻らない(戻らないことがある)のでしょうか、あるいは、testMainのend　sub　の後は値が保障されないのでしょうか。

あるいは、 Worksheet_SelectionChange　でend　sub　で終わっているので、それ以降は値が保持されないのでしょうか。

あるいは、どこかに欠陥のあるマクロなのでしょうか。

http://okwave.jp/qa/q6420530.html
への回答のno.6の例のマクロでendをコメントにするかしないかで、endをコメントにした場合はend　subで終わった後、値が保持されています。このこととも合わせて考えると、どこに問題があるのか、どのような問題があるのか、よく分からなくなってしまいます。

よろしくお願いします。

標準モジュールにーーーーーーーーーーーーーーーーー
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

回答No.3

Wendy02 · ベストアンサー

特に、サンプルコードだけを調べてみましたが、最初は良かったのですが、ちょっと変える（一定の値ではなく値を変化させる）と、上手くできません。前回書いたように、UserFormから、Public変数に戻すというのは、感覚的にダメだと思っていたのですが、やっぱり上手くないようです。しかし、問題点は、こちらの思惑とはは違いました。（正直なところ、私はこういう現象は知りませんでした。）

この前の話のように、ひとつの流れ（ワン・ルーチン）までですから、サンプルコードで説明すると、

0：Sub ini()　は、Auto_Openと同等で、最初の0の部分ですね。

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

1：Sub testMain()
If a = "" Then ini
MsgBox a
test1.Show
End Sub
'（おそらく、ここでは抜けは発生しない）
-----ここまでが、ワン・ルーチンの終わり------

------別ルーチンの始まり-----
Private Sub CommandButton1_Click()
a = "10"　'←前回、Public 変数a に送らないほうが良いというつもりだったけれど……
Unload Me
End Sub
------------------------------------
という説明でした。これは、証拠がなく感覚的なものでした。現実は、そういうことではなかったようです。やっぱりコードは書いて動かしてみるまでは分からないです。Unload Meで消えて（飛んで）しまうようです。モジュール全体の構造を変えてしまうとは思っていませんが、ただ、上手くないようです。その比較として以下のようにして試してみてください。

修正案：（褒められないコードだけれども……）
Test1(UserForm)
CommandButtonを二つ設けて、

'こちらは、OK
Private Sub CommandButton1_Click()
　a = "10"
　Me.Hide
End Sub

'こちらは、NG
Private Sub CommandButton2_Click()
　 a = "10"
　Unload Me
End Sub

だから、私は、これで良いというには、今ひとつ、不安が残るというか、これで終わらないような気がしているのですが、そちらでも、みていただけませんでしょうか。

補足

本当にいつも丁寧で詳しい説明、ありがとうございます。
＞（正直なところ、私はこういう現象は知りませんでした。）
このことばにビックリしました。Wendy02さんは、経験も知識も技能もとても豊富な方だと感じていますので、また実に的確に、いろいろな疑問に答えていますので、そのような方も知らない現象があったんだということに、また、そのような現象を私のようなものがたまたま出会って気付いてしまったということに、本当に驚いています。
＞a = "10"　'←前回、Public 変数a に送らないほうが良いというつもりだったけれど……
その通りだと今は理解しています。変数aに送らない方がいいのだろうなあと、Wendy02さんからの回答のあとは思っています。ただ、以前の質問の
http://oshiete.goo.ne.jp/qa/6458144.html
の時にWendy02さんからの回答以前に一度示したコードがあったので、それをもとに再質問したのです。
＞Me.Hide
とするのは分かるのですが、ただ、きちんと終わっていない気がして、もやもやとした感じがあります。正直言って、よく分からない感じが残っています。
一体、public変数は、どういうときに消え、どういうときに保持されるのか。

回答No.8

Wendy02

>もともとの問題の解決には至っていません。対症療法的に、こうすれば何とかなる、ということではなく、はっきりとした原因を知りたいと思います。

それは、趣味でVBAをするならともかく、プロの人はしない発想です。単刀直入に言うと、私としては、たかが、VBAでも、もし、開発を続けていくなら、仮に身内だけで使うものでも、ある程度の完成時期を想定して開発するわけですから、原因を求める発想をしていたら、半永久的にプログラム・システムは完成しません。そういう考え方はやめたほうがよいです。

私も他の人も、VBAプログラミングする場合は、ほぼ同じ条件の中で、対処しています。ご質問者さんが特別でも、私が鈍感でもないと思います。具体的な問題は知らなくても、私は、体験的にしてはいけないことは身についています。そして、自分たちの技術の範囲内の中で対処するだけです。こういうのは、やはり先輩の人から教わったり、自分の手痛い失敗の中で覚えることです。

>ただ、こんな事をしなければpublic変数が維持できないというのは、本当に「public」とは言えないのではないか、と感じてしまいます。MS社に仕様に文句を言いたくなります。

余計なイメージを先行させないことですね。私が、変数の値をセルに書かないのは、それだけの技術的な確信があってしているだけです。こういう手法の選択は、別に恣意的なものではありません。技術的な裏打ちからしているものです。そういう今までの回答自体を全面的に否定をされてしまいますと、これ以上、何を教えても意味がありませんね。

ある程度の大きさのシステムを開発する限りは、100%のエラーフリーは無理なのです。どんなに完璧でも、100%には至らないのです。時には、ユーザーの誤使用もあります。それを、どうエラー処理をするかということを覚えなくてはならないわけです。

端的に言えば、VBAというものは、キャッシュに中間言語を置いているので、その中間言語の保管の一部が壊れるというわけですが、それを知っても、現実は、何も変わらないように思います。

私の場合、安全策のコードを整えていくだけです。アプリケーションのハングやシステムのフリーズに至らない方法を避けて、後は、ユーザー負担にならない程度にリトライしてもらう方法を取ることを考えます。

ここのサイトの一部の人の、VBAのプログラミングでは、エラー自体を考えなかったり、エラーの発生そのものを無視するひとたちもいます。エラーの起こりやすい所を指摘したり、エラー処理の方法を書いただけで、意味のないクレームをつけたり中傷行為をする質問者や回答者がいます。それは、もう、私などがやっているVBAとは、まったく次元の違うところにいる人たちです。

UserForm をUnloadしたら、変数が壊れるなら、むやみにUnload をさせない方法取ります。出来る限り、既存の言語をお使い続けるつもりなら、考え方の軌道修正をしたほうが良いですね。

お礼

いろいろ有り難うございました。
いろいろ試みたのですが、すでに書いたコード(かなりの量)にVarPreserveのようなコードをすべて加えるのが大変だったため、一部ではunload　me　をme.hideに置換し、それで、起こる不具合(userformがイニシャライズされない）をuserform_activateに書き換えたりすることで、今回の問題の解決を図っています。また、一部ではVarPreserveのような形でやっています。ありがとうございました。

補足

いろいろ読んで、ああ、そうなんだなあ、と思っています。VBAは道具ですから、その道具に文句を言うよりも、その道具をいかに使いこなすかを考えるべき何だなあ、と思いました。うまく使いこなすためには、何度も失敗したり、いろいろな人にいろいろなアドバイスをもらいながら、少しずつ、使い方もうまくなっていくのかなあと思っています。ついつい失敗すると、道具を責めたくなったりしますが、それは、自分が道具を使いこなしていないからこそなんだなあ、と今、思っています。うまく使いこなすためには、さらなる工夫が必要ですね。これからもいろいろと試してみたいと思います。

＞そういう今までの回答自体を全面的に否定
全くそういう気はありません。もしも、気分を害したことがあれば、申し訳ありません。今までも、いろいろなアドバイスはとてもためになっています。本当にいろいろな事をいろいろな人から教えてもらっていると感じています。とってもありがたく思っています。

＞安全策のコードを整えていくだけ
確かにそうですね。

回答No.7

yorozu_ya

>ただ、もともとの問題の解決には至っていません。対症療法的に、こう
>すれば何とかなる、ということではなく、はっきりとした原因を知りたい
>と思います。また、そのためには、どういうときにpublic変数が消えど
>ういうときに消えないかを、いろいろな方々の例を挙げてもらいたいと
>思います。私自身、どういうときに消え、どういうときに維持されるの
>か分からないのです。まず、いつでも絶対にpublic変数が消える条件
>を探し出さないことには、それから先の原因の究明にもつながらない
>ように感じています。是非いろいろな方々の知恵と知識と技術での協
>力をお願いしたいと思います。

無駄な努力です。
どういうときに消えるか・・・これは判るかもしれません。
どういうときに消えないか・・・これはどうやって証明するつもりですか？
「有る」ことの証明は可能ですが、「無い」ことの証明は不可能です。
「これこれの条件でテストした結果では、無かった」という証明しかできません。

補足

＞「これこれの条件でテストした結果では、無かった」という証明しかできません。

読んでみて確かにそう思いました。その通りですね。

回答No.6

Wendy02

>＞Me.Hide
>とするのは分かるのですが、……ただ、きちんと終わっていない気がして、もやもやとした感じがあります。

この感覚は良く理解できます。私と同じ感覚です。実際、私は、UserFormをHideする代わりに、Win32APIでUserFormを最小化をさせておくことが多いです。別に、変数を確保するという目的ことではありませんが、シートへのアクセスを含む、ModalMode をFalse にしているわけです。（最小化の方法は、希望があれば掲示します。

なお、私は、UserFormで確保したPublic変数を、その為に、シートに設けるとか、別途セルに置けるというのは、いろんな状況を考えると、単独のブックとして保護していくならともかく、そうでない場合は、一旦セルに置くようなコードは、絶対というぐらいに書きません。
データ自体をシートに書くことはしますが、ルーチンの変数を一旦シートに置くということはしません。

ご質問者さんのコードの修正案を考えてみました。

'//標準モジュール
Option Explicit
Public a As Variant
Sub InitialPro()
MsgBox "初期化します"
a = 5
End Sub

Sub testMain()
If a = "" Then InitialPro
MsgBox a
Test1.Show
End Sub

Sub VarPreserve(ByVal arg)
　a = arg
End Sub
'//シートモジュール
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
testMain
End Sub
'//UserForm(Test1)モジュール
Private Sub CommandButton1_Click()
　a = a + 1
　VarPreserve (a) '←一旦、変数を標準モジュールに通す。
　Unload Me
End Sub

'//

なお、みなさんが良く引用する、Microsoft Support の「[VBA] Public 宣言された変数の有効期間」の文章は、一見、当たり障りのない処だとは思うのですが、サポートとして、一体、何のために書かれたものか私には疑問に感じます。英文にはないようです。一般論としてサポートの文章は話半分で、素人の方でない限りは、100%真に受けないほうがよいのではないかなって思います。今回のマ社の引用文は、実際のプログラマにとっても、ご質問者さんが今かかえている問題を解決する内容には至らないと思います。

補足

>データ自体をシートに書くことはしますが、ルーチンの変数を一旦シートに置くということはしません。

全くそのの通りだと思います。そもそもexcelのシートがない状態でプログラムを組む場合、シートにルーチンの変数を書くという発想すらないわけです。シートに書くべきはユーザーの欲するデータであって、それ以外のものは書くべきではないと感じています。ただ、私はいろいろな技術もないので、あるいは、ExcelのVBAの仕様が自分の思ったものを実現するのには不足で、あるいは、私のただの知識不足かも知れませんが、しょうがなく、嫌だけれども、シートに書いているのです。書かずに済むのであれば書きたくないと思っています。書かずに済む方法として例えば、public変数や引数(パラメータ)があると思います。クラスや構造体も使えるでしょう。先日教えていただいたCustomDocumentPropertiesもこれからは使ってみたいと思っています。Microsoft Support の「[VBA] Public 宣言された変数の有効期間」の文章の中にpublic変数が消えて困るようならシートに書いておく、ようなことがありましたが、正直言ってとってもがっかりしました。

＞VarPreserve (a) '←一旦、変数を標準モジュールに通す。
こんな事は思いもつかなかったので、目からウロコの感じです。こうすればいいんですね。実際的にはこの方法を使ってみようと思っています。ただ、こんな事をしなければpublic変数が維持できないというのは、本当に「public」とは言えないのではないか、と感じてしまいます。MS社に仕様に文句を言いたくなります。

＞UserFormをHideする代わりに、Win32APIでUserFormを最小化をさせておくこと
この方法も私は知らないので、教えていただければあり難いです。

ただ、もともとの問題の解決には至っていません。対症療法的に、こうすれば何とかなる、ということではなく、はっきりとした原因を知りたいと思います。また、そのためには、どういうときにpublic変数が消えどういうときに消えないかを、いろいろな方々の例を挙げてもらいたいと思います。私自身、どういうときに消え、どういうときに維持されるのか分からないのです。まず、いつでも絶対にpublic変数が消える条件を探し出さないことには、それから先の原因の究明にもつながらないように感じています。是非いろいろな方々の知恵と知識と技術での協力をお願いしたいと思います。

回答No.5

korin_

> への回答のno.6の例のマクロでendをコメントにするかしないかで、endをコメントにした場合はend　subで終わった後、値が保持されています。

Endステートメントは、アプリケーションの終了とPublic変数も含めた変数の破棄を行います。
ですから、この例に関してはEnd Subを通っていないからどうこうよりも、Endステートメントを通った時点で破棄されています。

また、End Sub を通ったからといって、Public変数が必ず破棄されるわけではありません。
むしろほとんどの場合は、破棄されないはずです。下記で示されている通りです。
[VBA] Public 宣言された変数の有効期間
http://support.microsoft.com/kb/408871/ja
『ほとんどの場合、プロシージャ終了後も値は保持されますが、意図しないタイミングで保持されていた Public 変数の値が破棄され、使えていた変数の値が突然使えなくなる場合があります』
こういう場合に必ず破棄されます、というのはMicrosoftでも答えられないでしょう。

そして必ず破棄されるという例で挙げられている項目は、
『モジュールの編集、プロジェクトの構造の変更、コンパイルエラーの発生、参照設定の変更、デザインモードへの切り替え、コントロールを削除して [元に戻す] を実行するなどのタイミング』
上記に当たります。
これらを行った場合は確実に破棄されているはずです。

ちなみに今回提示されたコードを私の環境で実行した場合、特に問題なく値は保持されていました。
ですが、VBAでPublic変数は保障されていないという事ですので、破棄されたら困る値なのであればシート等に値を保持しておくべきだと思います。

補足

ありがとうございます。
＞こういう場合に必ず破棄されます、というのはMicrosoftでも答えられないでしょう。
一番知りたい部分なんですが、、答えてもらえないんでしょうか。残念です。
＞そして必ず破棄されるという例で挙げられている項目は、
＞『モジュールの編集、プロジェクトの構造の変更、コンパイルエラーの発生、参照設定の変更、
＞デザインモードへの切り替え、コントロールを削除して [元に戻す] を実行するなどのタイミング』
＞上記に当たります。
今回の示したコードは『モジュールの編集、プロジェクトの構造の変更、コンパイルエラーの発生、参照設定の変更、デザインモードへの切り替え、コントロールを削除して [元に戻す] を実行する』のどれにも当てはまらないと思っています。すると残りは「『など』のタイミング」の『など』に該当すると考えられます。でもそれが具体的にどのようなものかが分からないと対処のしようがなくて困ってしまいます。マイクロソフトのバグなのでしょうか。あるいはマイクロソフトの不親切なのでしょうか。
このコードの場合、うまく動くことも動かないこともあります。こうしたら確実に動かない、というのが分かればそれを示したいのですが、それもできずに、困っています。
（今現在、とりあえずは、シートにデータを保存する形にしています。気は乗らずに、嫌なのですが。）

回答No.4

ki-aaa

こんばんわ

お示しのコードをそのまま実験してみました。

最初は、値は保持されませんでした。

しかし、このブックを閉じて開いてやると値は保持されました。

何度かUserFormを作り直しても、一度ブックを閉じないと
値は保持されませんでした。

こんな単純なことじゃないと思いますが、何かの参考になれば。

補足

ありがとうございます。
同じマクロならば、それ以前がどういう状況であるかにかかわらず、同じに動くことを期待します。そうでなければ、マクロの意味がないと思います。もし同じに動かないならば、「それ以前にどういう状況が必要か」をはっきりと明示できなければいけないと感じます。ブックを閉じるか閉じないかが1つのポイントになるのかも知れませんが、何かもやもやが残った感じです。

回答No.2

redfox63

お示しのマクロなら Worksheet_SelectionChange を抜けた後も aの値『10』は保持されています

Excel側で セルの選択をしてみればわかります
2回目以降は『初期化します』が表示されません

マクロをどこか修正した場合など保持していた変数の内容を破棄しますので iniが実行されます

補足

うまく動くことも、そうでないこともあります。どういうときにうまく動き、どういうときにうまく動かないのか、良く把握できていません。なので困っています。

回答No.1

yorozu_ya

> 以下のマクロは、時にはpublic変数が消えてしまいますが、しかし、消えないこともあります。

消えたことをどうやって確認しましたか？

セルの移動によって Worksheet_SelectionChange が発火され、
その End Sub に到達するまでの間は保証されているはずですが、
それを抜けた後は保証されていません。

保証されていないのですから、マクロに欠陥があるという話ではありません。

補足

ありがとうございます。
確かにsub　から　end　sub　の間だけ値が保持され、後は保障しないと考えると、その通りです。ただ、もし本当にそうなら、多くのExcel上のマクロはあまりにも脆弱な基盤に立っていると考えられます。確かに、そうとも考えられますが、そうであっては困ると思うのも確かです。現実的に多くのexcelのマクロはend　sub　のあともpublic変数は保持される事を当然視したものも多いのではないでしょうか。実際、ほんのいくつかですが、個人的に作ったマクロは、public変数はend　sub　後も保持されると考えて作り今までは問題なく動いています。もちろんそれはたまたま運が良かっただけかも知れません。マイクロソフトの仕様ならば、どうしようもない気も確かにするし、その範囲内で何とかすることを考えないといけないのかも知れませんが。