18.3 Introduction to VBA Sub Procedures and Macros

As we begin exploring VBA and its commands, we will write a macro together that will copy a name and address from a contact list and organize them on a new sheet so we can quickly print out an envelope for a person who is currently selected. Now, I use this example simply to show off several different VBA commands. Of course, we would probably use mail merge in Word if we were doing this in bulk, but the example will help you to see some of the functionality of VBA.

Rather than walk through all the commands here and describe where you need to put them, the text in this section will provide some of the basics of what the commands do, but you will want to watch the video to see how they are applied.

In this example, we will complete the following actions:

• Create a new worksheet called “Envelope” that represents data that would be printed on an envelope.

• Transfer data from the Client worksheet to the Envelope worksheet.

• Save our workbook as a macro-enabled workbook so that the macro is saved.

• Handle missing data using the delete method to remove fields that are blank.

• Record a macro to figure out how to print the data.

Create a New Worksheet

For this example, we will need a new worksheet that represents data that would be printed on an envelope. Insert a new worksheet and call it “Envelope.” Add your name and return mailing address to cells B2, B3, and B4. We don’t add this data to column A or row 1 because sizes and envelopes might differ, and this will allow us to resize according to the dimensions of our envelope.

Transfer Data from Client Worksheet

We want to copy over the information from the Client worksheet to the Envelope worksheet. So, go back to the Client worksheet and select A2. The macro we write will take the information based on the currently selected cell and transfer it over to the Envelope worksheet. So, by selecting A2, we will transfer information for Sharon Hagar.

What information do we want? Well, we will get the name, skip the phone number, and get the address 1, address 2, city, state, and zip. Let’s jump over to the VBA editor using Alt+F11 and then paste the following in a module window of the code editor pane (to the right of the Project pane) rather than pasting in the Immediate window.

Option Explicit

Typically, we will always add Option Explicit as the first line in our modules. This command ensures that all the variable names we use will be purposeful, or in other words, explicitly declared. Next, we need to define a block of code that can be called when we want to execute our program. In VBA, there are two main types of code blocks that can be executed by name: sub procedures and functions. Sub procedures are used when you are executing some code but not explicitly returning a value. Functions return a value. In this example, we will execute some code—copying over values and printing out the envelope—but the code won’t return a value. So, we will use a sub procedure. Add the following code to the module.

Sub MailingLabels()

End Sub

When creating a sub procedure, you need to provide it with a name. We are using the MailingLabels name. Whenever someone wants to run our VBA program, they will use the name MailingLabels to do so. Both procedures and functions can take zero or more parameters. By convention, the names of these parameters are encased in parentheses. If you don’t have any parameters or values to be passed, as is the case for this example, then you simply denote that by including an opening and closing parenthesis right after the name. Also, notice that you have an End Sub. That line of code helps us know where the procedure ends. We will put additional VBA statements between the start and end of the sub procedure.

It is helpful to add human-readable comments to your code so that you can easily remember what it does. Any programmer with a decent amount of experience will tell you how easy it is to forget what a code does after stepping away from it for a time. In VBA, you use a single apostrophe to denote a human-readable comment that Excel’s VBA engine should not interpret. You can add comments before or after the sub. Again, these comments are only for human eyes—in other words, when VBA sees the single quote mark, it'll actually just skip the rest of the line.

Sub MailingLabels()
     'Create some mailing labels for the selected client
End Sub

Now that we have the structure set up for our program, we need to insert some of the code. The first thing we will do is copy over the name from the current worksheet, Client, to the Envelope worksheet. So, jump back to the Client worksheet and select Sharon Hagar, the name in A2. VBA has a special object called Selection that returns the object associated with the current selection. The following code returns the value of the currently selected cells. Notice that we use .Cells() to select a value that is relative to the current selection, similar to the OFFSET function we have seen in earlier lessons. However, with the Cells attribute, column 1 and row 1 will return the current cell’s value.

Selection.Cells(1, 1).Value

If we want to refer to the value of a cell on another worksheet, we can use the Sheets() object. The following snippet of code would assign the currently selected value to cell D6 on the Envelope worksheet.

Sheets("envelope").Range("D6") = Selection.Cells(1, 1).Value

Let’s add that code snippet to our sub procedure.

Sub MailingLabels()
     'Create some mailing labels for the selected client
     Sheets("envelope").Range("D6") = Selection.Cells(1, 1).Value
End Sub

Let’s save our code so we can try it out. You can save by using Ctrl+S or pressing the blue disk icon on the menu. When you do so, you will get the following prompt.

To save your VBA code, you need to save your file as a macro-enabled file. Since you can do things like access files on the local hard drive, VBA is a security risk. As such, Microsoft requires you to save the file as a different file type, which allows the VBA code to be saved with your worksheet data. Select the No option on the previous prompt to change the extension type to an Excel macro-enabled workbook.

