Workbook reference without depending upon the name

[syswizard]

I'm moving all of the VBA code from an XLSM to a common XLAM that will now be a reference from the XLSM.
The XLSM will have no code, but only worksheets with data and tables.
Because of the extensive use of dynamic named range references in my code [tbData[products]], etc....I had to do an "thisworkbook.Activate" quite often.
Now I can't do that of course....I need the workbook's reference or handle.
Users will want their own "named" workbook.
I can think of two ways to do this:
1) force them to use a standard name for the first part of the WB name....i.e. "XLRMaint....." followed by their own name or special string
2) embed a keyword into the workbook and look for that in Properties when referencing.
To do either of the above, I must iterate all of the open workbooks each time I need a handle on the workbook containing the data.
Of course, these approaches will fail if they open more than one copy of the special workbook....that's another problem that will require an application-level Open event handler.
Another much less desired approach would be to force them to have ONLY the one workbook open....thus, Activeworkbook is always the same.
They are not going to like that however.

Looking for the best approach here...

[HansV]

I assume that the code will have to operate on the workbook that is active when the code is called, so you could have a variable of type Workbook and set it to ActiveWorkbook at the beginning of the code.

[Jan Karel Pieterse]

Can you show us a snippet of code that demonstrates what you are asking about?

[syswizard]

Essentially I think all I need to do is set a global var or public property to the workbook when it is opened....and then set it to nothing upon close.
Each users Excel instance will need to have a reference set to the addin.
The addin must detect if any workbook being opened is the one with the data sheets....which are uniquely named.
So there's another way of distinguishing the special workbook.....from the actual sheet names.
I'll need to establish a class to trap and handle the open and close events for any workbooks being opened or closed.

[Jan Karel Pieterse]

You could add a customDocument property to the workbooks "belonging" to the add-in and check for that.

[syswizard]

Yep, thanks for that....another way to go.
I've just got to determine which method is most reliable.