Excel/VBA - 使用动态范围的索引匹配功能

Question

如何有效地使用 Index/Match VBA中的公式？

背景:我有一个工作表，它严重依赖于使用一个公式，该公式根据将特定名称与其名称范围以及特定日期与其日期范围进行匹配来检索输出。

=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)

此外，还有一个硬编码的 VBA 子程序可以产生相同的输出

Sub ExampleHardCode() 
Dim Result As Variant
  Result = Evaluate("INDEX($C$4:$C$13,MATCH(1,($G$6=$A$4:$A$13)*($G8=$B$4:$B$13),FALSE),1)")
  ActiveCell.Value = Result
End Sub

问题:我想生成一个函数，它返回与上述选项相同的输出，但允许用户(i)通过引用相应的单元格来选择名称和日期值，以及(ii)选择每个范围(名称范围、日期范围和输出范围)。本质上在excel中使用=examplefunction(name value, name range, date value, date range, output range)。

我尝试了许多不同的解决方案，但都没有成功。下面是我迄今为止尝试过的一个示例，我认为匹配部分存在问题，因为即使我尝试设置范围(使用硬编码范围)它也会返回错误。

Function TestIndexMatch1(NameVal As Variant, DateVal As Date)

Dim NameRng As Range
Dim DateRng As Range
Dim OutputRng As Range
Dim Var1 As Variant  'should this be a range or integer?
Dim Result As Variant 

Set NameRng = Range("$A$4:$A$13")
Set DateRng = Range("$B$4:$B$13")
Set OutputRng = Range("$C$4:$D$13")

With Application.WorksheetFunction
    Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
    Result = .Index(OutputRng, Var1, 1)
End With
End Function

如果有帮助，我可以分享一个示例工作簿。我不确定这是否非常可行，但如果是这样，它真的会帮助很多对 excel 不够熟悉的用户正确使用索引/匹配 excel 公式。不幸的是，我的 excel 技能远远超过了我的 VBA 技能。

Answer 1

在 VBA 代码中使用数组公式 将 Application 对象的 ReferenceStyle 设置为 xlR1C1 (暂时，仅在您的函数执行期间)。最后调用评估得到公式的结果。

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

所以它看起来在表上: