Getting Started with VBA Development in Office 2010

  • Article

Office Visual How To

Summary:  Learn how to extend Microsoft Office 2010 with VBA programming for non-programmers. Included are an overview of the VBA language, how to access VBA in different Office 2010 applications, programming examples, and some example applications for VBA use.

Applies to: Excel 2010 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Word 2010

Published:  September 2010

Provided by:  Peter Gruenbaum, SDK Bridge, LLC

Overview

Visual Basic for Applications (VBA) for Office 2010 adds new capabilities to the applications in the Office suite. For example, you can apply repetitive changes to 50 tables in a Word document, or force a document to prompt a user for input when it opens, or efficiently move contacts from Outlook into an Excel spreadsheet.

The Office 2010 suite of applications has a rich set of features. There are many ways to author, format, and manipulate documents, e-mail, databases, forms, spreadsheets, and presentations. The great power of VBA programming in Office 2010 is that every possible operation that you can do with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done one time with VBA, it can be done as easily 100 times or 1000 times. Also, VBA can be used to add new functionality to Office applications; for example, prompting the user of your documents in ways that are specific to your business needs.

There are several compelling reasons to consider VBA programming in Office:

1. Automation and Repetition. VBA is highly effective for repetitive solutions to formatting or correction problems. Examples of repetitive tasks that can be automated with VBA include the following: changing the style of the paragraph at the top of each page in Word, reformatting several tables pasted from Excel into Word or an Outlook email, and making a repeated change to your Outlook contacts. Almost any formatting or editing change that can be done by hand can be done in VBA.

2. Extension. There are times when you want to encourage (or even compel) your user to interact with an Office application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.

3. Interaction between Office 2010 Applications. With VBA, you can move all the contacts from Outlook to Word or data from an Excel spreadsheet into a set of PowerPoint slides. Compared to copy-and-paste user interactions, VBA gives you speed and control.

VBA programming is a powerful solution. However, it is not always the optimal approach. Sometimes it makes sense to use other ways to achieve your aims. Before you start a VBA project, consider the built-in tools and standard functionalities. For example, if you have a time-consuming editing or layout task, consider using styles or keyboard shortcuts to resolve the problem. You may be able to perform the task once and then use CTRL+Y (Redo) to repeat it. Alternately, you can create a new document by using the correct format or template, and then copy the content into that new document.

This Visual How To shows how to use get started with VBA if you are not a programmer. It explains how to manipulate Office documents through VBA objects and provides simple programming examples.

Code It

This article contains sample code for several VBA macros. This section discusses the following subjects:

  • How to enable the Developer tab in Office 2010 applications

  • Recording a macro

  • VBA Example 1: Changing Excel Cell Borders

  • VBA Example 2: Branching and Looping

  • VBA Example 3: Create an Email in Outlook

  • VBA Example 4: Delete Empty Rows in Excel

  • VBA Example 5: Copying Data Between Office Applications

To use VBA, you create Macros using Visual Basic code. Information on how to program in VBA can be found in the Read It section.

Enabling the Developer Tab

All Office 2010 applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office 2010 does not display the Developer tab by default, you must enable it by using the following procedure.

To enable the Developer Tab

  1. On the File tab, select Options to open the Options dialog box.

  2. Click Customize Ribbon on the left side of the dialog box.

  3. Under Choose commands from on the left side of the dialog box, select Popular Commands.

  4. Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop-down list, and then select the Developer check box.

  5. Click OK.

Note

In the 2007 version of Microsoft Office applications, you use the Office Button to open the Options dialog. Under the Popular category click Show Developer tab in the Ribbon.

After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.

Figure 1. Buttons on the Developer tab

Buttons on the Developer tab

When you click the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.

Recording a Macro

Another button on the Developer tab in Word 2010 and Excel 2010 is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application. Record Macro is a useful tool that you can use to learn more about VBA. Reading the generated code can give insight into VBA and provide a stable bridge between your knowledge of Office 2010 as a user and your knowledge as a programmer. Be aware that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.

