如何在 VBA 中使用 OFFSET 工作表函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20795549/
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
How can I use the OFFSET worksheet function in VBA?
提问by JSRevenge
I want to create a custom worksheet function that emulates the vlookup-match-offset combination and accepts only 3 arguments: the vertical lookup value, the horizontal lookup value, and the array containing the return values and header/left margin. If those values are represented by v, h, and r, I want the custom function to emulate:
我想创建一个模拟 vlookup-match-offset 组合的自定义工作表函数，并且只接受 3 个参数：垂直查找值、水平查找值以及包含返回值和标题/左边距的数组。如果这些值由 v、h 和 r 表示，我希望自定义函数模拟：
=vlookup(v,r,match(h,offset(r,0,0,1),0),false)
The code I tried to write is as follows (using Excel 2010):
我尝试编写的代码如下（使用 Excel 2010）：
Function xlookup (v as Variant, h as Variant, r as Range) as Variant
xlookup = Application.WorksheetFunction.Vlookup(v, r, _
Application.WorksheetFunction.Match(h, _
Application.WorksheetFunction.Offset(r,0,0,1),0),false)
Unfortunately, I don't think Offsetis a method of WorksheetFunction. What's the best way to return the first row of a range as a range?
不幸的是，我不认为Offset是WorksheetFunction. 将范围的第一行作为范围返回的最佳方法是什么？
回答by JSRevenge
I figured it out. For my needs, Index works just fine.
我想到了。对于我的需要，Index 工作得很好。
Function xlookup(v As Variant, h As Variant, r As Range) As Variant
With Application.WorksheetFunction
xlookup = .VLookup(v, r, .Match(h, .Index(r, 1, 0), 0), False)
End With
End Function
回答by Slai
If anyone else is looking for an answer to this, the Excel Formula
如果其他人正在寻找答案，Excel 公式
OFFSET( A1, 1, 2, 3, 4 )
in VBA is
在 VBA 中是
Range("A1").Resize(3, 4).Offset(1, 2)
在问题中，
offset(r,0,0,1)offset(r,0,0,1)只是获取（列）范围内的第一个单元格，因此可以将其简化为r(1)r(1)VBA。回答by chiangy77
This can be done very simply with the Evaluate() function.
这可以使用 Evaluate() 函数非常简单地完成。
This evaluate function is super powerful. It can take your formula which you type into normal Excel and give you the answer straight away. For example, if you had a formula in a particular cell, for example, =sum(A1:A4), then in VBA you just go Evaluate("=sum(A1:A4)")!
这个评估功能超级强大。它可以将您输入到普通 Excel 中的公式立即给您答案。例如，如果您在特定单元格中有一个公式，例如 =sum(A1:A4)，那么在 VBA 中您只需执行 Evaluate("=sum(A1:A4)")！
The one thing to be wary of is you need the right number of brackets otherwise it'll return #value
需要注意的一件事是您需要正确数量的括号，否则它会返回 #value
