ExcelのVBA。public変数の値が消える
- Excelの2003や2007でVBAを使用しており、標準モジュールでpublic変数を定義しています。しかし、ユーザーフォームを使ってpublic変数に値を入れた後、標準モジュールに戻るとpublic変数がリセットされてしまう現象が発生します。
- 同じマクロでもこの現象が発生することとしないことがあり、再現性がありません。簡単なマクロではパブリック変数が保持されていますが、問題のあるマクロは長いため具体的な問題箇所を特定することができません。
- また、ユーザーフォームに対して引数を渡す方法や引数を受け取る方法があるかどうかもわかりません。現在はpublic変数や特定のセルに値を代入することで値の受け渡しを行っていますが、これはエレガントではなく汎用性もありません。
- ベストアンサー
ExcelのVBA。public変数の値が消える
VBAについて。Excelの2003や2007を使っています。標準モジュールで public 変数を定義しました。 ユーザーformを使い、パブリック変数に値を入れたり変更し、標準モジュールに戻ったとき、そのpabulic変数が resetされてしまっている事があります。全く同じマクロで、この現象が起こることと、起こらないことがあります。excelの2003でも2007でも、同じ現象が起こります。簡単なマクロで再現性のある具体的なものを示したいと思い、試しましたが、できませんでした。簡単なマクロでは、きちんとパブリック変数は保持されています。問題のあるマクロはかなり長いものなので、とても示せません。問題がどこにあるのかわかりません。このマクロで不都合があるという具体的なマクロを示すことができない状態での質問で、申し訳ありません。 また、このようなプログラミングをしていて思ったのですが、ユーザーフォームに対してユーザーが任意の引数を渡す方法あるいは、ユーザーフォームから引数を受け取る方法はあるのですか?今は、pubulic変数を用いたり、具体的なセルに値を代入したりしていますが、どう考えてもそれはエレガントではないし、汎用性もないと思います。引数で引き渡すのが一番綺麗でいいとは思うのですが、それが可能かどうかわかりません。
- qso
- お礼率39% (20/51)
- Visual Basic
- 回答数5
- ありがとう数6
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
IDは違うけれども、同じ内容の質問のようですね。 http://okwave.jp/qa/q6420530.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 変数が空かどうかチェックする方法も考えられますが、意味がありませんから、それなら、参照渡しにすればよいのです。
その他の回答 (4)
- yorozu_ya
- ベストアンサー率54% (76/140)
たぶん、質問者さんには理解して頂けたと思うのですが、 念のため解説しときます。 Office の VBA はドキュメントに付随しています。 Excel の場合ですと、ワークシートにプログラムが付随しています。 このワークシートというのは、手動でもプログラムからでも追加/削除ができます。 つまり、プログラム自体が変更される可能性があるということです。 プログラムが変更されたらリセットして再コンパイルする必要がありますが、 プログラム実行中にそんなことされたら、堪りませんよね。 それで、プロシージャ実行中は保証します、という仕様になっています。 プロシージャ実行中にプログラムが変更されたと判断されたら、 終了後にリセットする、ということです。
補足
度々ありがとうございます。 >Excel の場合ですと、ワークシートにプログラムが付随しています。 確かにいわれてみれば当たり前ですが、今まであまり、今回のような件で意識したことがありませんでした。こういう視点が大切だと気付かされました。 ありがとうございます。
- yorozu_ya
- ベストアンサー率54% (76/140)
> > その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 変数がアプリケーション終了時まで有効」にして欲しいですね。
- imogasi
- ベストアンサー率27% (4737/17068)
このようにコードも掲示できない(しない)場合に、このコーナーに質問しても無駄だと思いませんか。 読者は質問者のパソコンのコードも実行したり、多少変えて実行したりも出きずに、エラー原因の究明などできるわけが無い。 よほど典型的なエラーコードが出ている場合をのぞいて、このコーナーはデバッグの質問には適さない。 それなのに、エラーの原因の質問が出る。自分で究明するほか無いでしょう。この面では他人を当てにしないこと。 1つの提案はMsgbox (Public変数名)を要所要所に入れて実行してみて、何処で予想外に変化するか、見極めることは出来るのでは無いですか? こんなこともやってみましたか。 パブリック変数にもスコープ的なな考えは必要と思うので http://pc.nikkeibp.co.jp/pc21/special/2007_gosa/eg5.shtml などをよく読んで考えてみてください。 >ユーザーフォームに対してユーザーが任意の引数を渡す方法あるいは、ユーザーフォーム・・ は、こんな抽象的な書き方でなく、具体的にどういう場合に、どういうことをしたいのか書いて質問すべきだ。 http://okwave.jp/qa/q5160575.html の質問などあるが、ユーザーフォームに引数を渡す場合と言えるのかどうか? ーー 私は不勉強で、回答の>pabulic変数が resetされてしまっている事があります。ーー>Excel の仕様です。の具体例は想像できません。 ーー >pabulicはPublicのミスタイプです。プログラマーとしては、コード内に、こういう誤りは無いですか。
補足
以下のマクロでも、時には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
- yorozu_ya
- ベストアンサー率54% (76/140)
> その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変数の数は少ない方が見通しのいい、ミスの少ないプログラムになると感じたので。また、変数はどこでどう使うかを限定した方がいいと思うので。どうなのでしょうか。
関連するQ&A
- EXCELのVBAについて
エクセルのVBAでユーザーフォームに作ったテキストボックスに入力した値を、コマンドボタンによる「実行」とともに別に作った標準モジュールの変数として代入するにはどうすれば良いでしょうか? 具体的な流れは、 マクロ実行 ↓ ユーザーフォーム出現 ↓ テキストボックスに数字を代入→「実行」 ↓ 変数を代入された標準モジュールによる処理完了 というものです。 ご教授お待ちしております。
- ベストアンサー
- オフィス系ソフト
- VBA 変数の受け渡し
エクセルVBAで標準モジュール内でインプットボックスに入力した変数をユーザーフォームに受け渡してユーザーフォーム内のリストボックスに表示させる場合どのように変数の受け渡しを行えばよいか教えてください。 現在は、一度ワークシートの任意のセルに値を書き出し、その後ユーザーフォームのイニシャライズで先ほどのセルから値を受け取るようにしています。 標準モジュールではインプットボックス入力後「UserForm1.Show」でユーザーフォームの立ち上げを行っています。
- ベストアンサー
- Visual Basic
- EXCEL VBAでの変数の有効範囲
モジュール内のすべてのプロシージャで使う変数は(General)(Declarations)で定義すれば参照できますが、ユーザーフォームのオプション・ボタンの値などを別のモジュールで参照できないでしょうか?
- ベストアンサー
- Visual Basic
- エクセルVBAでVBAの修正は出来るのでしょうか?
エクセルVBAでVBAの修正は出来るのでしょうか? あるエクセルファイルにマクロを組み込んでいます(ユーザーフォームも使用)。 このエクセルファイルを複数個フォルダ内にコピーしています。 このコピーした全てのファイルのマクロの修正をVBAで出来ないかと考えています。 修正箇所はモジュール内のコード修正、モジュールの追加、既存のユーザーフォームにボタン等の追加等です。 エクセルは2003を使用しています。 どなたか教えてください。
- 締切済み
- Visual Basic
- エクセルVBAで標準モジュールを保護したい
エクセルのVBAマクロを使用したエクセルフィルがあります。 ユーザーは標準モジュールに記述した関数を使ってシートのマクロを変更してカストマイズできます。 ユーザーが標準モジュールを変更すると使用できなくなりますので、この標準モジュールだけをユーザーが変更できない、または見えないようにする方法をご教示いただけませんか。 よろしくお願いします。
- ベストアンサー
- その他(プログラミング・開発)
- 再度,ExcelVBA,public変数が消える
大変申し訳ありません。一度、この件で質問し、その回答を締め切ったばかりなのですが、やはりもう少し知りたくて質問させていただきます。 ある方の回答への補足で、以下のマクロを具体例としてあげました。ただし、以下のマクロは、時にはpublic変数が消えてしまいますが、しかし、消えないこともあります。 消える理由として、 回答していただいたものから考えて、 1.End Sub を通っていないままに終了しているから 2.不完全なマクロ 3.きちんと作られたマクロの流れ(ルーチン)ではない流れがある 4.「Public 変数は、標準モジュールを経由して、ローカルのUserForm に供給、しかし、それを戻すということはしない。つまり、a.ローカルで発生した値 →標準モジュールのPublic 変数 →UserFormのローカルのプロシージャ b.標準モジュールのPublic 変数 → UserFormのローカルのプロシージャ ※ただし、起動時の一回きり、それ以上の持ち回しはしない。逆もしない。」この件に関して、以下のマクロに問題がある。 この様なことを考えました。 4.の場合、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
- ベストアンサー
- Visual Basic
- エクセルVBAユーザーフォームの変数の設定方法について
すいません、エクセルVBAのユーザーフォームの変数の設定方法について質問があります。 1 ユーザーフォームを2つ用意する。 2 それぞれにComboBox1をおく。 3 立ち上げたユーザーフォームについて、UserForm_InitializeでComboBox1に"a"のAddItemを作る。 この、「立ち上げたフォームのComboBox1に"a"のAddItemを作る」 という作業を各々のユーザーフォームに記載するのではなく、標準モジュールでまとめて記載する方法で躓いています。 Public m As String Private Sub UserForm_Initialize() ’フォーム1を立ち上げた場合 m = "UserForm1" Call Test1(m) End Sub Private Sub UserForm_Initialize() ’フォーム2を立ち上げた場合 m = "UserForm2" Call Test1(m) End Sub ↓ 標準モジュールに記載 Sub Test1(m As String) VBA.UserForms.Add(m).ComboBox1.AddItem "a" End Sub これだとUserForm_InitializeとTest1の間で無限ループが始まってしまい、うまく進んでくれません。 ヘルプを見ましたが、Add(変数)でユーザーフォームを変数で指定できるということ以上のことは発見できず行き詰っています。 それぞれのフォームに書けばいいだけの話なのかもしれませんが、メンテを考えると出来ればまとめて記述しておきたいと考えています。 解決方法がありましたらどうぞよろしくご教示願います。
- ベストアンサー
- オフィス系ソフト
- VBAでユーザーフォームに引数を渡したい
標準モジュールからユーザーフォームに引数を渡し、フォーム上で表示させたいのですが、方法がわかりません。 可能でしょうか?教えてください。
- ベストアンサー
- Visual Basic
- フォーム間での共有変数
今、フォームを二つ表示し、お互い共通の変数を使って値の受け渡しをさせたいのですが、どうもうまくいきません。 知り合いには標準モジュールを使えばいいって聞いたので さっそく「プロジェクト」→「標準モジュールの追加」をし、 でてきたエディタに変数を追加 起動してみると・・・ちゃんと値の受け渡しができていませんでした。 ただ追加するだけでは受け渡しはできないのでしょうか? それとも標準モジュールの時点で間違いでしょうか? どなたか、レスお待ちしてます
- ベストアンサー
- Visual Basic
- 【VBA】値の引渡しについて
VBA初心者です。 VBAの勉強もかねて、今、【 標準モジュール 】と【 UserForm 】を用いて あるプログラムを作っています。 その中で、プロシージャ間をまたいで 『 値の受け渡し 』をしたいと思っています。 今回、質問をさせていただいたのは、その『 値の受け渡し 』が 「できる場合」と「できない場合」があるので、常にできるように その対応方法を教えていただきたく、今回投稿させていただきました。 ---------------------------------------------------------------- 今困っているパターンを例にあげると、 ユーザフォームの中に2つのボタン 「OK」ボタンと「キャンセル」ボタンが あるとします。 【 「キャンセル」ボタンがおされた場合には「終了する」 】という 仕組みにしたいと思っています。 そこで以下のようなプログラムを作ろうとしました。 ---------------------------------------------------------------- ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 1) General部にPublic変数(以下「P変数」)を宣言する。 Public cancel as byte 2) 【標準モジュール】のプロシージャから【フォーム】の「Userform1」を表示させる。 Sub A処理() Userform1.show End sub 3) 【フォーム】のUserform1._CommandButton2に以下のように記述する。 (キャンセルボタンがクリックされた場合) Private Sub CommandButton2_Click() cancel = False Unload Me Exit Sub End Sub 4) 【標準モジュール】のプロシージャの、一連の処理の最後に 以下のようにプログラムを記述する。 Sub A処理() Userform1.show ↓ ↓ if cancel = false then exit sub end if End sub ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 1)~4)のようにして 「キャンセルがクリックされた場合、すべての処理を終了する。」 というようなプログラムを記述してみました。 すると、デバッグを使って、変数「cancel」の中身を見てみると 3)の段階で、その変数「cancel」に「false」値を格納したはずなのに、 プロシージャ間をまたいだことで 4)の段階では、変数「cancel」の中身が必ず「""」と 値が空値になってしまいます。 4)の段階でも、変数「cancel」の値が保持できる、 何かしらの方法はないでしょうか。 回答をよろしくお願いします。
- 締切済み
- Visual Basic
補足
詳しい説明をありがとうございました。とっても役に立ちました。 >私の失敗の経験から、Public 変数については、ひとつのルーチン以上に用いないことです。また、動的には用いないことです。一回きりの静的な変数として使うべきです。静的といっても、Static 変数の意味ではなく、文字通り変数を変化させないということです。ただし、時間の間隔やカウントなどの単純なものは別です。 これからは、これを心がけたいと思います。 >理屈では、一旦、標準モジュールのPublic 変数に送っておいて、そこから他のプロシージャ等に送ればと思いますでしょうが、それはしないということです。ルーチンが別だからです。 この部分もとっても参考になります。 こちらも見ました。 http://oshiete.goo.ne.jp/qa/6420530.html endの部分の違い(コメントかそうでないか)により結果の違いはとても勉強にないました。 ありがとうございました。