We launched a new IT training category! Check out the 140+ courses now.

Learn it fast with expert-taught software and skills training at lynda.com. See what you can learn

By Curt Frye | Thursday, June 26, 2014

Getting Started with Excel VBA

excel-cells-in-repose

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:

  • properties, which describe the object
  • methods, which are things the object can do
  • events, which are occurrences the object can recognize and react to

A working example

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.

Excel VBA 001

Here’s the code that makes the monetary amounts in cells B5 through B8 bold:

Sub MakeBold()
   Range("B5:B8").Select
   Selection.Font.Bold = True
End Sub

Those four short lines of VBA, two of which signal the start and end of the code, make the change we wanted.

Excel VBA 002

Examining the VBA code

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.

Sub MakeBold()
⁞
End Sub

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.

What else can you do with Excel VBA?

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
End Function

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.

Further resources

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.

Learn it from the experts.

With online video courses at lynda.com, you can reach your goals faster. Learn software, improve your skills, and get an inside look at how the professionals work.

See what you can learn


Share this article:

Tags: , , , , ,

Get the latest news

  •   New course releases
  •   Pro tips and tricks
  •   News and updates
  
New releases submit clicked

You can change your email preferences at any time. We will never sell your email. More info

Featured articles

A lynda.com membership includes:

Unlimited access to thousands of courses in our library
Certificates of completion
New courses added every week (almost every day!)
Course history to track your progress
Downloadable practice files
Playlists and bookmarks to organize your learning
Become a member

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:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.