Address of the ExcelReference is wrong

[Sergey Zhilyakov]

Hi Govert,

I have COM Addin (not ExcelDna) that implements UI and makes some
calculations when I click on the 'Calculate' button on the toolbar of
the addin.

I have also ExcelDna addin that implements a function that takes
ExcelReference as a parameter. Then the address of this reference is
calculated via XlCall:

public static string MyFunction([ExcelArgument(AllowReference=true)]
object cells)
{
var address = (string)XlCall.Excel(XlCall.xlfReftext, xlRef,
true);
//...
}

Then in the code of COM addin I do the following (if the button
'Calculate' was clicked)

range.Calculate();

where 'range' is Microsoft.Office.Interop.Excel.Range object that
represents the cell where the XLL function is. The parameter of this
function is ExcelReference and it refers to cells in the same
worksheet, i.e. if the function is called in the 'Sheet1' worksheet,
the address could be [FileName]Sheet1!$A$1:$A$2.

If the function is called from active (!) sheet and I recalculate it
using range.Calculate(), all works fine. But if I switch to another
worksheet (e.g. 'Sheet2') and click on the 'Calculate' button again, I
get address which refers to Sheet2 instead of Sheet1.

I noticed if I recalculate entire workbook using
ExcelApplication.Calculate(), all works fine.

To fix it, I select the worksheet before the recalculation:

range.Worksheet.Select(Missing.Value);
range.Calculate();

This works, but I don't quite like it.

Is there another way to get address? Do you have an idea how this can
be fixed.

Best regards,
Sergey

[Govert van Drimmelen]

Hi Sergey,

OK, I can reproduce the problem - I paste a .dna file below.

Two points so far:
- The function needs to be marked as IsMacroType=true to get the sheet
wrong.
- If the function is in VBA the problem does not occur.

I'll look a bit more...

-Govert

<DnaLibrary Language="C#" RuntimeVersion="v4.0">
<![CDATA[
using ExcelDna.Integration;

public static class Test
{
[ExcelCommand(MenuName="Test RefText", MenuText ="Update")]
public static void Update()
{
dynamic xlApp = ExcelDnaUtil.Application;
xlApp.WorkSheets["Sheet2"].Activate();
xlApp.Range["Sheet1!A1"].Formula="=MyMacroTypeFunction(Sheet1!
$B$1)";
xlApp.Range["Sheet1!A2"].Formula="=MyFunction(Sheet1!$B$2)";
xlApp.WorkSheets["Sheet1"].Activate();
}

[ExcelFunction(IsMacroType=true)]
public static string MyMacroTypeFunction(
[ExcelArgument(AllowReference=true)] object cells)
{
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cells);
return sheetName;

}

public static string MyFunction(
[ExcelArgument(AllowReference=true)] object cells)
{

string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cells);
return sheetName;
}
}

]]>
</DnaLibrary>

[Govert van Drimmelen]

Some more tests below.

Excel references can be either 'internal' references, cells without
sheet info, or 'external' references which contain sheet information.
In the calls we are testing, Excel is passing an 'internal' reference
to Excel-DNA, even though the sheet is explicitly named in the
argument - I guess Excel figures out that it is the 'same' sheet as
the function. Now Excel-DNA tries to convert to an ExcelReference,
which is essentially an 'external' reference which has a SheetId too.
In converting an 'internal' reference to an 'external' reference,
Excel-DNA needs to determine the current sheet's SheetId. This step
seems to be going wrong in this case.

