ブック名、シート名を他のモジュールからも参照可能にする方法

マクロを書く場合に次のように宣言しています。

Sub test()
'(1)始まり
Dim WB1 As Workbook
Dim SH1 As Worksheet
Set Wb1 = Workbooks("Book1")
Set Sh1 = Wb1.Worksheets("Sheet1")
'(1)終わり

Msgbox Sh1.Cells(1, 1)
end Sub

新たにマクロを書く場合には、その都度上記コードの「'(1)始まり～'(1)終わり」を書いていますが、面倒です。
「Public ～」などのように一箇所に宣言しておけば,他のモジュールからもブックやシートを参照できるようにする方法は無いでしょうか？

よろしくお願いします。

No.1 ベストアンサー 回答者： papayuka 回答日時： 2005/02/21 08:11

Test1 が実行された後なら Test2 も Test3 も下記で動くけど、、、

そういう事ではない？

'Module1-------------------------------
Public wb As Workbook
Public ws As Worksheet
Sub Test1()
　　Set wb = ThisWorkbook
　　Set ws = wb.Worksheets(1)
End Sub
'--------------------------------------

'Module2-------------------------------
Sub Test2()
　　MsgBox ws.Cells(1, 1).Value
End Sub
'--------------------------------------

'Module3-------------------------------
Sub Test3()
　　MsgBox ws.Cells(1, 1).Value
End Sub
'--------------------------------------

この回答へのお礼

ありがとうございます。
私が知りたいのはpapayukaさんのおっしゃる「そういう事」です。
上記Test1を実行する必要があるのですね。
所定のコーディングをすれば、定数として設定できるものと思っていました。

お礼日時：2005/02/23 13:48

No.3 回答者： komet163 回答日時： 2005/02/21 16:24

標準モジュールを作成し、モジュールレベルの宣言部に Public で変数を定義して、同モジュールに変数に値を入れるプロシージャを作成し、ブックのオープンイベントで上のプロシージャをコールしてみてはどうでしょう。

具体的には、

module(標準モジュール)
Public WB1 As Workbook
Public SH1 As Worksheet
sub main
Set WB1 = Workbooks("Book1")
Set SH1 = WB1.Worksheets("Sheet1")
End Sub

ThisWorkBook(クラス)
Private Sub Workbook_Open()
Call main
End Sub

この回答へのお礼

ThisWorkBook（クラス）で呼び出すのは妙案ですね。
基本的にはNo1さんと同じと思うのですが、起動時にコールできれば楽ですね。
勉強になりました。ありがとうございます。

お礼日時：2005/02/23 13:44

No.2 回答者： KenKen_SP 回答日時： 2005/02/21 13:57

No.1の papayuka さんのご回答のとおりなのですが、別解としてはPublic定数としてシート名やブック名を定義しておく方法があります。

利点としては、、、あまり無いように思いますが（--；）

papayukaさんの方法は、オブジェクト変数Sｈにアクセスするために

Set Sh = Sheets("Sheet1")
Sh.Select

のように予めSetステートメントでShを定義しなければなりません。
一方、シート名を文字列型の定数にした場合は

Public Const cnsShName as String ="Sheet1"

Sub Sample()
　Sheets(cnsShName).Select
End Sub

のように、シートを指定できます。

どちらの方法でも、同一プロジェクトの全てのプロシージャからシートを指定できますし、コーディングの手間も変わりません。

好みの問題かもしれませんが、私はSetを省略できるので良く定数を用います。

また、自信がないのですが、文字列型定数でシート名を保持する方が、オブジェクト変数でシートオブジェクトを保持するよりも省メモリなのではないかと思います。
（勘違いかもしれません）

この回答へのお礼

ありがとうございます。
ただ、シート名を文字列型の定数にするのは、コーディングの手間と言う点で不利な気がするのですが。

sh.select　と書くか
Sheets(cnsShName).Select　と書くか

いかがでしょうか？

お礼日時：2005/02/23 13:54