UDF not returning value

[RogueCode]

I am writing a UDF that essential acts as the function LARGE but utilises a dynamic range.

When I run it, it returns Value, but I can't see what the issue is. kRange is a particular Cell on the sheet that contains a range as text e.g. N13:N75; iVal is the cell that contains the nth value that I want to look up. Is there anyone who can tell me what I have missed.
Function GetLarge(kRange As Range, iVal As Range) As Double

'kRange is calculation cell
'iVal is the number

Dim RangeK As String
Dim i As Double

RangeK = kRange.Value
i = iVal.Value

GetLarge = Application.WorksheetFunction.Large(RangeK, i)

End Function

 

[Osvaldo Palmeiro]

Try this:

GetLarge = Application.WorksheetFunction.Large(Range(RangeK), i)

 

[Dryver14]

you dont need the RangeK at all, application.worksheetfunctio.large(krange,i)

Although I am not sure of the use of this when you can use the large function anyway

 

[shg]

As written, Excel sees no dependency on RangeK, so will not automatically recalculate if it changes.

I agree w/ Dryver -- why not just use a formula?

 

[Fazza]

String RangeK is given a value in this code line,
RangeK = kRange.Value

and that string then feeds into
GetLarge = Application.WorksheetFunction.Large(RangeK, i)

It would help to have more understanding of the design requirement. regards

 

[Dryver14]

KRange is a range so will not give a value, when you started the udf you had already stated that krange was a range so that works in the formula.

If you said RangeK = krange, I suppose that would work

 

[Fazza]

KRange is a range so will not give a value, when you started the udf you had already stated that krange was a range so that works in the formula.

If you said RangeK = krange, I suppose that would work

Saying RangeK = krange doesn't get the right result though, because RangeK is explicitly dimmed as a string.

 

[Dryver14]

Are we falling out??

Why are you passing a range into a string, the formula does not want a string, it wants a range

 

[Fazza]

the formula does not want a string, it wants a range

Exactly. That is what I wrote about initially. I'm not passing a range into a string: it isn't my function. RogueCode wrote it.

I think we're all good.

 

[Dryver14]

LOL, I hadn't even noticed Roguecode dropped the question and ran