How to fix .Evaluate rounding result

Question

I am using Evaluate in order to execute this formula :

x = Application.Evaluate("AdInterp(" & cells(2,"C").value & "," & "'" & ThisWorkbook.Worksheets("Fonction DCF ").Range("U8:U31").Parent.Name & "'!" & ThisWorkbook.Worksheets("Fonction DCF ").Range("U8:U31").Address(external:=False) & "," & "'" & ThisWorkbook.Worksheets("Fonction DCF ").Range("V8:V31").Parent.Name & "'!" & ThisWorkbook.Worksheets("Fonction DCF ").Range("V8:V31").Address(external:=False) & ",IM:CUBD)")

It returns 1 (Seems that the .Evaluate round result). The excepted output is : 1,00042406864688

x is a double type so i don't understand why the result is rounded ... any idea ?

Question :

Did I make a mistake building the formula in order to evaluate it ?

---

Below several test that I have done

When I put the formula in a cells the output is : 1,00042406864688

Excel worksheet formula :

=AdInterp(C2;'Fonction DCF '!U8:U31;'Fonction DCF '!V8:V31;IM:CUBD)

Execution Window :

Note : When I put formula in cells and I do

Evaluate(Cells(2,"G").Formula)

the output is : 1,00042406864688

Answer 1

I would consider invoking the function directly, rather than getting the calculation engine to evaluate it.

But it should work regardless. I suspect it's working, even - but implicit ActiveSheet references are throwing a wrench into it.

This Cells member call isn't qualified:

& cells(2,"C").value &

That's implicitly referring to cells on whatever the active worksheet is. Contrast with:

ThisWorkbook.Worksheets("Fonction DCF ") & "!" ...

That means the input array being passed to the function depends on what sheet is active when that instruction runs, so if the intent is to work off the "Fonction DCF " sheet, I'd recommend being explicit about it.

Select the "Fonction DCF " sheet module in the Project Explorer (Ctrl+R), then press F4 to bring up the Properties toolwindow, and look at the (Name) property of that sheet. If it says Sheet1 (or similar - that's the default), change it to FonctionDCFSheet, then change your code to refer to that sheet using its programmatic name (I'm assuming the sheet exists in ThisWorkbook at compile-time) - also as @FunThomas aptly suggested, consider pulling the dynamically-evaluated string into its own local variable, to make it easier to debug:

With FonctionDCFSheet
    Dim dynamicEval As String
    dynamicEval = "AdInterp(" & .Cells(2,"C").value & "," & _
                  "'" & .Name & "'!U8:U31," & _
                  "'" & .Name & "'!V8:V31, IM:CUBD)"
    Debug.Print dynamicEval
    x = .Evaluate(dynamicEval)
End With

(line continuations to reduce horizontal scrolling)

Note that I also cleaned up / removed a number of extraneous steps: you don't need to get a Range of cells and then get the Parent of that Range to get the Name of a Worksheet you're already dereferencing by name, and you don't need to get the Address of a Range for a range of cells you're already dereferencing by address.

Also because we're invoking Worksheet.Evaluate now (the .Evaluate call is qualified by the With block variable), the string formula gets evaluated in the context of that sheet, so the sheet-naming within the string becomes superfluous and the string can be further simplified:

    CreateAdxUtilityModule.AdInterp(Periode, ThisWorkbook.Worksheets("Fonction DCF ").Range("U8:U31").Value, ThisWorkbook.Worksheets("Fonction DCF ").Range("V8:V31").Value, "IM:CUBR")

'Periode  is a date format dim

And just now I'm noticing that the last part IM:CUBD is also being evaluated unqualified; with Application.Evaluate that is passing IM:CUBD of the ActiveSheet; with Worksheet.Evaluate that is passing the same columns, but always on the correct sheet, no matter which sheet is active.