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
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
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:
Click the Developer tab on the Ribbon; then click the Record Macro button in the Code section to display the Record Macro dialog box.
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.
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.
Click the OK button to start recording the macro.
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.)
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.
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.
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.
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
Open the Object Browser. Press the F2 key, choose View
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
.
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).
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.
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 SubProblem:
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.
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.
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.
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.
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,
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.
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.
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.
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.
Press a single key to display, then hide, the Properties window.
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.
Open the Code window and use Help to find a code example.
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.
Display a message to the user when a workbook is opened in Excel.
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.
Use the Auto List Members and Auto Quick Info features to write a macro that saves a backup copy of the currently active presentation.
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.
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.
3.137.178.9