This chapter teaches you how to declare, initialize and display an Excel VBA variable. A variable is used to store a value. A variable can be of any type. In Excel VBA, we have variables of type Integer to store whole numbers, variables of type Double which can also store numbers after the comma, variables of type String to store text, variables of type Boolean to hold the value True or False and variables of type Date to store dates.
Place a command button on your worksheet and add the code lines described in this chapter. To execute the code lines, click the command button on the sheet.
Integer variables are used to store whole numbers.
x = 6
Range("A1").Value = x
1. The first code line 'Dim x As Integer' declares an Excel VBA variable with name x of type Integer. Letting Excel VBA know we are using a variable is called declaring a variable.
2. Next, we initialize the variable. In Excel VBA (and in many other programming languages), initializing simply means assigning a beginning (initial) value to a variable. This is done by adding the line 'x =6'.
3. Finally, we place the value assigned to the variable x into cell A1. This can be done by adding the line: 'Range("A1").Value = x'.
String variables are used to store text.
book = "bible"
Range("A1").Value = book
1. The first code line 'Dim book As String' declares an Excel VBA variable with name book of type String.
2. Next, we initialize the variable. For example, add the line: book = "bible". Always use apostrophes to initialize String variables.
3. Finally, we place the text assigned to the variable book into cell A1. You can achieve this by adding the line 'Range("A1").Value = book'
A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma.
x = 5.5
MsgBox "value is " & x
But that is not the right value! We initialized the variable with value 5.5 and we get the value 6. What we need is a variable of type Double.
x = 5.5
MsgBox "value is " & x
Note: You might wonder why you would use Integer variables, if you could use the more accurate Double variables. That is because Double variables, and even more accurate variables, need more space and as a result your code will run slower (as program size increases). Apart from this, you will see that errors are easier to find when you use variables of the right type.
In Excel VBA, you can use Boolean variables to hold the value True or False.
continue = True
If continue = True Then MsgBox "Boolean variables are cool"
1. The first code line declares a variable of type Boolean.
2. Next, we initialize the Boolean variable with value True.
3. Finally, we can use the Boolean variable to only display a MsgBox if the variable holds the value True.
To declare a date, we use the Dim statement. To initialize a date, we use the DateValue function. Want to learn more about dates? Go to the Date and Time chapter.
Did you find this information helpful? Show your appreciation, vote for us.