VBAでユーザーフォームからクラスモジュールの関数を呼び出したい

質問

ExcelのVBAでの質問です。

クラスモジュールよりユーザーフォームを呼び出し、そこから呼び出したクラスモジュールにある関数を呼び出したいと思っています。
ところが、実行すると
実行時エラー'91':
オブジェクト変数またはWithブロック変数が設定されていません。
と表示されてしまいます。

以下に、コードを抜粋します。

------------------------------------------------------------
・ClassDataBase（クラスモジュール）

Public Function EmployeeEdit()
Load DBUserEdit
DBUserEdit.Show
Unload DBUserEdit
End Function

Public Function DataBaseRead(ByVal Table As Variant, Optional ByVal Filter As Variant)
DataBaseBook.Sheets(Table).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "temp"
Range(Range("B3"), ActiveCell.SpecialCells(xlCellTypeLastCell)).Select
End Function
------------------------------------------------------------
・DBUserEdit（ユーザーフォーム）

Private Sub UserForm_Initialize()
Dim DB As New ClassDataBase
DBAuthority.List = Array("Admin", "User", "Data")
DBAuthority.Value = "Admin"
'この次の行でエラーを起こしていると思われます
DB.DataBaseRead ("社員情報")
End Sub
------------------------------------------------------------

どうすればいいのでしょうか？
よろしくお願いいたします。

回答No.3

dsuekichi · ベストアンサー

一番スマートなのは、ユーザフォームに既存のインスタンスを渡すことなんですが・・・
（たとえば、ユーザフォームにPublic変数を用意しておくとか・・・）
でも、ユーザフォームのInitializeイベントで使用するなら、その手も使えないでしょうし・・・

あまりスマートじゃありませんが、
標準モジュールを追加して、
----------------------------
Public mDB As ClassDataBase
----------------------------
としておいて、
最初に生成したインスタンスを保持しておくとか・・・

-----------------------------
・Sheet1コード
Private Sub NewDBMake_Click()
　　'一部のコードを省略
Dim DB As New ClassDataBase
　　'DataBaseFileに実際にはファイル名が入ります
Set mDB = DB '＜＝＝＝＝＝＝追加
DB.DataBaseOpen (DataBaseFile)
DB.EmployeeEdit
End Sub
-----------------------------

-----------------------------
DBUserEdit（ユーザーフォーム）

Private Sub UserForm_Initialize()
Dim DB As ClassDataBase　'＜＝＝＝Newはやめる
Set DB = mDB '＜＝＝＝追加
DBAuthority.List = Array("Admin", "User", "Data")
DBAuthority.Value = "Admin"
'この次の行でエラーを起こしていると思われます
DB.DataBaseRead ("社員情報")
End Sub
-------------------------------------

お礼

たびたびありがとうございます。
教えていただいた改良で、期待通りの動作を確認しました。
ただ、dsuekichiさんもおっしゃっているようにあまりスマートではありませんね・・・。
問題はこういう構造のコードを書いてしまった自分なのですが。
これを参考にしつつ、もっと根本的に見直してみます。
ありがとうございました。

回答No.2

dsuekichi

＃１です。

追記です。
>そこから呼び出したクラスモジュールにある関数を呼び出したいと思っています。

ってことですが・・・

DBUserEdit（ユーザーフォーム）の中の「UserForm_Initialize」で、
> Dim DB As New ClassDataBase
を行っているって事は、
このタイミングで、『呼び出したクラスのインスタンス』とは別に、
『新しい「ClassDataBase」のインスタンス』が生成されていますが・・・

ひょっとして、「『呼び出したクラスのインスタンス』では無いと動作しない」とかじゃないですよね？
＃クラスの中で、色々処理を行っていて、それらの処理が無いと駄目とか・・・

補足

申し訳ありません、テストで書いたコードなので、普通に見ると何でこのような書き方をしたかわかりませんよね。
説明不足で申し訳ありません。

ご説明に足りなかったコードを追加します。
---------------------------------------------------------
・Sheet1コード
Private Sub NewDBMake_Click()
　　'一部のコードを省略
Dim DB As New ClassDataBase
　　'DataBaseFileに実際にはファイル名が入ります
DB.DataBaseOpen (DataBaseFile)
DB.EmployeeEdit
End Sub
---------------------------------------------------------
・ClassDataBase（クラスモジュール）
Option Explicit
Public DataBaseBook As Workbook

Public Function DataBaseOpen(Optional ByVal DataBaseFile As Variant)
　　（省略、指定されたブックを開いている）
　　'DataBaseBookに開いたブックをセット
Set DataBaseBook = Workbooks(FileName)
End Function
---------------------------------------------------------
Sheet1コードの、
Private Sub NewDBMake_Click()
がまず実行されます。
そして、ブックを開き、そのブックに対してユーザーフォーム内のコードで処理したいと考えました。
ClassDataBase（クラスモジュール）の、
Public Function DataBaseRead()
を実行したかったので、『新しい「ClassDataBase」のインスタンス』を生成し、DataBaseRead()を実行しました。
（これ以外の方法がわかりませんでした）
ですが、これでは
Public DataBaseBook As Workbook
にブックがセットされていませんでした。
それでご指摘の行でエラーが起きていました。
Publicならセットされた値が保持されていると勘違いしていたのですが・・・。
このような場合、どうやって対処すればよいのでしょうか？
ご教授をお願いいたします。

説明が冗長で申し訳ありません。

回答No.1

dsuekichi

> オブジェクト変数またはWithブロック変数が設定されていません。
> どうすればいいのでしょうか？
> この次の行でエラーを起こしていると思われます
「思われます」ではなく、デバッグして、どの変数が設定さｆれていないか確認して、
必要な設定を行ってください。

ぱっと見、怪しいのは、いきなり出てきた、
> DataBaseBook.Sheets(Table).Copy After:=Sheets(Sheets.Count)
「DataBaseBook」ですが・・・

お礼

ご回答ありがとうございます。
おっしゃるとおりで、この「DataBaseBook」に値がないことが問題でした。
問題はそれをどのように解決するかなのですが・・・。
次に続きます。