public VBA variable

VBA variables scope or VBA variables visibility

Based on visibility VBA variables can be of three types

1. Sub or Function level variables
2. Private (Module level) variables
3. Public or Global variables or Project level variables

Sub or Function level variables and constants are visible within that Sub or Function only. You can use or refer or change value of these type of variables within that Sub or Function only

In the above image subroutine MsgBox_a and MsgBox_b both have msgStr variable but they are independent and you can change variable values in their respective Sub itself.

If you run MsgBox_a then you will get “My name is Jack” in message box display.

If you run MsgBox_b then you will get no string  in message box display. In MsgBox_b no value is assigned to msgStr hence it will get default value of its respective data type which is null string for string data type.

Private (Module level) variables in Excel are visible to all Subs or Functions which are present in that module (module having private variables) only. It is declared at the very top before any Sub or Function.

You can declare it implicitly like below

Dim v_msgStr As String

Or

Explicitly  like below

Private v_msgStr As String

Private variables appear in Intellisense list of the module in which variable is declared. Press CTRL+SPACE and write module level variable name.

In the above image v_msgStr is module level variable and V_MYNAME is module level constant. These two variables can be used by Subs or Functions of the module in which these two variables are declared. If you run MsgBox_a then “My name is Jack” will be displayed and this will also assign value to v_msgStr variable.

Now run MsgBox_b “My name is Jack” will be displayed in message box. This is due to Msgbox_b sub referred a module level variable whose value is already assigned by running MsgBox_a sub.

Note:-  When same name variable or constant is declared inside a Sub of Function then sub or function level variable will be preferred over module level or public variable. So, in case of variable name conflict preference will be given to lowest order (first Sub or Function level then module level then public level).

If you run sub test_1 then Rock will be displayed in the message box. In this case V_MYNAME value of test_1 will be preferred over module level  value of V_MYNAME.

If you run sub test_2 then Jack will be displayed in the message box this is due to  test_2 no variable or constant is there which is same as module level.

Public or Global variables are visible to all sub and functions of the given project or the workbook. They can be accessed by any sub or function of any module.

It is declared at the very top before any Sub or Function using Public or Global keyword.

In Module4 public variables (gv_msgStr, GV_MYNAME) are declared at the very top. These can be accessed by sub or function of any module.

Now go to any module in the above example sub is written in ThisWorkbook module. You have to specify respective module name in which your required public variable is declared. In this case it is Module4 put dot and press CTRL+SPACE to bring intellisense list. In intellisense you can see public variable of Module4.

Now we have accessed public const GV_MYNAME of module4 in ThisWorkbook module , if you run testPublicVariable sub then message box will show “Jack Public”.

Note:- It is good practice to declare variable at its lowest possible scope. Do not unnecessary declare variable at Public or Module level when sub or function level declaration is sufficient. One should have strong and valid reasons to have Public or Module level variables.

Lifetime of VBA variables

Sub or Function level variables will remain in memory till sub or function execution or run is completed. After execution is completed their respective assigned value in memory is destroyed. Static variable is exception to this.

Module level  variables and Public variables will remain in memory till workbook is open. After workbook closing their respective assigned value in memory is destroyed.

If you run above sub for first time then 2^nd  line will show no message string and 3^rd line will show 0 in message box. In 4 to 6^th line values are assigned to variables.

7^th line will show “My name is Jack” in message box and 8^th line 50 in message box.

Now if you run above sub second time then 2^nd  line will show “My name is Jack” in message box and 3^rd line will show 0 in message box. In second run 2^nd line behavior is different because this 2^nd line refers public and private (module level) variables which will be there in memory till workbook is open. In first run we have already assigned values to these public and private variables hence they are still there in memory due to this 2^nd  line showing  “My name is Jack” in second run.

But 3^rd line behavior is same in all the run, this is due to 3^rd line refers sub level variable which is destroyed after each execution hence showing default value 0 for its byte data type.

Note:- If there is run time error while running a sub or function then Public or Module level variable value will get destroyed and variable will gain default value of their respective data type.

Static variable in VBA

Static variables are special type of sub or function level variable which will retain its value in memory even after sub or function is executed unlike normal sub or function level variable. Static variables are declared like below

Static variableName as Datatype (Example-  Static myName as String)

Let us understand this with the help of below example.

	
	
Option Explicit
Sub test()
  Static myName As String
  Dim myCountry As String
  MsgBox myName 'static variable use
  MsgBox myCountry
  myName = "Rohit" 'static variable
  myCountry = "India"
  MsgBox myName 'static variable use
  MsgBox myCountry
End Sub
	

If you run above sub first time then 3rd and 4^th line will show no string in message box. 5^th and 6^th line will assign values to variables. 7^th and 8^th line will show “Rohit” and “India” respective in message box.

Now run it again second time 3^rd line will show different behavior compared to first run, it will show “Rohit” in message box. This because in first run we have already assigned “Rohit” value to static variable myName now this will remain in memory even after execution is completed. But myCountry variable value is erased from memory after execution is completed hence showing no string in second run at 4^th line.

Constant variable in VBA

As name suggests a variable whose value is constant or can not be changed after assigning a value. One can declare constant in VBA like below. Normal or not constant variable value can be changed more than one time but not in constant type variable.

Const var1 As String = “Rock”

Or

 Const var1=”Rock”

Let us understand const variable with the help of below example.

	
	
Option Explicit
Sub test()
   Const MYNAME = "Rohit" 'const variable
   Dim myCountry As String
   myCountry = "India"
   MsgBox MYNAME 'const variable
   MsgBox myCountry
   myCountry = "Nepal"
   MsgBox myCountry
   'MYNAME = "Rock"  ' remove comment to see error
End Sub
	

In the above example MYNAME is constant variable and myCountry is normal variable. myCountry variable value is changed two times at 3^rd and 6th line. Hence message box will show “India” and “Nepal” at 5^th and 7^th line . 4^th line which uses const variable MYNAME will show “Rohit” in message box. 

Till now everything is fine with no error.

Last line is commented , remove comment then try to run test() sub. You will get compile error Assignment to constant not permitted. This happened because MYNAME is constant variable and it has already assigned “Rohit” value, by uncommenting the last line you are trying to reassign a value to it which is not permitted.

Now if you want to change MYNAME value in 1^st line  to “Rock” then you must do it in 1^st line itself not in another line. So 1^st line will look like below

Const MYNAME = “Rock”