XLL Addin Version 8.0.4330 object parameter

Add-in Express™ Support Service
That's what is more important than anything else

XLL Addin Version 8.0.4330 object parameter
 
Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last 
Mark Meyer




Posts: 6
 Joined: 2016-01-07
Hello,

To work around the issue I have with string dates I've changed one of my parameters to be an object parameter in one of my functions. When the function parameter is set to "accept as array = false" the parameter value received is an ADXExcelRef instead of the value of that cell. If I change that parameter to "accept as array = true" then the value of the cell is provided instead of a cell reference. Is this by design? Can I get the cell value from an ADXExcel Ref? I don't want to accept an array for that parameter.
Posted 07 Jan, 2016 10:43:12 Top
nwein




Posts: 510
 Joined: 2011-03-28
You sure can.
Observe the following code snippet: 
public static object MyUdf(object myInput) // myInput: AcceptAsArray = False
{
	if (!(myInput is ADXExcelRef)) 
		return ADXExcelError.xlErrorRef;
	Range rng = Module.ExcelApp.Range[((ADXExcelRef)myInput).ConvertToA1Style()];
	var inputValue = rng.Value2;
	...
}


or 
public static object MyUdf(object myInput) // myInput: AcceptAsArray = False
{
	ADXExcelRef callerRef = (ADXExcelRef)Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller);	
	var inputValue = callerRef.GetValue();
	...
}


Hope that helps
Posted 07 Jan, 2016 11:12:09 Top
Mark Meyer




Posts: 6
 Joined: 2016-01-07
Thanks. The first option throws an exception of Unable to cast object of type 'AddinExpress.MSO.ADXXlReturnValue' to type 'System.String'. 

string convertToA1Style = ((ADXExcelRef)date).ConvertToA1Style();


I'm not sure how the second options works because it doesn't use 'myInput' at all
Posted 07 Jan, 2016 11:58:25 Top
Sergey Grischenko


Add-in Express team


Posts: 7202
 Joined: 2004-07-05
Hi Mark,

Did you set the AcceptAsArray property of the XLL parameter to false?
Posted 08 Jan, 2016 07:29:37 Top
Mark Meyer




Posts: 6
 Joined: 2016-01-07
Hi Sergey,

Yes, the property AcceptAsArray is set to false. Then I get the ADXExcelRef instead of a value. If I set it to true I get the value, but I don't want to accept an array.
Posted 08 Jan, 2016 08:25:41 Top
Sergey Grischenko


Add-in Express team


Posts: 7202
 Joined: 2004-07-05
Hi Mark,

Please send me a simple project that reproduces the issue. I will test it.
Posted 08 Jan, 2016 11:07:49 Top
Mark Meyer




Posts: 6
 Joined: 2016-01-07
I don't see how to attach an example here, but I did create a project. The 

string convertToA1Style = ((ADXExcelRef)date).ConvertToA1Style();


seems to work fine in the sample project but not in mine for some reason. Is it by design that when AcceptAsArray = false and the parameter is object type that ADXExcelRef is returned? This wasn't the way it worked in the previous version (7). It would return the value.
Posted 08 Jan, 2016 16:11:51 Top
Mark Meyer




Posts: 6
 Joined: 2016-01-07
I was able to send it through contact.
Posted 08 Jan, 2016 16:16:31 Top
Andrei Smolin


Add-in Express team


Posts: 14966
 Joined: 2006-05-11
Hello Mark,

Thank you for the sample project.

AcceptAsArray specifies the way in which you access the value(s) of the cell(s) a reference to which is passed to your function through the specified parameter. AcceptAsArray=False means you get an ADXExcelRef; AcceptAsArray=true means you get an array containing the value(s) of the cell(s) referenced. Assuming that getting such a value(s) is what you are looking for, the simplest way is to set AcceptAsArray=true. You can also get the value using ADXExcelRef.GetValue().

In your code you retrieve the value of the referenced cell using the Excel object model. This may be the cause of the issue. In fact, Microsoft recommends avoiding object model calls in the context of an Excel UDF call.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Jan, 2016 07:02:28 Top
Mark Meyer




Posts: 6
 Joined: 2016-01-07
Andrei,

Thank you much for the detailed explanation. That makes complete sense. I thought the AcceptAsArray referred to whether the function could accept a range of cells (more than one).

Thanks again!
Mark
Posted 11 Jan, 2016 08:18:43 Top

Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last