Internally, Excel-DNA tries to get the Current sheet (in
XlCallImpl.GetCurrentSheetId12()) by calling xlSheetNm, following the
documentation here: http://msdn.microsoft.com/en-us/library/bb687895.aspx.
Excel-DNA passes an 'external reference' with SheetId zero. The
Microsoft documentation says: "If the ID is set to zero, xlSheetNm
returns the name of the current sheet." This is not happening when our
function is registered as IsMacroType=true (this causes registration
with a # type, discussed here: http://msdn.microsoft.com/en-us/library/bb687900.aspx,
under the heading 'Registering Worksheet Functions as Macro Sheet
Equivalents').

The tests below show what is going wrong more directly:
* Without passing in the ExcelReference as an argument, but calling
xlSheetNm directly from the function shows the underlying problem (in
the IsMacroType=true case). The functions TestDirect / TestMacroDirect
show this, with TestMacroDirect returning the wrong sheet when another
sheet is active.
* Using xlfCaller does not seem to have this problem - the sheet
returned by caller seems to correctly be the Current sheet, whether
IsMacroType=true or not.

I'd have to think carefully whether the caller's sheet is always the
right sheet to use when converting from an 'internal' reference to an
'external' reference.

I'm afraid we're getting into details of the Excel SDK and the C API
which is beyond my expertise.

Are your functions marked IsMacroType=true?
Do they have to be?

Regards,

Govert


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

using System;

using ExcelDna.Integration;

public static class Test
{
[ExcelCommand(MenuName="Test RefText", MenuText ="Update")]
public static void Update()
{
dynamic xlApp = ExcelDnaUtil.Application;
xlApp.WorkSheets["Sheet2"].Activate();

xlApp.Range["Sheet1!A1"].Formula="=TestFunction(Sheet1!B1)";
xlApp.Range["Sheet1!
A2"].Formula="=TestMacroTypeFunction(Sheet1!B2)";
xlApp.Range["Sheet1!A3"].Formula="=TestDirect()";
xlApp.Range["Sheet1!A4"].Formula="=TestMacroTypeDirect()";
xlApp.Range["Sheet1!A5"].Formula="=TestCaller()";
xlApp.Range["Sheet1!A6"].Formula="=TestMacroTypeCaller()";
xlApp.WorkSheets["Sheet1"].Activate();
}

[ExcelFunction(IsMacroType=true)]
public static string TestMacroTypeFunction(

[ExcelArgument(AllowReference=true)] object cells)
{
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cells);
return sheetName;
}

public static string TestFunction(

[ExcelArgument(AllowReference=true)] object cells)
{
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cells);
return sheetName;
}

public static string TestDirect()
{
// Make an ExcelReference with SheetId = 0.
ExcelReference xlRef = new
ExcelReference(0,0,0,0,IntPtr.Zero);
// Returns the Sheet Name of the *Current* sheet
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
xlRef);
return sheetName;
}

[ExcelFunction(IsMacroType=true)]
public static string TestMacroTypeDirect()
{
// Make an ExcelReference with SheetId = 0.
ExcelReference xlRef = new
ExcelReference(0,0,0,0,IntPtr.Zero);
// Returns the Sheet Name of the *Current* sheet
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
xlRef);
return sheetName;
}

public static string TestCaller()
{
ExcelReference xlRef =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
xlRef);
return sheetName;
}

[ExcelFunction(IsMacroType=true)]
public static string TestMacroTypeCaller()
{
ExcelReference xlRef =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
xlRef);
return sheetName;
}

}

]]>
</DnaLibrary>

[Govert van Drimmelen]

We need this info about Worksheet References which discusses Current
vs. Active and Internal vs. External references:
http://msdn.microsoft.com/en-us/library/bb687847.aspx.

-Govert

[Govert van Drimmelen]

Hi Sergey,

I've checked in to CodePlex a version that should fix this bug.

Could you please have a look?
http://exceldna.codeplex.com/SourceControl/list/changesets

Thanks for reporting this :-)

Regards,
Govert

[Sergey Zhilyakov]

Hi Govert,

thanks for the quick response. Now it works as expected!

I would thank you for such amazing project as ExcelDna! :)

Best regards,
Sergey

On Nov 11, 12:53 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Sergey,
>

> I've checked in to CodePlex a version that should fix this bug.
>

> Could you please have a look?http://exceldna.codeplex.com/SourceControl/list/changesets

[Lamon]

Salut Govert,

I had the same issue. I though that this was some kind of limitation
(DNA or C api).

I initialy solved the problem by creating a new reference using the
UDF caller sheetid.

Public static object MyUDF([allowreference = true] object param){
excelreference xlr = (excelreference) param;
Excelreference xlref = new excelreference(xlr.rowfirst,
xlr.rowlast, xlr......, caller.sheetid)
}
It worked OK until I realized that some user had param referencing a
sheet different than where the function is called.

I went further by comparing the bookname of the caller with the book
name of param:

If(bookname(caller) != bookname(param))
Excelreference xlref = new excelreference(xlr.rowfirst,
xlr.rowlast, xlr......, caller.sheetid)
Else
Excelreference xlref = new excelreference(xlr.rowfirst,
xlr.rowlast, xlr......, param.sheetid)

I will checked this latest upgrade against myUDF.

Thank you for your support,

Guy

> > > > > > I noticed if I recalculate entire...
>
> plus de détails »