Strabge Error using

[CSharp Dummy]

I encountered a strange error in Excel 2007 when running the folowing
code:

ExcelReference cell =
(ExcelReference)XlCall.Excel(XlCall.xlfTextref,
Result[n - 1], false);
XlCall.Excel(XlCall.xlcFormula, "=Today()", cell);

The call fails and after this any subsequent call of XlCall.Excel
independent of the context will also fail.

When I trace it, it executes the following:

return TryExcelImpl12(xlFunction, out result, parameters);

xlReturn = Excel12v(xlFunction, parameters.Length, ppOperParameters,
pResultOper);

and the Dispose() of XLCustomMarshal12.cs

Any idea?

[Govert van Drimmelen]

I'm not sure what "Result" is.

Could you make a self-contained .dna file that exhibits the mystery
behaviour?

Thanks,
Govert

[CSharp Dummy]

Govert,

I was able to narrow the problem down:

Result is the return value of an inputbox dialog.

If the result value is in the form of : ='Sheet1'!$A$1
the following works:

ExcelReference cell =
(ExcelReference)XlCall.Excel(XlCall.xlfTextref,
Result[n - 1], false);
XlCall.Excel(XlCall.xlcFormula, "=Today()", cell);

If the result value is in the form of :$A$1

The code will fail.

> > Any idea?- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi,

Have a look at the XLMACR8.HLP file (which you can download from here:
http://www.microsoft.com/download/en/details.aspx?id=24039).

It shows that the Textref function takes two parameters, as follows:

TEXTREF(text, a1)
Text is a reference in the form of text.
A1 is a logical value specifying the reference type of text. If
a1 is TRUE, text is assumed to be an A1-style reference; if FALSE or
omitted, text is assumed to be an R1C1-style reference.

You are passing the value 'false' for the second parameter, but I
think it should be 'true' since you are using A1-style addressing.

If you are not controlling the string passed to Textref you should
also check the result.

-Govert

[CSharp Dummy]

Govert,


No I checked it, it is actually returned in R1C1.

> > > - Show quoted text -- Hide quoted text -

[Govert van Drimmelen]

OK.

Then I suggest you post a self-contained example in a .dna file that
reproduces the unexpected behaviour you see.

-Govert

[CSharp Dummy]

Ok here it is:

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

using System;
using ExcelDna.Integration;

public class MyAddIn
{

[ExcelCommand(MenuName = "Bug", MenuText = "Show DlgBox")]
public static void AddWorksheet()
{
object[,] DialogDef = new object[3, 7]
{
{
null, null, null,
372, 200, "Test", null
},
{
10, 170, 100, 90, null, "nothing",
null
},
{
1, 270, 100, 90, null, "ok",
null
}
};
object DialogRet = XlCall.Excel(XlCall.xlfDialogBox, DialogDef);

object Result = ((object[,])DialogRet)[1, 6];

ExcelReference cell =
(ExcelReference)XlCall.Excel(XlCall.xlfTextref,
(String)Result,

false);
XlCall.Excel(XlCall.xlcFormula, "=Today()", cell);

}
}

]]>
</DnaLibrary>

[Govert van Drimmelen]

Thank you.

OK - I get an AccessViolationException when I run it.
I'll try to dig a bit to see what might be going wrong - it seems the
SheetId in the ExcelReference is not valid.

-Govert

[CSharp Dummy]

Thank you.

Yes please let me know if there is a workaround.

[Govert van Drimmelen]

The SheetId of the reference returned by xlfTextref is not OK, and
causes the error in the xlcFormula call.

As you note, when the sheet name is explicitly added, everything seems
OK.

One way to get a valid reference to the Active sheet is to just create
the ExcelReference with no SheetId - then Excel-DNA will retrieve the
SheetId for the current sheet.

So pseudocode for a workaround would be:

Check whether the string you want to convert to an ExcelReference
contains a sheet name (perhaps search for '!')
if it does, convert the string to a reference using xlfTextref
if it does not,
Extract the row and column values from the string (maybe
with a regex)
Use the row and column (-1 in each case to change to 0-
based index) to make a new ExcelReference using
myRef = new ExcelReference(row - 1, col - 1);
use this ExcelReference instead of the result from
xlfTextref in your call to xlcFormula


Why is the SheetId returned from the xlfTextref not correct, and is
this an Excel bug or an Excel-DNA bug?
I don't know yet.

-Govert

[CSharp Dummy]

Yes, this works.

I also had to strip the workbook name, but overall it's workign fine.

Thanks Govert