Array in UDF

[jmvhugo]

Help, please. I am a very occasional VBA user. I want a simple lookup value from an array, based on an input parameter. I am using a UDF prtly for learning, but also to avoid a lot of similar, complex coding in the main model. I shall have three variants of this UDF when I finish. But let me ask one question first. The VBA is given below, but does not work. The idea is to look up a value P, with approximate match, in the first column of a two dimensional array ARR (usually 6 rows x 5 columns), and return the related value from column 5. Can you show me the correction please.

Function ssrate(p As Single, arr As Range) As Single
Dim a()
Dim n As Integer
n = arr.Rows.Count
ReDim a(1 To n, 1 To 5)
a = arr
ssrate = Application.WorksheetFunction.VLookup(p, a, 1, 5)

End Function

 

[offthelip]

try this code which doesn't use the vlookup function but does it in vba instead and can be much faster:

Function ssrate1(p As Single, arr As Range) As Single
Dim a()
Dim n As Integer
n = arr.Rows.Count
ReDim a(1 To n, 1 To 5)
a = arr
v = 1
For i = 1 To n
 If a(i, 1) > p Then Exit For
 v = i
Next i
ssrate1 = a(v, 5)
End Function

 

[QuestionBaker]

Dim a()

@offthelip can you explain what does the above line do?

 

[offthelip]

it defines a variable called "a" which is defined as as array the brackets tell VBA that it is an array. there is no definition as to what type of an array it is so it defaults to being a variant array.
You need to have a definition such as this before using a redim statement. However you don't need to do it this way, I only left the code unchanged from what you wrote, the way i would code it is like this:

Function ssrate2(p As Single, arr As Range) As Single
'Dim a()
'Dim n As Integer
'n = arr.Rows.Count
'ReDim a(1 To n, 1 To 5)
a = arr
v = 1
For i = 1 To UBound(a, 1)
 If a(i, 1) > p Then Exit For
 v = i
Next i
ssrate2 = a(v, 5)
End Function

 

[jmvhugo]

Thank you very much. My version had the last two arguments of the vlookup round the wrong way. But it's great to have a bit more VBA knowledge, as well as speeding up the code. Thanks.