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

`cells(2,"C")` is poiting to the sheet you want (Activesheet)? — FunThomas, Feb 12 '21 at 14:52
@FunThomas sure it is. My problem is that the result is rounded to 1 but i don't want it to be rounded — TourEiffel, Feb 12 '21 at 14:53
Going to be hard to reproduce but several issues perhaps: use `Worksheet.Evaluate` instead of `Application.Evaluate`. Then `ThisWorkbook.Worksheets("Fonction DCF ").Range("U8:U31").Parent.Name` can be simplified to just `"Fonction DCF "`. — BigBen, Feb 12 '21 at 15:05
@BigBen Yeah I know but its an interpolation formula from reuters, I can't make a reproducible example — TourEiffel, Feb 12 '21 at 15:07
Why do you use `ThisWorkbook.Worksheets("Fonction DCF ").Range("U8:U31").Parent.Name` - this will resolve in the sheetname, so why not use "Fonction DCF " directly? Same for the Range address. Write the formula into a variable, check it with the debugger and pass the variable to the `Evaluate` function. `Evaluate` itself does not round anything (easily to show with `evaluate("3/4")` — FunThomas, Feb 12 '21 at 15:07
No, via the API, e.g. [like this](https://community.developers.refinitiv.com/questions/17527/how-to-call-adinterp-in-vba-code.html)? — BigBen, Feb 12 '21 at 15:14
No, I mean `x = AdInterp(args)`. You're making the Excel calc engine evaluate a function, but you're already in code, the calc engine doesn't need to get involved - just invoke the function *if it's in scope* (see @BigBen's comment above, looks like it's indeed possible just need to import the function from the DLL). — Mathieu Guindon, Feb 12 '21 at 15:15
@MathieuGuindon Sure I will pass by the DLL then, I will give u feedbacks — TourEiffel, Feb 12 '21 at 15:16

score 2 · Accepted Answer · edited Feb 12 '21 at 16:39

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.

I would still advocate to write the formula into an intermediate variable (and check it with a `Debug.Print`) before throwing it to the `Evaluate`-Function. — FunThomas, Feb 12 '21 at 15:38
Invoking the function solve my issue. But with calculation engine i still have the same issue, thank you — TourEiffel, Feb 12 '21 at 16:35
@Dorian does the last snippet work as intended though? I fully expected the direct invoke to work, but I'm curious whether the implicit ActiveSheet references had something to do with the unexpected outputs :) — Mathieu Guindon, Feb 12 '21 at 16:36
@MathieuGuindon nop I had to modify it and to add reference to my wokbook to... and the syntax I used is slightly different, i will suggest an edit :) — TourEiffel, Feb 12 '21 at 16:38
@Dorian aye, `FunctionDCFSheet` would be undefined if you don't make that the sheet's `(Name)` - there is no need to pull from `Worksheets` by name, a sheet that exists at compile-time in `ThisWorkbook` =) — Mathieu Guindon, Feb 12 '21 at 16:51

How to fix .Evaluate rounding result

Question :

Below several test that I have done

1 Answers1