How to pass date as argument in Excel VBA function
I am trying to create a function that will take a date in a cell as an argument and then use that date to find the value. The date to be passed will be in the EffDate variable. Then the function should go to RateChgs worksheet, check NewPymtEffDateRange for EffDate and, finding it, go to EscrowPymtAmtRange (one column wide) and return the value on the same line there.
I just got to the test site in the nearest window by typing GetEscrowPymt (8/1/2000) (or whatever date). From the value of the Position variable, I can tell that the function does not find the date, even if it is. Is this a problem with how I pass the date?
Function GetEscrowPymt(EffDate As Date)
Dim PymtEffDateRange As Range
Dim EscrowPymtAmtRange As Range
Dim Position As Integer
Set PymtEffDateRange = Worksheets("RateChgs").Range("NewPymtEffDate")
Set EscrowPymtAmtRange = Worksheets("RateChgs").Range("EscrowPymt")
Position = Application.WorksheetFunction.Match(EffDate, PymtEffDateRange, 1)
MsgBox (Position)
End Function
source to share
The last argument in the function Match
allows you to return an approximate match. If you require an exact match, you must use the last argument 0
to get an exact match. Otherwise, using the arguments 1
or -1
will return an approximate match and also assume that the data is sorted in ascending order.
Position = Application.WorksheetFunction.Match(EffDate, PymtEffDateRange, 0)
The function Match
will effDate
fail if the value is not found in the search array, so error handling logic may be required to account for this possibility. I would probably use a function Application.Match
that can accept an error type, where the function Match
in the Worksheet class only takes long / integer values and throws an error if no value is found:
Dim Position as Variant
Position = Application.Match(EffDate, PymtEffDateRange, 0)
If IsError(Position) Then
MsgBox EffDate & " not found!", vbInformation
Exit Function
' -- OR --
' assign some other return value for the function, etc.
End If
Some functions are also tricky to work with date values, so let me know if that doesn't fix the problem.
VBA also plays badly with various system locales, if you expect "8/1/2000" to be anything other than August 1st 2000, you may have more problems as VBA will interpret this in US date format. not the system language (for example, in the UK this date would be January 8, 2000). In this case, it is best to treat the date as text and to match based on text rather than date.
source to share