Array in UDF

jmvhugo

New Member
Joined
Jul 24, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,748
Office Version
  1. 2010
Platform
  1. Windows
try this code which doesn't use the vlookup function but does it in vba instead and can be much faster:
VBA Code:
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
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,748
Office Version
  1. 2010
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Jul 24, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,731
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top