18.2 Getting Oriented with VBA

To get access to VBA, we use the Visual Basic option on the Developer menu. If you haven’t written VBA before, you likely won’t have that on your menu yet. So, how do you enable it? Go to File ➔ Options, and then click on the Customize Ribbon option on the left.

You can see the Main Tabs list on the right side. As you scroll down, there's one called Developer. Go ahead and check that and then select OK. That will enable the Developer menu on the ribbon.

There are a lot of different options under the Developer menu. Let’s first focus on the Visual Basic option. Clicking on this option will switch us into the Visual Basic for Applications editor, which looks like the following window.

Notice that there is a menu with lots of different options and several different panes. The Project pane lists all the worksheets and VBA add-ins currently open or enabled. Interestingly, Solver is an add-in that was written in VBA code, and it appears here because we enabled it in a previous lesson. We also have a sheet for the client mailing list—the worksheet we currently have open—and a couple of others that are functions and workbooks on my local computer. We'll be focusing on this client mailing list.

Below the Project pane, you can see the properties associated with the currently selected file. This worksheet has the name Sheet1, and there are a variety of other properties.

Go ahead and double-click on the Sheet1(client) option in the Project pane. This will open a file in the pane to the right of the project pane. This right pane might look like an area where you could write some code, and generally, you would. But before we write some code that we can execute in our workbook, we'll actually want to create what's known as a module. To do that, right-click on a project and select Insert from the context menu. From here, you can create a UserForm, Module, or Class Module.

A UserForm is a graphical user interface that you can design to interact with your VBA programs. Modules are the places where you'll generally put your code. A class module allows you to define a structure for the data you might want to capture. For this class, we'll focus mostly on modules. Go ahead and click Module, and you should now see a Modules folder with Module1 in addition to the objects that come with the worksheet.

We will save our code inside of this module. You can write your code in the pane to the right. You can do things like declare variables, create macros in the form of subroutines or procedures, and create your own functions that you can call from your worksheet. Referring to Figure 18.3, you'll see some additional windows underneath the code editor in the right pane. The Immediate window allows us to interact and write code one line at a time and try it out. The Locals window will show you some of the current values for the variables that you declared and that are currently loaded in your code as you run it. The Watches window allows you to explicitly add a watch to a particular parameter or variable to see the current value as you step through your VBA code. If you don't have these windows open, you can enable them under the View menu.

VBA is what's known as an interpretive language. Instead of having the program compiled all at once, the code compiles the program one line at a time as it’s executed. This process allows us to write code and execute it at the same time and allows the code to be a little bit more interactive than it would be in some other environments.

Okay, let's go ahead and write some VBA code and apply it to the current worksheet. We’ll use the Immediate window for these first few commands. Let’s start with the Print command, which allows you to print something to a console or terminal window. Try this command.

Print "Test"

After you type it in, press the Enter key. That will execute the command for the current line in the Immediate window. Notice the output. The value Test is printed in the Immediate window.

What if you wanted to print out something from the worksheet instead? There is a VBA code that allows us to refer to the worksheet that we are currently on and return values for a given address.

To switch back to the Excel worksheet, you can either use the combination of keys Alt+F11 or click on the Excel icon at the top of the page.

Switching back to the worksheet, suppose you wanted to get the value in cell A2, Sharon Hagar. Now, switch back to the VBA editor by either using Alt+F11 or clicking the VBA icon on the Developer menu. To refer to a specific cell, use the Range function. Type the following into the Immediate window.

Print Range("A2")

When you execute that line of code, it will return the value Sharon Hagar. Notice that the cell address is passed in double quotes, indicating it is a text or string value. In VBA, we refer to text variables as strings, as is common in other programming languages.

There are a lot of properties or data values that could be associated with a given cell. The VBA Range function returns an object with many different properties or values. One of those is the .Value property, the default property, so we can omit it if that is the property we would like. Of course, we could add it if it makes things clearer.

Print Range("A2").Value

In this case, it is clear that I want the underlying value of the cell, so we won’t make an explicit reference to it.

Print is a very common command and has a shortcut associated with it. We can actually just use the ? symbol instead of typing the Print command, and it will work just the same.

There are other ways to refer to cell values rather than just using the Range function. For example, the Cells function allows us to pass a row and a column, and it will return the value of that cell. Let’s run the following code.

Print Cells(2,3).Value

This code will get the value of the cell found in the second row and the third column, which happens to be a street address: 3669 Bagwell Avenue. Use Alt+F11 to switch back and forth to make sure you understand where the values are coming from.

In these examples, we've actually typed in a property and retrieved or read out the values from the worksheet. VBA also allows us to update the value in a cell. Let's go ahead and update A3. Instead of Casey Nowicki, let’s use the value Sally Smith or your own name.

Range("A3")="Sally Smith"

Notice that I get the cell or range object associated with A3, and then I update its default property, or value, with the new name. The = allows you to assign a new value to properties that can be updated. In other words, the variable on the left-hand side is assigned the value found on the right-hand side of the equal sign. Go ahead and confirm that your cell is updated using Alt+F11.

We've been working with properties, but objects can also have what are known as methods. Properties are data elements associated with an object. Methods are actions that operate on that specific object. We illustrate that concept using the select method, as shown in the following.

Range("C10").Select

As you began typing the command and pressed the period, did you notice that IntelliSense popped up, showing you all the available data and properties? If you didn’t notice, go ahead and retype it, pausing after the period. Scroll to see all the different options. Once you have the above command typed, press Enter and then switch back to your worksheet. You can see that cell C10 is now the currently active cell.

So, there are lots of different properties and methods associated with each object. As you gain experience with VBA, you’ll become familiar with many of these properties and methods. You don't have to know everything to start getting value from VBA. Excel allows you to record a macro and will generate VBA code that you can use as a starting point for your VBA. If you can record a macro that does what you want, then you can investigate which objects the macro used, which properties it was setting, and more. In that way, recording these macros is great, as it allows you to quickly see how to interact with an object you may not know much about. We’ll use this approach later in this lesson.

Let’s illustrate just one more example of properties before moving on. We can adjust not only the value of a cell but also its formatting. Suppose you wanted to bold the value in a given cell. You can use the following code to do that.

Range("A3").Font.Bold=True

The Range function gets the object for cell A3, and then we access the Font property, which is actually an object itself. Selecting the Bold property would tell us whether cell A3 is bolded or not. However, here, we assign that property, setting its value to True, and then it makes sure that the user interface matches. After executing the command, switch back and verify that your name has been bolded.