Best Way to Declare frequently-used Objects

Here's my setup:

A Master Workbook:

 Set wbMasterBook = Workbooks("MasterBook Version 3.xlsm")  'As the filename implies, I will rename it periodically.

Several tables in the master workbook:

 
       Set tblRawData = wbMasterBook.Sheets("Raw Data").ListObjects(1)            ' Used in one procedure
       Set tblProcessedData = wbMasterBook.Sheets("Processed").ListObjects(1)     ' Used in several procedures
       Set tblMoreData = wbMasterBook.Sheets("More").ListObjects(1)               ' Used in several procedures, including one from a different workbook.

I'm looking for the best way to define these objects, so I only have to do it once. The procedures are all in the same module (except for the one in a different workbook). So I'm thinking:

- Can I put Set statements in the Declarations section, in the module above the first procedure?
-- Does it matter that the two latter tables are destroyed and recreated? Would I have to Set them again afterwards?
- If not, is there a way to avoid putting the Set statements in every procedure? Can I make a Function to Set them?
- But if I do, will the Function be called every time I refer to one of these tables or workbook?
- Can I use Const to define these objects? I'm not sure how to do that.


Thanks for your help.

Originally Posted by Bernie Deitrick

Use Global variables, defined just after this at the top of your codemodule:

Option Explicit
Dim wbMasterBook As Workbook
Dim tblRawData As ListObject
Dim tblProcessedData As ListObject
Dim tblMoreData As ListObject

When you set them, they will retain their value until you release them or Excel closes.

Okay. But do I have to set them in every procedure that uses them? The table names never change, so duplicating the Set statements seems redundant. What's worse is the workbook name, which changes every few days. I don't want to have to change it in every procedure.

I was thinking of having another procedure just to run the Set statements, and having it called by the other procedures. Is there a better way?

...is there a way to avoid putting the Set statements in every procedure?

It depends on what you are doing, but there are definitely circumstances within the Excel environment when you need to re-initialise your module variables.

Can I make a Function to Set them?

Yes. Well, a call of some sort.

But if I do, will the Function be called every time I refer to one of these tables or workbook?

It will, but we take measures to prevent the heavy-lifting work from being re-done when it isn't necessary.

If I was working with those objects as a related group, then I would use this approach. One nifty advantage of using a Type variable (much older brother of the class module), is that intellisense kicks in so you don't have to type out the member names.

Option Explicit

Private Type MyObjectType
    Initialised   As Boolean
    MasterBook    As Workbook
    RawData       As ListObject
    ProcessedData As ListObject
    MoreData      As ListObject
End Type

Private Obj As MyObjectType

Private Sub InitObjects()
    If Obj.Initialised = False Then
        Set Obj.MasterBook = Workbooks("MasterBook Version 3.xlsm")
        Set Obj.RawData = Obj.MasterBook.Sheets("Raw Data").ListObjects(1)
        Set Obj.ProcessedData = Obj.MasterBook.Sheets("Processed").ListObjects(1)
        Set Obj.MoreData = Obj.MasterBook.Sheets("More").ListObjects(1)
        Obj.Initialised = True
    End If
End Sub

Public Function GetWorkbookPath() As String
    InitObjects
    GetWorkbookPath = Obj.MasterBook.Path
End Function

Public Function GetWorkbookName() As String
    InitObjects
    GetWorkbookName = Obj.MasterBook.Name
End Function

Originally Posted by FerJo

Why not try using Static variables?

Sub SUB_NAME()
Static wbMasterBook As Workbook
If wbMasterBook Is Nothing Then Set wbMasterBook = Workbooks("MasterBook Version 3.xlsm")

It has the adventage that if, for some odd dark reason, Excel loses track of that variable, it checks it back on every run and re-set if needed.

Wouldn't the "If wbMasterBook" command have to be in every procedure? Or only whichever procedure I run first that day?

Yeah, I use Types as a form of 'poor mans class module', when my needs don't seem to warrant a whole module.

In the QuickBasic days, the Type was as close as you got to object-orientation.