vba 将工作表单元格的值分配给常量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23951214/
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
Assigning the value of a worksheet cell to a constant
提问by Haris
I am trying to assign the value of a worksheet cell to a constant variable in a VBA macro. The logic behind that action is that the end user is supposed to enter the current week in a specified cell before running the Macro. Since this value is going to be reused throughout the macro and I wanted to play it safe, I tried to declare it as a public constant:
我正在尝试将工作表单元格的值分配给 VBA 宏中的常量变量。该操作背后的逻辑是，最终用户应该在运行宏之前在指定单元格中输入当前周。由于此值将在整个宏中重复使用，并且我想安全使用它，因此我尝试将其声明为公共常量：
private const thisWeek as Integer = Range("B1")
However I get an error message about a constant value being needed. So, is it even possible to declare a constant like this in VBA?
但是，我收到一条关于需要一个常量值的错误消息。那么，甚至可以在 VBA 中声明这样的常量吗？
采纳答案by L42
No it is not possible. As the word suggest it should be Constant.
不，这是不可能的。顾名思义，它应该是Constant。
Workaround:
解决方法：
Public Const weekRange As String = "$B"
Then in your code:
然后在你的代码中：
Sub Something()
Dim thisWeek As Integer: thisWeek = Range(weekRange).Value
'~~> some codes here
End Sub
回答by Dmitry Merkis
From help: You can't use variables, user-defined functions, or intrinsic Visual Basic functions (such as Chr) in expressions assigned to constants.
来自帮助：您不能在分配给常量的表达式中使用变量、用户定义函数或内部 Visual Basic 函数（如 Chr）。
In your case you have to use a variable.
在您的情况下，您必须使用变量。
回答by Joe Patrick
I know this is old but I was looking to do this myself and came up with this. I use a function:
我知道这很旧，但我想自己做这件事并想出了这个。我使用一个函数：
Function insertPNA(strSource As Long) As String
Dim strResult As String
strResult = Replace(strSource, strSource, ThisWorkbook.Sheets("Audits & Actuals").Range("pnacode").Value)
insertPNA = strResult
End Function
Whenever I want the pna code I just type insertpna(1). Hope someone finds this useful. (It doesn't have to be "1" obvs, I just used it to minimize typing.)
每当我想要 pna 代码时，我只需键入 insertpna(1)。希望有人觉得这很有用。（它不必是“1”obvs，我只是用它来减少打字。）
