10 Things You Need to Know about VBA Programming on Excel

Save hours automating your work!

Have you ever used Microsoft Excel to perform a tedious and time-consuming task, only to feel that your time could be better spent on more purposeful work? You’re not alone. There’s nothing more dreadful than spending your precious time and energy doing repetitive manual labor on spreadsheets (#FirstWorldProblems). Fortunately, there is a solution to end manual tasks and the dread that comes with it — That solution is VBA.

VBA (Visual Basics for Applications) is a programming language used in Microsoft Office applications. The capabilities of VBA are tremendous. With VBA, you can program macros to automate tasks like cleaning and formatting data or sending emails. A macro (also referred to as a sub procedure) is a grouping of code that performs a series of tasks or commands within Excel. After reading this article, you will have the knowledge necessary to utilize VBA so it can do the heavy lifting for you.

Below are 10 things you need to know about VBA to start automating your work!

1. Set up

Before you can start coding in Excel, you must display a new tab at the top of the screen: Developer. Displaying the Developer tab is easy (and you only have to do it one time). Just follow these steps:

File -> Options -> Customize Ribbon -> Put a checkmark next to “Developer”

We can now open the VBA editor to write our programs. In the Developer tab, click “Visual Basic” to open the editor. You can also open the editor with the shortcut Alt + F11.

In the editor, you will notice the file explorer on the left. Double click on “ThisWorkbook” under your current file directory. You can now use the editor to type and execute VBA code in your workbook!

2. Hello World

Let’s walk through a quick hello world program to get the hang of the basics. Before we start, you need to save your Excel file as a Macro-Enabled Workbook (.xlsm extension). Your macro will only run if the file is saved as a .xlsm extension.

File-> Save As “helloworld.xlsm”

Below is a video showing how to create your first hello world macro. Here are three ways to run the macro once your program is complete:

1. Click F5 on keyboard while in the editor
2. Click macros and ‘run’ your program from the Developer tab
3. Insert a button from the Developer tab

Helloworld Macro:

Sub helloworld()
    MsgBox "Hello World!" 'Outputs Hello World to screen
End Sub

Every VBA macro will start with “Sub” and end with “End Sub.” Sub Stands for sub procedure. Think of a sub procedure as a sequence of commands that you want your computer to execute. In this program, the MsgBox function displays the message “Hello World!”. The name of the macro is specified after the Sub statement with “helloworld()” in this case. You can make comments in your program by preceding text with an apostrophe.

3. Recorder

There are two ways to create an Excel macro. You can either write it manually like in the previous section or record it using the Excel Macro Recorder. When you utilize the Macro Recorder, Excel converts all of your keyboard and mouse actions to valid VBA code. Below, you will learn how to create a macro with the Macro Recorder that accomplishes the following actions:

1. Types your name into a cell
2. Enters current date and time in the cell below using the “=Now()” function
3. Formats both cells to bold and font size 22 point
4. Changes both cells to display center alignment

The macro you’re about to record can accomplish all these steps in a single action. To start recording your macro, follow these steps:

1. Select any cell
2. Choose Developer tab -> Code sub-tab -> Record Macro
3. In Record Macro Dialog Box, enter a “macro name” and click ok

Now your actions in Excel are being recorded. You’ll know you’re recording if you see the stop symbol in the bottom left of the status bar. You can stop recording by clicking this stop symbol.

Watch the 30-second clip below to see how to record this macro:

Once you’ve recorded your macro, you can view the code that you generated: Choose Developer tab ->Code sub-tab -> Macros -> Select your macro name -> Step Into

You may be surprised by the amount of code that’s generated by simple commands from the Macro Recorder. For example, although you changed the font size to point 22, Excel created code that set many other font-related properties. The Macro Recorder can be excessive, and it would be a good idea to simplify this macro by deleting irrelevant code.

Here is the code generated to change the font size to point 22:

With Selection.Font
    .Name = "Calibri"
    .Size = 22
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

And here is what the entire macro looks like after deleting unrelated code:

Sub recorder_example()
    Range("A1").Select
    ActiveCell.Formula = "John Doe"
    Range("A2").Select
    ActiveCell.Formula = "=NOW()"
    Range("A1:A2").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 22
    Selection.HorizontalAlignment = xlCenter
End Sub

4. Creating Variables

It’s good practice to declare the types of your variables in Excel because your code will run faster and use memory more efficiently. If you don’t set variable types, VBA will use the default data type: Variant. If you let VBA handle data types, you’ll be sacrificing speed and memory.

To force yourself to declare all variables you use, add the following statement to the top of your VBA module (outside all sub routines):

Option Explicit

When you use this statement, you will not be able to run your code if it contains undeclared variable types.

Now that you know the advantages of declaring variables, let’s put it to practice. The most common way to declare variables is to use the Dim statement. Here are some examples of how you declare variables:

Dim YourName as String
Dim x as Integer
Dim NetRevenue as Double
Dim RowNumber as Long
Dim Today as Date
Dim SampleValue

Dim is an old programming term that stands for dimension, which means that you are assigning a memory location for a specific variable.

You can also declare several variables with a single Dim statement:

Dim x as Integer, y as Integer, z as Integer

Once you declare variables, you can assign values to those variables:

Sub Variable_Example()
    x = 6
    y = 3
    z = x + 2 * y
    MsgBox z 'Outputs 12
End Sub

5. Selecting Data

To reference data on your Excel spreadsheet, you can use the Range object, which represents a range of data. A Range object can be as tiny as a single cell or be as large 10,293,289,228 cells in a worksheet. Once you define a range, you can use the Select method to select the range of cells.

Reference a single cell:

Range("Z4")

Refer to a range of multiple cells:

Range("A1:D5")

Refer to a range outside of the active sheet:

Worksheets("Sheet4").Range("A1:D5")

Refer to a range in a different workbook:

Workbooks("Sales.xlsx").Worksheets("Sheet1").Range("A1:D5")

You can also refer to a range using the Cells object, which takes two arguments (a row number and column number). The following example refers to the cell D3 on Sheet3 :

Worksheets("Sheet3").Cells(3,4)

Use Cells property to refer to a range of cells from A1 to F20:

Range(Cells(1,1),Cells(20,6))

Now that you know the Range object, here’s an example of how you can use the Select method on the Range object to copy and paste data:

Sub CopyRange()
    Range("A1:B10").Select
    Selection.Copy
    Range("Z1").PasteSpecial xlValues
End Sub

Other popular methods besides Select, include Clear and Delete. Clear deletes the content in a given range while Delete shift cells around to replace deleted cells. Ex. Range("A1:B10").Delete or Range("A1:B10").Clear

6. Important Properties

Here are five useful Excel properties you should know:

1. Value — Represents the value contained in a cell. You can do read-write operations:

MsgBox Range("A1").Value

Range("A1").Value = 150

2. Text — Returns a string that represents the text displayed in the cell:

MsgBox Range("A1").Text

3. Count — Returns the number of cells in a range:

MsgBox Range("A1:B10").Count

4. Formula — Represents the formula in a cell:

Range("A20").Formula = "=Sum(A1:A19)"

5. Row and Column — Returns row or column number of a single-cell range:

MsgBox Range("R5").Row

MsgBox Range("R5").Column

7. If-Then

Use the If-Then statement if you want to execute one or more commands conditionally. If you use the optional Else clause, you can execute one or more statements if the condition you’re testing is not true.

If-Then Example:

Sub Reminder()
    If Time > .5 Then 
        MsgBox "Don't Forget!"
    End IF
End Sub

If-Then-Else Example:

Sub Reminder()
    If Time > .5 Then 
        MsgBox "Don't Forget!"
    Else
        MsgBox "You still have time!"
    End IF
End Sub

In these If-Then examples, we are using the VBA Time function to get the system time. If the current time is greater than .5 (afternoon), the sub routine will display a reminder. Otherwise, if there’s an Else statement, it will show a different message.

8. Loops

The most popular types of loops in VBA are:

For-Next Loop

The most straightforward kind of loop is the For-Next loop. In a For-Next loop, looping is controlled by a counter which is incremented by the Next statement. The statements between the For and Next statements are repeated in the loop. Here’s an example sub routine using a For-Next loop to calculate the sum of the first positive 100 numbers:

Sub GetSum()
    Dim sum as Double
    Dim count As Long
    Sum = 0
    For count = 1 to 100
        sum = sum + count
    Next count
    MsgBox sum
End Sub

Do-While Loop

A Do-While loop will perform a loop as long as the condition at the beginning of the loop is true. The following sub routine loops down the rows of the first column while the current cell value is not empty. It counts how many values are greater than 1000 in the first column. The loop continues until it encounters an empty cell.

Sub DoWhileExample()
    i = 1
    count = 0
    Do While Cells(i, 1).Value <> ""
        If Cells(i,1).Value > 1000 Then
            count = count + 1
        End If
        i = i +1
    Loop
    MsgBox count
End Sub

For Each-Next Loop

The For Each-Next loop is used to loop through a collection of objects (sheets in this case). The macro below sets the cell value of A1 to 1 in every worksheet.

Sub HideSheets()
   Dim sht As Worksheet
   For Each sht In ActiveWorkbook.Worksheets
       sht.Cells(1, 1).Value = 1
   Next sht
End Sub

9. Sending Emails

With VBA, you have the power to automate the creation and delivery of emails. The code below generates an email using Microsoft Outlook. Before you see the code, here’s a short clip of what the macro does:

Send_newemail code:

Sub Send_newemail()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
            
    With OutMail
        .to = Range("A1").Value
        .CC = ""
        .Subject = "Meeting Reminder"
        .body = "Hi," & vbNewLine & vbNewLine _
         & "This is a friendly reminder that the meeting is today."
        '.Attachments.Add ("") 'You can add files here
        .display
        '.Send
   End With
            
   Set OutMail = Nothing
   Set OutApp = Nothing
    
End Sub

In this macro, you can fill out the contents of the email properties (.to, .CC, .Subject,.body, etc.) with the OutMail object. Notice the Send property is commented out, so the email does not send, and only a draft is created. If you want the email to send, uncomment the Send property.

10. Performance Improvement

Here are three ways you can improve the speed of your VBA programs:

ScreenUpdating

One significant performance drag in VBA programs is screen updating. When VBA updates data on the worksheet, it refreshes the screen image. To improve performance, you can turn off screen updating by setting the Excel application property to False. At the end of the macro, you can turn screen updates on again by setting it to True. See the example below:

Sub ScreenUpdating_Example()
    ‘Disable Screen Update
    Application.ScreenUpdating = False
    'Do Something
    Range("A1").Copy
    Range("B4").Paste
    'Enable Screen Update
    Application.ScreenUpdating = True
End Sub

Calculation

Typically, Excel recalculates a cell or range of cells when that cell’s or range’s dependent value changes in another cell. As a result, your workbook may recalculate too often, which can slow performance. See the example below for how to disable and enable automatic calculations:

Sub Calculation_Example()
    ‘Disable Automatic Calculation
    Application.Calculation = xlCalculationManual
    'Do Something
    Range("A1").Copy
    Range("B4").Paste
    'Enable Automatic Calculation
    Application.Calculation = xlCalculationAutomatic
End Sub

Please note that if you disable automatic calculations, and your macro relies on the values of updating cells, you can manually refresh the cells with the Calculate method. Ex. ActiveWorksheet.Calculate

Arrays

You may experience a situation where you have to modify data in over 100,0000 rows! If you decide to use a For-Next loop to go through each cell in the range, it will be slow because you are repeatedly accessing the Excel spreadsheet (whether you are reading/writing cell values or formatting cells). Instead of looping through rows, you can optimize performance by dumping the range values into an array and then loop through the array to modify the data.

The example below uses a For-Loop to iterate over each cell in range A2 to A1000, which is a non-optimal solution. In range A2 to A1000, we are changing all instances of the value “pending” to “complete.” This solution took 0.5 seconds to run on my machine:

Sub forloop_example()
    For i = 2 To 1000:
        If Cells(i, 1).Value = "pending" Then
            Cells(i, 1).Value = "complete"
        End If
    Next
       
End Sub

In the more optimal solution below, we are dumping the values of range A2 to A1000 into an array called “myarray,” and looping through the array to change all instances of the word “pending” to “complete.” We then set the range of values equal to the array. This solution took only 0.0078 seconds to run on my machine:

Sub array_example()
    Dim myarray As Variant
    myarray = Range("A2:A1000").Value
    
    For i = 1 To UBound(myarray, 1)
      myarray(i, 1) = Replace(myarray(i, 1), "pending", "complete")
    Next i
    
    Range("A2:A1000").Value = myarray
   
End Sub

In the code above, myarray is set as a variant so the array can store different data types such as strings and integers. We are using the UBound function to determine the length of the array, so we know how many times to loop. In the loop, we use the Replace function to change the value of the array item to “complete.” In this example, we only saved a fraction of a second when modifying 1000 cells. However, you’ll save significantly more time using arrays once you start changing data in over 100,000 cells.

Congratulations!

If you made it this far in the article, you should have enough VBA knowledge to create macros that can potentially save you hundreds of hours of work! Learning a new skill is something to be proud of. Now go off and conquer the world with your new VBA skills!