Henri Pellemans
Guest
|
I created a lot of XLL UDFs in VB.NET. Now I am trying to find out how to pass arrays to my UDFs. I did some tests with the sample function AllSupportedExcelTypes, but when I set 'Option Strict' in the Compile window, then I get a lot of error messages "Option Strict On disallows late binding".
I also noticed that IntelliSense does not generate 'GetLength' in arg.GetLength(0)
How do I solve these problems?
Could you provide me with an VB.NET exemple that passes an array to my XLL UDF? For exemple a UDF that returns the equivalent of SUM(A1:B10) while looping through the array. |
|
Eugene Astafiev
Guest
|
|
Henri Pellemans
Guest
|
Hi Eugene,
If I want multiple Excel cells as input into my XLL UDF I must define the input as 'Object' [not as Array because then the add-in fires an exception because Array is an unknown datatype in ADX]. For exemple:
Public Shared Function mySum(ByVal values As Object) As Decimal
If compile Option Strict is ON this resulted in some compiling errors. That was my question. The same happens when one uses the sample UDF and then turns on Option Strict.
I solved my problem by converting the Object into an Array. Then the Option Strict errors disappear and IntelliSense comes available. And then off course the mySum function is pretty easy. There is no need to do something with the ADXExcelParameterDescriptor class. But if you use function descriptions you should leave the AcceptAsArray property TRUE.
Please find below part of my function:
Public Shared Function mySum(ByVal values As Object) As Decimal
Dim myValues As Array
If (TypeOf values Is Object(,)) Then 'multiple Excel cells selected
myValues = CType(values, Array)
'count sum of selected cells using myValues instead of values
'Return sum of selected cells
Else 'only one Excel cell selected
Return Convert.ToDecimal(values)
End If
End Function |
|
Eugene Astafiev
Guest
|
Hi Henri,
You broke the ice! Thank you for sharing the solution for other forum readers. |
|