プロが教えるわが家の防犯対策術!

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

このQ&Aに関連する最新のQ&A

A 回答 (6件)

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



#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と違って、標準モジュールを使い、そのモジュールとそのプロシージャは、分離して使うことがあるから、グローバル変数を多用するのだと思います。
    • good
    • 0

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



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

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

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

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

    • good
    • 0

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


ところで
MsgBox test
は何を表示しようとしたのでしょうか?
    • good
    • 0

>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等では、読みにくくなるかもしれません。

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

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


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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

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

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

VBAについて。Excelの2003や2007を使っています。標準モジュールで public 変数を定義しました。 ユーザーformを使い、パブリック変数に値を入れたり変更し、標準モジュールに戻ったとき、そのpabulic変数が resetされてしまっている事があります。全く同じマクロで、この現象が起こることと、起こらないことがあります。excelの2003でも2007でも、同じ現象が起こります。簡単なマクロで再現性のある具体的なものを示したいと思い、試しましたが、できませんでした。簡単なマクロでは、きちんとパブリック変数は保...続きを読む

Aベストアンサー

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

IDは違うけれども、同じ内容の質問のようですね。
http://oshiete.goo.ne.jp/qa/6420530.html

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

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

「ほとんどの場合、プロシージャ終了後も値は保持されますが、
 ……Public 変数がアプリケーション終了時...続きを読む

QVBA モジュールで共通に使う変数の宣言方法

VBAにてプログラミングを覚えている者です。

現在、いくつかのモジュールがあり、それぞれDimにて宣言している共通の変数があります。
いくつものプロシージャに毎回宣言せず、どこかでひとまとめにしたいと思い、色々と調べています。

例えば、

Dim pic1 As Picture
Dim cell1 As String
Dim pass1 As String
Dim pic2 As Picture

などです。


Dimのほかに、Publicの宣言などがありますが、いま一つ使い方がピンときません。

共通宣言する変数をひとつのモジュールとして登録し、活用できると、いざ変更となったときに
その内容だけ置き換えればいいと思うのですが、みなさんはどのように宣言をしていますか?
(例えば、Stringなどは各モジュールで変更するのが大変だと思うのですが・・・)

ヒントを教えていただければと思います。よろしくお願いします。

Aベストアンサー

>モジュールで共通に使う変数の宣言方法

モジュールの先頭に書いたdimで宣言すると,そのモジュールの中だけ(に記入されている各プロシジャ)でパブリックになります。
モジュールの先頭に書いたpublicで宣言すると,全モジュール(に記入されている各プロシジャ)に対してパブリックになります。

「変数宣言モジュール」のようにモジュールを越えて参照させたいという事なので,DimではなくPublicで宣言します。


Module1:
public x as variant


Module2:
sub macro1() ’先に実行する
x = "abc"
end sub


Module3:
sub macro1()
msgbox x
end sub

QVBでグローバル変数を宣言するには

VB初心者ですが。クイズゲームのようなものを作成したいと考えてます。
Private Sub ~ End Sub の中で宣言した変数って他のところに呼び出したり(戻り値として渡す)出来るのでしょうか?
どこでも、いつでも呼び足せるグローバル変数の宣言とはどのようにやるのか、具体的に教えていただけないでしょうか?

Aベストアンサー

>Public a as Integerのように宣言して、初期化するにはどのように記述を行えばよいですか?

>Public a As Boolean = 0
のように記したら”コンパイルエラー”と出ました。

Booleanって整数値取れたかなと思いつつ。
Sub~End Sub内でa = 0を代入したりしてください

扱おうと言うことがあるかどうか疑問だけど

Public Const a As Integer = 10 'グローバルな定数の宣言

QEXCELファイルのカレントフォルダを取得するには?

EXCELファイルのカレントフォルダを取得するには?

C:\経理\予算.xls

D:\2005年度\予算.xls

EXCEL97ファイルがあります。

VBAで
  カレントフォルダ名
(C:\経理\,D:\2005年度\)
を取得する事は可能でしょうか?

CURDIRでは上手い方法が見つかりませんでした。

Aベストアンサー

こんばんは。
Excel97 でも、同じですね。以下で試してみてください。

