A Variable in VBA allows you to store information to be referenced at a later time in the code. Variables in VBA are not too different than variables like x and y in basic algebra except that you can assign almost anything to a variable in VBA.
The first thing you want to do when making a variable is to declare it’s data type. This isn’t always required, as Excel knows for the most part what type of data you are assigning to the variable, but it is a good practice to do when starting off as it will help you in the long run.
Now, before you math haters run off, check out the below example of a variable to see how easy assigning variables can be.
Sub Learning_About_Variables() x = 1 MsgBox x End Sub
The above code assigns the value of 1 to variable x and then displays what x is in a message box.
If you are having trouble getting the above code to work, please see this brief intro to macros to get the developer tab added to your Excel ribbon. You will then want to insert a module and paste the above code into that module. You can step through the code line by line by clicking inside of the Sub/End sub and pressing the F8 button.
In our above example, we assigned 1 to variable x. The number 1 is an integer.
The simplest way to declare a variable in VBA is to use Dim.
Dim x as Integer Dim y as Integer
Using Variables to complete a calculation
Add y as an integer to your code. You can assign any number you wish to it; I have given it the value of 25 below. Running your code again will show the sum of x and y, 26, in the message box.
Sub Learning_About_Variables() Dim x As Integer Dim y As Integer x = 1 y = 25 MsgBox x + y End Sub
Variables are not just numbers
You can make almost any value a variable, numbers, text strings, dates, currencies, and more. In the below for example, I am using a variable combined with an If-Then statement to tell me if x+y is greater than or less than than 30.
Sub Learning_About_Variables() Dim x As Integer Dim y As Integer x = 1 y = 25 If x + y > 30 Then IsTheSum30 = "This equation is over 30" If x + y < 30 Then IsTheSum30 = "This equation is below 30" MsgBox IsTheSum30 End Sub
Note I did not declare the variable “IsTheSum30”. Not doing so causes the variable to be variant, which VBA automatically assigns a data type to. If I had assigned a data type to it, I would have declared
Dim IsTheSum30 As String
If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.