Cannot match a date in a range using MATCH function and VBA

Question

I would like to write a user defined function that returns the value in a range given a series ID and a date.

The range looks something like this

I would like the function call =getParam("B",1/07/2005,Parameters) to return 22.

To do this, I am using a couple of =MATCH functions to find the correct row and column and then will use an =INDEX function to return the correct value. My function (not complete) is below.

The problem:

When I specify =MATCH(1/07/2005,A1:A11,0) in a normal Excel cell I get the correct row index of 6, but when I incorporate this inside my VBA function as per below, I get a #VALUE! error.

Any ideas?

Function getParam(Series As String, StartDate As Date, Parameters As Range)

    Dim IndexRow, IndexColumn As Integer

    IndexRow = Application.WorksheetFunction.Match(StartDate, Parameters.Columns(1), 0)
    IndexColumn = Application.WorksheetFunction.Match(Series, Parameters.Rows(1), 0)

    getParam = IndexRow

End Function

A few questions: 1. How do you call your Function? Give an example. 2. Why do you have IndexColumn seeing as you don't use it? — AAA

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-07-03T12:00:25

You need to convert your date into double to match it CDbl(StartDate)
The WorksheetFunction.Match method returns a Double therefore
```
Dim IndexRow As Double, IndexColumn As Double
```
you need to specify the type for both variables otherwise the first is automatically Variant.
Use a test procedure to feed your UDF (user defined function) if you want to debug it.

You end up with …

Option Explicit

Public Sub Test_getParam()
    Debug.Print getParam("B", DateSerial(2019, 7, 5), Range("A1:E13"))
End Sub

Public Function getParam(Series As String, StartDate As Date, Parameters As Range) As Variant
    Dim IndexRow As Double, IndexColumn As Double

    IndexRow = Application.WorksheetFunction.Match(CDbl(StartDate), Parameters.Columns(1), 0)
    IndexColumn = Application.WorksheetFunction.Match(Series, Parameters.Rows(1), 0)

    getParam = Parameters.Cells(IndexRow, IndexColumn)
End Function

In your formula you need to submit a real date DATE(2019,7,5)

=getParam("B",DATE(2019,7,5),A1:E13) 'returns 10 with the data below

because if you submit =getParam("B",1/07/2005,Parameters) your "date" is 1/07/2005 which actually means 1 devided by 7 devided by 2005 with the result of 0.0000712504453153… so this is the value you actually tried to match. You must use DATE(2005,7,1) to get a real date.

As a final improvement I recommend to include an error handling, to return an error if either date or series didn't match.

This will return error #NA if one of these didn't match.

Public Function getParam(Series As String, StartDate As Date, Parameters As Range) As Variant
    Dim IndexRow As Double, IndexColumn As Double

    On Error Resume Next
    IndexRow = Application.WorksheetFunction.Match(CDbl(StartDate), Parameters.Columns(1), 0)
    IndexColumn = Application.WorksheetFunction.Match(Series, Parameters.Rows(1), 0)
    On Error GoTo 0

    If IndexRow > 0 And IndexColumn > 0 Then
        getParam = Parameters.Cells(IndexRow, IndexColumn)
    Else
        getParam = CVErr(xlErrNA)
    End If
End Function

Cannot match a date in a range using MATCH function and VBA

1 Answers