How do I declare a global variable in 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.
What do I need to do to create the global variable?
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
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:
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.
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
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.
Read more... Read less...
To use global variables, Insert New Module from VBA Project UI and declare variables using
Global iRaw As Integer Global iColumn As Integer
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).
Two other quick things:
- Be organized when using workbook level variables, so your code doesn't get confusing. Prefer Functions (with proper data types) or passing arguments ByRef.
- If you want a variable to preserve its value between calls, you can use the Static statement.
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 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
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).
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
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