CODEWITHSUNDEEP
vbaexcel
rajeev
rajeev

Reputation: 137

VBA Worksheets.Calculate not working with Evaluate referring to expression on another sheet

I am intrigued by the VBA statement Worksheets.Calculate. It appears to me that it does not work correctly with custom function using Evaluate Statement when the function refers to cells on another sheet within the same workbook.

On Sheet1 I have Column A with just plain numbers.

Sheet2 in same workbook Column C has basic expressions on Column A Cells like A1+A2 etc.

I have this custom function in VBA Module

Function MyEval(Val1 As String)
    Application.Volatile
    MyEval = Evaluate(Val1)

End Function

And I put this formula =MyEval(Sheet2!C1) in say B1 in Sheet1 and drag it down. This works fine for the first time. It takes in Expression in Sheet2:C1 passes it to MyVal, inside evaluates and returns the result.

The issue is the moment I edit the string expression in Sheet2, this entire formula returns 0. I need to Press F9 on Sheet1 to recalculate.

I have put Application.Volatile in MyVal and Worksheets("Sheet1").Calculate in Worksheet Change event in Sheet2.

Still it does not work. The only way to make it work is Press F9 on Sheet1.

Is this expected behavior? Am I missing something. How to I make a custom formula in VBA using Evaluate statement recalculate when there’s worksheet change event and the formula refers to range on another sheet?

No issue if the reference is in the same sheet. It works fine though. I have Excel 2013. Thanks.

Screenshot

Upvotes: 0

Views: 590

Answers (1)

Rory
Rory

Reputation: 34075

The problem is that your code is calling Application.Evaluate which works in the context of the active sheet. If you want it to evaluate in the context of the sheet the function is on, you should use the worksheet.evaluate method like this:

Function MyEval(Val1 As String)
    Application.Volatile
    MyEval = Application.Caller.Worksheet.Evaluate(Val1)

End Function

Upvotes: 2

Related Questions