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: 577
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: 7233
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: 7233
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: 19138
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.
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
|