How do I declare a global variable in VBA

Question

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

User · Answer

Also you can use -  Private Const SrlNumber As Integer   910  Private Sub Workbook Open       If SrlNumber  gt  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

User · Answer

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    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

User · Answer

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 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

User · Answer

To use global variables  Insert New Module from VBA Project UI and declare variables using Global  Global iRaw As Integer Global iColumn As Integer

User · Answer

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  amp       amp  Numerator  amp       amp  Format Now   yyyy-mm-dd H-mm-ss    amp    CSV                        objAtt SaveAsFile saveFolder  amp       amp  FileName                       Numerator   Numerator   1            Set objAtt   Nothing      Next End Sub

User · Answer

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

User · Answer

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   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

User · Answer

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

[excel] How do I declare a global variable in VBA?

Examples related to excel

Examples related to vba

Examples related to scope

Examples related to global-variables