如何在 VBA 中声明全局变量？

How do I declare a global variable in VBA?

提问by Nimrod

I wrote the following code:

我写了以下代码：

Function find_results_idle()

    Public iRaw As Integer
    Public iColumn As Integer
    iRaw = 1
    iColumn = 1

And I get the error message:

我收到错误消息：

"invalid attribute in Sub or Function"

“Sub 或 Function 中的无效属性”

Do you know what I did wrong?

你知道我做错了什么吗？

I tried to use Globalinstead of Public, but got the same problem.

我尝试使用Global而不是Public，但遇到了同样的问题。

I tried to declare the function itself as `Public, but that also did no good.

我试图将函数本身声明为“公共”，但这也没有好处。

What do I need to do to create the global variable?

我需要做什么来创建全局变量？

回答by SLaks

You need to declare the variables outside the function:

您需要在函数外声明变量：

Public iRaw As Integer
Public iColumn As Integer

Function find_results_idle()
    iRaw = 1
    iColumn = 1

回答by Ben McCormack

This is a question about scope.

这是一个关于scope的问题。

If you only want the variables to last the lifetime of the function, use Dim(short for Dimension) inside the function or sub to declare the variables:

如果您只想让变量持续到函数的生命周期，请在函数或 sub 中使用Dim（Dimension 的缩写）来声明变量：

Function AddSomeNumbers() As Integer
    Dim intA As Integer
    Dim intB As Integer
    intA = 2
    intB = 3
    AddSomeNumbers = intA + intB
End Function
'intA and intB are no longer available since the function ended

A globalvariable (as SLaks pointed out) is declared outside of the function using the Publickeyword. This variable will be available during the life of your running application. In the case of Excel, this means the variables will be available as long as that particular Excel workbook is open.

甲全局变量（如SLaks指出）被声明使用该函数的外Public关键字。此变量将在您正在运行的应用程序的生命周期内可用。对于 Excel，这意味着只要该特定 Excel 工作簿处于打开状态，变量就可用。

Public intA As Integer
Private intB As Integer

Function AddSomeNumbers() As Integer
    intA = 2
    intB = 3
    AddSomeNumbers = intA + intB
End Function
'intA and intB are still both available.  However, because intA is public,  '
'it can also be referenced from code in other modules. Because intB is private,'
'it will be hidden from other modules.

You can also have variables that are only accessible within a particular module (or class) by declaring them with the Privatekeyword.

您还可以通过使用Private关键字声明变量来拥有只能在特定模块（或类）中访问的变量。

If you're building a big application and feel a need to use global variables, I would recommend creating a separate module just for your global variables. This should help you keep track of them in one place.

如果您正在构建一个大型应用程序并且觉得需要使用全局变量，我建议您为全局变量创建一个单独的模块。这应该可以帮助您在一个地方跟踪它们。

回答by YOU

To use global variables, Insert New Module from VBA Project UI and declare variables using Global

要使用全局变量，从 VBA 项目 UI 插入新模块并使用声明变量 Global

Global iRaw As Integer
Global iColumn As Integer

回答by FCastro

The question is really about scope, as the other guy put it.

正如另一个人所说，问题实际上是关于范围的。

In short, consider this "module":

简而言之，考虑这个“模块”：

Public Var1 As variant     'Var1 can be used in all
                           'modules, class modules and userforms of 
                           'thisworkbook and will preserve any values
                           'assigned to it until either the workbook
                           'is closed or the project is reset.

Dim Var2 As Variant        'Var2 and Var3 can be used anywhere on the
Private Var3 As Variant    ''current module and will preserve any values
                           ''they're assigned until either the workbook
                           ''is closed or the project is reset.

Sub MySub()                'Var4 can only be used within the procedure MySub
    Dim Var4 as Variant    ''and will only store values until the procedure 
End Sub                    ''ends.

Sub MyOtherSub()           'You can even declare another Var4 within a
    Dim Var4 as Variant    ''different procedure without generating an
End Sub                    ''error (only possible confusion). 

You can check out this MSDN referencefor more on variable declaration and this other Stack Overflow Questionfor more on how variables go out of scope.

您可以查看此MSDN 参考以获取有关变量声明的更多信息，并查看此其他堆栈溢出问题以获取有关变量如何超出范围的更多信息。

Two other quick things:

另外两个快速的事情：

1. Be organized when using workbook level variables, so your code doesn't get confusing. Prefer Functions(with proper data types) or passing arguments ByRef.
2. If you want a variable to preserve its value between calls, you can use the Staticstatement.

1. 使用工作簿级别的变量时要井井有条，这样您的代码就不会变得混乱。首选函数（具有适当的数据类型）或传递参数ByRef。
2. 如果希望变量在调用之间保留其值，可以使用Static语句。

回答by Zai

If this function is in a module/class, you could just write them outside of the function, so it has Global Scope. Global Scope means the variable can be accessed by another function in the same module/class(if you use dimas declaration statement, use publicif you want the variables can be accessed by all function in all modules) :

如果这个函数在一个模块/类中，你可以把它们写在函数之外，所以它有Global Scope. 全局作用域意味着该变量可以被同一模块/类中的另一个函数访问（如果dim用作声明语句，public如果您希望变量可以被所有模块中的所有函数访问，请使用）：

Dim iRaw As Integer
Dim iColumn As Integer

Function find_results_idle()
    iRaw = 1
    iColumn = 1
End Function

Function this_can_access_global()
    iRaw = 2
    iColumn = 2
End Function

回答by Dan Bidner

Create a public integer in the General Declaration.

在通用声明中创建一个公共整数。

Then in your function you can increase its value each time. See example (function to save attachements of an email as CSV).

然后在你的函数中你可以每次增加它的值。请参阅示例（将电子邮件的附件保存为 CSV 的功能）。

Public Numerator As Integer

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
Dim FileName As String

saveFolder = "c:\temp\"

     For Each objAtt In itm.Attachments
            FileName = objAtt.DisplayName & "_" & Numerator & "_" & Format(Now, "yyyy-mm-dd H-mm-ss") & ".CSV"
                      objAtt.SaveAsFile saveFolder & "\" & FileName
                      Numerator = Numerator + 1

          Set objAtt = Nothing
     Next
End Sub

回答by Double E CPU

A good way to create Public/Global variables is to treat the Form like a class object and declare properties and use Public Property Get [variable] to access property/method. Also you might need to reference or pass a Reference to the instantiated Form module. You will get errors if you call methods to forms/reports that are closed.
Example: pass Me.Form.Module.Parent into sub/function not inside form.

创建公共/全局变量的一个好方法是将 Form 视为类对象并声明属性并使用 Public Property Get [variable] 访问属性/方法。此外，您可能需要引用或传递一个引用到实例化的 Form 模块。如果您调用已关闭的表单/报表的方法，您将收到错误消息。
示例：将 Me.Form.Module.Parent 传递到子/函数中，而不是在表单中。

Option Compare Database 
Option Explicit
''***********************************''
' Name: Date: Created Date Author: Name 
' Current Version: 1.0
' Called by: 
''***********************************''
' Notes: Explain Who what when why... 
' This code Example requires properties to be filled in 
''***********************************''
' Global Variables
Public GlobalData As Variant
''***********************************''
' Private Variables
Private ObjectReference As Object
Private ExampleVariable As Variant
Private ExampleData As Variant
''***********************************''
' Public properties
Public Property Get ObjectVariable() As Object
   Set ObjectVariable = ObjectReference
End Property 
Public Property Get Variable1() As Variant 
  'Recommend using variants to avoid data errors
  Variable1 = ExampleVariable
End property
''***********************************''
' Public Functions that return values
Public Function DataReturn (Input As Variant) As Variant
   DataReturn = ExampleData + Input
End Function 
''***********************************''
' Public Sub Routines
Public Sub GlobalMethod() 
   'call local Functions/Subs outside of form
   Me.Form.Refresh
End Sub
''***********************************''
' Private Functions/Subs used not visible outside 
''***********************************''
End Code

So in the other module you would be able to access:

因此，在另一个模块中，您将能够访问：

Public Sub Method1(objForm as Object)
   'read/write data value
   objForm.GlobalData
   'Get object reference (need to add Public Property Set to change reference object)
   objForm.ObjectVariable
   'read only (needs Public property Let to change value)
   objForm.Variable1
   'Gets result of function with input
   objForm.DataReturn([Input])
   'runs sub/function from outside of normal scope
   objForm.GlobalMethod
End Sub

If you use Late Binding like I do always check for Null values and objects that are Nothing before attempting to do any processing.

如果您像我一样使用后期绑定，则总是在尝试进行任何处理之前检查 Null 值和为 Nothing 的对象。

回答by IshaniNet

Also you can use -

你也可以使用 -

Private Const SrlNumber As Integer = 910

Private Sub Workbook_Open()
    If SrlNumber > 900 Then
        MsgBox "This serial number is valid"
    Else
        MsgBox "This serial number is not valid"
    End If
End Sub

Its tested on office 2010

它在 office 2010 上进行了测试