In this chapter, you'll practice creating macros (procedures) from scratch in the Visual Basic Editor. The examples walk you through the process of creating a macro in Word, Excel, and PowerPoint.

For the examples in this book, the Visual Basic Editor should be set up a certain way visually, and set to require explicit declarations of variables (considered a good programming practice), so we'll start off this chapter by ensuring that these conditions are met.

The purpose of this chapter is to give you a feel for creating code in the Visual Basic Editor before you study the details of the VBA language in future chapters. Here you'll work briefly with VBA elements (such as objects, properties, methods, variables, and constants), but you will learn about them more fully later in this book.

Also you'll meet here several of the many helpful tools that the Visual Basic Editor provides, including the Macro Recorder, the Object Browser, and the Help system. You'll explore these tools, too, more thoroughly later in this book.

Setting Up the Visual Basic Editor to Create Macros

You'll find it easiest to follow the instructions in this chapter's macros—and in the rest of the book—if you have the Visual Basic Editor set up in a custom configuration. (Any changes you make to the VBA Editor will be in effect across all VBA-enabled Office applications.) So, if you set up the Editor as described next, it will look like this whether you open it in Excel, Word, Access, Outlook, or PowerPoint.

The following steps describe how to set up the Visual Basic Editor so it looks like Figure 4.1:

1. Open the Visual Basic Editor.

   

2. If the Project Explorer isn't displayed, choose View ➢ Project Explorer or press Ctrl+R to display it.
3. If the Properties window isn't displayed, choose View ➢ Properties Window or press the F4 key to display it.
4. Unless you really prefer things otherwise, dock the Project Explorer in its traditional position at the upper-left corner of the main Visual Basic Editor area.
5. Dock the Properties window below the Project Explorer, again in its default position.

   To change docking:

   1. Choose Tools ➢ Options.
   2. Click the Docking tab, and select the Docking options.

   To dock a window that is undocked (floating), double-click its title bar. And to float a window, double-click its title bar again.

   Now we'll set up the Visual Basic Editor to enforce a requirement that your variables be declared explicitly. The Editor will then create a rule that says you must declare each variable formally before you can use it in your code.

6. Choose Tools ➢ Options to display the Options dialog box, ensure the Require Variable Declaration check box on the Editor page is checked, and then click the OK button.

We'll discuss variable declaration in greater detail later in the book, but here's a brief summary. This setting makes the Visual Basic Editor automatically enter an Option Explicit statement for all code modules and user forms you create from now on. And that statement causes the Editor to check during runtime (when you execute a macro) for any implicitly declared variables (considered bad practice) and remind you that you must declare them explicitly, like this:

Dim txtName As String

Creating a Procedure for Word

The macro you'll create for Word causes the Track Changes feature to toggle how deleted text will be displayed (whether Strikethrough or Hidden). In other words, using this macro, you'll be able to switch instantly between having deleted text remain onscreen with a line through it or having it simply disappear.

Start by using the Macro Recorder to provide the necessary object qualifications. Then you can modify the code by hand in the Editor to specify the toggle behavior we're after.

Follow these steps to record the macro:

1. Start Word. If Word is already running, exit it and restart it.
2. Record a macro to get to the object qualifications (properties and settings) you need. (Remember that to some, recording may feel like cheating, but the Macro Recorder is truly a gift when it comes to finding objects and getting complicated syntax correctly coded.) Follow these substeps:
   1. Click the Developer tab on the Ribbon; then click the Record Macro button in the Code section to display the Record Macro dialog box.
   2. Either accept the macro name that the Macro Recorder automatically assigns (Macro1, Macro2, and so on) or create a scratch name of your own, such as Temp, that will remind you to delete the macro if you forget to do so.
   3. Leave the Store Macro In drop-down list set to All Documents (Normal.dotm). Leave the description blank. This is a temporary macro just for practice, so we won't add it to our permanent collection.
   4. Click the OK button to start recording the macro.
   5. Click the Review tab on the Ribbon, and then click the small arrow in the lower-right corner of the Tracking section.

      The Track Changes Options dialog box opens.

   6. In that box, click the Advanced Options button. Now ensure that Strikethrough is selected in the Deletions drop-down list (see Figure 4.2), and then click OK twice to close the two Track Changes Options dialog boxes.

      Strikethrough is the default, so it's probably already selected—but we want the Recorder to show us how this option is coded in VBA. Clicking OK to close a dialog box records all the current settings in that box.

      

   7. Repeat the preceding steps (e and f) to reopen the Track Changes Options dialog box.
   8. Now, select Hidden in the Deletions drop-down list, and again click OK twice to close the dialog boxes.
   9. Stop recording the macro by clicking the white recording button in the status bar or by clicking the Stop Recording button on the Developer tab on the Ribbon.
