Ensuring the function "Evaluate(Name)" is using the correct workbook when multiple workbooks are open

[bbhgroup]

Hello Excel Geniuses. Every time I log in I learn how much I don't know about Excel. Thanks to you all.

Here's my latest conundrum. I have a workbook template that is populated externally. When this template is opened, VBA code uses takes that data and organizes it, adds formulas, populate other tabs, etc.

In this template, I store variables in the "Names" section of the workbook. Some are static, others are calculated.
I refer to these values using the Evaluate() Method from VBA.

I am pretty sure that when I open up multiple workbooks, especially at the same time, that the Evaluate method is not always pointing to the 'Names' list where the VBA macro code is running.

I have found that I should use "ThisWorkbook.Worksheets()" as opposed to just 'WorkSheets()' as this ensure the executing macro is from the correct workbook.

I don't see any similar way to refer to the Names from within the Evaluate method. There are times I can see with multiple workbooks open that the ActiveWorkbook.Name and ThisWorkbook.Names are different. It is certain that the results of the Evaluate() Method has to be confined to the correct workbook or I am sunk.

Can anyone help me with this?
Thank you all.

 

[Jerry Sullivan]

Hi bbhgroup,

In addition to the Application.Evaluate method, there is an Worksheet.Evaluate method.

Typically it is better to use the Worksheet.Evaluate method to ensure the correct context for the evaluation (it's also faster, which only matters if you are doing a process that requires numerous calls to Evaluate.

To simply ensure the expression is evaluated from the context of the current workbook, you could use...

ThisWorkbook.Worksheets(1).Evaluate(expression)

Or to ensure the expression is evaluated from the context of a specific worksheet, you could use...

ThisWorkbook.Worksheets("MySheetName").Evaluate(expression)