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.

 

What is Data Types in VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *