What is a Variable in VBA Excel?
Variable: A variable is a name of the memory location. It is used to store data that can be changed during the script execution. We can run our program without declaring a variable but it is not good for according to programming. We should declare a variable for our programming.
There are following rules to use for Variable Naming.
- The first character must be alphabetic.
- You cannot use a special character in the variable name except Under Score ( _ )
- The length on a variable name cannot be more than 255 characters.
- You cannot use any Visual Basic reserved keywords as a variable name.
- After declaring constant user need to assign the value to the declared constant.
Syntax
In VBA, It is the good practice to declare the variables before using them.
Dim <<variable_name>> As <<variable_type>>
Example
Dim S as String
S = “Akash”
Dim I as Integer
I = 108
Variable Scope
There are two types of Variable Scope
Local – We use Private Keyword for Local Variable. We cannot use the same Variable to another code module.
Example – Private S as String
Global – We use Public Keyword for Global Variable. We can use the same Variable to another code Module.
Example – Public S as String
What is Object Variable in VBA?
Object Variable is a variable type that represents an entire object such as a Workbook, Worksheet, Range, Cells, Chart, and Pivot Table.
When we referring to an object then we have to use a Set keyword for assigning a reference to an Object.
Example
Dim sh as Worksheet. A worksheet is an Object
Set sh = Sheet1
Dim wkb as Workbook. A workbook is an Object
Set wkb = ThisWorkbook
Dim rng as Range. The range is an Object
Set rng = Range(“A1:A10”)
Dim ch as Chart. A chart is an Object
Set ch = Charts.Add
Dim Pt as PivotTable. PivotTable is an Object.
Set pt = Sheet2.PivotTableWizard
What is Constant Variable in VBA?
A constant Variable is a name of the memory location. It is used to store data that CANNOT be changed during the script execution. If a user tries to change a Constant value after declared constant value, the script execution ends up with an error. Constants are declared the same way as variables are declared but when we declare Constant Variable we need assign value for Constant Variable. Ex. Const S as String = “Akash”. Constant is default as private, we cannot use a same constant variable in another module so we have to assign Public Keyword for using all module. Ex. Public Const S as String = “Akash”.
Syntax
Const <<constant_name>> As <<constant_type>> = <<constant_value>>
Example
Const Num As Integer = 42
Const SartDate As Date = #2/2/2020#
Const DaysName As String = “Sunday”
What is Static Variable in VBA?
Static Variable is a Variable that holds the variable value as long as the code module is active.
For Example,
Sub Static_Variable()
Static i As Integer
i = i + 1
MsgBox i
End Sub
Whenever we execute the code the static variables always hold their values and the variable value must increase until the code module is active.