【VBA】UserForm1の中で使うワークシート変数を統一したい

Question

UserForm1の中で使うワークシート変数を統一したいと思っています。

そのため宣言セクションで画像のように宣言してみましたが、エラーが出ます。

要するに「変数の宣言は出来るけれど、変数への代入は宣言セクションでは出来ない」
ということだと思います。

この場合「Ls_sh」という変数をワークシート変数として宣言したので、
UserForm1のプロシージャーの中では「Ls_sh」という変数名を
長整数型や文字列型で使えないというメリットがあるのは理解出来ます。

ですが宣言セクションで変数にシート名を割り当てることができないため

Set Ls_sh = Worksheets("Sheet10")

を各プロシージャーに毎回書くことになってしまいます。
おまけに、間違えて

Set Ls_sh = Worksheets("Sheet11")

とセットしてしまう可能性があり、あまり意味がないように思います。

ワークシート名(変数)の間違い防止のため、ユーザーフォームの中での
シート名の変数を一括管理したいのですが、
こういう場合、上級者の方はどういう方法で管理しているのでしょうか？

やっぱり各プロシージャーの中で、毎回

Set Ls_sh = Worksheets("Sheet10")

を行って、変数の割り当てをしているのでしょうか？

抽象的な質問なので返答しにくいと思いますが
何かアドバイスやお勧めの方法があれば教えて下さい。

以下のサイトも見たのですが、ちょっと違うような気がするので
質問してみました。

http://officetanaka.net/excel/vba/tips/tips94.htm

追記
可能でしたら、コードで説明して頂ければうれしいです。
※急いでおりません。

めぐみん_ · Accepted Answer

No.1です。

でもユーザーフォームモジュールのみでも

Dim Ls_sh As Worksheet

Private Sub UserForm_Initialize()
Set Ls_sh = Worksheets("Sheet1")
End Sub

Private Sub CommandButton1_Click()
MsgBox Ls_sh.Name
End Sub

Private Sub UserForm_Terminate()
Set Ls_sh = Nothing
End Sub

ユーザーフォームが開かれる際のイベントでセットを行なえば、個々のイベントで宣言の必要はなくなるからこちらの方が楽ですかね。

でも標準モジュールの方でPublicで宣言すれば、ユーザーフォームを複数作成したとか標準モジュール内でも使用させたい時には有利になりますし、
目的次第って所かもです。

・標準モジュール
Option Explicit

Public Ls_sh As Worksheet

・ユーザーフォームモジュール（ユーザーフォーム１）
Private Sub UserForm_Initialize()
Set Ls_sh = Worksheets("Sheet1")
End Sub

Private Sub CommandButton1_Click()
MsgBox Ls_sh.Name
UserForm2.Show
End Sub

Private Sub UserForm_Terminate()
Set Ls_sh = Nothing
End Sub

・ユーザーフォームモジュール（ユーザーフォーム2）
Private Sub CommandButton1_Click()
MsgBox Ls_sh.Name
End Sub

みたいな。
あとは確か・・・クラスモジュールと組み合わせたい時とかでも使われたような気はします。

めぐみん_ · Answer

No.1のお礼に対して。

No.3さんの回答を読んで『そういう事態もあり得るんだ』とちょっとビビってしまっている状態ではあるのですが。
取り敢えず疑問点について。

＞Private Sub UserForm_Initialize()
＞Private Sub UserForm1_Initialize()

仮に複数のユーザーフォームをデザイン画面で作成したとしても、『UserFoam』と言う大元のオブジェクト名は変わる事はないため、
任意にFoamコントロールのオブジェクト名を変更してもその部分は変えられないと思います。

VisualBasic（.NET）の似たようなイベント（Load）では、Foamの名前を変更すれば

Private Sub abcdefg_Load(ByVal　～

みたいに変わるのですがね。
VBAはもっと古いVBをベースにしているので、開発の際に『そこまでの必要性』が当時なかったのではないか・・・と思います。（言い切れなくてごめんなさい）

No.3さんのご意見はVB.NETには標準モジュールに似たクラスを追加すれば私が回答した方法もできますが、まずその方法を行なうような解説は見つからなかったですね。
キチンとFoam間での明確な値のやり取りを解説されてます。
その点から行くとやはりお薦めできる方法ではなかったのかもですが、であればどうコード化するか？となっても直ぐには思いつかなかったです。

WindFaller · Answer

こんにちは。

ふつうは、モジュール・スコープでも、オブジェクト型の変数はしないですね。
テクニックでは、#1,#2さんで、示された通りですが、

Office Tanaka で示されているのは、
http://officetanaka.net/excel/vba/tips/tips94.htm

プロシージャで引き数で渡す方法（参照渡し)で、なぜそうするかといとう、モジュール・スコープでも、グローバル変数（用語に問題あり)でも、オブジェクト型は、壊れやすいからです。そのために、確実に渡す方法以外は、それをプロシージャ間で回して使うことはしません。＊グローバル変数とはいっても、通常は、1つのプロジェクト内だけです。そして、私自身は、ほとんどグローバル定数(Const) のみに限られます。

クラスモジュールで定義する方法もありますが、クラスでインスタンス生成のように変化・変性しないものならともかく、一般的なオブジェクトは、とても長期では保持できるものではないと思うのです。
何らかのエラーが、オブジェクトの取りこぼしにつながるケーズが本当に多いのです。つまり、どこかでエラーが走れば、オブジェクト型の変数はこわれてしまうのです。

実際に私が以前書いていた、とてもみっともないコードですが、プロシージャの始めに、
以下のようなチェックプログラムを置くことになってしまいました。
If sh Is Nothing Then
　 Set sh = Worksheets("Sheet1")　'実際はもうちょっと複雑でサブルーチンにしました。
End If

その愚かさに気づくのに、数年掛かりました。とても大掛かりなものだったので、最初のAuto_Open から書き直す気持ちが起きませんでした。限られたモジュール内では、もしかしたらトラブルなく通せるかもしれませんが、なかなかそうはいかないように思うのです。これがベストとは言いません。プログラムが大きくなればなるほど、そのように考えたくなるのはやむを得ないことです。

言葉で說明してもなかなか実感が分からないと思います。何度となく試してみることで、自分なりのスタイルが見つかるかもしれません。

めぐみん_ · Answer

変数に対してSheetが固定で良いなら。

・『標準モジュール』で Private ではなく Public で変数『だけを』宣言します。

Option Explicit

Public Ls_sh As Worksheet

・ユーザーフォームモジュールにて

Private Sub UserForm_Initialize() 'ユーザーフォームが開かれる際のイベントでセット
Set Ls_sh = Worksheets("Sheet1")
End Sub

Private Sub CommandButton1_Click() '任意のイベントで変数を使用
MsgBox Ls_sh.Name
End Sub

Private Sub UserForm_Terminate() 'ユーザーフォームが閉じられる際のイベントで解放
Set Ls_sh = Nothing
End Sub

とかならやりますよ。（上級者ではなく初級レベルの者ですので、必ずこれが良いとは胸を張っては言えませんけど）

【VBA】UserForm1の中で使うワークシート変数を統一したい

No.1です。

No.1のお礼に対して。

こんにちは。

変数に対してSheetが固定で良いなら。

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング