xlfEvaluate and alternatives

[Morné Loubser]

Hi all,

I wrote a little UDF using the (frankly brilliant) Excel-dna, but I have run into a wall.

The function basically gets a value from another workbook (using a specified workbook name, sheet name, column & row).

A small C# snippet is as follows:

tempFormula = "='" + wProperName + SheetName + "'!" + ColumnName + RowNum.ToString();

retval = XlCall.Excel(XlCall.xlfEvaluate, tempFormula);

When I tell the function to return the "tempFormula"string, and I copy the string to any other cell and calculate that cell, then it works (but Excel gives the normal "Links to 'xyz.xlsm' were not updated because 'xyz.xlsm' was not recalculated before it was last saved" error.

It works perfectly if the workbook in question is the one running the UDF (even for another sheet), but gives #REF! errors for other workbooks.

Please help! Is there any way to tell it to still return the value regardless of whether or not it was recalculated before it was saved?

Thank you!

Morne

PS I have tried LinqToExcel, it worked but it returned the formatted number (for example if you are expressing 10 000 as 10 - i.e. in '000 - then only 10 is returned), and this is problematic.

[Govert van Drimmelen]

Hi Morne,

What is the exact string that you are passing to xlfEvaluate?

The book name needs to be in square brackets, so it might be something like: "[MyBook.xlsx]Sheet1!A1"

An easy function to test with just takes the formula:

    public static object DoEvaluate(string formula)

    {

        return XlCall.Excel(XlCall.xlfEvaluate, formula);

    }

Then if you make a workbook called MyBook, this should work from another workbook:

=DoEvaluate("[MyBook.xlsx]Sheet1!A1")

Or if you want to add the path, you'll have to add the single quotes in just the right place:

=DoEvaluate("='c:\Test\[MyBook.xlsx]Sheet1'!A1")

It looks like xlfEvaluate (or Application.Evaluate, which should be equivalent) works with books that are not open in the current Excel session.

This is different to entering the formula in a cell, which does allow you to refer to a workbook that is not open. I don't know of documentation that discusses this, but calling Application.Evaluate in the VBA Immediate Window seems to behave the same as xlfEvaluate.

-Govert

[Morné Loubser]

Hi Govert,

Thanks for the quick response!

I am aware that the workbook name needs to be in square brackets (hence the "wPoperName"). When I output the string (tempFormula in my example) and I copy and paste it somewhere else and calculate that cell, then it works but with the prompt. 

So I guess this is something that is not possible with the Excel API.

Thanks again!

Morne

[Govert van Drimmelen]

Hi Morne,

Is the workbook you are trying to reference from the UDF already open in the Excel session?

Does the formula string work when you try with Application.Evaluate(...) in the VBA immediate window?

-Govert

[Morné Loubser]

Hi Govert,

No the workbook is not already open. In VBA you can suppress warnings if you write a macro, but the code I am writing has to be a function. I had the same problem when I wrote a UDF in VBA. The immediate window also gives the same error (error 2023).

Thanks again,

Morné

[Govert van Drimmelen]

Hi Morne,

OK - I think this is a difference between how the formula is evaluated on a sheet and how Application.Evaluate or xlfEvaluate works.

You might post to the Excel for Developers forum, and ask why Application.Evaluate fails to dereference an unopened workbook, when the reference works in a formula.

Anyway - this does not look like an Excel-DNA issue.

-Govert

[Morné Loubser]

Hi Govert,

Thanks again for the assistance. I managed to get it resolved by using OpenXML in conjunction with Excel-dna.

Absolutely loving Excel-dna, opening up many new possibilities, once again thank you!

Morne