Can a UDF handle a sheet range and an array from another UDF?

[JenniferMurphy]

This UDF selects a random element from a list of cumulative weights and returns the index. As written, it can be called from another UDF. Is there a way I can code it so that it can also be called from a sheet passing it a column of numbers? Or do I need to write a pre-UDF to accept the range and convert it to an array and then call the UDF?

The function is still in test, which is why all of the Debug statements.

Function RndWtdIndex(pCumWts() As Double) As Integer

Debug.Print ""
Debug.Print "RndWtdIndex..."
Debug.Print "pCumWts = " & pCumWts(1) & " " & pCumWts(2) & " " _
          & pCumWts(3) & " " & pCumWts(4) & " " & pCumWts(5)
'Dim CumWts() As Variant
'CumWts = pCumWts.Value

Dim r As Double
Dim needle As Double

With Application
  Debug.Print "Max CumWts = " & pCumWts(UBound(pCumWts))
  r = Rnd
  Debug.Print "Rnd = " & Format(r, "0.00000")
  needle = r * pCumWts(UBound(pCumWts))
  Debug.Print "Needle = " & Format(needle, "0.0000")
  RndWtdIndex = .XMatch(needle, pCumWts, 1) - 1  '-1 because 0 origin?
  Debug.Print "RndWtdIndex = " & RndWtdIndex
End With
Debug.Print ""

End Function

Thanks

 

[RoryA]

You'd have to change the parameter to pCumWts As Variant then test the input type in the function.

 

[JenniferMurphy]

You'd have to change the parameter to pCumWts As Variant then test the input type in the function.

Ok. Can you help me with the tests?

Do I use the VarType function?

I see vbArray as one option, but it always has another for the type. So I guess I could do

If vartype(pCumWts) = vbArray + vbDouble then ...

but I don't see a test for a range. How do I test for that?

Thanks

 

[RoryA]

I'd use TypeName for the range test - for example:

   If TypeName(pCumWts) = "Range" Then
      Debug.Print "It's a range"
   ElseIf VarType(pCumWts) And vbArray = vbArray Then
      Debug.Print "It's an array of some kind"