Enhancing Excel Macros Through Visual Basic for Applications

Apr 6, 2020 • 14 Minute Read

Introduction

In Getting Started with Visual Basic for Applications in Microsoft Excel, you learned to record, run, and update macros to automate routine tasks. However, the VB editor (VBE)—which provides access to all the bells and whistles of the language—was not explored in depth. This guide will explain how to manipulate a data set using features unique to VBA and other programming concepts you are probably familiar with: conditional statements and loops. You will also explore how to invoke and modify the behavior of macros using forms—all inside of Microsoft Excel.

To follow along with this guide, be sure to first enable the developer as described in the guide noted above. If you wish, you can download the spreadsheet used in the examples below by clicking on this link.

Step 1: Creating the User Form

To insert a new form, launch the VBE and right-click on the VBAProject, where you will see the current file name. Next, choose Insert and then UserForm:

Please set an alt value for this image...

This will add a new form in the design area and display the toolbox where you can select controls for it. In this example, insert a placeholder for an image, five buttons, and two drop-down lists as shown below. To do this, drag and drop those controls from the toolbox into the user form.

Don't worry about the styling, the form caption, or the other labels at this point. We'll take care of them shortly.

Step 2: Editing Control Properties

To select one of the controls that you just added in the form, click on it. When you do, you'll find its Properties in the left-hand area. There, modify the following fields in each control:

In UserForm1, change:

BackColor to a light yellow.
(Name) to frmHistoricalPurchaseOrders.
Caption to Historical Purchase Orders.

The Properties section should look similar to the following image:

Next, update the attributes in the other controls as follows:

Control	Property	New Value
CommandButton1	(Name)	btnViewOrdersByCountry
CommandButton1	Caption	Choose Country
CommandButton2	(Name)	btnViewOrdersByEmployee
CommandButton2	Caption	Choose Employee
CommandButton3	(Name)	btnViewPendingOrders
CommandButton3	Caption	View Pending Orders
CommandButton4	(Name)	btnFormatTable
CommandButton4	Caption	Format Table
CommandButton5	(Name)	btnClearFormats
CommandButton5	Caption	Clear Formats
ComboBox1	(Name)	cbListCountries
ComboBox2	(Name)	cbListEmployees
Image1	(Name)	imgLogo
Image1	BorderStyle	0 - frmBorderStyleNone
Image1	Picture	Select a logo image of your choosing

At this point, the form should look as follows:

As you can see, the background colors of the buttons and the checkbox were changed to light green and yellow, respectively. We are now ready to add actions to the controls.

Step 3: Populating Drop-Down Lists

The cbListCountries and cbListEmployees drop-down lists should be populated when the form first loads. Said another way, when the form is initialized a macro will add items (without duplicates) to each combo-box control.

To accomplish this goal, right-click on the form control inside the project explorer and select View Code:

On the right, enter the following code blocks. The first, UserForm_Initialize(), is private to the form and triggers when it loads. On the other hand, Populate(startCell As String, cbList As comboBox) is the custom macro that fills the controls when called from the first:

          Private Sub UserForm_Initialize()
    Populate "G2", cbListCountries
    Populate "H2", cbListEmployees
End Sub

Sub Populate(startCell As String, cbList As comboBox)
    Dim itmCollection As New Collection
    Dim cell As Range
    On Error Resume Next
    With cbList
        .Clear
        For Each cell In Range(startCell, Range(startCell).End(xlDown))
            If Len(cell) <> 0 Then
                Err.Clear
                itmCollection.Add cell.value, cell.value
                If Err.Number = 0 Then .AddItem cell.value
            End If
        Next cell
    End With
    cbList.ListIndex = 0
End Sub
    

Let's examine each of these procedures in more detail:

Macros are defined within Sub blocks, which stands for sub-procedure.
Within UserForm_Initialize(), the Populate macro is called twice: once for each combo-box control. The latter takes two arguments that indicate 1) where to start looking for values to populate each list, and 2) which control should be filled in each case.
When a sub-procedure is passed two or more arguments, they should be separated by commas.
Dim itmCollection As New Collection declares and creates a collection of items that can be handled as a unit. This type of object is more appropriate than others for what we will be doing later with the items.
A Range object can be a single cell, a row, a column, or a bigger block. With Dim cell as Range, you are defining a placeholder variable called cell of type Range.
On Error Resume Next means that if an error occurs during runtime while accessing objects, the macro will not crash. Instead, it will continue with the next statement. You can read more about the On Error statement in the VBA online reference here.
Inside the With block, when a word is preceded by a dot it refers to the object that is being accessed. Thus, .Clear and .AddItem are combo-box methods from cbList. Keep in mind that this variable represents the second argument passed to Populate.
The For Each loop is used to iterate over a Range object that consists of all the cells between the one specified by startCell down to the last non-empty location in the same column. Here, Range(startCell).End(xlDown) produces the same effect as placing the cursor over startCell manually and pressing Ctrl + Shift + the down arrow. The Next keyword tells the loop to proceed with the next cell after each iteration.
Items are added to the collection and errors are cleared only if the current cell is not empty. In other words, the macro will check that its length is greater than zero.
One distinguishing feature of the Collection object is that its items can be accessed either by index (the position in the collection) or key (a unique value). In itmCollection.Add cell.value, cell.value, the first cell.value is the actual item that is being added whereas the second represents the key. This approach allows us to check for errors in each step and add an item only if Err.Number equals 0. This condition is met only when the current item does not exist in the collection already.
When an If block spans two or more lines, it needs to close with the corresponding End If statement. Otherwise, we can omit the latter.
Finally, cbList.ListIndex = 0 indicates that the first item in the collection should be selected by default. Feel free to change this to another value if you wish. However, no item will be selected if the number is larger than the size of the collection.

