Chapter 1: Writing Your First Macro

In this chapter you will learn about such concepts as "macro" and "Visual Basic for Applications." You will also record your very own macros, link macros with new tools (which you create) in the toolbar, change the menu structure, and put together a simple database application. This chapter offers ”to the extent possible with simple examples ”a brief glance at the material that will be covered in much greater detail in the remainder of this book. Welcome aboard!

1.1 Definition of Terms

Macros

This chapter is entitled "Writing Your First Macro." It thus seems appropriate at this point to explain the term "macro." A macro is a sequence of instructions to the computer that the computer then carries out when ordered to do so.

What is the purpose of macros? With macros it is possible to simplify and automate tasks that are frequently repeated. You can customize Excel to meet your particular needs and equip Excel with new menu commands and tools. Furthermore, you can simplify Excel for other users so that they can make use of particular Excel applications with almost no additional training. And finally, you can write full-blown "programs" that will be hardly recognizable as having originated from within Excel.

Since the computer unfortunately does not understand instructions such as "save this data" or "display the three selected cells in a larger font," macro instructions must be formulated in a special language. For reasons of compatibility, Excel offers two such languages from which to choose:

• The original macro programming language, called XLM, was created for early versions of Excel. Macros written in this language are referred to as XLM macros or Excel 4 macros, since the basic structure of this language has not been changed since version 4 of Excel.

• Beginning with version 5, the new language Visual Basic for Applications (VBA for short) was introduced. It offers more extensive and refined possibilities for the control of programs, though at first glance it may seem somewhat complex ( especially for those who have written XLM macros).

Note  

In Excel version 5 several dialects of VBA in several languages were available, such as French, German, and Spanish. In version 7 these dialects were still supported, but they were no longer the default language. With version 97 they disappeared entirely. Foreign-language VBA code is now automatically converted to English when a file is loaded. This book describes English VBA exclusively. Here is an example in the two macro languages (first as an Excel 4 macro, then in VBA) that saves the current file:

 =SAVE()                      'XLM macro (Excel 4) ActiveWorkbook.Save          'VBA (Excel 5, 7, 97, 2000 etc.) 

Our next example displays a group of selected cells in a larger font size (first as an Excel 4 macro, then in VBA):

 =FONT.PROPERTIES(, GET.CELL(19)+2)              'Excel 4 Selection.Font.Size = Selection.Font.Size + 2   'VBA 

When the term "macro" is used in books or in on-line documentation it is not always made clear whether the XLM or VBA language is meant . In this book, however, the term "macro" will always mean a VBA macro.

Note  

The above examples are not usable in the form given. An Excel 4 macro must begin with the name of the macro and end with the command =RETURN().VBA macros must be bracketed between Sub Name () and End Sub. The basic syntax of VBA should become clear from the examples presented in this chapter. A detailed description of VBA syntax will be presented in Chapter 4.

Recording Macros

In general, there are two ways of creating macros: You can input the VBA commands from the keyboard, or you let Excel "record" the macro. By this is meant that (using the mouse and keyboard) you perform actions such as data entry, formatting of cells, and executing commands, and Excel follows your actions and writes the corresponding VBA instructions in a module. When later you execute the macro that has been created in this way, the exact same steps that you had previously executed by hand are executed.

In reality, macros are generally created by a combination of these two methods . You will certainly frequently let Excel record your actions, but it will also frequently be necessary to alter or supplement these macros by typing at the keyboard.

Executing Macros

The least satisfactory method of executing a macro is provided by the command ToolsMacro, which produces a list of all the defined macros in all open workbooks. When you click on the name of a macro, the corresponding macro is executed.

However, there are quite a few more elegant methods available: You can link a macro to an arbitrary (new) tool in the toolbar, to a menu item, or to a keyboard shortcut Ctrl+ letter. The macro is executed when you choose the menu item, click on the tool, or type the keyboard shortcut. Macros implemented in this way can save a great deal of effort, as shown in the examples that appear in the following section.

There is even the possibility of having macros execute automatically when certain events occur. Excel knows about a large number of such events, such as a change in the active worksheet, the recalculation of a worksheet, and saving a workbook. Event procedures will be dealt with in detail in Chapter 4.

Programs

Many users of Excel, even those who have already created macros, are ready to tear out their hair when they hear the term "programming." One frequently hears the following opinion expressed : "Programming? That is something for computer professionals, for that you need an advanced degree." But relax! You are, in fact, already a programmer by virtue of having created your first macro, even if it is only three lines long. In principle, every macro is a program.

In this book the notion of a program will be construed for the most part somewhat more broadly. By a program we will mean a freestanding Excel application, distinguished in general by having its own menu commands, forms, and, usually, a large number of macros. This book will take you from the baby steps of your first macro (in this chapter) all the way to the giant steps of substantial programs.