Is it possible to declare a public variable in vba and assign a default value?

I want to do this but it won't compile:

Public MyVariable as Integer = 123

What's the best way of achieving this?

.NET has spoiled us :) Your declaration is not valid for VBA.

Only constants can be given a value upon application load. You declare them like so:

Public Const APOSTROPHE_KEYCODE = 222

Here's a sample declaration from one of my vba projects:

If you're looking for something where you declare a public variable and then want to initialize its value, you need to create a Workbook_Open sub and do your initialization there. Example:

Private Sub Workbook_Open()
  Dim iAnswer As Integer
  
  InitializeListSheetDataColumns_S
  HideAllMonths_S
  
  If sheetSetupInfo.Range("D6").Value = "Enter Facility Name" Then
    iAnswer = MsgBox("It appears you have not yet set up this workbook.  Would you like to do so now?", vbYesNo)
    If iAnswer = vbYes Then
      sheetSetupInfo.Activate
      sheetSetupInfo.Range("D6").Select
      Exit Sub
    End If
  End If

  Application.Calculation = xlCalculationAutomatic
  sheetGeneralInfo.Activate
  Load frmInfoSheet
  frmInfoSheet.Show
  
    
End Sub

Make sure you declare the sub in the Workbook Object itself:

Just to offer you a different angle -

I find it's not a good idea to maintain public variables between function calls. Any variables you need to use should be stored in Subs and Functions and passed as parameters. Once the code is done running, you shouldn't expect the VBA Project to maintain the values of any variables.

The reason for this is that there is just a huge slew of things that can inadvertently reset the VBA Project while using the workbook. When this happens, any public variables get reset to 0.

If you need a value to be stored outside of your subs and functions, I highly recommend using a hidden worksheet with named ranges for any information that needs to persist.

Sure you know, but if its a constant then const MyVariable as Integer = 123 otherwise your out of luck; the variable must be assigned an initial value elsewhere.

You could:

public property get myIntegerThing() as integer
    myIntegerThing= 123
end property

In a Class module then globally create it;

public cMyStuff as new MyStuffClass

So cMyStuff.myIntegerThing is available immediately.

Little-Known Fact:
_{A named range can refer to a value instead of specific cells.}

This could be leveraged to act like a "global variable", plus you can refer to the value from VBA and in a worksheet cell, and the assigned value will even persist after closing & re-opening the workbook!

---

• To "declare" the name myVariable and assign it a value of 123:

  ThisWorkbook.Names.Add "myVariable", 123
  

• To retrieve the value (for example to display the value in a MsgBox):

    MsgBox [myVariable]
  

• Alternatively, you could refer to the name with a string: (identical result as square brackets)

  MsgBox Evaluate("myVariable")
  

• To use the value on a worksheet just use it's name in your formula as-is:

  =myVariable
  

• In fact, you could even store function expressions: (sort of like in JavaScript)
  ^{(Admittedly, I can't actually think of a situation where this would be beneficial - but I don't use them in JS either.)}

  ThisWorkbook.Names.Add "myDay", "=if(isodd(day(today())),""on day"",""off day"")"
  

---

Square brackets are just a shortcut for the Evaluate method. I've heard that using them is considered messy or "hacky", but I've had no issues and their use in Excel is supported by Microsoft.

There is probably also a way use the Range function to refer to these names, but I don't see any advantage so I didn't look very deeply into it.

---

More info:

• Microsoft Office Dev Center: Names.Add method (Excel)

• Microsoft Office Dev Center: Application.Evaluate method (Excel)

As told above, To declare global accessible variables you can do it outside functions preceded with the public keyword.

And, since the affectation is NOT PERMITTED outside the procedures, you can, for example, create a sub called InitGlobals that initializes your public variables, then you just call this subroutine at the beginning of your statements

Here is an example of it:

Public Coordinates(3) as Double
Public Heat as double
Public Weight as double

Sub InitGlobals()
    Coordinates(1)=10.5
    Coordinates(2)=22.54
    Coordinates(3)=-100.5
    Heat=25.5
    Weight=70
End Sub

Sub MyWorkSGoesHere()
    Call InitGlobals
    'Now you can do your work using your global variables initialized as you wanted them to be.
End Sub

You can define the variable in General Declarations and then initialise it in the first event that fires in your environment.

Alternatively, you could create yourself a class with the relevant properties and initialise them in the Initialise method

This is what I do when I need Initialized Global Constants:

1. Add a module called Globals

2. Add Properties like this into the Globals module:

   Property Get PSIStartRow() As Integer
   PSIStartRow = Sheets("FOB Prices").Range("F1").Value
   End Property
   Property Get PSIStartCell() As String
   PSIStartCell = "B" & PSIStartRow
   End Property

there is one way to properly solve your question. i have the same concern with you for a long time. after searching and learning for a long time, finally i get a solution for this kind of question.

The solution is that no need to declare the variable and no need to set value to the variable, and even no need VBA code. Just need the "named range" in excel itself.

For example, the "A1" cell content is "hello, world". and we define the "A1" cell a name as "hello", that is, the "A1" cell have a name now, it's called "hello". In VBA code, we just need use this method [hello], then we can get the "A1" value.

Sub test()
msgbox [hello]
end sub

the msgbox will show "Hello, word".

this way, we get a global variable without any declaration or assignment. it can be used in any Sub or Function.

we can define many named range in excel, and in VBA code we just use [] method to get the range value.

in fact, the [hello] is a abbreviation of the function Evaluate["Hell"], but it's more shorter.

It's been quite a while, but this may satisfy you :

Public MyVariable as Integer: MyVariable = 123

It's a bit ugly since you have to retype the variable name, but it's on one line.