CODEWITHSUNDEEP
excelvbaevaluatenamed-ranges
ptownbro
ptownbro

Reputation: 1298

Excel VBA Evaluate Function Wrong When Reference is Not Active Sheet for Named Range

EDITED WITH BETTER EXAMPLE

I'm trying to use the Evaluate function to evaluate a formula reference for a named range. However, when using the Evaluate function, if you do not explicitly state the sheet reference along with the cell reference, it will assume the active sheet as the cell reference. This causes the wrong result

In my real project I'm trying to only evaluate a part of the named range's formula, so it makes it even trickier.

Using a basic example of what I'm trying to do, let's say you have the following formula in Sheet 1 cell A1 whose name is MyCell:

="Don't evaluate this part"&"My Result Is " & A2

If the Active Sheet is Sheet 2 and you run the following code it will give you the wrong results (this is a quick and dirty example to illustrate the problem)

Dim s As String
s = Replace(Range("MyCell").Formula, """Don't evaluate this part""&", "")
Debug.Print Evaluate(s)

Instead of giving me the value that is in cell A2 of Sheet 1, it gives me the value that is in cell A2 of Sheet2.

Any ideas around this?

This is closest I found, but it is not my exact problem (despite similar titles) and it doesn't provide a solution: Excel VBA evaluate formula from another sheet

Upvotes: 1

Views: 2509

Answers (3)

Charles Williams
Charles Williams

Reputation: 23505

Its nearly always better to use Worksheet.Evaluate rather than the default Application.Evaluate: as Mark Balhoff points out that allows you to control unqualified references. But Worksheet.Evaluate is also usually twice as fast as Application.Evaluate. See my blog post here for details https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

Upvotes: 1

Mark Balhoff
Mark Balhoff

Reputation: 2356

The problem you are having is that by design Excel will assume all unspecific cell references are referring to the existing worksheet. This is why whenever possible it is recommended to explicitly state the worksheet in all code.

The cleanest way (verified with some MSDN definintion hunting) is to just explicitly state the worksheet without activating it:

Sub test2()
    Debug.Print Range("MyCell").Worksheet.Evaluate(Range("MyCell").Formula)
End Sub

Alternatively this code will change the active worksheet to the correct one and then change it back after evaluation. Not recommended to perform sheet activations like the code below without extenuating circumstances. Not even here.

Sub test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim s As String
    s = Replace(Range("MyCell").Formula, """Don't evaluate this part""&", "")
    Range("MyCell").Worksheet.Activate  ' Don't remember if .Worksheet or .Parent ??
    Debug.Print Evaluate(s)
    ws.Activate
End Sub

As pointed out in the comments by ThunderFrame, it is important to remember that this code assumes MyCell is a simple cell reference as stated in the question. Otherwise you will need to use other methods to determine the target worksheet name (or hardcode it).

Upvotes: 2

ThunderFrame
ThunderFrame

Reputation: 9461

Your line:

Debug.Print Evaluate(Range("MyCell").Formula)

is equivalent to:

Debug.Print Evaluate("=""My Result Is "" & A2")

which is why you get results according to the value of A2 in the ActiveSheet.

If you want to inspect the contents of the formula, you can use this line:

Debug.Print [MyCell].Formula

If you want the value of MyCell with respect to Sheet1, then you have 2 options:

1 - Use Debug.Print Range("Sheet1!MyCell").Value

2 - Use Debug.Print Sheet1.Range("MyCell").Value

Upvotes: 0

Related Questions