Working with Variables in Excel VBA – A Complete Tutorial

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