VBA Starter Kit – Section 4 – Declaring & Using VBA Variables

Using VBA variables is an incredibly important aspect of writing VBA code. You’ll use variables in almost every function and subroutine you write. When using variables, it’s very important that we declare them first. This is not a requirement. If you don’t declare a variable, VBA will auto-declare it to the “variant” type. However, this will make your code slow to compile and could result in a lot of “type-mismatch” errors. I always recommend using the setting “Option Explicit” at the top of any module. What is Option Explicit? It’s simply your way of telling the compiler to check and make sure that you’ve declared all your variables. If you have a variable undeclared, it will give you a compile error and not let your sub run. If you don’t want to add this piece of code (or don’t want to forget to!) then you can also set this as an option in the “Tools” section of your VBA editor.

Declaring VBA Variables

Declaring VBA variables is done with the term “Dim”, which is short for “Dimension”. So, if you type “Dim A as Long”, you are declaring a variable “A” and dimensioning it as type “Long” (Long is an int64 data type. To learn about data types, click here). You usually find the Dim statements at the beginning of a sub but they can be placed anywhere within the code. Declaring your variables helps ensure you don’t assign the incorrect type of data to a variable.

If you do accidentally try to assign incorrect data to a variable, the compiler will tell you about the error.

Declaring VBA variables also helps others understand your code. If you declare a variable like “Dim fName As String”, most anyone looking at that would be able to understand that you are talking about a text string that represents someone’s first name. That way, if someone comes along after you to make improvements to the code or fix bugs, they can easily see what your intentions were.

Using VBA Variables

After you’ve declared a variable, you can now use that variable in your code to represent data! Using a variable depends on the type of variable you declared. You obviously would do different things with String variables than you would Long variables or Boolean variables. In fact, that’s why they exist. Let’s explore a bit of code using variables.

I’ve assigned this code to a button with some data. Let’s see the result.

Using VBA Variables

This simple code applied discounts to orders where the quantity ordered was more than 500. By using the variable discount instead of hardcoding it, we can now update that single variable if the discount ever changes. In this simple example, that wouldn’t be necessary, but what if you had this variable referenced 5, 10, or even 20 times in your code? What if you needed to have the user input the discount for your code to use? This is where using VBA variables rather than hard coding data can save you time.

Dim discount As Double
Dim i As Long
discount = 0.15
For i = 2 To 4
    If qtyCheck(Cells(i, 3).Value) = True Then
        Cells(i, 5).Value = discount
    Else
        Cells(i, 5).Value = 0
    End If
Next i
End Sub

Function qtyCheck(val As Long) As Boolean
If val >= 500 Then
    qtyCheck = True
Else
    qtyCheck = False
End If
End Function