在 Excel 2007 VBA 函数中使用 VLOOKUP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8504476/
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
Using VLOOKUP in an Excel 2007 VBA Function
提问by Sniff The Glove
I have tried hours and hours of examples but I continually get the same error of #NAME?
我已经尝试了数小时的示例，但我不断收到相同的 #NAME 错误？
I need to use VLOOKUP within a VBA function and then work with the result before putting the result to a cell. Firstly I am having a problem just to get the VBA VLOOKUP part working.
我需要在 VBA 函数中使用 VLOOKUP，然后在将结果放入单元格之前处理结果。首先，我遇到了一个问题，只是为了让 VBA VLOOKUP 部分工作。
For testing here is the details Spreadsheet is...
此处用于测试的详细信息电子表格是...
Worksheet = Sheet1
工作表 = Sheet1
4 rows x 2 columns of data. Cells D1:E4 Dance : 23 French : 42 English : 2 Music : 33
4 行 x 2 列数据。单元格 D1:E4 舞蹈：23 法语：42 英语：2 音乐：33
In cell A1 I will have a user imputed content that will a value from column D eg French
在单元格 A1 中，我将有一个用户输入的内容，该内容将来自 D 列的值，例如法语
In Cell B1 I will call the function =GetQty(A1,D1:E4,2) but I always get #NAME?
在单元格 B1 中，我将调用函数 =GetQty(A1,D1:E4,2) 但我总是得到 #NAME？
1) How do I call the function properly. Am I doing this corretly?
1) 如何正确调用函数。我这样做正确吗？
In my VBA function "GetQty" I just want to do the VLOOKUP on a value of cell A1 and return the value from the matching entry from the table(Cell D1:E4), eg return 42 and then place that value into column B1 and (also column C1 via the VBA and not just using a formula in the worksheet cell of =B1 )
在我的 VBA 函数“GetQty”中，我只想对单元格 A1 的值执行 VLOOKUP 并从表（单元格 D1：E4）中的匹配条目返回值，例如返回 42，然后将该值放入列 B1 和（也是通过 VBA 列 C1 而不仅仅是在 =B1 的工作表单元格中使用公式）
Here is one of the many examples I have tried in my function just to get the VLOOKUP to work.
这是我在函数中尝试过的众多示例之一，只是为了让 VLOOKUP 正常工作。
Function GetQty(Celly As Range, CellyRange As Range, Colretval As Integer) As Integer
Dim result As String
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("Sheet1")
GetQty = Application.WorksheetFunction.VLookup(sheet.Range(CellyRange), sheet.Range(CellyTable), Colretval, False)
End Function
I have tried many examples but I always get #NAME? error
我试过很多例子，但我总是得到#NAME？错误
回答by aevanko
There are a few issues, mainly:
有几个问题，主要是：
- First argument for Vlookup cannot be a range, needs to be a value
- It's safer to not declare the return type as Integer
- Vlookup 的第一个参数不能是一个范围，必须是一个值
- 不将返回类型声明为 Integer 更安全
Here's a working example of how to use Vlookup from VBA, maybe it'll help (you call it just VLookup):
这是一个关于如何从 VBA 使用 Vlookup 的工作示例，也许它会有所帮助（您称之为 VLookup）：
Function VBAVlookup(ByVal search As Variant, _
cell_range As Range, _
offset As Long, _
Optional opt As Boolean = False)
Dim result As Variant
result = WorksheetFunction.VLookup(search, cell_range, offset, opt)
'do some cool things to result
VBAVlookup = result
End Function
Unless what you are doing is rather sophisticated, you can always use =Vlookup(...) * 55 +2and stuff like that to return a manipulated result from vlookup using just one formula.
除非你正在做的事情相当复杂，否则你总是可以使用=Vlookup(...) * 55 +2和类似的东西来使用一个公式从 vlookup 返回一个操纵结果。
回答by Shankar
Below is the sample for getting the range of a column:
以下是获取列范围的示例：
Range("A2", Range("A2").End(xlDown)).Rows.Count
Customize you way, so this may help you passing your range.
自定义您的方式，因此这可以帮助您通过您的范围。
