Introduction to VBA variables
In VBA, as in other programming languages, we work with variables. The VBA language does not require declaring variables. You can, for example enter variable name that will be interpreted as a variable, and then you can execute any instructions on it. I put an example listed below.
'OfficeInside.Org Sub VarDef1() MsgBox (MyVar * 5) End Sub
The result of the example operation is 0 and means that the variable has received the numeric type at the beginning. We know this because the default value for numeric types is 0. For what then should we define VBA variables? The correct definition of variables is one of the principles of good programming. It avoids future conflicts in your code.
You also avoid cases when variables in you code operation can get an unwanted results. It is also a good rule to add a prefix specifying the data type to the variable name, ie. intNum, StrText, datStart, etc.
If you want the VisualBasic editor to inform you every time you use an undeclared variable, you should write the short code Option Explicit before the name of your program. The VB editor will return an error message when you run the code with undeclared variables.
Option Explicit 'OfficeInside.Org Sub VarDef2() varNotDeclared = 3 End Sub
How to declare VBA variables
You can declare variables in the VBA code by adding the phrase Dim before the variable name and adding the correct data type at the end. This is called explicit declaration of variables . The phrase Dim informs you, that the variable has been declared at the level of the Sub() procedure.
In the later, code of the program, need to avoid conflicts, ie. if you try to assign text value to the numeric data type, etc. All available data types in Excel VBA can be found in this chapter: VBA data types.
'OfficeInside.Org Sub VarDef3() Dim intVar As Integer Dim datVar as Date Dim strVar as String MsgBox (intVar * 5) End Sub
Assigning values to variables in VBA Excel
Ok, you already know what declaring variables is and what data types are. Let’s try to make a simple example by defining variables, their data types and then assigning the right values to them. You can assign values to a declared variable by entering the variable name, equal sign and the value that you want to assign. The example is listed below:
'OfficeInside.Org Sub VarDef4() Dim intVar As Integer Dim datVar As Date Dim strVar As String intVar = 100 datVar = "2016/01/01" strVar = "simple text message" MsgBox (intVar) MsgBox (datVar) MsgBox (strVar) End Sub
In the example listed above, you assigned a date to the previously declared varDat variable. You can see that the date entry mask in the VB code looks like “YYYY/MM/DD”. As you can see, yue assign values for numeric variables without quotes. Variables for text and dates require quotes.
Actions on variables in VBA Excel
At the moment when your VBA variables are already defined and have assigned values, you can perform any actions on them using the VBA code. Let’s make a simple example using variables.
We want to connect the text string with another text string. With the numeric value, we want to do simple arithmetic operation. In the case of a date value, let’s do a simple function that returns the month from date. Finally, let’s display the example using the VBA MsgBox window.
'OfficeInside.Org Sub VarDef5() Dim intVar As Integer Dim datVar As Date Dim strVar As String intVar = 100 datVar = "2016/01/01" strVar = "text 1 " strVar2 = "text 2 " MsgBox (intVar * 10) MsgBox (Month(datVar)) MsgBox (strVar + strVar2) End Sub
Levels of variable declarations
Ok, you have declared variables at the level of the procedure by adding the phrase Dim before the variable name. You can also use the Static, Public, Private declarations to declare space for values. The description of all declarations is described in this table:
|Level of declaration||Description|
|Dim||Variable declared on the procedure level. It only works with procedure in which it was declared.|
|Static||The variable used for the counter. It does not lose its value between successive calls to the procedure.|
|Private||Variable declared on the Private level. It operates within the module and procedures in the module in which it was declared.|
|Public||Variable declared on the Public level. It works on all modules and all procedures.|
An example of using a numeric variable
Let’s write a short program calculating the square field based on the length of its side. The program should call the VBA InputBox window, in which you will enter the length of the side of the square. The program in cell A1 of the sheet will insert text value value “Square field:” and in B2 cell, the result of this operation.
'OfficeInside.Org Sub IntExample6() Dim intSquare As Single Dim strTxt As String strTxt = "Square field: " intSquare = InputBox("Please enter the side length of the square: ") Range("A1") = strTxt Range("B1") = intSquare * intSquare End Sub
File with all examples is attached here: excel_vba_tutorial_variables_in_vba.xlsm
Tasks (You can enter the solution in a comment)
- Modify the example from task with calculating square field, displaying the result using a MsgBox message.
- Create and test a variable declared at the public level. See if it also works in the code of other modules and procedures.