Modifying the Macro

Before you can modify your macro, you must find the location where the macro recorder placed its code. As you recall, when you turned on the macro recorder, you selected This Workbook for the location. The easiest way to find your macro is by opening the Macro dialog box shown in Figure 1-4.

1. Choose Tools | Macro.

2. Select the name of the macro (WhatsInACell, in this case).

3. Click the Edit button.

Microsoft Excel opens a special window called Visual Basic Editor (also known as VBE), as shown in Figure 1-5. Using the keyboard shortcut Alt+F11, you can quickly move between the Microsoft Excel application window and the Visual Basic Editor window. Now take a moment and try moving between both windows. To close the Visual Basic Editor window, select Close on the VBE File menu and return to Microsoft Excel.

Don't worry if the Visual Basic Editor window seems a bit confusing at the moment. As you work with the recorded macros and start writing your own VBA procedures from scratch, you will become familiar with all the elements of this screen. For now, take a look at the menu bar and toolbar in the Visual Basic Editor window. Both of these tools differ from the ones in the Microsoft Excel window. The Visual Basic menu bar and toolbar contain tools required for programming and testing your VBA procedures. As you work through the individual chapters of this book, you will become an expert in using these tools.

Microsoft Visual Baste Chap01.xls [Module! (Code)]

^ File Ed* View insert Fgrrrei Debug Run Tods ¿dd-lns Window Help j5]i!-l3 a ^ ► it bM: ^ Z)

% % :□ -n • - • -r,^- ?¡Hit ¿My ^ ifiwa ■

BBHI

fix)

B VBAProject (ChapOLxIs)

6 (ED Microsoft Excd Objects B Modules

Möddel

Module 3

Alphabetic | Categorized |

Modulel

13 f iWlMlslnACtll

Sub What sin ACellQ

WhatsInACell Macro

Macro recorded 5/31/2002 by Julitta Korol

Indicates the contents of the underfying cells: teat, numbers, formulas.

| Selection. SpeciaICells(xlCel UypeConstants. 2). Select With Selection. Font .Name = "Arial" .FontStyle = "Bold" .Size = 10

. St riketh rough = False .Superscript - False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyteNone .Colorlndex = 13 End With

Range("B6").Selee5

Selection. SpecialCefls(xlCeHTypeConstants, 1). Select

Figure 1-5: The Visual Basic Editor window is used for editing macros as well as writing new procedures in Visual Basic for Applications (VBA).

The main part of the Visual Basic Editor window is a docking surface for various windows that you will find extremely useful during the course of creating and testing your VBA procedures. Figure 1-5 displays three windows that are docked in the Visual Basic Editor window: the Project window, the Properties window, and the Code window. The Project window shows an open Module folder in which Module1 is selected. Excel records your macro actions in special worksheets called Module1, Module2, and so on. In the following chapters of this book, you will use modules to write the code of your own procedures. A module resembles a blank document in Microsoft Word. Individual modules are stored in folders called modules.

Tip 1-5: Macro or Procedure?

A macro is a series of commands or functions recorded with the help of a built-in macro recorder or entered manually in a Visual Basic module. Beginning with Excel 5.0, the term "macro" is often replaced with the broader term "procedure." Although the words can be used interchangeably, many programmers are in favor of procedures. While macros allow you to mimic keyboard actions, true procedures can also execute actions that cannot be performed using the mouse, keyboard, or menu options. In other words, procedures are more complex macros that incorporate language structures found in the traditional programming languages.

The Code window (see Figure 1-5) displays the following code created by the macro recorder:

Sub WhatsInACell() ' WhatsInACell Macro

' Macro recorded 5/31/2002 by Julitta Korol ' Indicates the contents of the underlying cells: text, numbers, ' formulas.

Selection.SpecialCells(xlCellTypeConstants, 2).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = 13 End With

Range("B6").Select

Selection.SpecialCells(xlCellTypeConstants, 1).Select With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10

.Strikethrough = False

.Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = 11 End With

Range("C6").Select

Selection.SpecialCells(xlCellTypeFormulas, 23).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = 3 End With

Range("A1:A3").Select Selection.EntireRow.Insert Range("A1").Select With Selection.Interior .Colorlndex = 13 .Pattern = xlSolid .PatternColorlndex = xlAutomatic End With

Range("B1").Select ActiveCell.FormulaR1C1 = "Text" Range("A2").Select With Selection.Interior .Colorlndex = 5 .Pattern = xlSolid .PatternColorlndex = xlAutomatic End With

Range("B2").Select ActiveCell.FormulaR1C1 = "Numbers" Range("A3").Select With Selection.Interior .Colorlndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With

Range("B3").Select ActiveCell.FormulaR1C1 = "Formulas" Range("B4").Select End Sub

For now, let's focus on finding answers to two questions: How do you read the macro code, and how can you edit macros?

0 0

Post a comment