excel - 无法使用 MATCH 函数和 VBA 匹配范围内的日期

Question

我想编写一个用户定义的函数，它返回给定系列 ID 和日期的范围内的值。

范围看起来像这样



我想要函数调用 =getParam("B",1/07/2005,Parameters)返回 22 .

为此，我使用了几个 =MATCH函数来查找正确的行和列，然后将使用 =INDEX函数返回正确的值。我的功能(不完整)如下。

问题:

当我指定 =MATCH(1/07/2005,A1:A11,0)在一个普通的 Excel 单元格中，我得到了正确的行索引 6 ，但是当我按照下面将其合并到我的 VBA 函数中时，我得到一个 #VALUE!错误。

有任何想法吗？

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

Answer 1

您需要将日期转换为 double 以匹配它CDbl(StartDate)

WorksheetFunction.Match method返回 Double所以

Dim IndexRow As Double, IndexColumn As Double

您需要为 指定类型两个变量，否则第一个自动为 Variant .

如果您想调试它，请使用测试过程来提供您的 UDF(用户定义的函数)。

你最终...

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

在您的公式中，您需要提交一个真实的日期 DATE(2019,7,5)

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

因为如果你提交 =getParam("B",1/07/2005,Parameters)你的“日期”是 1/07/2005这实际上意味着 1除以 7除以 2005结果为 0.0000712504453153…所以这是您实际尝试匹配的值。您必须使用 DATE(2005,7,1)得到一个真正的约会。



作为最后的改进，我建议包含错误处理，如果日期或系列不匹配则返回错误。

这将返回错误 #NA如果其中之一不匹配。

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