Excel / VBA - 使用动态范围的索引匹配函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16176726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel / VBA - Index Match function using Dynamic Ranges
提问by user2312552
How to effectively use an Index/Matchformula in VBA?
如何有效地使用Index/MatchVBA 中的公式？
Background: I have a worksheet that relies heavily on the use of a formula that retrieves an output based on matching a specific name to its name range as well as a specific date to its date range.
背景：我有一个工作表，它在很大程度上依赖于使用一个公式，该公式根据将特定名称与其名称范围以及特定日期与其日期范围进行匹配来检索输出。
=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)
Additionally, there is a hard-coded VBA sub that produces the same output
此外，还有一个硬编码的 VBA 子程序可以产生相同的输出
Sub ExampleHardCode()
Dim Result As Variant
Result = Evaluate("INDEX($C:$C,MATCH(1,($G=$A:$A)*($G8=$B:$B),FALSE),1)")
ActiveCell.Value = Result
End Sub
Question: I'd like to produce a function that returns the same output as the above options but allows the user to (i) select the Name and Date values by referencing respective cells and (ii) select each range (name range, date range and output range). Essentially using =examplefunction(name value, name range, date value, date range, output range) in excel.
问题：我想生成一个函数，该函数返回与上述选项相同的输出，但允许用户 (i) 通过引用相应的单元格来选择名称和日期值，以及 (ii) 选择每个范围（名称范围、日期范围）和输出范围）。本质上是在excel中使用=examplefunction(name value, name range, date value, date range, output range)。
I've tried a number of different solutions but with no success. Below is an example of what I've tried so far, I think there is an issue with the match portion as even when I try to set the ranges (with hardcoded ranges) it returns an error.
我尝试了许多不同的解决方案，但都没有成功。下面是我迄今为止尝试过的示例，我认为匹配部分存在问题，因为即使我尝试设置范围（使用硬编码范围），它也会返回错误。
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:$A")
Set DateRng = Range("$B:$B")
Set OutputRng = Range("$C:$D")
With Application.WorksheetFunction
Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
Result = .Index(OutputRng, Var1, 1)
End With
End Function
I have an example workbook if it helps I can share. I'm not sure if this is very do-able but if so it'd really help a lot of users that aren't familiar enough with excel to use the index/match excel formula correctly. Unfortunately for me my excel skills far exceeds my VBA skills.
如果有帮助我可以分享，我有一个示例工作簿。我不确定这是否非常可行，但如果是这样，它确实可以帮助许多对 excel 不够熟悉的用户正确使用索引/匹配 excel 公式。不幸的是，我的 excel 技能远远超过了我的 VBA 技能。
采纳答案by dee
To use array formulas within VBA code set ReferenceStyle for Application object to xlR1C1(temporarily, only during your function is executed). Finally call Evaluateto get the result of the formula.
要在 VBA 代码中使用数组公式，请将应用程序对象的 ReferenceStyle 设置为 xlR1C1 （暂时，仅在您的函数执行期间）。最后调用 Evaluate来获取公式的结果。
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
And so it looks on sheet:
所以它在工作表上看起来：