3. Click Developer ➢ Macros to display the Macros dialog box.
4. Select the Temp macro you just recorded and click the Edit button to open it for editing in the Visual Basic Editor.

   Your code should look like this:

   1.  Sub temp()
   2.  '
   3.  ' temp Macro
   4.  '
   5.  '
   6.      With Options
   7.          .InsertedTextMark = wdInsertedTextMarkUnderline
   8.          .InsertedTextColor = wdRed
   9.          .DeletedTextMark = wdDeletedTextMarkStrikeThrough
   10.         .DeletedTextColor = wdRed
   11.         .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
   12.         .RevisedPropertiesColor = wdByAuthor
   13.         .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
   14.         .CommentsColor = wdRed
   15.         .RevisionsBalloonPrintOrientation = _
   wdBalloonPrintOrientationPreserve
   16.     End With
   17.     ActiveWindow.View.RevisionsMode = wdMixedRevisions
   18.     With Options
   19.         .MoveFromTextMark = wdMoveFromTextMarkDoubleStrikeThrough
   20.         .MoveFromTextColor = wdGreen
   21.         .MoveToTextMark = wdMoveToTextMarkDoubleUnderline
   22.         .MoveToTextColor = wdGreen
   23.         .InsertedCellColor = wdCellColorLightBlue
   24.         .MergedCellColor = wdCellColorLightYellow
   25.         .DeletedCellColor = wdCellColorPink
   26.         .SplitCellColor = wdCellColorLightOrange
   27.     End With
   28.     With ActiveDocument
   29.         .TrackMoves = False
   30.         .TrackFormatting = True
   31.     End With
   32.     With Options
   33.         .InsertedTextMark = wdInsertedTextMarkUnderline
   34.         .InsertedTextColor = wdRed
   35.         .DeletedTextMark = wdDeletedTextMarkHidden
   36.         .DeletedTextColor = wdRed
   37.         .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
   38.         .RevisedPropertiesColor = wdByAuthor
   39.         .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
   40.         .CommentsColor = wdRed
   41.         .RevisionsBalloonPrintOrientation = _
   wdBalloonPrintOrientationPreserve
   42.     End With
   43.     ActiveWindow.View.RevisionsMode = wdMixedRevisions
   44.     With Options
   45.         .MoveFromTextMark = wdMoveFromTextMarkDoubleStrikeThrough
   46.         .MoveFromTextColor = wdGreen
   47.         .MoveToTextMark = wdMoveToTextMarkDoubleUnderline
   48.         .MoveToTextColor = wdGreen
   49.         .InsertedCellColor = wdCellColorLightBlue
   50.         .MergedCellColor = wdCellColorLightYellow
   51.         .DeletedCellColor = wdCellColorPink
   52.         .SplitCellColor = wdCellColorLightOrange
   53.     End With
   54.     With ActiveDocument
   55.         .TrackMoves = False
   56.         .TrackFormatting = True
   57.     End With
   58. End Sub

   That's a daunting amount of code for the few rather simple actions you took. Remember that this is because the Macro Recorder records the settings for all of the possible options in the Track Changes Options dialog box that you visited, not just the single option you selected and modified.

