Excel UDF with two workbooks open

user_13262569 0
Viewed 59

I'm using a simple excel UDF to evaluate a string as a formula.

Function Eval(Ref As String)
           
    Application.Volatile
    Eval = Evaluate(Ref)
        
End Function

The strings are stored in a data table maintained by accounting, and I'm using power query to bring that into a margin sheet. My problem is there is the is a likelihood of a user having multiple workbooks open at the same time. Is there a way to keep it from trying to recalculate based on the other sheet?

The more I think about it the more I realize I should just have them store the formulas as formulas and use the 'show formulas' button.

But I'd still love to know if there's an answer to my question.

user-defined-functions vba excel
2 Answers

You want the Evaluate to be performed in the context of the worksheet from which the function is called.

You can get a reference to that sheet with Application.ThisCell.Parent:

Eval = Application.ThisCell.Parent.Evaluate(Ref)
user_92458530

If you want to reference a specific workbook, you can do so in the reference itself like this:

Ref = "[WorkbookFileName.xlsm]" & Ref

Or for the current workbook (this will resolve to the currently executing macro's workbook):

Ref = "[" & ThisWorkbook.Name & "]" & Ref

You could also refer to a specific sheet:

Ref = "SheetName!" & Ref

Or

Ref = "[WorkbookFileName.xlsm]SheetName!" & Ref

Then you can perform the Eval.

user_127070300
Related