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 Names | Invalid Names |
| My_Watch | My.Watch |
| NewCar1 | 1_NewCar (not begin with number) |
| EmployeeID | Employee ID ( Space not allowed) |
In VBA we have two types of data types i.e.
- Numeric Data Types
- 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.
| Type | Storage | Range of Values |
| Byte | 1 byte | 0 to 255 |
| Integer | 2 bytes | -32,768 to 32,767 |
| Long | 4 bytes | -2,147,483,648 to 2,147,483,648 |
| Single | 4 bytes | -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values. |
| Double | 8 bytes | -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values. |
| Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 12 bytes | +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places) |
| Data Type | Bytes Used | Range of Values |
| String (fixed Length) | Length of string | 1 to 65,400 characters |
| String (Variable Length) | Length + 10 bytes | 0 to 2 billion characters |
| Boolean | 2 bytes | True or False |
| Date | 8 bytes | January 1, 100 to December 31, 9999 |
| Object | 4 bytes | Any embedded object |
| Variant(numeric) | 16 bytes | Any value as large as Double |
| Variant(text) | Length+22 bytes | Same as variable-length string |
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