vba excel vba中的公共静态变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21806425/
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
Public Static variable in excel vba
提问by Zeus
Is it possible to have a static variable declared in one procedure, and use this variable in several different procedures using Excel VBA?
是否可以在一个过程中声明一个静态变量，并使用 Excel VBA 在几个不同的过程中使用这个变量？
i.e.
IE
Public myvar as integer
Sub SetVar()
static myvar as integer
myvar=999
end sub
sub Usevar()
dim newvar as integer
newvar=myvar*0.5
end sub
I need myvar to be seen by other procedures, and not change or get "lost". The code above works if myvar is not declared as a static variable, but more code then the variable is "lost". If the static declaration is used, myvar is not seen by the usevar procedure. And "Public Static myvar as integer" is not accepted by VBA.
我需要 myvar 被其他程序看到，而不是改变或“丢失”。如果 myvar 未声明为静态变量，则上面的代码有效，但是更多的代码则该变量“丢失”。如果使用静态声明，则 usevar 过程看不到 myvar。VBA 不接受“公共静态 myvar 作为整数”。
Thanks for your help
谢谢你的帮助
Zeus
宙斯
采纳答案by Gary's Student
Try this by calling MAIN:
通过调用MAIN试试这个：
Public myvar As Integer
Sub MAIN()
Call SetVar
Call UseVar
End Sub
Sub SetVar()
myvar = 999
End Sub
Sub UseVar()
Dim newvar As Variant
newvar = myvar * 0.5
MsgBox newvar
End Sub
If you declare an item Static, its value will be preserved within the procedure or sub. If you declare the item Public, its value will be preserved and it will be visible to other procedures as well.
如果您声明一个项目Static，其值将保留在过程或子中。如果您声明项目Public，它的值将被保留并且它也对其他过程可见。
回答by Egalth
Although this question was answered over four years ago by @Gary's Student, there's a subtle nuance worth mentioning, since the solution can depend on the data type of myvar.
尽管@Gary 的学生在四年多前回答了这个问题，但有一个微妙的细微差别值得一提，因为解决方案可能取决于myvar.
First of all, as you've noted in the question, Public Static myvar as Integerdoesn't work, because Staticis only allowed inside a sub or function.
首先，正如您在问题中所指出的，Public Static myvar as Integer不起作用，因为Static仅允许在子或函数内部使用。
As noted in the comments to the OP by @Patrick Lepelletier, you can easily get around this by declaring a Constantinstead (assuming you don't need to change it dynamically): Public Const myvar as Integer = 999. (Or possibly Private Const myvar...)
正如@Patrick Lepelletier 对 OP 的评论中所述，您可以通过声明 aConstant来轻松解决此问题（假设您不需要动态更改它）：Public Const myvar as Integer = 999。（或可能Private Const myvar...）
Another option is to declare myvaras a functioninstead of a variable or constant, effectively turning it into a pseudo-constant:
另一种选择是声明myvar为函数而不是变量或常量，有效地将其转换为伪常量：
Private Function myvar() as Integer
Static intMyvar as Integer
intMyvar = 999
myvar = intMyvar
End function
In this simple example where myvaris an integer, the pseudo-constant approach is obviously unnecessary and adds the overhead of a function call. Simply declaring a Constantdoes the job. However, using a constant only works if the value is static and not an object. It will not work if myvaris an object, for example a Range. In that case, using pseudo-constants might be useful:
在这个简单的例子中，其中myvar是一个整数，伪常量方法显然是不必要的，并且增加了函数调用的开销。只需声明 a 即可Constant完成工作。但是，仅当值是 static 而不是 object 时，才使用常量。如果myvar是一个对象，例如 a ，它将不起作用Range。在这种情况下，使用伪常量可能很有用：
Private Function myvar() as Range
Set myvar = Range("A1")
End Function
Another advantage is that you can use code inside the function to check for certain conditions and assign different values to myvaraccordingly.
另一个优点是您可以使用函数内部的代码来检查某些条件并相应地分配不同的值myvar。
The pseudo-constant approach can also be combined with naming worksheet ranges: If cell A1 is a named range, say MyRange, then you could write:
伪常量方法也可以与命名工作表范围结合使用：如果单元格 A1 是命名范围，例如MyRange，那么您可以编写：
Dim strMyString as String
strMyString = "MyRange"
Private Function myvar() as Range
Set myvar = Range(strMyString)
End Function
Now it is possible to move around the content of cell A1 without breaking the code, since the named range follows along if you cut and paste the cell. I find this approach useful in the design stage, when things tend to move around a lot in a worksheet.
现在可以在不破坏代码的情况下移动单元格 A1 的内容，因为如果您剪切和粘贴单元格，命名范围将随之而来。我发现这种方法在设计阶段很有用，因为工作表中的东西往往会移动很多。
Pseudo-constants also help avoiding some problemsusually associated with global (or module-level) variables that might be an issue in larger projects.
伪常量还有助于避免一些通常与全局（或模块级）变量相关的问题，这些问题在大型项目中可能是一个问题。
回答by On Chang
The key is to use 2 variables. In the code below, myvar is public but not static. stvar is static but not public. Its scope is only within Main(). By assigning myvar=stvar and stvar=myvar, it effectively creates a variable that is both public and static. The value is preserved.
关键是使用2个变量。在下面的代码中，myvar 是公开的但不是静态的。stvar 是静态的，但不是公开的。它的范围仅在 Main() 内。通过分配 myvar=stvar 和 stvar=myvar，它有效地创建了一个公共和静态变量。该值被保留。
Public myvar As String
Sub Main() 'in module 1
Static stvar As String
myvar = stvar
toInput
stvar = myvar
End Sub
Sub toInput() 'in module2
myvar = InputBox("enter something", "Input", myvar)
End Sub