To record a macro

  1. Open Excel 2010 to a new Workbook or Word 2010 to a new document.

  2. Click the Developer tab in the ribbon. Click Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location.

  3. Click OK to begin recording the macro. Be aware of how the button text changes to Stop Recording. Click the button when you complete the actions that you want to record.

  4. Click in cell B1 and type the first string: Hello World. Stop typing and look at the Stop Recording button; it is dimmed because Excel 2010 is waiting for you to finish typing the value in the cell.

  5. Click in cell B2 to complete the action in cell B1, and then click Stop Recording.

  6. Click Macros on the Developer tab, select Macro1 if it is not selected, and then click Edit to view the code from Macro1 in the Visual Basic Editor.

Figure 2. Visual Basic Editor showing recorded macro

Visual Basic Editor showing recorded macro

The macro that you created should resemble the following code example.

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Hello World"
    Range("B2").Select
End Sub

In the code, cell B1 is selected, and then the string "Hello World" is applied to the cell that was made active. The quotation marks around the text specify a string value instead of a numeric value.

The last line shows how you clicked cell B2 to display the Stop Recording button again. The macro recorder records every keystroke.

The lines of code that begin with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA ignores any line, or part of a line, that begins with a single quotation mark. Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this Visual How To. Subsequent references to this code in the article do not include those four comment lines.

Tip

The Application object is implied in all VBA macros. The code that you recorded works with the Application object.

Using Developer Help

When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. If you do not recognize a given property, there are many resources that are available to help you. For example, in the macro that you recorded, the macro recorder generated code that refers to the ForumulaR1C1 property. It is not at all obvious what that means, so you can look it up using Developer help.

Select ForumulaR1C1  in the recorded macro and press F1. The Help system runs a quick search, determines that the appropriate subjects are in the Excel 2010 Developer section of the Excel 2010 Help, and lists the ForumulaR1C1  property. You can click the link to read more about the property, but before you do, be aware that the Excel Object Model Reference link near the bottom of the window. Click the link to view a long list of objects that Excel 2010 uses in its object model to describe the Worksheets and their components. Click any one of those to see the properties and methods that apply to that particular object, and cross references to different related options. Many Help entries also have brief code examples that can help you. For example, you can follow the links in the Borders object to see how to set a border in VBA.

Worksheets(1).Range("A1").Borders.LineStyle = xlDouble


Note

The Borders code looks different from our recorded macro. One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 here.

VBA Example 1: Changing Excel Cell Borders

The first example, ExcelBorders, sets the cell A1 to have a value of "Wow!" and sets the border style to be a double line. It uses the code that was mentioned earlier that we found in the Developer section of Excel 2010 Help.

From Excel, open up the Visual Basic Editor, and from the File menu, Choose Import, and then select ExcelBorders.bas.

Sub ExcelBorders()
    Worksheets(1).Range("A1").Value = "Wow!"
    Worksheets(1).Range("A1").Borders.LineStyle = xlDouble
End Sub

Return to the Excel worksheet and then click the Macros button again. Select ExcelBorders and then click the Run button. You should now see A1 contains the text Wow! with a double line border around it.

Figure 3. Results of your first macro

Results of your first macro

VBA Example 2: Branching and Looping

The second example, BranchingLooping, checks the value of cell A1 to see whether it says "Fill". If it does, it fills in the next rows, from A2 to A10, with a number that is equal to two times the row number. If it does not have the value "Fill" in A1, then it displays a message box telling you to do so. From Excel, open up the Visual Basic Editor, and from the File menu, Choose Import, and then select LoopingBranching.bas. This is described in more detail in the Branding and Looping section.

Sub BranchingLooping()
    If Worksheets(1).Range("A1").Value = "Fill" Then
        Dim row As Integer
        For row = 2 To 10
            Worksheets(1).Range("A" & row).Value = row * 2
        Next row
    Else
        MsgBox ("Put Fill in Cell A1")
    End If
End Sub

VBA Example 3: Create an Email in Outlook

The next example, MakeMessage, creates an Outlook mail message that has an HTML body. From Outlook, open up the Visual Basic Editor, and from the File menu, Choose Import, and then select MakeMessage.bas. Run the Macro.

