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

Question

I want to do this but it won t compile   Public MyVariable as Integer   123   What s the best way of achieving this

User · Answer

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

User · Answer

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

User · Answer

Little-Known Fact  A named range can refer to a value instead of specific cells  This could be leveraged to act like a  quot global variable quot   plus you can refer to the value from VBA and in a worksheet cell  and the assigned value will even persist after closing  amp  re-opening the workbook    To  quot declare quot  the name myVariable and assign it a value of 123  ThisWorkbook Names Add  quot myVariable quot   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  quot myVariable quot     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  quot myDay quot    quot  if isodd day today      quot  quot on day quot  quot   quot  quot off day quot  quot   quot      Square brackets are just a shortcut for the Evaluate method   I ve heard that using them is considered messy or  quot hacky quot   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

User · Answer

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

User · Answer

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

User · Answer

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   amp  PSIStartRow   End Property

User · Answer

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

User · Answer

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

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

Examples related to vba

Examples related to variables

Examples related to default-value

Examples related to public

Examples related to variable-declaration