Sub test()
'このブックのパス
a = ThisWorkbook.Path
'アクティブブックのパス
b = ActiveWorkbook.Path
'Excelで設定されたデフォルトパス
c = Application.DefaultFilePath
'カレントディレクトリ
d = CurDir
MsgBox "このブックのパス   : " & a & Chr(13) & _
   "アクティブブックのパス: " & b & Chr(13) & _
   "デフォルトパス    : " & c & Chr(13) & _
   "カレントディレクトリ : " & d & Chr(13)
End Sub

QExcel VBAで、ユーザーフォームの値を、モジュールで使用したい。

VBA初心者です。(おそらく)基本的な質問で、申し訳ありません。
ユーザーフォーム1には、テキストボックス1とコマンドボタン1が配置されているとします。

Sub TEST ()
Dim N
Userform1.Show
MsgBox N
End Sub

Private Sub CommandButton1_Click()
Dim N
N = TextBox1.Text
UserForm1.Hide
End Sub

Sub TESTを実行した時に、ユーザーフォーム1からNの値を引き継ぐには、どうしたら良いのでしょうか?よろしくお願いします。

Aベストアンサー

'モジュールに変数宣言(グローバル変数)
'Public 宣言すると他のモジュールやフォームと
'共有できる変数になります。
Public AA As String

'sample
'A1 を実行する。

Sub A1()
Call A2
AA = InputBox("input", , AA)
End Sub

Sub A2()
AA = InputBox("input", , AA)
End Sub

Qエクセル VBA ユーザーフォームを閉じる

ユーザーフォームを開く時は
UserForm1.Showですが
閉じる時は?
UserForm1.Close
だとコンパイルエラーになります。
End
にするしかないですか?

Aベストアンサー

Unload Me とか Unload UserForm1 でユーザーフォームを閉じることができます。

QEXCEL VBA で現在開いているブックのファイル名を取得する方法

EXCEL2003 VBAで業務を簡素化するために、現在開いているブックのファイル名を取得する方法が分かりません。
作業手順をマクロを使って処理していますが、オリジナルのワークブックをファイル名を変えて保存し、以後、このワークブックを読み込んで使用しています。
このときのVBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり、以後の業務に使用できません。
常にファイル名を取得出来るVBAをどなたか、教えて下さい。

Aベストアンサー

>現在開いているブックのファイル名
 ちょっと曖昧な表現かなぁという気もいたしますが、VBAが書いてあるブックのブック名は
ThisWorkbook.Name
で、現在 "アクティブにして" 操作対象になっているブックの名前は
ActiveWorkbook.Name
ですね。

 しかし、
>VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり
というような文脈からすると、
ThisWorkbook.Name
の方ですかね。

QEXCEL VBAで計算値を四捨五入、切り上げ、切捨てする方法

ネットで探してみたのですが、計算結果を四捨五入して特定のセルを
返すにはどうしたらいいのでしょうか?

Sub hokangosa()

Dim ZPS As Double
Dim ZPOS As Double
Dim DMN As Double
MsgBox (" >>> 補間誤差自動計算 <<< ")
MsgBox (" >>> 初期値入力します <<< ")
ZPS = InputBox(">>> ステップを入力してください<<<")
ZPOS = Sheet1.Cells(22, 4).Value
DMN = ZPOS / ZPS
Sheet1.Cells(23, 6).Value = DMN
End Sub

ここでDMNの値を四捨五入したいです。

またこれとは別に切上げ、切捨ても教えていただけるとありがたいです。

Aベストアンサー

DMN = Application.WorksheetFunction.Round(ZPOS / ZPS, 0)
で、四捨五入
DMN = Application.RoundDown(ZPOS / ZPS, 0)
で切り捨て
DMN = Application.RoundUp(ZPOS / ZPS, 0)
で切り上げです。

引数で、対象桁を変更できます。

QVBAで別モジュールへの変数の受け渡し方法

初歩的な質問で申し訳ありません、
googleでもどのように検索してよいのかわからないので教えてください。


Userformにて作成したコード内にhogehogeという変数を宣言したとします。
これをPublic Sub CommandButton1_Click()内でhogehogeに文字列abcを代入します。

そしてcall を使ってmodule1の処理を行うのですが、
module1内で変数hogehogeに文字列abcが既に代入されているものとして
hogehogeを使いつつ、処理を続けるにはどのようにすればよいのでしょうか?