Sub MakeMessage()
    Dim OutlookMessage As Outlook.MailItem
    Set OutlookMessage = Application.CreateItem(olMailItem)
    OutlookMessage.Subject = "Happy New Year!"
    OutlookMessage.HTMLBody = "<HTML><BODY>Dear <p>Happy New Year! We have had a good year this year…</BODY></HTML>"
    OutlookMessage.Display
    Set OutlookMessage = Nothing
End Sub

Note

There are many applications in Outlook for which you may want to automate creating an email. For example, you could loop through all the contacts, select contacts that you want to send this message to (for example, those that have a business associated with them), and automatically generate messages for only those contacts. In addition to VBA, you can use templates.

VBA Example 4: Delete Empty Rows in Excel

Another example, DeleteEmptyRows, deletes any row in Excel where the first column in the selection contains a blank cell. From Excel, open up the Visual Basic Editor, and from the File menu, Choose Import, and then select DeleteEmptyRows.bas.

Sub DeleteEmptyRows()
    SelectedRange = Selection.Rows.Count
    ActiveCell.Select
    For i = 1 To SelectedRange
        If ActiveCell.Value = "" Then
            Selection.EntireRow.Delete
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Next i
End Sub

The first line of the subroutine creates a variable called SelectedRange and sets it to the number of rows that the user has selected. Note that there is no Dim statement first to create the variable; in Visual Basic, using Dim is optional. Next the active cell, which will be the upper-left cell in the original selection, is selected. Now a variable i is looped from 1 to the number of rows the user originally selected. If the active cell is empty, it will delete the whole row where the selection is occurring. If it is not, then the selected cell is moved down one row.

In your spreadsheet, fill the A column with data, but leave some cells empty. Select all the rows, and run the macro. You can see where it deleted the rows.

VBA Example 5: Copying Data Between Office Appications

The final example, CopyCurrentContact, takes data from an open contact in Outlook and puts it into an open document in Word. From Word, open up the Visual Basic Editor, and from the File menu, Choose Import, and then select DeleteEmptyRows.bas. Open up a contact in Outlook. Finally, open up a blank Word document, and run the macro.

Sub CopyCurrentContact()
    Dim OutlookObj As Object
    Dim InspectorObj As Object
    Dim ItemObj As Object
    Set OutlookObj = CreateObject("Outlook.Application")
    Set InspectorObj = OutlookObj.ActiveInspector
    Set ItemObj = InspectorObj.CurrentItem
    Application.ActiveDocument.Range.InsertAfter(ItemObj.FullName & " from " & ItemObj.CompanyName)
End Sub

The first three lines create variables: one for the Outlook application, one for the Inspector, which identifies what item is open in Outlook, and one for the open contact. The next line initializes the Outlook variable. The following line sets the inspector, and the line after that uses the inspector to find the open contact. The last line of the subroutine gets the full name and company name and inserts it into the open Word document.

Read It


Writing VBA Code: Objects, Properties, and Methods

You might think that writing code is mysterious or difficult, but you will find with some commonsense and logic, it is not that hard. Office applications are created in such a way that they expose things called objects that can receive instructions, much as a telephone is designed with buttons that you use to interact with the telephone. When you press a button, the telephone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. In programming, you interact with the application by sending instructions to various objects in the application. These objects can do a lot, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.

Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. So Word, for example, has a top-level Application object that contains a Document object. The Document object contains Paragraph objects and so on. Object models approximately mirror what you see in the user interface. They're a conceptual map of the application and what it can do.

Be aware that objects have properties and methods. Properties are other objects that belong to your object, and methods are actions that the object can take. To access either a property or method, you place a period after the object, and then you put the property or method name. For example, an object that represents a cell in an Excel spreadsheet would have a Value property that returns the value that is contained in the cell, and it has a Select method that causes the cell to become selected.

Start with Examples

The VBA community is large; a search on the web can usually yield an example of VBA code that does something similar to what you want to do. If you cannot find a good example, try to break the task down into smaller units and search on each of those, or try to think of a more common, but similar problem. Starting with an example can save you hours of time.

