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

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


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

VBA Constant Image

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.ShowEnd Sub

Make sure you declare the sub in the Workbook Object itself:enter image description here


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= 123end property

In a Class module then globally create it;

public cMyStuff as new MyStuffClass

So cMyStuff.myIntegerThing is available immediately.