callで呼ぶ際に変数hogehogeの受け渡しなどが必要なのでしょうか?
(ちなみに全てPublic Subで書いております)

初心者なので質問がおかしいかもしれませんが
よろしくお願いします。

Aベストアンサー

標準的なコードの書き方があります。
具体的なコードがないので、こちらは想像の範囲なので、行き違いがあるのはご容赦願います。

>Userformにて作成したコード内にhogehogeという変数を宣言したとします。
変数を共有化(厳密にはグローバル化されていない、プロジェクト内のみ共有)するには、通常、「標準モジュール」で、プロシージャ外の変数は、Public変数になります。そして、明示的に、Public ○○と書きます。仮に、それが、UserFormモジュール内で使用されるものでも、標準モジュールに書くことになります。

>これをPublic Sub CommandButton1_Click()内でhogehogeに文字列abcを代入します。
そういう書き方は、本当に特殊な例を除いて書きません。Privateキーワードだけです。

つまり、ローカルモジュール(シートモジュール、ThisWorkbookモジュール、UserFormモジュール)には、Public キーワードは用いません。また、そこへのCallでの、呼び出しもしません。共有化させる場合は、一般的には「標準モジュール」を利用します。

しかし、別途、ユーザー定義関数やサブプロシージャで、引き数を参照渡し/値渡しで、変数の内容を渡すことがあります。この方が安定していますが、多少、コードの可読性が落ちます。

>callで呼ぶ際に変数hogehogeの受け渡しなどが必要なのでしょうか?
具体的にどのようなコードになっているか分かりませんし、Callでどう呼ぶのかは分かりませんが、引き数を設ければ、それはそれで済みます。

例:
'UserForm1 上 CommandButton1とTextBox1 を用意
'UserFormモジュール内
Private Sub CommandButton1_Click()
Dim a As Variant
  a = Val(TextBox1.Value)
 Call Test1Ref(a)
 MsgBox a
End Sub

'標準モジュール、そのままで参照渡しになっている
Public Sub Test1Ref(arg1 As Variant)
 arg1 = arg1 * 10
End Sub

ただし、この程度ならば、UserFormモジュール内で、Test1Refを置いても同じです。その時は、Publicキーワードは不要です。

標準的なコードの書き方があります。
具体的なコードがないので、こちらは想像の範囲なので、行き違いがあるのはご容赦願います。

>Userformにて作成したコード内にhogehogeという変数を宣言したとします。
変数を共有化(厳密にはグローバル化されていない、プロジェクト内のみ共有)するには、通常、「標準モジュール」で、プロシージャ外の変数は、Public変数になります。そして、明示的に、Public ○○と書きます。仮に、それが、UserFormモジュール内で使用されるものでも、標準モジュールに書くことになりま...続きを読む

QエクセルVBAでテキストボックスの値の取得と変更について

エクセルのVBAを使ってシート上のテキストボックスのテキストを取得・変更するマクロを作成したいと思っていますがうまく行きませんので、お知恵を拝借したいとおもいます。

環境:WindowsXPでオフィス2002
状況:
エクセルブックa.xlsのシートに「コントロールツールボックス」のテキストボックスを配置(オブジェクト名はTEXTBOX_C)
エクセルブックb.xlsにコードを書き、a.xlsのTEXTBOX_CのプロパティのValueかTextを取りだしたい

試した事:
コントロールを配置したシートに次のマクロ
TEXTBOX_C.Text = "これはコントロールのテキストボックス"
を書くとテキストボックスに文字を入れ込めますが、別のエクセルブックからだと上手く行きません。

また、オートシェイプのテキストボックスの場合は簡単に出きるのですが、コントロールツールボックスではどうしても上手く行きませんので、対象法などご存知の方いらっしゃいましたら教えてください

Aベストアンサー

エクセルを新規に開きました。
そのSheet1に(コントロールツールボックスの)TextBoxを貼りつけました。
そのBook1から、ファイル-開くで別ブックを開きました。
別ブックのMojule1側に下記を書いて
Sub test02()
MsgBox Workbooks("book1").Worksheets("sheet1").textbox1.Text
End Sub
を実行すると、Book1のTextBoxに入れた文字列が表示
されました。
がそんな質問ではないのですか。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング