如何在VBA中声明一个全局变量?

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"

“子或函数中的无效属性”

Do you know what I did wrong?

你知道我做错了什么吗?

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

我试着用全球化而不是公共，但也遇到了同样的问题。

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

我试图将这个函数本身声明为“Public”，但这也没有什么好处。

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

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

6 个解决方案

#1

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

#2

This is a question about 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:

如果您只希望变量持续使用函数的生命周期，则在函数或子函数中使用Dim(维度)来声明变量:

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 global variable (as SLaks pointed out) is declared outside of the function using the Public keyword. 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 Private keyword.

您还可以拥有仅在特定模块(或类)中使用私有关键字来访问的变量。

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.

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

#3

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

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

Global iRaw As Integer
Global iColumn As Integer

#4

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 dim as declaration statement, use public if you want the variables can be accessed by all function in all modules) :

如果这个函数在一个模块/类中，你可以把它们写在函数外面，这样它就有全局作用域。全局范围意味着变量可以在同一个模块/类中被另一个函数访问(如果您使用dim作为声明语句，如果您希望变量可以被所有模块中的所有函数访问):

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

#5

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 reference for more on variable declaration and this other Stack Overflow Question for 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. 使用工作簿级变量时要组织起来，这样代码就不会混乱。更喜欢函数(具有适当的数据类型)或通过ref传递参数。
3. If you want a variable to preserve its value between calls, you can use the Static statement.
4. 如果需要一个变量来保存调用之间的值，可以使用静态语句。

#6

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