XLL sample function AllSupportedExcelTypes

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

XLL sample function AllSupportedExcelTypes
Error: Option Strict On disallows late binding 
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.
Posted 23 Apr, 2011 10:45:16 Top
Eugene Astafiev


Guest


Hi Henri,

It looks like you need to use the AcceptAsArray property of the ADXExcelParameterDescriptor class. Please read more about this in the http://www.add-in-express.com/docs/net-excel-xll-addins.php#configuring-udf section of the online documentation. Also please take a look at the http://www.add-in-express.com/docs/net-excel-udf-tips.php#modify-multiple-cells section.

Unfortunately there is no such ready-made sample add-in project because it is very easy to implement. Don't you think so now?
Posted 23 Apr, 2011 11:44:19 Top
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
Posted 26 Apr, 2011 06:13:01 Top
Eugene Astafiev


Guest


Hi Henri,

You broke the ice! Thank you for sharing the solution for other forum readers.
Posted 26 Apr, 2011 12:08:00 Top