What did I do wrong in this UDF to use XMatch?

[JenniferMurphy]

I need a UDF that can use the XMatch worksheet function to find a random element in a list of cumulative weights. Here's my code. It's in a code module for this .xlsm workbook with other UDFs that do work.

Function RndWtdIndex(pCumWts() As Range) As Integer

Dim CumWts() As Variant
CumWts = pCumWts.Value

With Application
  RndWtdIndex = XMatch(Rnd * CumWts(UBound(CumWts), 1), CumWts, 1)
End With

End Function

And here is how it is called from the worksheet:

Typing Tutor Adaptive Learning Algorithm.xlsm
	C	D	E	F
6	2	2	#VALUE!	3
7	3	5	#VALUE!	3
8	5	10	#VALUE!	2
9			#VALUE!	1
10			#VALUE!	2
Sheet3

Cell Formulas
Range	Formula
E6:E10	E6	=RndWtdIndex($D$6:$D$8)
F6:F10	F6	=XMATCH(RAND()*10,$D$6:$D$8,1)
D6	D6	=C6
D7:D8	D7	=D6+C7

Thanks

 

[RoryA]

You're not passing an array of ranges, so the declaration is wrong, and you didn't qualify the XMatch call. Try:

Function RndWtdIndex(pCumWts As Range) As Integer

   Dim CumWts() As Variant
   CumWts = pCumWts.Value

  RndWtdIndex = Application.XMatch(Rnd * CumWts(UBound(CumWts), 1), CumWts, 1)

End Function

 

[JenniferMurphy]

You're not passing an array of ranges, so the declaration is wrong, and you didn't qualify the XMatch call. Try:

Function RndWtdIndex(pCumWts As Range) As Integer

   Dim CumWts() As Variant
   CumWts = pCumWts.Value

  RndWtdIndex = Application.XMatch(Rnd * CumWts(UBound(CumWts), 1), CumWts, 1)

End Function

Doh! Of course. I initially had it coded as RndWtdIndex(pCumWts() as Variant), then realized that it was a range and forgot to remove the ().

And my call didn't work because I forgot the "." before XMatch .XMatch. That's what you mean by "qualify", right?

Here's the working code.

Function RndWtdIndex(pCumWts As Range) As Integer

Dim CumWts() As Variant
CumWts = pCumWts.Value

With Application
  RndWtdIndex = .XMatch(Rnd * CumWts(UBound(CumWts), 1), CumWts, 1)
End With

End Function

Thank you

 

[RoryA]

That's what you mean by "qualify", right?

Correct.

FWIW, there's not much point to a With block if you only call one method of the relevant object.

 

[JenniferMurphy]

Correct.

FWIW, there's not much point to a With block if you only call one method of the relevant object.

The code I posted is just a sample of what will become a much larger UDF. The With block will make more sense then. I was just practicing. I need a lot of practice.