When we execute the MailingLabels sub procedure, we need to be on the Client worksheet with a client name that we want to copy over. So, go back to your worksheet and make sure that Sharon Hagar is still selected. To execute the code, we’ll pull up a list of all the macros that we have associated with this worksheet. These can be found by pressing the Macros button on the Developer menu under the Code group.

The Macro dialog box will show all the macros in your workbook, including all sub procedures that you have defined. Currently, we just have the one. So, select MailingLabels and click the Run button.

After you run the macro, the dialog box will close. It may look like nothing happened. However, if you switch back to the Envelope worksheet, you’ll notice that Sharon Hagar is now listed in D6. All right, that's fantastic. Congratulations on executing your first macro! However, we’ve got a bit more to go.

Now I want more than just the name—I want the entire address. Let’s switch back to the Client worksheet and take a quick look to make sure we know what we want. We want address 1, address 2, and then the city, state, and zip. So, we want three more lines here, going down to D7, D8, and D9 for those elements. Back on Client, hit Alt+F11, and let’s add some more code to our sub procedure.

The addresses are found in columns C and D, or in other words, the third and fourth columns in relation to our selection in column A. So, we will use similar code to copy those values to cells D7 and D8 on the Envelope worksheet. We’ll do something similar for the state, which is found in column E.

Sub MailingLabels()
     'Create some mailing labels for the selected client
     Sheets("envelope").Range("D6") = Selection.Cells(1, 1).Value
     Sheets("envelope").Range("D7") = Selection.Cells(1, 3).Value
     Sheets("envelope").Range("D8") = Selection.Cells(1, 4).Value
     Sheets("envelope").Range("D9") = Selection.Cells(1, 5).Value 
End Sub

Switch back and run your code to make sure it works the way you expect it to. If it doesn’t, then troubleshoot to see what went wrong. If it ran correctly, you will have the city name Doral in cell D9 on the Envelope worksheet. We also want the state and zip. Those are in columns F and G. Of course, we also want those in cell D9 with some appropriate formatting. VBA text concatenation is done using the ampersand (&), similar to how it is done when creating a formula. So, adjust your last line to add the state and zip. Since the line gets a little long, you can optionally use the line continuation character—the underscore (_)—to tell VBA that the next line of code is just a continuation of the previous line. The following code will combine the city, state, and zip all on to one row.

Sheets("envelope").Range("D9") = Selection.Cells(1, 5).Value & _
     ", " & Selection.Cells(1, 6).Value & _
     " " & Selection.Cells(1, 7).Value

Typically, you will tab in the line of code so that it is easy to see that it belongs with the previous line. Switch back to the Client worksheet, run the macro, and verify it works as intended. If not, take some time to troubleshoot.

It isn’t that painful to run this macro by going to the Developer menu, pulling up the list of macros, and hitting Run—but it is kind of a lot of steps. Wouldn’t it be nice if we could add a button directly to our worksheet and just click on that to execute our macro? Yes, it would. Luckily, we can do that easily. To add a button, use the Insert option in the Controls group on the Developer menu.

Notice that you can insert buttons, drop-downs, checkboxes, radio buttons, text, and more. You can also group these. We’ll keep it simple and just add a button right now. Go ahead and add a couple of rows at the top of the Client worksheet so we can insert the button. Then, from the Insert option under the Developer menu, select the first option under Form Controls. Now, move your mouse over your worksheet, and the cursor will change to a plus sign or crosshairs. Holding the left mouse button down, drag your mouse to create a button with the desired size. When you release your mouse, you will then select which macro gets associated with your button. Select MailingLabels and click OK. Now, anytime you click that button, it will run your macro. To change the name that is displayed on the button, right-click on the button and select Edit Text. Change it to Print Envelope and click somewhere off the button to save that change.

Adjustments to the button size or position can made by right-clicking on the button and then adjusting its size or moving it. Test out the button and make sure that it works as intended.

Let’s try out another name on our list. Find Matthew Saunders, select the name, and click on the Print Envelope button. Then go check out the Envelope worksheet. What do you notice? We have a blank between the address and city, state, and zip because there isn’t a second part of the address. Well, that looks pretty terrible.

How should we take care of that? We could check for it when we are inserting the address, and if it is blank, then we can shift which cells we are putting everything in. While that is possible, there is a much simpler way. We can keep the code how we have it so far, and then we can just check to see if that cell is empty. If it is blank, we can use a method on the cell to delete it, and that will shift everything up in the sheet.

If Sheets("envelope").Range("D8") = "" Then
     Sheets("envelope").Range("D8").Delete
End If

