CODEWITHSUNDEEP
vbaexcel
OutThere
OutThere

Reputation: 475

How to pass a date as an argument in an Excel VBA function

I'm trying to make a function that will take a date in a cell as an argument, then use that date to lookup a value. The date to be passed will be in the variable EffDate. Then the function should go to the worksheet RateChgs, check the NewPymtEffDateRange for the EffDate, and, upon finding it, go to the EscrowPymtAmtRange (one column wide) and return the value on the same row there.

I've only gotten to the point of testing it in the immediate window by typing GetEscrowPymt(8/1/2000) (or some other date). From the value of the Position variable, I can tell that the function isn't finding the date even though it's there. Is this a problem with how I'm passing 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

Upvotes: 3

Views: 7642

Answers (1)

David Zemens
David Zemens

Reputation: 53623

The last argument in the Match function allows for returning an approximate match. If you require an exact match, then you should use the last argument of 0 to require an exact match. Otherwise, using the arguments 1 or -1 will return approximate match and assume also that the data is sorted ascending.

Position = Application.WorksheetFunction.Match(EffDate, PymtEffDateRange, 0)

The Match function will error if the effDate value is not found in the lookup array, so you may need error handling logic to account for that possibility. I would probably use the Application.Match function which can accept an error type, where the Match function in the Worksheet class will only accept long/integer values and will raise an error if the value isn't 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 also have difficulty working with date values, so let me know if that doesn't solve the issue.

VBA also doesn't play well with various system locales, if you're expecting "8/1/2000" to be anything other than August 1, 2000, you may have more problems since VBA will interpret that by the US date format, not the system locale (e.g., in the UK that date would be 8 January, 2000). In that case, it may be best to treat the date as text and do a match based on text rather than date.

Upvotes: 3

Related Questions