在 VBA 函数中使用命名单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17232674/
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 named cells in a VBA function
提问by user2453446
I have a worksheet where I have defined names for certain cells. These cells will be used in the function and I am calling them using their names.
我有一个工作表，我在其中为某些单元格定义了名称。这些单元格将在函数中使用，我使用它们的名称来调用它们。
However, I get 0 as a return of the function when I call it with Excel, as if the names were not linked or had a 0 value.
但是，当我用 Excel 调用函数时，我得到 0 作为函数的返回值，就好像名称没有链接或具有 0 值一样。
Below is the code I wrote. "Sum_Len_1", "L_W_2" ans "L_W_1" are the names I gave to the source cells.
下面是我写的代码。“Sum_Len_1”、“L_W_2”和“L_W_1”是我给源单元格起的名字。
Function min_w(depth)
If depth < Sum_Len_1 Then
min_w = L_W_1 * 0.868 * depth / 1000
Else
min_w = L_W_1 * 0.868 * Sum_Len_1 / 1000 + L_W_2 * 0.868 * (depth - Sum_Len_1) / 1000
End If
End Function
How can I solve the problem?
我该如何解决问题？
回答by MiVoth
If you just write min_w = L_W_1 * 0.868 * depth / 1000vba thinks L_W_1it's variable (of the type variant with value=0).
You have to do it like this Range("L_W_1").Valueto reference the named cell.
如果你只是写min_w = L_W_1 * 0.868 * depth / 1000vba 认为L_W_1它是变量（值=0 的类型变体）。您必须像这样Range("L_W_1").Value引用命名单元格。
It should work if you change it to:
如果您将其更改为：
Function min_w(depth As Long)
If depth < Range("SUM_LEN_1").Value Then
min_w = Range("L_W_1").Value * 0.868 * depth / 1000
Else
min_w = Range("L_W_1").Value * 0.868 * Range("SUM_LEN_1").Value / 1000 + Range("L_W_2").Value * 0.868 * (depth - Range("SUM_LEN_1").Value) / 1000
End If
End Function
回答by robotik
You can just put them in brackets to mark them as a range: [Sum_Len_1].Value, [L_W_2].Value and [L_W_1].Value
您可以将它们放在括号中以将它们标记为一个范围：[Sum_Len_1].Value、[L_W_2].Value 和 [L_W_1].Value
