XlCall.Excel(XlCall.xlfCaller) fails from compiled library

Mar 7, 2010 at 5:55 PM

I have written a function that is to be called from Excel. The function is in a compiled library, and is:

        [ExcelFunction(Category = "Test")]
        public static double DoTest()
        {
            ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            return 0.0;
        }

The call to XlCall.Excel fails with the following exception: "Exception of type 'ExcelDna.Integration.XlCallException' was thrown".

I debugged the call to ExcelDna.Integration.XlCall.Excel(xlFunction) and saw that ExcalDna.Integration.TryExcelImpl returned XlCall.XlReturn.XlReturnFailed because its static tryExcelImpl member was null. It was null because SetTryExcelImpl had not been called. At this point, I stopped investigating.

My ultimate objective is to construct a string that describes the address of the calling cell, in the form [sheetName]!RxCy, where x and y are the row and column numbers returned by xlfCaller, and sheetName is returned by xlSheetNm.

I tried creating a "wrapper" function within the ".dna" file, which called xlfCaller and then called DoTest() in my dll, but this time with the ExcelReference as an argument, i.e.:

// This function is in the ".dna" file.
[ExcelFunction(Category="Test")]
public static double DoTest()
{
	try
	{
		ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
		return XlFunctions.DoTest(reference);
	}
	catch (Exception e)
	{
		MessageBox.Show(e.Message);
		return 0.0;
	}
}

// This function is in the dll:
public static double DoTest(ExcelReference caller)
{
    return 0.0;
}

But Excel displayed #VALUE! as the return value. No exception was caught by the exception handler.

So right now I'm unable to determine the caller's address inside my dll. Any help would be appreciated.

Coordinator
Mar 8, 2010 at 9:04 AM

Hi,

It should work fine. I try this in a .dna file with ExcelDna 0.22:

 

[ExcelFunction()] <DnaLibrary Language="C#">
<![CDATA[
using System;
public class MyFunctions
{
[ExcelFunction(IsMacroType=false)] 
public static string CallingSheetName() 
ExcelReference reference = (ExcelReference)XlCall.Excel (XlCall.xlfCaller); 
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference); 
return sheetName;
[ExcelFunction(IsMacroType=true)] 
public static string CallingFileName() 
ExcelReference reference = (ExcelReference)XlCall.Excel (XlCall.xlfCaller); 
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference); 
return System.IO.Path.Combine( 
(string)XlCall.Excel(XlCall.xlfGetDocument, 2, sheetName),
(string)XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName)); 
}
]]>
</DnaLibrary>
public static string CallingFileName() 
   ExcelReference reference = (ExcelReference)XlCall.Excel 
(XlCall.xlfCaller); 
   string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, 
reference); 
   return sheetName; 

 

<DnaLibrary Language="C#">
<![CDATA[
using ExcelDna.Integration;
public class MyFunctions
{

	[ExcelFunction(IsMacroType=false)] 
	public static string CallingSheetName() 
	{ 
		ExcelReference reference = (ExcelReference)XlCall.Excel (XlCall.xlfCaller); 
		string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference); 
		return sheetName;
	} 
	
	[ExcelFunction(IsMacroType=true)] 
	public static string CallingFileName() 
	{ 
		ExcelReference reference = (ExcelReference)XlCall.Excel (XlCall.xlfCaller); 
		string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference); 
		return System.IO.Path.Combine( 
			(string)XlCall.Excel(XlCall.xlfGetDocument, 2, sheetName), 			
			(string)XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName)); 
	} 
}
]]>
</DnaLibrary>

The second function needs to be marked as IsMacroType=true for the xlfGetDocument calls to work, and CallingFileName only works if your workbook has been saved to a file.

Check whether these work for you - I don't expect it to be different in a compiled library.

I think there is some other problem in your environment...

 

Regards,

Govert

 

 

Coordinator
Mar 8, 2010 at 9:05 AM

Oh - you can search for xlfCaller in the Google Group - http://groups.google.com/group/exceldna - there have been some more discussions on this.

Mar 8, 2010 at 4:26 PM

Thanks Govert. Actually, I found the ExcelDNA google groups this morning and found what looks like the solution to my problem. I'll know when I get home this evening. (Yes, I do this in my spare time!)

I think it'd be a good idea if you put a link to the google group on the exceldna.typepad.com blog.

Regards,

Tony

Mar 8, 2010 at 8:24 PM

Problem solved. This thread on google groups gave the solution: I had to ensure that <YourExcelDnaCopy>.xll was in the same directory as ExcelDna.Integration.dll and my dll.

Once again, thanks Govert. I'm looking forward to working with ExcelDna.

Regards

Tony

 

Coordinator
Mar 9, 2010 at 11:15 AM

Hi Tony,

You do not need ExcelDna.Integration.dll to run your add-in - a copy is already embedded in the .xll. You just have to make sure that the version you referenced when compiling your library is the same as the version of ExcelDna you are using.

Govert