Now that you know something about how Microsoft Excel 2010 exposes its object model, you can try calling object methods and setting object properties. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon.

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 using the following procedure:

To enable the Developer tab

  1. On the File tab, choose Options to open the Excel 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, and then select the Developer check box.
  5. Click OK.

After Excel displays the Developer tab, note the location of the Visual Basic, Macros, and Macro Security buttons on the tab.

 

Security Issues

Click the Macro Security button to specify which macros can run and under what conditions. Although rogue macro code can seriously damage your computer, security conditions that prevent you from running helpful macros can seriously undermine your productivity. Macro security is a complex and involved topic that you should study and understand if you work with Excel macros.

For the purposes of this article, be aware that if the Security Warning: Macros have been disabled bar appears between the ribbon and the worksheet when you open a workbook that contains a macro, you can click the Enable Content button to enable the macros.

Also, as a security measure, you cannot save a macro in the default Excel file format (.xlsx); instead, you must save the macro in a file with a special extension, .xlsm.

Visual Basic Editor

This following procedure shows you how to create a new blank workbook in which to store your macros. You can then save the workbook in the .xlsm format.

To create a new blank workbook

  1. Click the Macros button on the Developer tab.
  2. In the Macro dialog box that appears, type, Hello under Macro Name.
  3. Click the Create button to open the Visual Basic Editor with the outlines of a new macro already typed in.

VBA is a full-featured programming language with a correspondingly full-featured programming environment. This article examines only those tools that you use to get started with programming, and that excludes most of the tools in the Visual Basic Editor. With this caveat, close the Properties window on the left side of the Visual Basic Editor, and ignore the two dropdown lists that appear above the code.

 

The Visual Basic Editor contains the following code.

 
Sub Hello()

End Sub

Sub stands for Subroutine, which you can define for now as “macro”. Running the Hello macro runs any code that is between Sub Hello() and End Sub.

Now edit the macro so that it looks similar to the following code.

 
Sub Hello()
   MsgBox ("Hello, world!")
End Sub

Go back to the Developer tab in Excel and click the Macros button again.

Select the Hello macro in the list that appears and then click Run to display a small message box that contains the text, “Hello, world!”

You just created and implemented custom VBA code in Excel. Click OK in the message box to close it and finish running the macro.

If the message box does not appear, check your macro security settings and restart Excel.