在 VBA 函数中使用命名范围进行 VLOOKUP

Using named range in VBA function for VLOOKUP

提问by Mike Rockétt

I have a the following on my worksheet:

我的工作表上有以下内容：

• A cell that shows a currency [in A1]
• A range of cells (two columns, one for the currency, and the other for a corresponding commission percentage) [defined as/named RANGE, and scoped to the worksheet]
• A cell that [is trying] to determine the calculated commission percentage based on A1and RANGE

• 显示货币的单元格 [in A1]
• 一系列单元格（两列，一列用于货币，另一列用于相应的佣金百分比）[定义为/命名RANGE，范围限定于工作表]
• [正在尝试]根据A1和确定计算的佣金百分比的单元格RANGE

I then have a VBA function called Calculate, as follows:

然后我有一个名为 的 VBA 函数Calculate，如下所示：

Function Calculate(LookupValue As Double, LookupRange As Range) As Double
    Calculate = [VLOOKUP(LookupValue, LookupRange, 2)]
End Function

The cell that determines the percentage has the following:

确定百分比的单元格具有以下内容：

=Calculate(A1, RANGE)

Problem is that the cell just returns #VALUE!...

问题是单元格刚刚返回#VALUE!......

Any idea what I could be doing wrong?

知道我可能做错了什么吗？

I have tried several things like type-hinting to Range(), passing LookupRange.Value2to VLOOKUP, etc, none of which worked.

我尝试了几种方法，例如类型提示 to Range()、传递LookupRange.Value2toVLOOKUP等，但都没有奏效。

I have also tried to debug, noting that LookupRangedoes actually contain the range required in Value2, which is is why I tried to pass it to the function.

我也尝试过调试，注意到LookupRange确实包含 中所需的范围Value2，这就是我尝试将其传递给函数的原因。

Side Note:The function and layout mentioned above is just a dummy - the actual function is somewhat more complex as it relies on negotiated rates, monthly margins, etc. This is why I'm using VBA in the first place. I know that I'm doing something wrong with the lookup, as it is the only thing that seems to be failing within the function - everything else corresponds and calculates.

旁注：上面提到的功能和布局只是一个虚拟的 - 实际功能有点复杂，因为它依赖于协商费率、每月利润等。这就是我首先使用 VBA 的原因。我知道我在查找时做错了，因为它是函数中唯一似乎失败的东西 - 其他一切都对应并计算。

回答by Dmitry Pavliv

From MSDN:

从MSDN：

The advantage of using square brackets is that the code is shorter. The advantage of using Evaluateis that the argument is a string, so you can either construct the string in your code or use a Visual Basic variable.

使用方括号的好处是代码更短。使用的优点Evaluate是参数是一个字符串，因此您可以在代码中构造字符串或使用 Visual Basic 变量。

in other words you canuse

换句话说，你可以使用

Calculate = [VLOOKUP(3, A1:B100, 2)]

but you can notuse

但你不能使用

LookupValue = 3
LookupRange = Range("A1:B100")
'or
'LookupRange = "A1:B100"
Calculate = [VLOOKUP(LookupValue, LookupRange, 2)]

What you can do is:

你可以做的是：

Option 1:

选项1：

Function Calculate(LookupValue As Double, LookupRange As Range) As Double
    Calculate = Evaluate("VLOOKUP(" & LookupValue & "," & LookupRange.Address & ", 2")
End Function

or better:

或更好：

Function Calculate(LookupValue As Double, LookupRange As Range) As Double
    Calculate = Evaluate("VLOOKUP(" & LookupValue & ",'" & _
        LookupRange.Parent.Name & "'!" & LookupRange.Address & ", 2")
End Function

However I suggest:

不过我建议：

Option 2:

选项 2：

Function Calculate(LookupValue As Double, LookupRange As Range) As Double
    Calculate = Application.VLookup(LookupValue, LookupRange, 2)
End Function

I hope you know about meaning of 4th parameter:

我希望你知道第四个参数的含义：

If TRUEor omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUPmay not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

如果TRUE或 省略，则返回精确或近似匹配。如果未找到精确匹配，则返回小于 lookup_value 的下一个最大值。table_array 第一列的值必须按升序排列；否则，VLOOKUP可能不会给出正确的值。您可以通过从“数据”菜单中选择“排序”命令并选择“升序”将值按升序排列。

Btw, Calculateis not good name for UDF, since VBA already has function Application.Calculate. I'd rename it to avoid confusion.

顺便说一句，CalculateUDF 不是个好名字，因为 VBA 已经有函数Application.Calculate. 我会重命名它以避免混淆。