VBA Variables, Data Types and Dim

Declaring VBA Variables using Dim

This guide breaks down VBA variables, Data Types, and Dim. Typically, the very first step after naming your macro is declaring your variables. Variables are names for different pieces of the data that the macro will be working with.

However, this sometimes proves difficult since it’s hard to plan ahead how many variables will be used in the macro. Eventually, when the macro is written, the user may add or remove certain variables. This will become more apparent further into this guide to writing VBA macros.

The very top of each macro after the sub name is a section called the declarations. Here, the user lists and names all the different variables he or she will use, and declares their data types. This is done by using the “Dim” statement. The “Dim” statement is followed by the name of the variable, and sometimes the statement “as [datatype]”.

For example, if we wanted to create a variable for a Stock Price, we could write “Dim stockPrice as double”. This creates a variable called the stockPrice, which takes on the data type double. A double data type is one of the data types that allows for decimals, as opposed to the integer data type.

It’s not necessary to always declare the data type. Sometimes, it’s sufficient to declare the name, and VBA can infer the data type when the variable is used in the code later on. However, it’s generally safer to declare the data type you expect to use.

Each declaration will take its own line. It’s helpful to group variables of the same data type together.

Variable Data Types

There are quite a few VBA data types, but for the general purposes of financial modeling not all of them are used.

Below is a list of common VBA variables (known as data types) used in macros and their purposes:

• Integer: Used to store number values that won’t take on decimal form.
• Single: Used to store number values that may take on decimal form. Can also contain integers.
• Double: A longer form of the single variable. Takes up more space, but needed for larger numbers.
• Date: Stores date values.
• String: Stores text. Can contain numbers, but will store them as a text (calculations cannot be performed on numbers stored as a string)
• Boolean: Used to store binary results (True/False, 1/0)

Again, there are other data types, but these are the most commonly used for creating macros.

Storing a Value in a Variable

After a variable has been created, storing a value in it is simple.

Variable name = Variable value

String variable name = “Variable value”

(When using strings, you have to surround the text in quotation marks. This is not true for number or binary values)

Each named variable can only hold one value at a time.

Example of Declaring Variable Data types with Dim

Here is a break down of how to use Dim in VBA:

• Declaring a company name variable: “Dim companyName as String”
• Setting the company name variable:
  • companyName = “Tesla”
  • companyName = “Wells Fargo”
  • companyName = “No company name is available”

• Declaring a variable to store net income: “Dim netIncome as Single” (or Double, depending on the scale)
• Setting the net income variable:
  • netIncome = -5,000
  • netIncome = 0
  • netIncome = 1,000,000.64

• Declaring a binary variable to store growth: “Dim isGrowthPositive as Boolean”
• Setting the growth variable:
  • isGrowthPositive = True
  • isGrowthPositive = False
  • isGrowthPositive = 1 (same as True)

As you can see in the above example, these variables (and some extra variables to show grouping best practices) have been declared. Values have also been stored in the main variables.

However, if this macro were to be run, it would simply store these values in the variables, and not use them in any way. To continue learning how to use variables, you need to know the VBA methods available to each one.

Additional Resources

Thank you for reading CFI’s guide to VBA variables, Data Types, and Dim. To keep learning and progressing your Excel skills we highly recommend these additional CFI resources:

• Excel Keyboard Shortcuts
• Advanced Excel Formulas
• VBA Methods
• VBD Do Loops
• See all Excel resources