Curt Frye |
Thursday, June 26, 2014
Excel is a powerful and versatile tool you can use to analyze data—but not every capability you might want is built in.
Using the Visual Basic for Applications (VBA) language, you can script custom processes in Excel. VBA is an object-oriented language, which means that elements of Excel—such as workbooks, worksheets, and the program itself—are represented as objects. An object has three main components:
As an example of what VBA code might look like, I’ll create a simple script to apply bold formatting to the cell range B5:B8 in an accounting worksheet.
Here’s the code that makes the monetary amounts in cells B5 through B8 bold:
Selection.Font.Bold = True
Those four short lines of VBA, two of which signal the start and end of the code, make the change we wanted.
Let’s take a closer look at the VBA code we just created. The first and last lines go together, so I’ll discuss them first.
As you probably guessed, these two lines signal the start and end of the code. The first line, Sub MakeBold(), indicates the start of a subroutine named MakeBold. A subroutine can make changes to your workbook but doesn’t return a value that can be used in a formula (that’s the job of a function, which I’ll discuss later). If you gave the MakeBold subroutine values to use in its code, you would put them in the parentheses. In this case there are no such values, so you leave them empty. End Sub is just that: the end of the subroutine.
The next line, Range("B5:B8").Select, is a little more complex. The first part of the statement, Range("B5:B8"), uses the Range object to identify the cells you want to affect. After the period, the word Select tells Excel to select the range to the left of the period. Using the Select method on this Range object has exactly the same effect as using your mouse to click and drag over the cell range B5:B8.
Using a period to separate object names from properties, methods, and events is called dot notation.
Now that the target range is selected, Excel can move on to your third line of code:
Selection.Font.Bold = True
When you use the Select method, the cell range you identify is saved to the built-in object Selection, which has all of the properties of the Range object. One of those properties is actually another object, the Font object, which has its own properties. One of those properties is Bold, which indicates whether the text referred to by the Selection object should be displayed in bold characters. If True, it’s displayed in bold type; if False, it’s displayed in regular type.
In addition to subroutines, which affect your workbook but don’t return values that can be used in formulas, you can also use VBA to define functions that you are able to use in formulas. As an example, you might create a function named multByPi to multiply a number by pi.
Function multByPi(dNumber As Double)
multByPi = dNumber * 3.14159
You can use functions in cell formulas, so if cell B12 contained the number 4, the formula =multByPi(B12) would return the value 12.56636.
These simple examples barely scratch the surface of what you can do with Excel VBA. Excel has had built-in scripting capabilities for over 20 years, so the product team has had ample time to build VBA (the latest of those languages) into a powerful tool that can affect every aspect of your workbooks so you can automate time-consuming manual tasks and build powerful analytical tools.
lynda.com offers several courses to help you start using VBA to enhance Excel’s functionality. Dennis Taylor’s Excel 2013: Macros in Depth, and his companion courses for Excel 2007 and Excel 2010, show you how to record sequences of actions as VBA macros that you can replay as desired. My own course, Up and Running with VBA in Excel, shows you how to write your own code for Excel 2007, Excel 2010, and Excel 2013, regardless of whether you start by recording a macro or work from scratch.
Advancing your VBA skills lets you shape Excel’s capabilities to your needs—and acquiring those capabilities will be well worth your time.
Tags: Curt Frye, Excel, Microsoft Excel, Spreadsheets, VBA, Visual Basic
Check out these popular Business courses.
Thanks for signing up.
We’ll send you a confirmation email shortly.
Sign up and receive emails about lynda.com and our online training library:
Keep up with news, tips, and latest courses with emails from lynda.com.
We've updated our terms and conditions (now called terms of service).Go Review and accept our updated terms of service.