That does not mean that free and well-thought-out code is located on the web waiting for you to come along. In fact, some code that you find might have bugs or mistakes. The idea is that the examples that you find online or in VBA documentation give you a head start. Remember that learning programming requires time and thought. Before you use another solution to resolve your problem, ask yourself whether VBA is the best choice for this problem.

Make a Simpler Problem─Build a Piece First

Programming can get complex quickly. It is important, especially as a beginner, that you break the problem down to the smallest possible logical units, then write and test each piece in isolation. If you have too much code in front of you and you are confused, stop and set the problem aside. When you come back to the problem, copy out a small piece of the problem into a new module, solve that piece, get the code working, and test it to ensure that it works. Then move on to the next part.

Bugs and Debugging

There are two main kinds of programming errors: syntax errors, which violate the grammatical rules of the programming language, and run-time errors, which look syntactically correct, but fail when VBA attempts to execute the code.

Although they can be frustrating to fix, syntax errors are easy to catch. The Visual Basic Editor beeps and flashes at you if you type a syntax error in your code. Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it.

It is a good idea to learn how to use the debugger more deliberately for longer, more complex programs. At a minimum, learn a how to set break-points to stop execution at a point where you want to examine the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line. These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts.

Using Reference Materials

To open the Developer Reference that is built into Office 2010 Help, open the Help reference from any Office 2010 application by clicking the question mark in the ribbon or by pressing F1. Then, to the right side of the Search button, click the drop-down arrow to filter the contents. Click Developer Reference. If you do not see the table of contents in the left panel, click the little book icon to open it and then expand the Object Model Reference from there.

Figure 4. Filtering developer help

Filtering developer help

Time spent browsing the Object Model reference pays off. After you understand the basics of VBA syntax and the object model for the Office 2010 application that you are working with, you advance from guesswork to methodical programming.

Also, the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

Searching Forums and Groups

All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. Fortunately, the Internet has fostered a community of developers who help one another solve programming problems.

Any search on the web for "office developer forum" reveals several discussion groups. You can search on "office development" or a description of your problem to discover forums, blog posts, and articles also.

If you have done everything that you can to solve a problem, do not be afraid to post your question to a developer forum. These forums accept posts from newer programmers and many of the experienced developers are glad to help.

The following are some points of etiquette to follow when you post to a developer forum:

  • Before you post, look on the site for a FAQ or for guidelines that members want you to follow. Ensure that you post content that is consistent with those guidelines and in the correct section of the forum.

  • Include a clear and complete code sample, and consider editing your code to clarify it for other users if it is part of a longer section of code.

  • Describe your problem clearly and concisely, and summarize any steps that you have taken to resolve the problem. Take the time to write your post as well as you can, especially if you are flustered or in a hurry. Present the situation in a way that will make sense to readers the first time that they read the problem statement.

  • Be polite and express your appreciation.

Going Further with Programming

Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started. This section briefly discusses several more key topics.

Variables

In addition to manipulating objects that the application had already created, you might want to create your own objects to store values or references to other objects for temporary use in your application. These are called variables.

To use a variable in VBA, you must identify the object type that the variable represents by using the Dim statement. Then you set its value and use it to set other variables or properties.

The following code example creates a variable called i that will store an integer (that is, a whole number, not a decimal or fraction).

Dim i As Integer

Branching and Looping

The simple programs in this article execute one line at a time, from the top down. The real power in programming comes from when you use options that determine which lines of code to execute, based on one or more conditions that you specify. You can extend those capabilities even more when you can repeat an operation many times. The If statement checks whether something is true, and then does all the steps after it until it comes to Else or End If. The For statement will repeat all of the steps in between itself and the Next statement, increasing the variable within its range every time. The following code example shows how to use If and For statements to check for a value in a cell, then fill up several other cells. It also displays a message box if the value in the cell is not what is expected.

Sub Macro1()
    If Worksheets(1).Range("A1").Value = "Yes!" Then
        Dim i As Integer
        For i = 2 To 10
            Worksheets(1).Range("A" & i).Value = "OK! " & i
        Next i
    Else
        MsgBox "Put Yes! in cell A1"
    End If
End Sub
 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/e2543329-40f0-49a0-86b7-175bf1f2aefd]

Length: 00:09:57

Click to grab code

Grab the Code

Explore It