setting ranges and vlookup in custom functions

[SPavlyuk]

Hi,

below is a piece of code of a custom function I worked on:

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = (Day(maturity) + 3) & "/" & Month(maturity) & "/" &
Year(maturity)
Else
adj_maturity = (Day(maturity) + 1) & "/" & Month(maturity) & "/" &
Year(maturity)
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.WorksheetFunction.VLookup(adj_maturity, lookuprng, 2, False)
Futures_unr = a
End Function

so the input is a date and output should be a numerical value. I get an
error while, I think, trying to set up a range for vlookup. Does any have any
idea what is causing an error? (vlookup in excel function works fine) Thank
you in advance.

regards,
S

 

[Bob Phillips]

Function Futures_unr(maturity As Date) As Double
Dim lookuprng As Range, adj_maturity As Variant, a As Variant

If Weekday(maturity, vbMonday) = 5 Then
adj_maturity = maturity + 3
Else
adj_maturity = maturity + 1
End If

Set lookuprng = ThisWorkbook.Worksheets("Sheet1").Range("E6:J83")
a = Application.VLookup(CLng(adj_maturity), lookuprng, 2, False)
Futures_unr = a
End Function

 

[SPavlyuk]

Bob, thanks!

so I understand I did a mistake in handling the date value... it works well
now. thanks again.

regards,
Sergiy

 

[Bob Phillips]

Yeah, the thing to remember is that however it looks, a date is just a
number under the covers (the number of days since Jan 01 1900), so just work
with numbers.