User Defined Fuction Returning Range Help

  • Thread starter Thread starter xcelion
  • Start date Start date
X

xcelion

Hi All, :confused:

Iam new to Vba coding .I have a foumul
=PERCENTRANK(CategoryLookup!$F$10:$G$10,D4,3) In this formula the firs
parameter ie "CategoryLookup!$F$10:$G$10 " is a range that is to b
dynamcally generated based on some
lookup values.So i tried wriiting a User Defined Fuction(UDF) returnin
that range based on some logic.But it's not working .What can be th
problem.Is it possible to substitute a range parameter in a workshee
function using a UDF returning range.Can anybody help me on this

Thanks in advance
Xcelio
 
This works for me.


In a module:

Function GetRange() As Range

Set GetRange = ActiveSheet.Range("A1:A5")

End Function



In a worksheet formula:
=AVERAGE(GetRange())


Maybe you could post your UDF ?

Tim.
 
Hi Tim,

Sorry for the late reply.I was out of station .here is my udf


Function GetSalaryRange(strTitle As String) As Range
Dim wsCatLookup As Worksheet
Dim rngTemp, rngSalRng, rngTemp1 As Range
Dim rngVar As Range
Set wsCatLookup = ThisWorkbook.Sheets("CategoryLookup")
Set rngTemp = wsCatLookup.Range("A4:B19")
Set rngSalRng = wsCatLookup.Range("E4:G19")



'rngTemp.Select

strCategory = WorksheetFunction.VLookup(strTitle, rngTemp, 2
True)

Set rngVar = rngSalRng.Find(what:=strCategory,, LookAt:=xlWhole, _
LookIn:=xlValues).Offset(0, 1).Resize(1, 2)



Set GetSalaryRange = rngVar



End Functio
 
What version of excel are you using?

If it's before xl2002, then excel won't allow you to use .find in a UDF called
from a cell in a worksheet.

(xl2002 allows it. I think others have posted that xl2003 does, too.)

Maybe you could use a couple of application.match() (one for each column you're
searching through).
 
Hi Dave ,

Iam using xl2002 .I will try using Application.Match().Thanks for yo
help


xcelio
 
I don't think that this will help.

I think you'll want the UDF you posted to return a string.

Then you can wrap =indirect() around that string and use it as a range.

I'm not sure what your UDF is doing, but maybe:

Option Explicit
Function GetSalaryRange(strTitle As String) As String

Application.Volatile 'see notes later

Dim wsCatLookup As Worksheet
Dim rngTemp As Range
Dim rngSalRng As Range
Dim rngVar As Range

Dim strCategory As Variant

Set wsCatLookup = ThisWorkbook.Sheets("CategoryLookup")
Set rngTemp = wsCatLookup.Range("A4:B19")
Set rngSalRng = wsCatLookup.Range("E4:G19")

Set rngVar = Nothing

strCategory = Application.VLookup(strTitle, rngTemp, 2, True)

If IsError(strCategory) Then
'do what
Else
Set rngVar = rngSalRng.Find(what:=strCategory, LookAt:=xlWhole, _
LookIn:=xlValues)
If rngVar Is Nothing Then
'not found, do what
Else
Set rngVar = rngVar.Offset(0, 1).Resize(1, 2)
End If
End If

If rngVar Is Nothing Then
GetSalaryRange = CVErr(xlErrRef)
Else
GetSalaryRange = rngVar.Address(external:=True)
End If

End Function

Then the formula in the cell would look like:

=PERCENTRANK(indirect(getsalaryrange(...)),D4,3)

========
But you have another problem. Excel will calculate your UDF whenever it sees
the need to. If you change the string you passed, it knows that your UDF is
dependent on that string (strTitle).

But if you change the data on the categorylookup worksheet, excel doesn't know
about it.

You have two choices.

Add
application.volatile
to your UDF.

Then your UDF recalcs whenever excel recalcs. This is usually overkill--most
times the value returned won't be changing. And if you're unlucky, you could be
looking at results that are a step behind--your workbook needs to be
recalculated and your UDF result is old.

A better/safer approach would be to pass everything your function needs.

Option Explicit
Function GetSalaryRange(strTitle As String, _
rngtemp As Range, _
rngSalRng As Range) As String

Dim rngVar As Range
Dim strCategory As Variant

Set rngVar = Nothing

strCategory = Application.VLookup(strTitle, rngtemp, 2, True)

If IsError(strCategory) Then
'do what
Else
Set rngVar = rngSalRng.Find(what:=strCategory, LookAt:=xlWhole, _
LookIn:=xlValues)
If rngVar Is Nothing Then
'not found, do what
Else
Set rngVar = rngVar.Offset(0, 1).Resize(1, 2)
End If
End If

If rngVar Is Nothing Then
GetSalaryRange = CVErr(xlErrRef)
Else
GetSalaryRange = rngVar.Address(external:=True)
End If

End Function

And you'd call it in a cell like:

=getsalaryrange("whatever",categorylookup!$a$4:$b$19,categorylookup!$e$4:$g$19)

So your percentrank formula would become:

=percentrank(getsalaryrange("whatever",categorylookup!$a$4:$b$19,categorylookup!$e$4:$g$19),d4,3)

A couple of ps's:

This line:
Dim rngTemp, rngSalRng, rngTemp1 As Range
declares rngTemp as a range, but rngTemp and rngSalRng are variants.

either:
Dim rngTemp as range, rngSalRng as range, rngTemp1 As Range
or
Dim rngTemp As Range
Dim rngSalRng As Range
Dim rngVar As Range

(and rngtemp1 wasn't used.)


And finally, there's a lot of stuff that might mismatch. I wasn't sure what to
do if something returned an error/something wasn't found, so I just put "do
what" comments in the code.
 
Back
Top