Variables in Excel VBA
You will learn how to create and use variables in Excel VBA in this easy tutorial. When you declare a variable in Excel VBA, you are simply telling the program to create space for storing a value. Initialization means setting a value to a variable so it can be used in the program.
Add a command button to your worksheet and input the code lines provided below. Click the command button to run the code.
💎 Integer
Integer variables store whole numbers without decimals.
Dim x As Integer x = 6 Range("A1").Value = x
Result:
Explanation: In the first line of the code, a variable ‘x’ is declared, and its data type is specified as Integer. Next, we initialize x with the value 6. In the final step, we save the value of x in cell A1.
💎 String
String variables are used to store text.
Code:
Dim book As String book = "bible" Range("A1").Value = book
Result:
Explanation: The variable ‘book’ is declared in the first line, with its data type as String. Next, we create a variable named book and assign it the value “bible” as content. Always use apostrophes to initialize String variables. Finally, variable ‘book’ transfers its value to cell A1.
💎 Double
An Integer holds whole numbers only, while a Double can store more accurate values, including numbers with decimal places.
Code:
Dim x As Integer x = 5.5 MsgBox "value is " & x
Result:
But that is not the right value! We set the variable to 5.5, but it now shows 6. Use a Double type variable to store numbers with decimals or floating-point values accurately.
Code:
Dim x As Double x = 5.5 MsgBox "value is " & x
Result:
Note: Long variables have even larger capacity. Always use variables of the right type. This helps you find errors more easily and makes your code run faster.
💎 Boolean
A Boolean variable always holds either True or False as its value.
Code:
Dim continue As Boolean continue = True If continue = True Then MsgBox "Boolean variables are cool"
Result:
Explanation: The first line of code creates a Boolean variable named continue, which can store either True or False values.
We set the variable ‘continue’ to True. Finally, we use this Boolean variable to show a message box only when its value is True.
1/5 Completed! Learn much more about variables ➝
Next Chapter: If Then Statement
Explanation: The first line of code creates a Boolean variable named continue, which can store either True or False values.
We set the variable ‘continue’ to True. Finally, we use this Boolean variable to show a message box only when its value is True.
1/5 Completed! Learn much more about variables ➝
Next Chapter: If Then Statement