ExcelReference.GetValue() throws XlCallException

[Carlos]

Hi Govert,

 

I'm running into a problem similar to the one described on https://groups.google.com/forum/?fromgroups#!topic/exceldna/8z1no1PcVeg.

 

I have a function (see below) which accepts reference parameters. This function is typically inserted into columns of tables that are conected to external data. This way, when the data is refreshed, the columns with the formulas grow/shrink accordingly.

 

Sometimes after I refresh the external data on some heavy workbooks, the calls to the function start to fail randomly, due to a XlCallException on the GetValue() call. When I hit F9, I can see the cells on the table switching from the correct value to #VALUE.

 

Uninstalling the addin and reinstalling it does not solve the issue. I need to close Excel entirely.

 

Do you have any clues on to how to attack this issue? If I want to return the value of a specific cell within the range represented by the ExcelReference, is the approach below the correct one?

 

Best regards,

Carlos

 

 

 

<DnaLibrary Language="CS" RuntimeVersion="v4.0">
<![CDATA[

using System;
using ExcelDna.Integration;

public class Test
{
    [ExcelFunction(
        Name = "TestLookup",
        IsThreadSafe = true,
        IsMacroType = false)]
    public static object TestLookup(
        object lookupValue,
        [ExcelArgument(AllowReference = true)] object lookupRange,
        [ExcelArgument(AllowReference = true)] object valuesRange,
        [ExcelArgument()] bool rangeLookup)
    {

        //Cast input to ExcelReference
        ExcelReference lookupRangeAsRef = (ExcelReference)lookupRange;
        ExcelReference valuesRangeAsRef = (ExcelReference)valuesRange;

        //Use match find the value we're looking for
        object indexNumber = XlCall.Excel(XlCall.xlfMatch, lookupValue, lookupRangeAsRef, (rangeLookup) ? 1 : 0);

        if (indexNumber is ExcelError) return indexNumber;
        else
        {

            //Create a new reference that points to the cell being searched

            int indexNumberInt = (int)((double)indexNumber);

            int row = valuesRangeAsRef.RowFirst + (indexNumberInt - 1);
            int column = valuesRangeAsRef.ColumnFirst;

 

            ExcelReference xlRef = new ExcelReference(row, row, column, column, valuesRangeAsRef.SheetId);

            return xlRef.GetValue();
        }

    }
}

 

]]>

</DnaLibrary>

[Govert van Drimmelen]

Hi Carlos,

I'm a bit surprised that the ExcelReference.GetValue() call works at
all - I'd expect this to require an IsMacroType=true tag on the
function.
I'd suggest testing with IsThreadSafe=false and IsMacroType=true, and
if that works well, try changing each back to your current settings
individually.

I'm guessing there is some interaction with the threading,
recalculation and sheet restructuring - perhaps the reference is
changed while your function is running on some other thread.

Regards,
Govert

On Jun 20, 6:02 pm, Carlos <jourdan.gade...@gmail.com> wrote:
> Hi Govert,
>

> I'm running into a problem similar to the one described onhttps://groups.google.com/forum/?fromgroups#!topic/exceldna/8z1no1PcVeg.

[Carlos]

I've been working with these types of thread safe non-macro types for well over an year now, and they have worked fine until I introduced large workbooks with external data queries.

 

I'll try to do what you sugested, but this really beats the point I'm trying to achieve with ExcelDNA, which is to have a few small, efficient, thread-safe functions to speed up development and execution of large workbooks on multi-core computers.

 

Is there any way to keep the IsMacroType and ThreadSafe attributes, and still work with the lightweight ExcelReference object? Note that in this particular case, I would think that I never broke the calculation chain, because the ExcelReference I'm creating is always contained within the ExcelReference that was passed as a parameter.

 

Best regards

Carlos

[Carlos]

Btw, isn't ExcelReference a valid return type for UDFs? Shouldn't I be able to just return the reference directly to Excel, and have it handle the fetching of the data? If I try that, I see no exceptions on my UnhadledExceptionHandler, but the return values are all #VALUE.

[Govert van Drimmelen]

Hi Carlos,

I'm speculating . . . :

Maybe IsMacroType=true is not needed when you read from a range that is inside your formula's caller. But when the reference is resized, that reference temporarily points outside the calling range (because of timing with the multi-threaded recalculation), and now returns #VALUE.

I don't know how Excel deals with the combination of the threading, macro-type and GetValue you have, but Excel-DNA doesn't do anything funny in any of those cases. So whatever behaviour you see, that's just how Excel works, and I can well imagine there are some limitations or even bugs when the combination of multi-threaded recalculation, and value dereferencing is going on. Excel-DNA gives you all the options that Excel allows us.

I only added ExcelReference as a valid return type in a recent check-in on CodePlex. You'll have to download from here to experiment with that option: https://exceldna.codeplex.com/SourceControl/list/changesets

Please write back if you learn more.

Regards,

Govert

[Carlos]

So here's what I have been able to find so far: setting IsMacroType=true seems to solve the issue (I`m not entirely sure, because the error is somewhat hard to reproduce).

 