Notice that the IF statement takes an expression. In this case, we are testing to see if the value in cell D8 is an empty string or a blank. If so, then the next line of code will be executed. Similar to the End command for the sub procedure, the End If allows VBA to know what code belongs to the IF statement. Also, notice the Delete method that is found on the Range object. This does more than just delete the value—by default, it also shifts the values of all the cells below it up one row.

The IF statement is called a control flow statement because it can control the flow of your program. There are several other statements like this and also variants of IF statements that we’ll introduce a little later. Mastering VBA will involve getting familiar with various control flow statements. There are also lots of different VBA objects, and each one typically has a lot of different properties and methods. In this course, we will introduce you to a few of these, but as you use VBA more, you are sure to continually discover more and more functionality associated with the numerous objects that are available.

Add that code to your sub procedure and see how it works.

Sub MailingLabels()
     'Create some mailing labels for the selected client
     Sheets("envelope").Range("D6") = Selection.Cells(1, 1).Value
     Sheets("envelope").Range("D7") = Selection.Cells(1, 3).Value
     Sheets("envelope").Range("D8") = Selection.Cells(1, 4).Value
     Sheets("envelope").Range("D9") = Selection.Cells(1, 5).Value & _
          ", " & Selection.Cells(1, 6).Value & _
          " " & Selection.Cells(1, 7).Value
     If Sheets("envelope").Range("D8") = "" Then
          Sheets("envelope").Range("D8").Delete
     End If

End Sub

Go ahead and test it out and troubleshoot where needed. Mine is looking pretty good.

Recording a Macro to Discover Code Behind Functionality

So, when we want to print our envelope or label, we can select the client we want, switch to the Envelope worksheet, and print the mailing information out. Wouldn’t it be nice if it would just print out from the Envelope worksheet without having to switch back and forth between worksheets? We can do that with VBA, but maybe you don’t know the code to do that right off. What can you do instead? You can record a macro to figure out how to switch from the Client worksheet to the Envelope worksheet, print the page, and then switch back to the Client worksheet. The recorded macro will then give you code that will perform all the steps at once without having to switch between worksheets.

Before you record a macro, try to imagine where you will be executing it from. We will want this new functionality to run right after the copying is done. So, make sure that you are on the Client worksheet and then record a macro by clicking on the Record Macro option on the Developer menu. You will be asked to name your macro and optionally associate the recorded macro with a shortcut keystroke. Let’s name ours PrintEnvelope.

Once you click OK, Excel will record all your subsequent actions and turn them into code. We will later be able to use that code in our own sub procedures. Once you click OK, complete the following actions:

• Click on the Envelope worksheet.

• Print that sheet using the menu options File ➔ Print or the shortcut Ctrl+P.

• Select your desired printer and print the envelope.

• Finally, click on the Client worksheet so that you are ready to print another client when you finish printing this one.

Once you have finished these steps, click the Stop Recording option on the Developer menu. Notice that this button only shows up when you are recording a macro.

Once you stop, click on the Macros option and edit the newly recorded procedure. Here is the code that was captured.

Sub PrintEnvelope()
'
' PrintEnvelope Macro
'

'
     Sheets("envelope").Select
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
          IgnorePrintAreas:=False
     Sheets("client").Select
End Sub

Notice that each line of code corresponds to the actions that you did while you recorded the macro. We could copy the code within this sub to our MailingLabels function. However, we can actually leave the code in this PrintEnvelope sub and simply call it from within the other sub procedure as follows. Notice that you will have to switch back to Module1 in the project window to edit the MailingLabels function.

Sub MailingLabels()
     'Create some mailing labels for the selected client
     Sheets("envelope").Range("D6") = Selection.Cells(1, 1).Value
     Sheets("envelope").Range("D7") = Selection.Cells(1, 3).Value
     Sheets("envelope").Range("D8") = Selection.Cells(1, 4).Value
     Sheets("envelope").Range("D9") = Selection.Cells(1, 5).Value & _
          ", " & Selection.Cells(1, 6).Value & _
          " " & Selection.Cells(1, 7).Value
     If Sheets("envelope").Range("D8") = "" Then
          Sheets("envelope").Range("D8").Delete
     End If

     Call PrintEnvelope

End Sub

Notice how you call another sub procedure; you use the Call command and then the name of the sub procedure. After making the changes, go ahead and try it out. Select a different client, click the Print Envelope button, and watch it print out your envelope. Congratulations, you have written a useful macro or sub procedure.

A lot of VBA code focuses on creating sub procedures to process or manipulate data. In this course, this will be the last program like that we write. Instead, we will focus on writing user-defined functions in VBA that you can use in your worksheets. Using UDFs is a very powerful approach to creating complex processing that most Excel users can take advantage of. So, let’s transition to functions for the rest of this lesson.