Best Way to Declare frequently-used Objects

[shawnvw]

Here's my setup:

A Master Workbook:

Please Login or Register  to view this content.

Several tables in the master workbook:

Please Login or Register  to view this content.

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.

[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.

[shawnvw]

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?

[Bernie Deitrick]

You can use a procedure to set all the values once, and any values that need to change can be changed within the code.

The workbook to use could be read from a cell on one of the sheets, so there will be no need to change the code:

Please Login or Register  to view this content.

[cyiangou]

...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.

Please Login or Register  to view this content.

[FerJo]

Why not try using Static variables?

Please Login or Register  to view this content.

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.

[shawnvw]

Why not try using Static variables?

Please Login or Register  to view this content.

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?

[cyiangou]

Static only works if there is only one procedure that needs to use the cached object, which is why I deliberately gave 2 calling procedures in my example above.

[Kyle123]

Variation on a theme:

Please Login or Register  to view this content.

[cyiangou]

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.