However, I have now run into a calculation problem that is extremely dangerous, because it`s very silent.

 

Just in case it`s not clear, the primary goal of the UDF in question is to serve as semantic suger for excel formulas that look like OFFSET(VALUES_RANGE, MATCH(LOOKUP_ID,IDS_RANGE,0)-1,0,1,1), which is a much better practice than to use the VLOOKUP function, eliminating the fragile LOOKUP_COLUMN parameter and allowing to search for values even when the IDS_RANGE is not exactly to the left of the VALUES_RANGE.

 

So here`s the scenario that I have. I have a table with several columns that come from an external data, and 3 columns that are created in excel: GROUP_ID, ITEM_PROPERTY and GROUP_PROPERTY.

 

GROUP_ID is entered by hand.

ITEM_PROPERTY is a concatenation of the GROUP_ID with other columns in the table

GROUP_PROPERTY is the formula TestLookup([@GROUP_ID];[GROUP_ID];[ITEM_PROPERTY];FALSE)

 

Note that GROUP_ID is not unique. What I`m trying to do is to get the ITEM_PROPERTY of the first element of the given group.

 

Now suppose that you have the following data:

 

GROUP_ID, ITEM_PROPERTY, GROUP_PROPERTY

1, 1-a, 1-a

1, 1-b, 1-a

1, 1-c, 1-a

2, 2-d, 2-d

2, 2-e, 2-e

 

And now we change the GROUP_ID in the third row to 3. Suprinsingly, we get the following results:

1, 1-a, 1-a

1, 1-b, 1-a

3, 3-c, 1-c

2, 2-d, 2-d

2, 2-e, 2-e

 

Note that the correct result for the formula should be "3-c". "1-c" isn't even present on the worksheet anymore!

 

What I'm working out from this is that, when previously calculating the value of cell [3,3], Excel only registered the dependance to cell [1,2], which is the one where GetValue() was called. That's despite the fact tath the whole ITEM_PROPERTY column was passed on to the function.

 

So now, when processing the change on cell [3,1], the first thing it does is to fire up the call to the TestLookup function, getting the value "1-c". It's only after that the cell [3,2] is updated to "3-c".

 

This behaviour is found on a specific workbook, when the IsMacroType property is set to true. It is independent of the IsThreadSafe attribute. The biggest trouble is that I have been unable to reproduce this problem on a small standalone workbook, probably because of the complex calculating chain of the rest of the original workbook.

 

This problem has put me between a rock and hard place. IsMacroType=true seems to solve the original #VALUE problem. However, it creates this other unexpected behaviour, which is actually much worse because it can be very silent.

 

Do you have any thoughts on how to keep the calculation chain intact? Is there any way to make Excel understand that my formula depends on the entire range, without the need to actually get it's entire value (which would eliminate the performace gains I have been getting with ExcelDNA over VBA)?

 

Best regards,

Carlos Jourdan

[Govert van Drimmelen]

Hi Carlos,

As I understand it,

* you need IsMacroType=true to be able to read other parts of the sheet from inside your function

* calling ExcelReference.GetValue() does feed into the Excel dependency tree somehow. Functions registered with IsMacroType=true can read uncalculated cells which I think might be what you're seeing.

* combining IsMacroType=true with an AllowReference=true parameter causes Excel to consider your function Volatile, and it would recalculate on any sheet change.

You might like to have a look at Charles Williams's site: http://www.decisionmodels.com/calcsecretsc.htm

That's about the best write-up of the Excel calculation model that I know of.

Here's one snippet from one of his pages:

"For User Defined Functions Excel has to execute the function in order to determine if it contains hidden dependencies in references to cells which are not in the argument list. Note that if you put a false dependency in the argument list (a reference which is not actually used inside the function) Excel will execute the function, but not neccessarily in the sequence you expect. Also Excel will not recognise a dependency that is bypassed by an IF statement. These factors can cause problems in your UDF unless you take the appropriate precautions."

Excel-DNA does not interfere with any of this, so the behaviour you see is purely the black box of Excel.

The Excel-DNA flags map to Excel registration parameters as follows:

* IsMacroType=true registers the function with an additional # character in the xlfRegister call (http://msdn.microsoft.com/en-us/library/office/bb687900.aspx). See the section on "Registering Worksheet Functions as Macro Sheet Equivalents".

This also meantions a bit about how uncalculated cells are dealt with.

* AllowReference=true changes the parameter registration type to "U" meaning that range references can be accepted.

* Calling ExcelReference.GetValue() just does an xlCoerce call (http://msdn.microsoft.com/en-us/library/office/bb687880.aspx).

If you can get a simple example to behave (or misbehave) in a consistent way, I can try to help figure out what's going on.

Regards,

Govert

[Carlos]

Govert,

 

Thanks for the great references. I'll try to see what I can do with them. Right now I'm thinking the best approach is to mark the function as being dependant on the entire input references, using the "fake dependency" approach suggested by William.

 

One question, though: what would GetValue() return when xlCoerce is sending xlRetUncalced?

 

Carlos

[Govert van Drimmelen]

Hi Carlos,

GetValue() would probably throw an XlCallException with the xlReturn value set to XlCall.XlReturn.XlReturnUncalced.

If the XlCallException were not caught in your code, the function would return #VALUE.

Instead of calling ExcelReference.GetValue() you could get the value yourself, with 

   object result;

   XlCall.XlReturn xlReturn = XlCall.TryExcel(XlCall.xlCoerce, out result, myReference);

   // Now check xlReturn before using result...

Regards,

Govert