When you run the form by pressing F5 from the editor, you will notice that France and Steve Buchanan appear as the default values of the drop-down lists. Those are precisely the first values in the G and H columns.

Step 4: Responding to Click Events

The expectation when you click on Choose Country or Choose Employee is to see the list of orders shipped to that place or associated with that person, respectively. As you double click on these controls in the editor, an empty private sub-procedure will be created for you to start typing code.

Since we want to add new functionality (filtering the spreadsheet on a column based on a given value), we will create a macro for that. To do so, insert the following code below Populate:

          Sub Filter(col As String, argValue As String)
    With Worksheets(ActiveSheet.Name)
        .AutoFilterMode = False
        .Range(col).AutoFilter Field:=1, Criteria1:=argValue
    End With
End Sub
    

Worksheets is another example of a collection in VBA. Since it contains all the sheets in the current workbook, we use ActiveSheet.Name to stipulate the macro be applied to whatever sheet is active at a given time. This strategy helps us write more flexible code than if we utilized the name of the sheet, Orders.
If used without arguments, the .AutoFilterMode = False method will remove any filters that have been applied to the sheet. The following line, .Range(col).AutoFilter Field:=1, Criteria1:=cbList.value, applies a filter to the column defined by col. We can pass the arguments as 1, cbList.value, but doing it as Filter:=1 and Criteria1:=cbList.value makes clearer what is being done. The VBA online reference has more information about the parameters accepted by .AutoFilter here.
The value of Field is the column number inside the selected range—not the entire worksheet. In this case, Field:=1 represents the entire column given by col.

Finally, be sure to call the Filter macro from the Click event of each button:

Press F5 to run the report and choose a country or employee to see the associated orders.

Step 5: More Filtering

In this step, you will use the Click event of btnViewPendingOrder to view only those orders that still have not been shipped. Since it is the same filter functionality implemented earlier, we will reuse the Filter sub-procedure after a slight change.

In Filter, change the type of the second argument to String as follows:

          Sub Filter(col As String, argValue As String)
    With Worksheets(ActiveSheet.Name)
        .AutoFilterMode = False
        .Range(col).AutoFilter Field:=1, Criteria1:=argValue
    End With
End Sub
    

In the Click event of btnViewPendingOrders and the other previous two buttons, do:

          Private Sub btnViewOrdersByCountry_Click()
    Filter "G:G", cbListCountries.value
End Sub

Private Sub btnViewOrdersByEmployee_Click()
    Filter "H:H", cbListEmployees.value
End Sub

Private Sub btnViewPendingOrders_Click()
    Filter "E:E", ""
End Sub
    

As you can see, the value by which the table will be filtered will come from the calling event instead of setting it up in Filter as before. This is another step towards writing more efficient, maintainable code.

Step 6: Formatting and Clearing Formats

The last two buttons will serve the purpose of 1) calling the FormatTable macro from the previous guide, and 2) removing all the formats from the active sheet. To achieve this, insert the following code blocks inside the corresponding Click events of each button:

          Private Sub btnFormatTable_Click()
    Module1.FormatTable
End Sub

Private Sub btnClearFormats_Click()
    With Worksheets(ActiveSheet.Name)
        .AutoFilterMode = False
        .Cells.ClearFormats
        .Cells.Borders.LineStyle = xlLineStyleNone
    End With
End Sub
    

In the first Click event above, the FormatTable sub-procedure is invoked by preceding it with the name of the module where it resides. In the second, after identifying the active sheet, all formats, borders, and filters are removed.

Step 7: Launching the Form

Once all the form code has been put in place, open the Module1 module and insert the following macro at the top:

          Sub OpenForm()
    frmHistoricalPurchaseOrders.Show
End Sub
    

The sub-procedure above will open the form when it is run. By putting it in Module1, it will become available in the list of macros that we can access from the spreadsheet menus. If you now go to Developer and click Macros, you will notice that OpenForm has been added to the list. Click Run to run the macro and launch the form:

Take a couple of minutes to play around and test the form and its features.

Conclusion

In this guide, we have learned how to go beyond macro recording to leverage the power of Visual Basic for Applications. Also, you have learned how to create forms, add controls to them, and link them to sub-procedures. Although covering the ins and outs of VBA would take dozens of articles, you now have a better idea of the possibilities it offers.