5. Look over the code briefly to see the many settings that were recorded from the options inside the dialog box displayed in Figure 4.2.

   If you look at the figure, you can see how the code reflects the settings. For example, see the .SplitCellColor = wdCellColorLightOrange line of code and locate the setting it refers to in the dialog box.

   A second set of nearly identical settings in the code represents your second visit to the dialog box. Notice lines 9 and 35 in particular; these are key. Line 35 reflects the change made on your second visit—specifying a hidden rather than strikethrough property for the DeletedTextMark property of the Options object. Notice, too, the two values for this property: wdDeletedTextMarkStrikeThrough (when you recorded the Deletions drop-down specifying Strikethrough) and wdDeletedTextMarkHidden (when you set it to Hidden).

6. Now in the Editor, select the entire recorded macro, from the Sub temp statement down to the End Sub statement, and press the Delete key to get rid of it.
7. Make sure the Visual Basic Editor is set up as described in the section “Setting Up the Visual Basic Editor to Create Macros” earlier in this chapter.
8. In the Project Explorer window, right-click anywhere in the Normal item and choose Insert ➢ Module from the context menu.

   The Visual Basic Editor inserts a new module in the Normal.dotm global template and displays a Code window for it.

9. Press the F4 key to activate the Properties window for the new module. (By activate I mean give the focus to—whatever window has the focus is the one where typing will be displayed or mouse clicks will have an effect.)

   The Visual Basic Editor selects the (Name) property, the only property available for this new module. (Confusingly, the property's name is enclosed in parentheses.)

10. Type a name for the new module in the Properties window. For this example, delete the default name (Module 1 or Module 2 or whatever it is) and type the name Procedures_to_Keep_1.
11. Press the F7 key or click in the Code window to activate it.
12. Verify that the Visual Basic Editor has entered the Option Explicit statement in the declarations area at the top of the code sheet (the code area) in the Code window. If not, go back and complete steps 4 and 5 in the list at the start of this chapter.
13. Below the Option Explicit statement, type the Sub statement for the procedure and press the Enter key. Name the procedure Toggle_Track_Changes_between_Hidden_and_Strikethrough:

    Sub Toggle_Track_Changes_between_Hidden_and_Strikethrough

    When you press the Enter key, the Visual Basic Editor inserts for you the required parentheses at the end of the Sub statement, a blank line, and the End Sub statement and places the insertion point on the blank line, ready for you to start typing in some programming:

    Sub Toggle_Track_Changes_between_Hidden_and_Strikethrough()
    End Sub

14. Press the Tab key to indent the first line below the Sub statement.
15. Type if options . (in lowercase, and be sure to end with the period).

    Now the Editor displays the List Properties/Methods drop-down list.

16. Type down through the list (type d, e, and then l) and use the ➢ key, or simply scroll with the mouse, to select the DeletedTextMark entry.
17. Now just type = (the equal sign).

    The Visual Basic Editor enters the DeletedTextMark command for you, followed by the equal sign, and then displays the List Properties/Methods list of constants that can be used with the DeletedTextMark property (see Figure 4.3).

    

18. Select the wdDeletedTextMarkHidden item and enter it into your code by pressing the Tab key or by double-clicking it.
19. Type Then and press the Enter key.

    Note that when you start the next line of code (by pressing Enter), the Visual Basic Editor checks the line of code for errors. If you used lowercase for the If Options part of the statement, the Visual Basic Editor applies capitalization. (This is just for show—thankfully, VBA pays no attention to capitalization when executing code). If there are no space characters on either side of the equal sign, the Visual Basic Editor adds them too.

20. Enter Options.DeletedTextMark=wdDeletedTextMarkStrikethrough, using the assistance offered by the Visual Basic Editor's Auto List Members features (described earlier, in steps 15 through 17), and then press Enter.
21. Press the Backspace key or Shift+Tab to unindent the new line of code by one tab stop.
22. Type the ElseIf keyword, and then enter the rest of the procedure as follows:

    ElseIf Options.DeletedTextMark = wdDeletedTextMarkStrikeThrough Then
    Options.DeletedTextMark = wdDeletedTextMarkHidden
    End If

23. Make sure your completed procedure looks like this:

    Sub Toggle_Track_Changes_between_Hidden_and_Strikethrough()
        If Options.DeletedTextMark = wdDeletedTextMarkHidden Then
            Options.DeletedTextMark = wdDeletedTextMarkStrikeThrough
        ElseIf Options.DeletedTextMark = wdDeletedTextMarkStrikeThrough Then
            Options.DeletedTextMark = wdDeletedTextMarkHidden
        End If
    End Sub

24. Click Developer ➢ Visual Basic to switch to Word, and then type in a line or two of text.
25. Arrange the Word window and the Visual Basic Editor window side by side.
26. In Word, click the Review tab on the Ribbon, and click the upper half of the Track Changes button (the graphic icon) to activate the feature that marks up (or otherwise handles) revisions.
27. Delete a word in your text, and notice whether it is struck through or is simply hidden.
28. You have a macro that toggles between these two behaviors, so in the Visual Basic Editor, press the F5 key or click the Run Sub/UserForm button (on the Standard and Debug toolbars) to run the macro.
29. Back in Word, see what effect the deletion has now. You can also take a look at the Track Changes Options dialog box to see that the Deletions setting has changed.
30. Click the Save button on the Standard toolbar in the Visual Basic Editor.

    Note that you could alternatively write this macro using a With statement for the Options object so that it looks like this:

    Sub Toggle_Track_Changes_between_Hidden_and_Strikethrough_2()
        With Options
            If .DeletedTextMark = wdDeletedTextMarkHidden Then
                .DeletedTextMark = wdDeletedTextMarkStrikeThrough
            ElseIf .DeletedTextMark = wdDeletedTextMarkStrikeThrough Then
                .DeletedTextMark = wdDeletedTextMarkHidden
            End If
        End With
    End Sub

There are usually several ways to code a given behavior in VBA. Although formal (professional) programmers learn a set of “best practices,” if you're just a hobbyist writing VBA for your own personal use, go ahead and code however you wish. Use whatever works.

Creating a Macro for Excel

The macro you'll create for Excel is short but helpful. When the user runs Excel, this macro maximizes the Excel window and opens the last file used. The macro also illustrates some useful techniques, including:

• Writing a macro that executes when an application first starts up
• Working with events
• Using the Object Browser to find the objects, methods, and properties you need

Follow these steps to create the procedure:

1. Start Excel if it's not already running.
2. If necessary, press Alt+Tab to cycle through your workbooks to locate Personal.xlsb. If your Personal Macro Workbook is currently hidden:
   1. Click the Unhide button in the Window section of the View tab on the Ribbon.
   2. Select PERSONAL.XLSB in the Unhide Workbook list box.
   3. Click the OK button.
3. Click Developer ➢ Visual Basic to open the Visual Basic Editor.
4. Make sure the Visual Basic Editor is set up as described earlier in this chapter.
5. In the Project Explorer window, expand VBAProject (PERSONAL.XLSB) if it's collapsed. To expand it, either double-click its name or click the + sign to its left.
6. Expand the Microsoft Excel Objects folder.
7. Double-click the ThisWorkbook item to open its code sheet in a Code window.

   The ThisWorkbook object represents the current workbook.

8. Verify that the Visual Basic Editor has entered the Option Explicit statement in the declarations area at the top of the code sheet. If not, go back and complete steps 4 and 5 in the list at the start of this chapter.
9. In the Code window click below any existing macros, and press Enter to make room for this new macro. Then type

   Private Sub Auto_Open

   and then press the Enter key.

   The Editor will add the required parentheses and the End Sub line.

10. Open the Object Browser: press the F2 key, or choose View ➢ Object Browser, or click the Object Browser button on the Standard toolbar to display the Object Browser window (see Figure 4.4).

    

    The first action we want to take in this macro is to maximize the Excel's application window. As in any application, VBA uses the Application object to represent the Excel application, but you need to find the correct property of this object to utilize.

11. Select Excel in the Project/Library drop-down list (see the label in Figure 4.4), type maximize in the Search Text box, and either click the Search button or press the Enter key.

    The Object Browser displays the result of the search (see Figure 4.5) in its Search Results pane (which was collapsed and not visible in Figure 4.4). The constant xlMaximized is a member of the class XlWindowState.

    

12. Press the F7 key to activate the Code window. (Alternatively, click the Code window, choose View ➢ Code, or choose the Code window from the Window menu.)
13. Type application . (in lowercase and including the period) so that the Visual Basic Editor displays the drop-down list, type w to jump to the items beginning with W, and select the WindowState item.
14. Type = to enter the WindowState item in your code and to display the list of constants available for WindowState (see Figure 4.6).

    

15. Select the xlMaximized item and press Enter to insert that property in the code, and move down a line to start writing a new statement.

    The second action for the macro is to open the last file used—file 1 on the recently used files list (this is the list that appears in the Recent Documents list when you click the Recent item in the File tab on the Ribbon).

16. Press the F2 key to activate the Object Browser again.
17. Leave Excel selected in the Project/Library drop-down list, type recent, and either press the Enter key or click the Search button.

    The Object Browser displays the results of the search (see Figure 4.7). The item you need is the RecentFiles property of the Application object. The RecentFiles property returns the RecentFiles collection, an object that knows the information about the files in the recently used files list.

    

18. Press the F7 key to return to the Code window. Type application . and select RecentFiles from the List Properties/Methods drop-down list.
19. Type (1) . to indicate the first item in the RecentFiles collection, and select the Open method from the List Properties/Methods list:

    Application.RecentFiles(1).Open

    That's it. Your procedure should look like this:

    Private Sub Auto_Open()
        Application.WindowState = xlMaximized
        Application.RecentFiles(1).Open
    End Sub

20. Press Alt+Q or choose File ➢ Close And Return To Microsoft Excel to return to Excel.
21. Click the File tab on the Ribbon and choose Save.
22. Click the Hide button in the Window section of the View tab on the Ribbon. This hides PERSONAL.XLSB from view.
23. Open a sample document, type something into one of the cells, save it, and close it.
24. Press Alt+F4 to exit Excel. If you are asked if you want to save the changes you made to the current workbook and your Personal Macro Workbook, choose Yes.
25. Restart Excel.

    Notice how Excel automatically maximizes the application window and opens the most recently used file. If you see an error message, it most likely means that you've renamed or moved the most recently used file. To prevent this problem, you can add some error-trapping code. We'll explore the On Error command thoroughly in Chapter 17, “Debugging Your Code and Handling Errors.”

26. But if you like, you can make the following changes to your Auto_Open macro:

    Private Sub Auto_Open()
    On Error GoTo Problem
        Application.WindowState = xlMaximized
        Application.RecentFiles(1).Open
    Exit Sub
    Problem:
        MsgBox “Error: “ & Application.RecentFiles(1).Path & “ can't be opened."
    End Sub

The Auto_Open name is special. When you name a macro Auto_Open, VBA knows that whatever actions are in the macro code should be executed when Excel starts running. This is one of a handful of special names called Excel's events—things that happen to an object, in this case the Open event of the Excel application. (Notice that an object's methods are actions it can take, such as a print method sending a document to the printer. Conversely, an object's events are things that can happen to it, such as a user clicking a button or opening an application.)

Creating a Procedure for PowerPoint

The procedure you'll create for PowerPoint is short and straightforward, but it can save the user enough effort over the long run to make it worthwhile. It adds a title slide to the active presentation, inserting a canned title that includes the current date and the company's name as the presenter.

Follow these steps to create the procedure:

1. Start PowerPoint. If PowerPoint is already running, close it and restart it.
2. Click to open the Blank Presentation template. (Or if you've suppressed Start Screen, choose File ➢ New ➢ Blank Presentation.)
3. Click Developer ➢ Visual Basic to open the Visual Basic Editor.

   If the Developer option isn't visible on the Ribbon, follow the instructions in the sidebar titled “A Warning about Security” in Chapter 1.

4. Make sure the Visual Basic Editor is set up as described earlier in this chapter.
5. In the Project Explorer window, right-click VBAProject(Presentation1) and choose Insert ➢ Module from the context menu.

   The Visual Basic Editor inserts a new module in the project, displays a Code window containing the code sheet for the module, and expands the project tree in the Project Explorer.

6. Verify that the Visual Basic Editor has entered the Option Explicit statement in the declarations area at the top of the code sheet. If not, go back and complete steps 4 and 5 in the list at the start of this chapter.
7. Press the F4 key to activate (move to) the Properties window.
8. Delete the default name Module 1 and replace it by typing General_Procedures.
9. Press the F7 key or click in the Code window to give it the focus.
10. Below the Option Explicit statement, type the Sub statement for the procedure and press the Enter key:

    Sub Add_Title_Slide

    When you press Enter, the Visual Basic Editor enters the parentheses at the end of the Sub statement, a blank line, and the End Sub statement for you, and places the insertion point on the blank line:

    Sub Add_Title_Slide()
    End Sub

11. Press the Tab key to indent the first line below the Sub statement.

    This is strictly for your benefit. It makes the code a little easier to read.

12. Let's now identify which objects we need for this macro. You'll be working with the active presentation, which is represented by the ActivePresentation object. As you'll see in Part 6 of this book, “Programming the Office Applications”—which is all about objects—there are several ways to get information when programming with objects. For now, let's try searching online help rather than using the Editor's built-in Object Browser.
13. Using Google, search for object model reference powerpoint 2019. (It should be dated 2018 or later, but if you see earlier dates on various pages within this reference material, that's fine.) Now you can locate the details about the application object's ActivePresentation property object, as shown in Figure 4.8.

    

14. Click the Presentation link in “Returns a Presentation object…” near the top, as shown in Figure 4.8.

    This link will take you to the Presentation object's Help screen. We're drilling down in this Help system to find example code and other assistance that will show us how to work with slides and related objects. All this will become much clearer to you in Part 6 of this book. For now, just follow along to get the general idea.

15. Now on the Presentation object's Help page, scroll way down to locate the Slides object in the properties list.
16. Click the Slides link. Then click a second Slides link.

    See the pointing finger in Figure 4.9. Now you see the information about the Slides Collection object, as shown in Figure 4.10. From this screen, you learn two pieces of information: first, that a slide is represented by a Slide object (stored in a Slides collection), and second, that you use the Add method to create a new slide.

    

    

17. Here's a shortcut when writing code. Type the following code line:

    Dim sldTitleSlide As Slide

    It's a declaration for an object variable of the Slide object type, which will be used to represent the slide created in this Sub. Notice that after you type as and a space, the Visual Basic Editor displays the list of available objects.

18. Type s and l to move down the list until you see Slide selected (highlighted) and then press the Enter key to complete the code line and move down to the next line.

    Here's another shortcut. Use a Set statement to assign to the sldTitleSlide object a new slide you create by using the Add method.

19. Type set sld and then press Ctrl+spacebar to make the Editor's Complete Word feature enter sldTitleSlide for you.
20. Then type = activepresentation.slides.add(, using the Visual Basic Editor's assistance, so that the line reads as shown here:

    Set sldTitleSlide = ActivePresentation.Slides.Add(

    When you type the parenthesis, the Auto Quick Info feature displays the syntax for the Add method, as shown in Figure 4.11.

    

21. Type the Index argument, a colon, an equal sign, the value 1 (because the title slide is to be the first slide in the presentation), and a comma:

    Set sldTitleSlide = ActivePresentation.Slides.Add(Index:=1, _
        Layout:=ppLayoutTitle)

22. Break the statement to the next line with a line-continuation character (an underscore preceded by a space).
23. Then type a tab to indent the new line, type the Layout argument, a colon, and an equal sign, and pick the ppLayoutTitle constant from the List Properties/Methods drop-down list, as shown in Figure 4.12.

    

24. Type the parenthesis to end the statement:

    Set sldTitleSlide = ActivePresentation.Slides.Add(Index:=1, _
    Layout:=ppLayoutTitle)

25. Press the Enter key to start a new line, and then press either the Backspace key or Shift+Tab to unindent the new line by one tab stop.
26. You'll be working with the sldTitleSlide from here on, so create a With statement using it, and place the insertion point on the line between the With statement and the End With statement:

    With sldTitleSlide
    End With

    Next, the macro will manipulate the two items on the slide. To make it do so, you need to know the objects that represent them. You could use the Macro Recorder to find the objects, but this time try a more direct method.

27. Place the insertion point on the line within the With statement and type . (a period) to display the List Properties/Methods drop-down list of available properties and methods for the Slide object.

    Sometimes the List Properties/Methods drop-down list is of little help because it displays so many possibly relevant properties and methods that you can't identify the property you need. But if you scan the list in this case, you'll see that the Shapes property (which returns the Shapes collection) is the only promising item for our purposes.

28. Press Ctrl+G, choose View ➢ Immediate, or click the Immediate Window button on the Debug toolbar to display the Immediate window for a bit of testing.
29. Type the following test statement into the Immediate window and press the Enter key to execute this statement:

    ActivePresentation.Slides(1).Shapes(1).Select

    The Immediate window is a sometimes helpful and quick way to test individual lines of code without having to run the entire macro.

30. Now switch to the PowerPoint window to see if the item was, in fact, selected (whether it has a frame drawn around it).
31. Click Developer ➢ Visual Basic or click the View Microsoft PowerPoint button on the Standard toolbar to display the PowerPoint window to verify that VBA has selected the first Shape object on the slide.

    Okay, this is the right object to start with, but now you need to find out how to add text to the shape.

32. Go back to the Code window (click in the Code window or press the F7 key). Press the Backspace key to delete the period, and then type it again to redisplay the list.
33. Type te to jump down to the items in the list whose names start with text.
34. Select the TextFrame item in the list, and then type a period to enter the term and display the next list.
35. Scroll down the list, select the TextRange object, and type a period to enter the term and display the next list.
36. In the next list, select the Text property.
37. Type an equal sign to enter the term.
38. Then type double quotation marks followed by the text to assign to the text property Pollution Update : (with a space after it), double quotation marks, an ampersand, and the date (supplied by the Date function):

    Shapes(1).TextFrame.TextRange.Text = “Pollution Update: “ & Date

39. Assign information to the second Shape in the same way:

    .Shapes(2).TextFrame.TextRange.Text = “JMP Industrials."

    The finished procedure should look like this:

    Sub Add_Title_Slide()
        Dim sldTitleSlide As Slide
        Set sldTitleSlide = ActivePresentation.Slides.Add(Index:=1, _
            Layout:=ppLayoutTitle)
        With sldTitleSlide
            .Shapes(1).TextFrame.TextRange.Text = _
                “Pollution Update: “ & Date
            .Shapes(2).TextFrame.TextRange.Text = _
                “JMP Industrials"
        End With
    End Sub

40. Press F5 to test the procedure. Look at the slides in PowerPoint. There should be a new first slide in the collection of slides on the left.
41. Delete all slides from the presentation: select slides by pressing Shift while clicking a range of slides in the left pane, and then press Delete.
42. If you want, right-click the Quick Access Toolbar in the upper-left corner of PowerPoint's screen, and then choose Customize Quick Access Toolbar. Then add a Quick Access Toolbar button for the Add_Title_Slide macro.
43. Save the presentation under a name such as Procedures.pptm.
44. You might see a warning about personal-information risks. Click OK to close that Be Careful! message box.
45. Create a new presentation; then test the toolbar button or menu item for the procedure.
46. If you see a security warning, read the sidebar titled “A Warning about Security” in Chapter 1.
47. Close the presentation without saving changes.

Creating a Procedure for Access

Access has a long tradition of dissimilarity from the other Office applications, and this applies as well to its implementation of macros. It has no Recorder, for example, nor does it permit you to assign macros to shortcut key combinations.

In addition, Access includes a legacy “Macro Builder,” which you can take a look at by double-clicking the Macro button on the Create tab of the Ribbon. (Note that in Access there is no Developer tab on the Ribbon. Another divergence. You open the Visual Basic Editor from the Database Tools tab.)

The Macro Builder utility has been generally unpopular over the years because the Visual Basic Editor offers far more options, objects, and features. The Builder is for nonprogrammers—a way to create simple macros via lists rather than actual programming. However, the Builder has now and then been somewhat improved over the years, including new provisions for error handling and the ability to embed macros within individual forms.

Additional improvements were made for Access to the point that enough enhancements were added that Microsoft renamed it the Macro Designer! But a dead rose by any other name is still a dead rose. If you're interested in details about the Macro Designer and its curious, some might say simplistic, reliance on repeated If…Then structures, see the sidebar titled “Using the Macro Builder” in Chapter 28, “Understanding the Access Object Model and Key Objects.”

For the reasons I mentioned, you will likely prefer to use the Visual Basic Editor rather than the Builder/Designer for any but the most elementary macros. After all, relying on a list of If queries is not only limiting, it's downright clumsy.

So let's now get a brief taste of how to write a real VBA macro in Access. In this example, you'll write a macro that displays today's date and time:

1. Start Access.
2. Double-click the Blank Desktop Database icon.
3. Click Database Tools ➢ Visual Basic to open the Visual Basic Editor.
4. Right-click the database name in the Project Explorer, and then choose Insert Module to open a new module in the Code window, where you can write macros.
5. In the Code window, type the following macro:

   Sub ShowDate()
   MsgBox ("Hi there! It's: “ & Now)
   End Sub

6. Click anywhere within this code, and then press F5 to execute the macro.

   You should see a message box that displays the current date and time. (Note that you don't type the End Sub. The Editor automatically inserts it for you.)

We'll cover Access macro programming in depth in Chapters 28 “Understanding the Access Object Model and Key Objects,” and 29, “Accessing One Application from Another Application.” Also, you might have noticed that the Editor automatically inserted a line of code at the top: Option Compare Database. This specifies a particular way to go about comparing text strings.

The Bottom Line

• Set up the Visual Basic Editor to create macros. How you arrange the various components of the Visual Basic Editor is your personal choice, but while using this book, it's easiest if you set up the Editor to resemble the way it appears in the book's figures. Besides, this arrangement is quite close to the default layout, which has proven to be the most effective one for the majority of programmers (according to various focus groups and polls) for the decades that Visual Basic has been used.
  • Master It Press a single key to toggle (to display and then hide) the Properties window.
• Create a macro for Word. Using the Help feature in any VBA-enabled application allows you to find code examples that you can copy and paste into your own code.
  • Master It Open the Code window and use Help to find a code example.
• Create a macro for Excel. Certain procedure names are special. In a previous Excel exercise, you added line numbering and gave that procedure a name of your own choice. But some procedure names have a special meaning—they are triggered by an event in Excel itself. They will execute automatically when that event takes place (you don't have to run events by choosing Run from the Macro dialog box or by assigning the macro to a keyboard shortcut or Quick Access Toolbar button). One such event is Excel's Auto_Open procedure.
  • Master It Display a message to the user when Excel first executes.
• Create a macro for PowerPoint. As you type a procedure, the Visual Basic Editor provides you with lists of objects' members (the Auto List Members feature) and with syntax examples, including both required and optional arguments (the Auto Quick Info feature). These tools can be invaluable in guiding you quickly to the correct object and syntax for a given command.
  • Master It Use the Auto List Members and Auto Quick Info features to write a macro that saves a backup copy of the currently active presentation.
• Create a procedure for Access. Although Access includes a variety of macro-related features that are unique (such as its Macro Builder/Designer), its Visual Basic Editor is quite similar to the Visual Basic Editors in the other Office applications.
  • Master It Open the Visual Basic Editor in Access and write a macro that displays today's date using the Date function rather than the Now function. Use the Access Visual Basic Editor Help system to understand the difference between these two functions.