跨同一模块和工作簿中多个 vba 宏的变量和常量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19635899/
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
Variables and constants across multiple vba macros in same module & workbook
提问by gabx
After a lot of small sub() writing in the same Excel workbook, I realised that I often used same part code, variables and constants. Thus I decided to write funcions() for the code, and declare variables & constant/static as Public outside functions and sub. I am very new to vba declarations and this is not so easy. Let me give you one summary of what i want to achieve. I have writen all funcions and sub in one module under the module directory of the workbook.
在同一个 Excel 工作簿中写了很多小 sub() 之后，我意识到我经常使用相同的部分代码、变量和常量。因此，我决定为代码编写 funcions()，并将变量和常量/静态声明为公共外部函数和子函数。我对 vba 声明很陌生，这并不容易。让我给你总结一下我想要实现的目标。我已经在工作簿的模块目录下的一个模块中编写了所有功能和子程序。
Option Explicit
Public ToDate As String ' variable I use in many sub and functions
Public MyPath As String ' variable I use in many sub and functions
Public NameOfWorker As Variant ' constant I use in many sub and functions
Public Salary As Double ' constant I use in many sub and functions
NameOfWorker = Cells(14, 19) ' !!! PB : 14 is highlighed with error : incorrect instruction outside a procedure
Salary = Cells(20, 7).Value '!!! same as above
How and where shall I declare such constants/statics ? Shall I write a "special" procedure to declare all these variables and constants ? I tried many way to declare them with no success.
我应该如何以及在哪里声明这样的常量/静态？我应该编写一个“特殊”程序来声明所有这些变量和常量吗？我尝试了很多方法来声明它们但没有成功。
Public Static NameOfWorker = Cells(14, 19) As String ' not working
''''''
Public Static nameOfWorker As String
NameOfWorker = Cells(14, 19) ' not working
''' etc etc
Thank you for help.
谢谢你的帮助。
EDIT: after more reading, I found one solution this way:
编辑：经过更多阅读，我以这种方式找到了一种解决方案：
Public Const MY_PATH = "Y:\path\to\directory\"
Public Const WORKERNAME = "14, 19"
Not so bad :-)
没那么糟糕:-)
采纳答案by Gary's Student
Good question!
好问题！
I would Dimthe globals above all the subs in the module, but initializethe globals at a convenient spot within some sub. For example:
我会变暗的模块中的所有潜艇上面的全局变量，但初始化一些子内便利的全局。例如：
Public NameOfWorker As String
Public AgeOfWorker As Long
Public SetupComplete As Boolean
Sub MAIN()
If SetupComplete Then
Else
NameOfWorker = Sheets("Sheet1").Range("B9")
AgeOfWorker = Sheets("Sheet1").Range("B10")
SetupComplete = True
MsgBox "Global variable set up complete!"
End If
End Sub
回答by ForkandBeard
You could create a new module called something like DataHelperwhich looks like this:
您可以创建一个名为类似的新模块DataHelper，如下所示：
Private NameOfWorker As String
Private AgeOfWorker As Long
Private SetupComplete As Boolean
Public Function GetNameOfWorker()
If NameOfWorker = "" Then
NameOfWorker = Sheets("SomeSheet").Cells(14, 19)
End If
GetNameOfWorker = NameOfWorker
End Function
Public Function GetAgeOfWorker()
...
End Function
Now in any other code you can retreive the value:
现在在任何其他代码中，您可以检索该值：
Sub SomeMethod()
Cells(1, 1).Value = DataHelper.GetNameOfWorker()
End Sub
...and you never have to worry if it's been set.
...如果它已经设置，你永远不必担心。
