XLLModule Excel Range vs. Single String Entry

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

XLLModule Excel Range vs. Single String Entry
Determining if user passed a range or a single data point..... 
Ian Carroll




Posts: 24
 Joined: 2011-09-03
Hi.

Our Excel Add-In project includes a UDF class with a handful of defined custom functions.

We need to be able to determine if the user has passed a range or just a single data point into our functions. It seems that Add-In automatically converts the user entry into a generic object or string values.

How can we determine if the user has specified a range of values or just a single data point entry? 


        Public Shared Function MyUDF(ByVal FeedType As String, ByVal Num_Range As Object) As Object(,)
            'Dim ExcelApp As Excel.Application = CurrentInstance.HostApplication
            Dim strDataPoints As String = String.Empty
            Dim output As Object(,) = Nothing
            Dim DPArray As Object(,)

            If TypeOf Num_Range Is String Then
                strDataPoints = Num_Range
                output = New Object(0, 0) {}
                ReDim DPArray(0, 0)
            Else
                Dim SelRange As Object(,) = DirectCast(Num_Range, Object(,))
                output = New Object(SelRange.Length - 1, 0) {}

                ReDim DPArray(SelRange.Length - 1, 0)

                Try
                    For i = 0 To SelRange.Length - 1
                        strDataPoints += SelRange(i, 0) & ","
                    Next

                Catch ex As Exception
                    output(0, 0) = "Invalid Input Range"
                    Return output
                End Try
            End If


Thanks in advance!

Ian
Posted 16 May, 2012 09:02:45 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hi Ian,

I suggest using AcceptAsArray=False in the corresponding parameter descriptor.

From the manual:
AcceptAsArray = True means that your code will receive an array of parameters when the user passes a range to your UDF; otherwise, an instance of ADXExcelRef will be passed to your code.


That is, if the parameter is of the ADXExcelRef type, then the user passes a range reference.


Andrei Smolin
Add-in Express Team Leader
Posted 16 May, 2012 09:54:50 Top
nwein




Posts: 577
 Joined: 2011-03-28
I'm not familiar with VB, but in C# I usually specify the input parameter as an object[,] and based on the length/dimensions of the array I know how many cells the user has selected. A single string would result in a object[1,1], i.e. only one entry.
The function can be called as such:

=MyFunction("Just a string")
or
=MyFunction(A1:B5)

and it would work just fine.

That way you don't need to worry about AcceptAsArray = False (in fact this method will work only with the default settings of the AcceptAsArray, i.e. = True) and apply conversions of the actual Excel range etc.

Just my two cents :)
Posted 16 May, 2012 10:42:57 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hi Nir and Ian,

Consider passing 1) a string and 2) a cell containing a string. If AcceptAsArray is true (default), your UDf will receive string values in both scenarios. If AcceptAsArray is False, in scenario #2, you'll receive an ADXExcelRef describing the cell rather than the string that the cell contains. I have an impression that Ian talks about differentiating these scenarios.

Now, lets pass an empty cell to your UDF. With AcceptAsArray = true (this is #3), this results in receiving Nothing (null in C#), not an empty string. With AcceptAsArray = false (#4), you'll receive an ADXExcelRef describing the cell.

Ian, in case of #3, your code will bomb.


Andrei Smolin
Add-in Express Team Leader
Posted 17 May, 2012 08:53:30 Top