Excel:ThisWorkbookオブジェクト内にプロシージャを追加し、それを標準モジュールから呼び出すことは可能？

質問

（おかしなことを言っているかもしれませんし、
VBAについて正しく理解できていないのかもしれません）

ThisWorkbookオブジェクト内にプロシージャ(Public)を追加して、
それを標準モジュールから呼び出すことが出来るのでしょうか？

質問内容のことを行いたいのです。
しかし、それ以前にThisWorkbookオブジェクト内にプロシージャを追加して、
それをThisWorkbookオブジェクト内から呼び出すことも今現在出来ていないません。
（ThisWorkbookオブジェクトにプロシージャの追加、またそれの呼び出しが可能か不可能かも分かっていません）

回答No.1

BLUEPIXY · ベストアンサー

ThisWorkbookの所に
Public Function test(A, B)
test = A + B
End Function
と記述したとしたら
標準モジュールから
Public Sub mtest()
dim ans
ans=ThisWorkbook.test(10, 20)
End Sub
のように呼び出せます

お礼

回答ありがとうございました。
非常に助かりました。

回答No.2

Wendy02

こんにちは。

私は、この件について、多くのテキストには載っていることですが、一度、自分自身でまとめようと思いました。

VBAでは、Public キーワードの使い方は、基本中の基本なのですが、VBAは、標準モジュールが優先されるので、Visual Basic とは異なる点を持っています。

ThisWorkbook オブジェクトではなくて、ThisWorkbook モジュールだと思いますが、Public キーワードをプロシージャにつけたからと言って、それで呼び出されるようにはなりません。良いVBAのテキストをみると、Public キーワードの説明は、変数に関してのみのはずです。

VBAでは、Public キーワード／ステートメントは、プロジェクトの範囲をスコープ（適用範囲）とするもので、また、VBAでは、変数につけるものです。基本的には、標準モジュールに書かれたプロシージャは、暗黙的にPublic をつけたものとみなされます。つまり、明示的にPublic キーワードを用いるだけです。

サンプルを用意しましたので、試してみてください。
プロシージャのスコープと、変数のスコープを比べられると思います。
二つのブック(Book1とBook2)を使います。なお、最初の二つのプロシージャは、状況がわかったら、コメントアウトして切り替えてみたりしてください。実行は、カーソルをそのプロシージャ内で、F5かF8(ステップイン）で行ってください。

最初、Book2 のほうは閉じてお使いください。

全て、標準モジュール側から実行させます。

全体のおおよそのことが分ったら、三つ目のブックを用意してみてください。単に開けるだけで結構です。また、反応が変わるはずです。

'Book1の標準モジュール
'----------------------------------------------
Public myTime1 As String
Sub TestSample1()
'ThisWorkbook への呼び出し
　'最初のコードはプロシージャを呼び出せません
　'コメントアウトしたものを入れ替わり使ってみてください。
　 Test1
　'ThisWorkbook.Test1
　 MsgBox myTime1
　 Test2
　'ThisWorkbook.Test2
　 MsgBox myTime2
　 'MsgBox myTime1
　End Sub
Sub TestSample2()
'ThisWorkbookへの呼び出し
　On Error Resume Next
　'この二つのプロシージャは呼び出せません
　'TestSamle1 のようにThisWorkbook. をつけると呼び出せます。
　 Call Test1
　 MsgBox myTime1
　 Call Test2
　 MsgBox myTime2
End Sub
Sub TestSample3()
'変数のスコープの違い
　Call ThisWorkbook.Test2
　MsgBox myTime1
　MsgBox myTime2 '失敗
　MsgBox myTime3 '失敗
End Sub
Sub TestSample4()
　'TestBook2へのThisWorkbookの呼び出し
　Application.Run "Book2.xls!ThisWorkbook.Test4"
　MsgBox myTime4
End Sub
Sub TestSample5a()
'TestBook2へのThisWorkbookの引数付きの呼び出し
Dim myTime5 As String
　Application.Run "Book2.xls!ThisWorkbook.Test5", myTime5
　MsgBox myTime5
　MsgBox myTime1　'状況によって反応が変わります。
End Sub
Sub TestSample6a()
'TestBook2への標準モジュールの関数の呼び出し
Dim myTime6 As String
　myTime6 = Evaluate("'Book2.xls'!Test6()")
　MsgBox myTime6
End Sub

Sub TestSample6b()
'TestBook2への標準モジュールのPublic関数の呼び出し
Dim myTime6b As String
　myTime6b = Evaluate("Test6b()")
　MsgBox myTime6b
End Sub

'----------------------------------------------
'Book1 のThisWorkbook

Public myTime2 As String
Public Sub Test1()
　myTime1 = "myTime1:" & Format$(Now)

End Sub
Sub Test2()
Dim myTime3 As String
　myTime1 = "myTime1:" & Format$(Now)
　myTime2 = "myTime2:" & Format$(Now)
　myTime3 = "myTime3:" & Format$(Now)
End Sub

'=============================================
'ブックを開いた状態と閉じた状態で比較します。
'ただし、Book2は、Book1 と同一のフォルダにあるとします。
'Book2 の標準モジュール
Function Test6(Optional myTime6 As String)
　Test6 = "myTime6:" & Format$(Now)
End Function

Public Function Test6b(Optional myTime6b As String)
Test6b = "myTime6b:" & Format$(Now)
End Function

'----------------------------------------------
'Book2 のThisWorkbook
Public myTime4 As String
Public Sub Test4()
　myTime4 = "myTime4:" & Format$(Now)
End Sub
Sub Test5(myTime5 As String)
　myTime5 = "myTime5:" & Format$(Now)
　myTime1 = myTime5
End Sub

'Book2 の標準モジュール
Function Test6(Optional myTime6 As String)
　Test6 = "myTime6:" & Format$(Now)
End Function

'//

サンプルを試してみると良く分るかと思います。

結論:
ThisWorkbookのプロシージャにPublic キーワードをつけても、つけなくても変わりはありません。#1 さんの例も良いと思います。そのコードから、Public を取り除いて、実行してみてください。結果は同じです。つまり、逆に、呼び出されたくないものがあります。その場合は、必ず、Private キーワードを付けなくてはなりません。

なお、他のブックと連結して使うときは、基本的には、「参照設定」して、共有する方法があります。参照設定すると、他のブックの連結状態は、また変化しますが、それはご自身で確認してみてください。基礎とは言うものの、なかなか覚えにくく、分りにくいものだと私は思います。VBAは、時々、可読性よりも実効性を優先するために変則的な書き方をします。これは、チームを組んで開発することがないから、ということと、スクリプト言語というのが理由です。その点で、実際のコードの書き方（最適化法）は、原則からは多少外れているように感じます。

お礼

回答ありがとうございました。
詳しい説明とサンプルまで書いて頂き、ありがとうございました。