Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 4. Creating Code from Scratch in the Visual Basic Editor

In this chapter, you'll practice creating procedures from scratch in the Visual Basic Editor. The examples walk you through creating a procedure in Word, Excel, and PowerPoint.

For the examples in this book, the Visual Basic Editor should be set up a certain way, and (for good practice) set to require explicit declarations of variables. 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 language. You'll work quickly with VBA elements (such as objects, properties, methods, variables, and constants) that you'll learn about more fully later in this book. Along the way, you'll meet 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 forms of assistance later in this book as well.

In this chapter you will learn to do the following:

Set up the Visual Basic Editor for creating procedures
Create a procedure for Word
Create a procedure for Excel
Create a procedure for PowerPoint
Create a procedure for Access

Setting Up the Visual Basic Editor for Creating the Procedures

You'll find it easiest to follow the instructions in the following procedures if you have the Visual Basic Editor set up in a default configuration (like the layout you see the first time you display the Visual Basic Editor from a VBA host).

The following steps describe how to set up the Visual Basic Editor:

If the Project Explorer isn't displayed, choose View
If the Properties window isn't displayed, choose View
Unless you really prefer things otherwise, dock the Project Explorer in its conventional position at the upper-left corner of the main Visual Basic Editor area. Dock the Properties window below the Project Explorer, again in its default position. (To change docking, choose Tools
Close any Code windows or user form windows that are open. You'll see two X buttons in the upper-right corner of the editor. The top button is colored white on red and closes the entire editor. Instead, click the black X button underneath the red one to close the Code window.
Set the Visual Basic Editor up to require variables to be declared explicitly. The editor will then enforce a rule that you must declare each variable formally before you can use it in the code. Choose Tools
```
Dim txtName As String
```

Creating a Procedure for Word

The procedure you'll create for Word causes the Track Changes feature to toggle (between Strikethrough and Hidden) how deleted text will be displayed. 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 create the toggle behavior.

Follow these steps to record the macro:

Start Word. If Word is already running, exit it and restart it.
Record a macro to get to the object, property, and settings you need. (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 bottom half of the Track Changes button to display three tracking options. Choose Change Tracking Options. When Word displays the Track Changes Options dialog box (see Figure 4.1), select Strikethrough in the Deletions drop-down list, and then click OK to close the Track Changes Options dialog box. (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.)
  Figure 4.1. The Track Changes Options dialog box in Word
6. Repeat the same steps from the previous item (e.) to reopen the Track Changes Options dialog box. Now, select Hidden in the Deletions drop-down list, and again click OK to close the dialog box.
7. Stop recording the macro by clicking the blue recording button in the status bar or by clicking the Stop Recording button on the Developer tab on the Ribbon.

Press Alt+F8 to display the Macros dialog box. Select the 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 = wdByAuthor
9.              .DeletedTextMark = wdDeletedTextMarkStrikeThrough
10.             .DeletedTextColor = wdByAuthor
11.             .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
12.             .RevisedPropertiesColor = wdByAuthor
13.             .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
14.             .RevisedLinesColor = wdAuto
15.             .CommentsColor = wdByAuthor
16.             .RevisionsBalloonPrintOrientation = wdBalloonPrintOrientationPreserve
17.         End With
18.         ActiveWindow.View.RevisionsMode = wdMixedRevisions
19.         With Options
20.             .MoveFromTextMark = wdMoveFromTextMarkDoubleStrikeThrough
21.             .MoveFromTextColor = wdGreen
22.             .MoveToTextMark = wdMoveToTextMarkDoubleUnderline
23.             .MoveToTextColor = wdGreen
24.             .InsertedCellColor = wdCellColorLightBlue
25.             .MergedCellColor = wdCellColorLightYellow
26.             .DeletedCellColor = wdCellColorPink
27.             .SplitCellColor = wdCellColorLightOrange
28.         End With
29.         With ActiveDocument
30.             .TrackMoves = True
31.             .TrackFormatting = True
32.         End With
33.         With Options
34.             .InsertedTextMark = wdInsertedTextMarkUnderline
35.             .InsertedTextColor = wdByAuthor
36.             .DeletedTextMark = wdDeletedTextMarkHidden
37.             .DeletedTextColor = wdByAuthor
38.             .RevisedPropertiesMark = wdRevisedPropertiesMarkNone
39.             .RevisedPropertiesColor = wdByAuthor
40.             .RevisedLinesMark = wdRevisedLinesMarkOutsideBorder
41.             .RevisedLinesColor = wdAuto
42.             .CommentsColor = wdByAuthor

43.             .RevisionsBalloonPrintOrientation = wdBalloonPrintOrientationPreserve
44.         End With
45.         ActiveWindow.View.RevisionsMode = wdMixedRevisions
46.         With Options
47.             .MoveFromTextMark = wdMoveFromTextMarkDoubleStrikeThrough
48.             .MoveFromTextColor = wdGreen
49.             .MoveToTextMark = wdMoveToTextMarkDoubleUnderline
50.             .MoveToTextColor = wdGreen
51.             .InsertedCellColor = wdCellColorLightBlue
52.             .MergedCellColor = wdCellColorLightYellow
53.             .DeletedCellColor = wdCellColorPink
54.             .SplitCellColor = wdCellColorLightOrange
55.         End With
56.         With ActiveDocument
57.             .TrackMoves = True
58.             .TrackFormatting = True
59.         End With
60.     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 options in the Track Changes Options dialog box that you visited. Look over the code briefly to see the many settings that were recorded from the options inside the dialog box displayed in Figure 4.1.
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 36 in particular; these are key. Line 36 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).
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.
Make sure the Visual Basic Editor is set up as described in the section "Setting Up the Visual Basic Editor for Creating the Procedures," earlier in this chapter.
In the Project Explorer window, right-click anywhere in the Normal item and choose Insert
Press the F4 key to activate the Properties window for the new module. (By activate I mean give the focus—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. (Confusingly, the property's name includes the parentheses.)
Type the name for the new module in the Properties window. This example uses the name Procedures_to_Keep_1.
Press the F7 key or click in the Code window to activate it.
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, type that statement now.
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
```
Press the Tab key to indent the first line below the Sub statement.
Type if options. (in lowercase and including the period) to display the List Properties/Methods drop-down list.
Type down through the list (type d, e, and then l) and use the ↓ key, or simply scroll with the ↓ key or the mouse, to select the DeletedTextMark entry.
Now just type = (the equal sign). The Visual Basic Editor enters the DeletedTextMark keyword 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.2).
Select the wdDeletedTextMarkHidden item and enter it into your code by pressing the Tab key or by double-clicking it.
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—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.
Enter Options.DeletedTextMark=wdDeletedTextMarkStrikethrough, using the assistance that the Visual Basic Editor's features offer you, and then press Enter.
Press the Backspace key or Shift+Tab to unindent the new line of code by one tab stop.
Figure 4.2. The Visual Basic Editor's List Properties/Methods list displays the constants available for the DeletedTextMark property.

Type the ElseIf keyword, and then enter the rest of the procedure as follows:

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

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

Press Alt+F11 to switch to Word, and then type in a line or two of text.
Arrange the Word window and the Visual Basic Editor window side by side. 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. Delete a word in your text. Notice whether it is struck through or is simply hidden. 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. 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.
Click the Save button on the Standard toolbar in the Visual Basic Editor.

Note that you can 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. Although formal (professional) programmers learn a set of "best practices," if you're just writing VBA for your own personal use—go ahead and code however you wish. Whatever works.

Creating a Procedure for Excel

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

Write a macro that executes when an application first starts up
Work with events
Use the Object Browser to find the objects, methods, and properties you need

Follow these steps to create the procedure:

Start Excel if it's not already running.
Press Alt+Tab to cycle through your workbooks to locate PERSONAL.XLSB. If your Personal Macro Workbook is currently hidden, click the Unhide button in the Window section of the View tab on the Ribbon. Select PERSONAL.XLSB in the Unhide Workbook list box, and then click the OK button.
Press Alt+F11 to open the Visual Basic Editor.
Make sure the Visual Basic Editor is set up as described in the section "Setting Up the Visual Basic Editor for Creating the Procedures" earlier in this chapter.
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.
Expand the Microsoft Excel Objects folder.
Double-click the ThisWorkbook item to open its code sheet in a Code window. The ThisWorkbook object represents the workbook.
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, type the statement now.
In the Object drop-down list (it currently says General) at the upper-left corner of the Code window, select Workbook. The Visual Basic Editor automatically creates the stub of an Open event for the Workbook object and places the insertion point on the blank line, like this:
```
Private Sub Workbook_Open()
End Sub
```
Macros Have Scope
The Private keyword limits the scope of a macro—the area in which it can operate. Private scope makes the macro available to all procedures in the module that contains it but not to procedures in other modules. Chapter 6 explains scope in more detail.
Open the Object Browser. Press the F2 key, choose View
Figure 4.3. Use the Object Browser to find the objects, methods, and properties you need for a procedure.
The first action you want to take in the macro is to maximize the 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 work with. Select Excel in the Project/Library drop-down list (see the label in Figure 4.3), 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.4) in its Search Results pane (which was collapsed and not visible in Figure 4.3). The constant xlMaximized is a member of the class XlWindowState.
Figure 4.4. The result of the search for "maximize" in the Object Browser
Press the F7 key to activate the Code window. (Alternatively, click the Code window, choose View
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.
Type = to enter the WindowState item in your code and to display the list of constants available for WindowState (see Figure 4.5).
Figure 4.5. Use the list of constants to enter the constant quickly and easily.
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). Press the F2 key to activate the Object Browser again.
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.6). 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.
Figure 4.6. The result of the search for "recent" in the Object Browser
Press the F7 key to return to the Code window. Type application. and select RecentFiles from the List Properties/Methods drop-down list. Then 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 Workbook_Open()

    Application.WindowState = xlMaximized
    Application.RecentFiles(1).Open

End Sub

Press Alt+Q or choose File
Click the File tab on the Ribbon and choose Save.
Click the Hide button in the Window section of the View tab on the Ribbon. This hides PERSONAL.XLSB from view.
Open a sample document, make a change to it, save it, and close it.
Press Alt+F4 to exit Excel. If you are asked if you want to save the changes you made to the Personal Macro Workbook, click Yes.
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, but if you wish, you can make the following changes to your Workbook_Open macro:
```
Private Sub Workbook_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 Workbook_Open name is special. When you name a macro Workbook_Open, VBA knows that whatever actions are in the macro code should be executed when any workbook is opened. This handful of special names are events—things that happen to an object, such as the Open event of a workbook.

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:

Start PowerPoint. If PowerPoint is already running, close it and restart it. If PowerPoint creates a default presentation on startup, close the presentation (click the File tab and choose Close).
Create a new presentation based on the Contemporary Photo Album template (click the File tab and choose New and Sample Templates). Make sure the default slide on the presentation has the Title Slide layout by right-clicking the slide, then choosing Layout
Press Alt+F11 to open the Visual Basic Editor.
Make sure the Visual Basic Editor is set up as described in the section "Setting Up the Visual Basic Editor for Creating the Procedures" earlier in this chapter.
In the Project Explorer window, right-click anywhere in the VBAProject(Presentation1) item and choose Insert
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, type the statement now.
Press the F4 key to activate the Properties window.
Replace the default name Module 1 by typing (in the Properties window): General_Procedures.
Press the F7 key or click in the Code window to activate it.
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
```
Press the Tab key to indent the first line below the Sub statement.
Now identify the objects you need by using the Help system. You'll be working with the active presentation, which is represented by the ActivePresentation object. As you'll see in Part —which is all about objects—there are several ways to get information when programming with objects. For now, try the MSDN (Microsoft Developer Network) online-help approach. In your browser type www.msdn.com. In the Bing search field that appears on the MSDN home page, type PowerPoint ActivePresentation, then click the search icon (the magnifying glass). A list of links appears. Click the one that looks most relevant (often one containing the words Developer Reference). You should then see the details about the ActivePresentation object, as shown in Figure 4.7.
Figure 4.7. The Active Presentation Property screen
Click the Presentation link in "Returns a Presentation object..." near the top as shown in Figure 4.7. 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. For now, just follow along to get the general idea.
Now in the Presentation object's Help page, click the Presentation Object Members (scroll to find it near the bottom) link, and then scroll to locate the Slides object in the list. Click the Slides link (see Figure 4.8), then in the new Web page that appears, click a Slides link again (it's near the top where it says "Returns a Slides collection..."). Now you see the information about the Slides Collection Object, as shown in Figure 4.9.
From this screen, take 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.
Figure 4.8. Select the Slides object from the list.
Figure 4.9. The Slides Collection Object Help screen
Type a declaration for an object variable of the Slide object type to represent the slide the procedure creates. Notice that after you type as and a space, the Visual Basic Editor displays the list of available keywords. Type down through the list (type s and l) until you have selected Slide, and then press the Enter key to complete the term and start a new line of code:
```
Dim sldTitleSlide As Slide
```
Use a Set statement to assign to the sldTitleSlide object a new slide you create by using the Add method. Type set sld and then press Ctrl+spacebar to make the Editor's Complete Word feature enter sldTitleSlide for you. 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 for you the syntax for the Add method, as shown in Figure 4.10.
Figure 4.10. The Auto Quick Info feature displays the syntax for the Add method when you type the parenthesis after the Add method.
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,
```
Choosing between Labeled and Implied Argument Lists
When a method uses arguments, as the Add method does here, you can choose between specifying the argument names or omitting them and letting VBA infer the arguments from the order of the values or constants. For example, in this case, you can specify either Add(Index:=1, Layout:=ppLayoutTitle) or Add(1, ppLayoutTitle). The latter is more concise and easier to type in, but the former is much clearer to read.
Break the statement to the next line with a line-continuation character (an underscore preceded by a space). 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.11.
Figure 4.11. Choose the ppLayoutTitle constant for the Layout argument.

Type the parenthesis to end the statement:

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

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.
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 out, but this time, try a more direct method: 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.
Press Ctrl+G, choose View
Type the following exploratory 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 quick way to test individual lines of code without having to run the entire macro.) Now switch to PowerPoint's window to see if the item was, in fact, selected (whether it has a frame drawn around it). Press Alt+F11 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. 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. Type te to jump down to the items in the list whose names start with text. Select the TextFrame item in the list, and then type a period to enter the term and display the next list. Scroll down the list, select the TextRange object, and type a period to enter the term and display the next list. In the next list, select the Text property. Type an equal sign to enter the term. 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
```

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

Press F5 to test the procedure. Look at the slides in PowerPoint. There should be a new first slide in the collection. Then delete all slides from the presentation (select slides by pressing Shift while clicking a range of slides in the left pane, then press Delete).
If you wish, right-click on the Quick Access Toolbar in the upper left corner of PowerPoint's screen, then choose Customize Quick Access Toolbar. Then add a Quick Access Toolbar button for the Add_Title_Slide macro.
Save the presentation under a name such as Procedures.pptm.
Create a new presentation; then test the toolbar button or menu item for the procedure. If you see a security warning, read the section titled "A Warning about Security" in Chapter 1. Close the presentation without saving changes.

Creating a Procedure for Access

Access has a long tradition of independence from the other Office applications, and this applies as well to its implementation of macros. It has no recorder, does not permit you to assign macros to shortcut key combinations, and will not be part of the new Office Web Applications Suite.

In addition, Access includes a legacy "Macro Builder," which you can take a look at by clicking the Macro button on the Create tab of the Ribbon. (Note that in Access there is no Developer tab on the Ribbon. You can open the Visual Basic Editor from the Database Tools tab or press Alt+F11.)

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 non-programmers—a way to create simple macros via lists rather than actual programming. However, the Builder was somewhat improved in Access 2007, including provisions for error handling and the ability to embed macros within individual forms. And additional improvements were made for Access 2010, enough improvements that Microsoft renamed it the Macro Designer. But a rose by any other name is still a rose. If you're interested in details about this newly revamped Macro Designer and its curious, some might say simplistic, reliance on repeated If...Then structures, see the sidebar titled "The Revamped Macro Builder" in Chapter 28.

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 retro.

Let's get a feel for writing VBA macros in Access. In this example, you'll write a macro that displays today's date and time:

Start Access.
Double-click the Blank Database button.
Press Alt+F11 to open the Visual Basic Editor.
Right-click the database name in the Project Explorer, then choose Insert Module to open a new module in the Code window where you can write macros.

In the Code window, type the following macro:

Sub ShowDate()

MsgBox ("It is: " & Now)

End Sub

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; Access automatically inserts it for you.)

We'll cover Access macro programming in depth in Chapter 6 and Chapter 8.

The Bottom Line

Set up the Visual Basic Editor for creating procedures

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 employed an editor.

Master It: Press a single key to display, then hide, the Properties window.

Create a procedure 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 procedure for Excel

Certain procedure names are special. In the previous exercise, you added line numbering and gave that procedure a name of your own choice. But some procedure names have a special meaning—they represent an event. 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 Workbook_Open procedure.

Master It: Display a message to the user when a workbook is opened in Excel.

Create a procedure 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 2007 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for 4. Creating Code from Scratch in the Visual Basic Editor

Create new playlist

Sign In

Sign Up