What is variable in excel VBA?

Variables are specific values that are stored in a computer memory or storage system. Later, you can use that value in code and execute. The computer will fetch that value from the system and show in the output. Each variable must be given a name.

To name the variable in VBA, you need to follow the following rules.

• It must be less than 255 characters
• No spacing is allowed
• It must not begin with a number
• Period is not permitted

Examples of Defining VBA Variables.

Valid NamesInvalid Names
My_WatchMy.Watch
NewCar11_NewCar (not begin with number)
EmployeeIDEmployee ID ( Space not allowed)

In VBA we have two types of data types i.e.

  1. Numeric Data Types
  2. Non-numeric Data Types

Before we assign any data type, first we need to declare the variable name.

Variable is a name that holds the value through the assigned data type. While giving a name to the variable we need to keep in mind a few things.

TypeStorageRange of Values
Byte1 byte0 to 255
Integer2 bytes-32,768 to 32,767
Long4 bytes-2,147,483,648 to 2,147,483,648
Single4 bytes-3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values.
Double8 bytes-1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.
Currency8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal12 bytes+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places)
Data TypeBytes UsedRange of Values
String (fixed Length)Length of string1 to 65,400 characters
String (Variable Length)Length + 10 bytes0 to 2 billion characters
Boolean2 bytesTrue or False
Date8 bytesJanuary 1, 100 to December 31, 9999
Object4 bytesAny embedded object
Variant(numeric)16 bytesAny value as large as Double
Variant(text)Length+22 bytesSame as variable-length string
Examples Of Variables in excel VBA​

Examples Of Variables in excel VBA​

Sub test()

Dim YourName As String
Dim JoiningDay As Date
Dim Income As Currency

YourName = \”James\”
JoiningDay = \”1 April 2016\”
Income = 10000
Range(\”A1\”) = YourName
Range(\”A2\”) = JoiningDay
Range(\”A3\”) = Income

End Sub

————————x———————————x———————-

Sub AddValue()

Dim x As Long
Dim y As Long
Dim z As Long

x = 47
y = 53
z = x + y

Range(\”j1\”).Value = z
MsgBox z
End Sub

———————x——————x——————–

Sub Practice()
Dim x As Integer
Dim name As String
Dim DOB As Date
x = 2019
name = \”Target Year\”
DOB = 19 / 5 / 2018
MsgBox x
Range(\”c3\”).Value = x

End Sub