Turn on the macro recorder in Word and create a macro that moves the insertion cursor up three lines. Then turn off the macro recorder and view the code in the Visual Basic Editor.

Click the Developer tab on the Ribbon. Click the Record Macro button in the Developer tab, then give the macro a name, such as temporary and, if necessary, change the Store Macros In target to All Documents (Normal.dotm). Click OK to close the Record Macro dialog. This begins the recording process. Press the up arrow key three times. That's what you want to record.

Now click the Stop Recording button in the Developer tab or on the status bar at the bottom of the screen. Press Alt+F11 to open the Visual Basic Editor. Open Normal, then Modules, and double-click NewMacros in the left (Project) pane of the Editor. Scroll the code window until you locate your new macro (it will have been added to the bottom of the collection of macros. The macro code should look something like this:

Sub temporary()
'
' temporary Macro
'
'
    Selection.MoveUp Unit:=wdLine, Count:=3
End Sub

Assign an existing macro to a new Quick Access Toolbar button.

Right-click the Quick Access Toolbar. Choose Customize Quick Access Toolbar. In the Choose Commands From list, select Macros. Click a macro's name to select it in the list. Click the Add button to insert this macro's name in the Customize Quick Access Toolbar list. Click OK to close the dialog. Now you see a new button on the Toolbar that when clicked launches your macro.

Execute a macro from within the Visual Basic Editor.

Open the Visual Basic Editor by pressing Alt+F11. Click to put the insertion cursor anywhere in the code within one of your macros in the right pane (between the Sub and End Sub lines of code). Press F5 to execute that macro.

Temporarily remove a macro, then restore it, using the Visual Basic Editor.

Press Alt+F11 to open the Visual Basic Editor. The code within this editor is just text, similar to Notepad. Locate a macro within the Editor. Each macro is the code that starts with Sub and concludes with End Sub. So drag your mouse to select the entire macro, including its Sub...End Sub lines. (Note that the Editor displays a horizontal line between each macro, so you can easily see where each macro's code begins and ends.) Press Ctrl+C to copy the macro's code. Then press Delete to delete the macro. Close the Visual Basic Editor and press Alt+F8 to open the Macros dialog and see your list of macros. Scroll this list and notice that the macro you deleted in the Editor no longer exists.

Now close the Macros dialog. Restore the macro by pressing Alt+F11 to reopen the Editor. Then click on a blank line at the very top of the right pane where the various macros' code is displayed. However, you want to put the blinking insertion cursor outside of any other macro's Sub...End Sub code area. Finally, press Ctrl+V to paste the macro you previously deleted. It's restored. Remember, this Visual Basic Editor merely accepts ordinary, plain text for its source code—so you can freely cut, copy, and paste code. In fact, from this book's website you can copy and paste all the code examples from the book at www.sybex.com/go/masteringvba2010.

Open the Visual Basic Editor in Word and create a macro.

Press Alt+F11.

Open the Visual Basic Editor and display a particular macro in the Code window.

Press Alt+F8 to open the Macros dialog box, click the name of the macro you want to work with, then click the Edit button.

Switch between folder and contents view in the Project Explorer.

Click the icon (a picture of a folder) on the right side just under the Project Explorer's title bar.

Lock a project so others can't modify or even read its contents.

Right-click the project's name in the Project Explorer to open the shortcut menu. Choose <ProjectName> Properties. Click the Protection tab and select the Lock Project For Viewing text box. In the Password To View Project Properties group box, enter a password for the project in the Password text box, and then enter the same password in the Confirm Password text box. Click the OK button and close the project.

Undock the Properties window and change its size.

Double-click the title bar of the Properties window to undock it. Position your mouse pointer in the lower-right corner until the pointer changes to a double arrow. Then drag the window to resize it. Restore the Properties window to its default docked position by double-clicking its title bar again.

Open a macro; then step through it to see if anything goes wrong.

Press Alt+F8 to open the Macros dialog box. Select the name of the macro that you want to test, and then click the Step Into button. The Visual Basic Editor opens and the insertion cursor is located within the chosen macro, thereby making it the currently active one (the one with which the editor's features—such as the Step tool—will work). The first line is highlighted, indicating that it is the next line that will execute. Press F8 to execute the first line, then press F8 repeatedly to step down through each line of code. See if any problems occur—either problems you observe in the behavior of the macro in your application or problems that VBA notifies you of by displaying an error message box.

Set a breakpoint in, and add a comment to, a macro.

Set a breakpoint by clicking in the gray margin indicator bar to the left of a line of code where you want to halt execution. The line of code on which you set a breakpoint is shaded brown by default. You can set as many breakpoints as you wish. Now type in a line such as 'The following With block describes the format for this new paragraph. Because you've started this line with a single-quote symbol, it will be ignored when the editor executes the macro.

With the Visual Basic Editor open, choose a macro and modify it.

Click a line between the Sub and End Sub lines that envelope the macro you want to modify. This puts the insertion cursor where you want it. Now simply type in whatever adjustments you want to make to the code.

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

Press F4 to display the Properties window. Click the close button to dismiss it.

Open the Code window and use Help to find a code example.

Press F7 to open the Code window, and then press F1 to open Help. Click the Word Object Model Reference link. Scroll down until you see the Line Numbering Object link. Click it, then click the Line Numbering Object link in this newly displayed information. You'll find a code example that adds line numbers to the active document. Select and copy this code, then paste it into the Visual Basic Editor. Note that many code examples are not full procedures but merely snippets of code, so it's up to you to add the Sub...End Sub to turn them into actual procedures. Your final procedure in the Visual Basic Editor should look like this:

Sub AddLines()

With ActiveDocument.Sections(1).PageSetup.LineNumbering
    .Active = True
    .CountBy = 5
    .RestartMode = wdRestartPage
End With


End Sub

I named it AddLines; name it whatever you wish. But keep in mind that to be able to execute it—for it to be a formal macro—you must include the Sub...End Sub.

Press F5 to try it out, and then look at the document and see the line numbers.

Remove the line numbers from the document by clicking the Line Numbers option in the Page Setup section of the Page Layout tab on the Ribbon and choosing None.

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

Press F7 to open the Code window in Excel's Visual Basic Editor. Locate VBAProject (theprojectname) in the Project Explorer, double-click it to open its contents, and then double-click ThisWorkbook under the project. An empty Sub (an open event) appears in the Code window. Type the code in boldface into the procedure:

Private Sub Workbook_Open()
    MsgBox "Opened"
End Sub

Close and then reopen the workbook to see the message automatically displayed.

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 new presentation based on a template of your choosing. Press Alt+F11 to open the Visual Basic Editor. Choose Tools

Right-click the name of the presentation in the Project Explorer, then choose Insert Module so you'll have a place to write a macro.

Type the following line of code to create a macro:

Sub SaveTemp()

When you press the Enter key, the Visual Basic Editor automatically adds End Sub.

Now type Application. in the macro. As soon as you press the period (.), a list of properties and methods of the Application object is displayed. Choose ActivePresentation. Again, when you press the period key, a list of the ActivePresentation object's members appears. Choose SaveCopyAs. Press the spacebar to insert a space after SaveCopyAs; you'll see that this method has only one required argument: a filename string (meaning you must provide a literal filename within quotes or a string variable or constant). In this case, just type "temporary", and then press the F5 key to execute the macro. You didn't specify a path, so you can find your file in your My Documents folder, where it is saved by default.

Your macro should look like this:

Sub SaveTemp()

Application.ActivePresentation.SaveCopyAs "temporary"

End Sub

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.

In Chapter 4, you wrote a macro in Access that displays today's date and time. Here you will display the date only.

Name the two types of procedures used in VBA (and indeed in most computer languages), and describe the difference between them.

A function always returns a value after it finishes executing. For example, you can display a message box, and when the user clicks a button to close that box, the value returned represents which button the user clicked. When the statement that called (invoked) the MsgBox function gets that value, it can respond in whatever way the programmer finds appropriate. Often it's something like this: If the user clicked OK, then check the spelling, or if the user clicked Cancel, then close the document.

A subprocedure (or subroutine) does not return a value. It does a job, then quits without sending back any information to the code or action that triggered it. Events are always subprocedures.

Write a subprocedure in the Visual Basic Editor that displays a message to the user. Then execute that subprocedure.

In the Visual Basic Editor Code window, type code similar to this:

Sub showmessage()

MsgBox "Hi, user."

End Sub

Execute this code by clicking within the subprocedure to position the insertion point there and then pressing F5.

Open the Immediate window, type in a line of code, and then execute that line.

Press Ctrl+G to open the Immediate window, then type a line of VBA code. Press the Enter key when you've finished executing that statement.

Look up the Document object in the Visual Basic Editor's Help system; then look at its methods.

With the Visual Basic Editor the active window, choose Help

Maximize the Help dialog box so you can see the large lists of objects and members. Scroll down until you see the Document object link. Click that link, and then click the link Document Object Members. As you scroll down, you'll see the many methods, properties, and events for this object. Click any of them to get a description, syntax, and, usually, a helpful code example you can cut and paste into your Code window.

Explicitly declare a variable named CustomersAge.

This code explicitly declares a variable:

Dim CustomersAge

If you decided to declare the variable as an Integer type, it would look like this:

Dim CustomersAge As Integer

This variable name cannot be used for two reasons. Fix it so it is a legitimate variable name:

Dim 1Turn! as Integer

Dim Turn as Integer

You can't begin a variable name with a digit, nor can you use an exclamation point anywhere in the name.

Create a variable named AnnualSales that will be available to any procedure within its own module but not to other modules.

Use the Private command to declare a module-level variable. It will be accessible from any procedure in its module but not from other modules in its project. Here is an example:

Private AnnualSales As Integer

Define a string constant using the Dim command. Name your constant FirstPrez, and assign it the value George Washington.

This code line defines a constant, and assigns a value to it:

Const FirstPrez As String = "George Washington"

In the Project Explorer, click the ThisDocument object in the Normal project to select it. Then locate the JustificationMode property in the Properties window, and open its list of enumerated values.

You'll see the drop-down list of enumerated values for the JustificationMode property, as illustrated in the following screen shot.

What is the difference between an array and an ordinary variable?

An ordinary variable (formally called a scalar variable) can contain only a single value. However, an array can contain multiple values, each identified by its own index number.

There are four keywords that can be used to declare arrays. Name at least three of them.

Arrays can be declared using the same keywords that are employed to declare ordinary variables: Dim, Private, Public, and Static.

Redimensioning an array with the ReDim statement causes you to lose any values that are currently in that array. However, you can preserve these values using a special keyword. What is it?

To preserve values when redimensioning an array, use the Preserve command, like this:

ReDim Preserve arrTestArray(5)

Write a line of code that erases an array named arrMyArray.

Use the Erase statement with the name of an array you want to erase. The following statement erases the contents of the fixed-size array named arrMyArray:

Erase arrMyArray

Which built-in function can you use in VBA to find out whether a variable is an array or an ordinary, single-value variable?

Use the IsArray function with the variable's name to see if a variable is an array. For example, the following statement checks the variable MyVariable to see if it's an array:

If IsArray(MyVariable) = True Then

Name a popular, understandable, but relatively inefficient sorting technique.

The bubble sort is easy to visualize but relatively inefficient.

Name two common ways to search an array.

You can use either the simple linear search or the binary search, which requires that an array be sorted first.

By using creatable objects, you can often omit the Application object when referencing it in code. What are creatable objects?

Because you'd have to go through the Application object to get to pretty much anything in the application, most applications include a number of creatable objects—objects that you can access without referring explicitly to the Application object. These creatable objects are usually the most-used objects for the application, and by using them, you can access most of the other objects without having to refer to the Application object. For example, Excel exposes the Workbooks collection as a creatable object, so you can use the following statement, which doesn't use the Application object:

Workbooks(1).Sheets(1).Range("A1").Select

Are collections objects? Do they have methods and properties?

Yes, collections are themselves objects—in the same sense that a flower vase contains a group of flower objects but the vase, too, is an object. A collection can have its own properties and methods, though collections usually have fewer properties and methods than other objects.

How do you employ Auto List Members to find out which properties and methods are available for Word's Document object?

Type Document. in the Code window, and as soon as you type the period, a list of the Document object's members appears.

What keywords do you use to declare an Object variable?

The same keywords are used to declare Object variables as you use for any other variable. To create an Object variable, declare it using a Dim, Private, or Public statement. For example, the following statement declares the Object variable objMyObject:

Dim objMyObject As Object

However, you assign a value to an ordinary variable by using the = sign:

strMyString = "Harry"

But you assign an object to an Object variable using the Set command in addition to the = sign, like this:

Dim wksSheet1 As Worksheet
Set wksSheet1 = ActiveWorkbook

A function is quite similar to a subroutine, but there is a significant difference. What is it?

Subroutines don't return a value; functions do.

You can combine multiple functions in a single line of code. The MsgBox function displays a message box containing whatever data you request. The only required argument for this function is the prompt. The Now function returns the current date and time. Write a line of code that calls the MsgBox function and uses the Now function as its argument.

MsgBox Now

The MsgBox function displays a message box, which, in this case, is the date and time returned by the Now function.

What built-in function is used quite often to display information in a dialog box to the user while a procedure runs?

Both the MsgBox and InputBox functions are used to display information to the user in a dialog box.

What built-in function would you use to convert a string such as "12" (which, in reality, is two text characters, the digits 1 and 2) into an integer data type, the actual number 12, that you can manipulate mathematically.

The built-in function CInt transforms other data types into an Integer type. Here's an example:

intMyVar = CInt(varMyInput)

Which built-in function would you use to remove any leading and trailing space characters from a string. For example, you want to turn

"this         "

into

"this"

Use the Trim function.

Describe the difference between passing data by reference and passing data by value?

The memory address of the actual value is passed to the function when passed the data is by reference. This means that the value can be changed by the function. When the data is passed by value, a copy of the data is sent to the function, leaving the original data unmodifiable by the called function. By reference is the default.

Create a function that displays the current year in a message box. This function will require no arguments, nor will it return any value.

This function displays the current year:

Function ShowYear()

 MsgBox (Year(Now))

End Function

Write a function that displays the number of hyperlinks in the currently active document. Use Word's Hyperlinks collection to get this information.

This function displays the number of hyperlinks in a document:

Function FindHyperCount()

    MsgBox (ActiveDocument.Hyperlinks.Count)

End Function

Using the Sheets collection of Excel's ActiveWorkbook object, write a function that displays the number of sheets in the current workbook.

This function displays the number of sheets in a workbook:

Function SheetsCount()

MsgBox (ActiveWorkbook.Sheets.Count)

End Function

Write a function that returns how many slides are on a presentation. Pass the ActivePresentation object as an argument to this function; then you display the number of slides the presentation contains. Call this function from a subroutine.

This function—called by a subroutine—displays the number of slides in a presentation:

Option Explicit

Function CountSlides(objPresentation As Presentation) As Integer

    CountSlides = objPresentation.Slides.Count
End Function

Sub SeeNumber()

    MsgBox (CountSlides(ActivePresentation))
End Sub

Notice that your CountSlides function is called from within the SeeNumber sub. The ActivePresentation object is passed as an argument. The CountSlides function's argument list includes a variable of the Presentation type. And the entire function is defined as an Integer type, meaning it will pass back integer data to the caller. CountSlides is assigned the integer value provided by the Count method and thus returns this value to the calling subroutine.

Write a function that closes Access by using the DoCmd object's Quit method. Ensure that all data is saved by employing the acQuitSaveAll constant as an argument for the Quit method.

This function closes Access with the DoCmd object's Quit method:

Function QuitApp()

DoCmd.Quit (acQuitSaveAll)

End Function

Write a line of code that uses a less than comparison to test whether a variable named Surplus is less than 1200.

Here's an example of the less than comparison operator:

If Surplus < 1200 Then

What symbol do you use to determine if VariableA is lower in the alphabet than VariableB?

You use the less than symbol <, like this:

If VariableA < VariableB Then

Name two of the most commonly used logical operators.

The most often used logical operators are And, Or, and Not. Tests can be combined using these operators, like this:

If A < B AND C = F Then

Write an If... Else... End If block of code that displays two message boxes. If the temperature (the variable Temp) is greater than 80, tell the user that it's hot outside. Otherwise, tell the user that it's not that hot.

Your code may vary somewhat from this, but see if you've followed the basic structure:

Sub tempShow()

  Dim Temp As Integer

Temp = 66

  If Temp > 80 Then
    MsgBox "Hey, it's hot outside!"
  Else
    MsgBox "It's not that hot."
  End If

End Sub

When should you use a Select Case structure?

Select Case is often more readable than a lengthy, complex If... ElseIf... ElseIf... multiple-test decision-making block.

Use the Select Case statement when the decision you need to make in the code depends on one variable or expression that has more than two or three different values that you need to evaluate.

What is the alternative to looping if you are carrying out repetitive tasks in a macro?

You can copy the repeated code, then paste it into the Visual Basic Editor as many times as you want to repeat the task. Programmers, however, frown on repeated code because they consider it a redundancy, which it often is. If some behavior needs to be repeated, you can almost always employ some form of loop structure.

Write a For...Next loop that counts up to 100, but use the Step command to increment by twos.

This code increments its counter variable (i) by twos:

For i = 1 To 100 Step 2

Next

There are two types of Do... loops. Do While... Loop and Do Until... Loop loops test a condition before performing any action. What is the other type?

The second type of Do... loop includes loops that perform an action before testing a condition. Do... Loop While and Do... Loop Until fall into this category.

Think of a programming task where nested loops would be useful.

Nested loops would come in handy if you're accessing a multidimensional array or a table of data, for example. You could use one loop to search through each row in the table and another loop to search through all the columns. The example of nested loops given in this chapter is that you need to create a number of folders, each of which contains a number of subfolders. Structurally, this subfolders-within-folders example resembles a multidimensional array or a table of data.

How do you avoid creating an infinite loop?

Be sure that it is possible for your loop to terminate at some point. Ensure that a condition will occur that ends the looping.

Write a small sub in the Visual Basic Editor that displays the current date and time in the status bar.

This procedure shows how to display information in the status bar.

Sub Experimentation_Zone()

Application.StatusBar = Now

End Sub

Write a small sub in the Visual Basic Editor that displays the current date and time using a message box.

Here's how to display information in a message box.

Sub Experimentation_Zone()

MsgBox Now

End Sub

Write a small sub in the Visual Basic Editor that asks users to type in their name. Use the InStr function to see if there are any space characters in the returned string. If not, it means either they are Madonna or they have typed in only one name—so display a second input box telling them to provide both their first and last names.

You can handle this several ways, but this example code uses a Do...Loop Until structure to repeatedly display an input box until the user types in at least two words:

Sub Get_Name()

Dim response As String

response = InputBox("Please type in your full name:", _
   "Enter Name")

If Not InStr(response, " ") Then 'found no space character

    Do

        response = InputBox _
 ("You entered only one name. Please type in your full name:", _
 "Enter First and Last Names Please")

    Loop Until InStr(response, " ")

End If



End Sub

In addition to the limitations on the amount of information you can retrieve from the user, name the other major limitation of message and input boxes.

You are limited in the formatting and the amount of information you can display to the user.

Which VBA statement would you use to stop a macro from continuing execution?

The End command halts execution.

How do you switch between the form design window (sometimes called the object window) and the Code window in the Visual Basic Editor?

The easiest way is to press F7 to display the Code window and press Shift+F7 to display the design window. However, you can also use the View menu, or double-click the module name in the Project Explorer (to switch to design mode), or double-click the form or one of its controls (to switch to the Code window).

How do you add a command button to a custom dialog box?

If the Visual Basic Editor Toolbox isn't visible, click the form. Or you can choose View

Create a small custom dialog box that displays a message in a label control saying, "Would you like to know the current date and time?" Put an OK button and a Cancel button on this form. Write code that simply ends the procedure if the user presses the Cancel button but that displays the date and time in the label if the user clicks the OK button. If the user clicks OK a second time, end the procedure.

After you've placed the label with its Caption property set to "Would you like to know the current date and time?" and also put two appropriately captioned buttons on the form, double-click the Cancel button to open the code window. Name the label lblShowTime.

In the Cancel button's Click procedure, type End. Now use the Object drop-down list (on the top left of the Code window) to select the OK button.

In the OK button's Click procedure, type the code shown here:

1.  Private Sub btnCancel_Click()
2.      End
3.  End Sub
4.
5.  Private Sub btnOK_Click()
6.
7.      If Label1.Caption = "Would you like to see the current date
       and time?" Then
8.          Label1.Caption = Now
9.      Else
10.         End
11.     End If
12.
13. End Sub

Here's how the code works:

There are other ways to test whether the user has clicked OK twice. You could create a Static (or local, or procedure-scope variable) a Boolean variable type that is set to True the first time the OK button is clicked. Line 7 would then test the value of this variable in the following way:

If blnToggle = True Then

Create a new dialog box that contains three option buttons captioned Small, Medium, and Large and named optSmall, optMedium, and optLarge. Write code in each option button's Click procedure to change the button's caption to boldface when the button is clicked.

The following code shows how to employ the Font object's Bold property to turn boldface on and off in a caption on a dialog box:

1.  Private Sub optSmall_Click()
2.      optSmall.Font.Bold = True
3.      optMedium.Font.Bold = False
4.      optLarge.Font.Bold = False
5.  End Sub
6.
7.  Private Sub optMedium_Click()
8.      optSmall.Font.Bold = False
9.      optMedium.Font.Bold = True
10.     optLarge.Font.Bold = False
11. End Sub
12.
13. Private Sub optLarge_Click()
14.     optSmall.Font.Bold = False
15.     optMedium.Font.Bold = False
16.     optLarge.Font.Bold = True
17. End Sub

You could write this code a different way by creating a function that accepted small, medium, or large as an argument and then used a Select Case structure to make the appropriate caption boldface. That solution would look like this:

Private Sub optSmall_Click()
    ChangeSize ("small")
End Sub

Private Sub optMedium_Click()
    ChangeSize ("medium")
End Sub

Private Sub optLarge_Click()
    ChangeSize ("large")
End Sub


Function ChangeSize(strChoice As String)

Select Case strChoice

Case Is = "small"

optSmall.Font.Bold = True
optMedium.Font.Bold = False
optLarge.Font.Bold = False


Case Is = "medium"

optSmall.Font.Bold = False
optMedium.Font.Bold = True
optLarge.Font.Bold = False

Case Is = "large"

optSmall.Font.Bold = False
optMedium.Font.Bold = False
optLarge.Font.Bold = True

End Select

End Function

Describe two types of dynamic behavior typical of complex dialog boxes.

The following types of dynamic behavior are typical of complex dialog boxes:

Name the two most common techniques you can use to display additional options in a dialog box.

The two most common techniques for displaying additional options in a dialog box are as follows:

How does the TabStrip control differ from the MultiPage control? What are the common uses for each?

A MultiPage control allows the user to switch among different virtual pages (with differing controls and varied layouts). The MultiPage control is most often employed to display a set of property pages for a feature (such as fonts) that includes many possible options and settings. You therefore can subdivide all these options among multiple pages in the form: one page for font properties and another page for character spacing, for example.

A TabStrip control contains multiple tabs but not multiple pages. In other words, the layout of the rest of the dialog box (apart from the tab strip itself), stays the same no matter which tab on the tab strip the user clicks. This is useful for displaying records from a database because the fields (such as text boxes) remain identical no matter which record the user is viewing.

How do you make a user form modeless?

Set its ShowModal property to False. The default is True.

Name two of the three most useful events available in VBA programming.

The three most commonly useful events for VBA programming are Click, Initialize, and Change.

Shorter, self-contained, single-task procedures offer the programmer several advantages. Name three.

The advantages of shorter, self-contained, single-task procedures are as follows:

How do you call a procedure?

You can use the optional Call statement, like this, to call a procedure named FormatDocument:

Call FormatDocument

But most programmers omit the Call keyword, using just the name of the procedure, like this:

FormatDocument

What kind of procedure can pass back information to the caller?

Only functions can pass back information to a caller. Subroutines can accept data (arguments) like functions, but subroutines cannot pass data back to the caller.

What is the difference between a class and an object?

Choose the correct answer (only one answer is correct):

The answer is 3. A class is like a blueprint and an object is like a house built from that blueprint.

How do you create a class module in the VBA Editor?

There are three ways to create a class module:

When testing your code, try to imagine ways that the code could fail. Describe a situation that can produce unanticipated results.

A user may try to run a document-formatting procedure without first opening a document.

Or the user might try to open a file, and trigger certain errors—perhaps the file doesn't exist; or is currently in use by another computer; or is on a network drive, floppy drive, CD-ROM drive, or removable drive that isn't available at the time

You'll also run into other peripheral-related errors if the user tries to use a printer or other remote device (say, a scanner or a digital camera) that's not present, connected, powered up, or configured correctly.

Similarly, any procedure that deals with a particular object in a document (for example, a chart in Excel) will run into trouble if that object is not present or not available.

Name two of the four basic types of programming errors.

Here are the four basic types of programming errors:

The Watch window is especially useful because you can set watch expressions (also known as conditional breakpoints). Describe this debugging tactic.

Watch expressions are flexible and powerful debugging tools. You can ask the VBA Editor to break on any kind of expression you can think up, such as any line that causes a variable to exceed a certain value, go below zero, change to a shorter string length, and so on.

You specify a condition (a watch expression such as X < 0), and the VBA Editor automatically halts execution and displays the line where this occurs.

Error handlers are special statements and sections of code that detect and then manage runtime errors. What VBA statement detects a runtime error?

VBA's On Error statement triggers when there is a runtime error, allowing you to write code that responds to the error.

Name two ways programmers can write procedures that don't annoy users.

Here are some ways programmers can avoid annoying users by their procedures' actions:

Assume that you are writing a procedure that employs Word's Search and Replace feature. This feature retains its settings between uses so the user can repeatedly trigger the same search or replace actions. How can you temporarily store the status of the user's last search or replace so that you can restore this data after your procedure is finished executing?

To store such information, you can use private variables, public variables, or custom objects.

Describe a way to let the user know that a procedure isn't frozen—that activity is taking place during execution.

You can tell users via a message box before starting a lengthy process that they should anticipate a delay. Alternatively, you can display messages on the status bar. Or you could disable screen updating for parts of a procedure and turn it back on, or refresh it, for other parts.

If a procedure accesses data from a file, name an error that could occur and thus should be trapped.

You should trap errors in case a file being accessed hasn't been opened, doesn't exist (has been deleted or moved), or doesn't contain data that the procedure expects to find in it.

Cleaning up involves three major tasks. Name one.

The three main ways that a procedure cleans up after itself are as follows:

Name two ways that users are protected from malicious VBA code.

Users are protected from malicious VBA code in the followings ways:

Describe the limitations of certifying a VBA macro project for yourself—without obtaining a certificate from your company or a commercial certification authority.

The quickest and easiest way of getting a digital certificate is to create one yourself. However, this kind of certification only works on the computer on which the certificate was created, and it's the least trustworthy type of digital signature. A digital certificate you create yourself is of little value to people other than you and those who personally trust you.

Name some of the ways you may be required to prove your identity when obtaining a digital signature from a commercial certification authority.

The procedure for proving your identity varies depending on the commercial certification authority and the type of certificate you want. Generally speaking, the greater the degree of trust that the certificate is intended to inspire, the more proof you'll need to supply. For example, you can get a basic certificate on the strength of nothing more than a verifiable email address, but this type of certificate is unlikely to make people trust you. Other certificate types require you to appear in person before a registration authority with full documentation (such as a passport, driver's license, or other identity documents). Such certificates carry more trust.

To set a suitable level of security for your purposes, open the Trust Center in Access, Word, Excel, or PowerPoint. You'll see four settings. Which one of the following five settings is not available:

There is no Enable All Macros With Notification option.

How do you add a publisher to the list of trusted publishers?

To add a trusted publisher, open a document or template that contains VBA code from the publisher you want to add. A Security Alert dialog box opens. When you open a file (or load an add-in) that contains code from a source with a valid digital signature, you have the option of adding this publisher to your list of trusted publishers. In the Security Alert dialog box, click the Trust All Documents From This Publisher option button.

What is the one drawback to locking your code?

The lock requires an extra step to accessing the modules and forms in the pro-ject because you must first provide the password. However, for the protection you gain by locking your code, this small extra effort can be well worth the trouble.

When you look at the Word Object Model Map, what is the highest object in the hierarchy—the object that contains all other objects?

The highest object in the hierarchy is the Application object.

What is a creatable object?

A creatable object is simply one that doesn't require you to use the term Application when invoking it. For example, the Documents collection object is creatable, so the word Application is optional in this code: Application.Documents.Count does the same thing as Documents.Count.

Here is the syntax for creating a new document in the Documents collection:

Documents.Add Template, NewTemplate, DocumentType, Visible

If you merely want to add a new, empty document (based on the default Normal.dotm template) to the documents currently open in Word, the code is quite simple. What is the code that you would write in VBA to accomplish this?

The code is as follows:

Documents.Add

One kind of selection is described as a collapsed selection. What is that?

A collapsed selection is an insertion point (the blinking cursor). Nothing is visibly selected. The insertion point, however, is still thought of as technically a selection (pointing to a place within the document), even though this special kind of selection has no contents.

Although a range is similar to a bookmark, what is the significant difference between them?

The main difference between a range and a bookmark involves their lifetimes. A range exists only as long as the VBA procedure that defines it is executing. A bookmark is persistent: It is saved with the document or template that contains it and can be acc-essed at any time (whether or not a procedure is running).

In Word, one object controls many of the options. This object has dozens of properties but no methods. Name this object.

The Options object controls many of the options in Word.

Sometimes when replacing, you need to go through a document more than once—using a loop structure. Why would you need to repeatedly search and replace the same document?

In some situations, the act of replacing actually generates new instances of the target of the replacement activity. Let's say you want only single-space strings in a document. (For example, you want sentences separated by only a single space character, but sometimes a typist accidentally presses two spaces following a period.) You set up a search and replace that looks for double-space character strings and replaces them with single-space characters. You have to take into account that there can also be some multiple-space-character strings. Consider a string of six adjacent space characters. During the first pass, your double-to-single search and replace reduces the six-space string to a three-space string (three instances of double spaces would reduce to three instances of single spaces). The second pass would reduce it to a two-space string, requiring yet a third pass through the loop to reach the desired single space. This same situation can apply to multiple paragraph spacing and tabs.

Name two types of headers you can use in a Word document.

Here are the major types of Word headers: the primary header, different first-page headers, different even-page headers, and different sets of headers for each of the sections in the document.

What method of the Window object can be used to easily accomplish this task?

The ScrollIntoView method of the Window object moves the view to a target you specify.

Name two important and useful objects within the Tables collection or the Table object.

Some the of most useful objects within a Table object or a Tables collection are:

What code would you write to create a new, blank notebook?

To create a blank workbook, omit the Template argument, like this:

Workbooks.Add

Name the object you use in VBA code to represent a worksheet.

Each worksheet is represented by a Sheet object. The Sheet objects are contained within the Sheets collection.

What is the difference between the active cell and a selection?

The active cell is always a single cell, but the selection can either be a single cell or encompass multiple cells or other objects.

What is unique about UsedRange?

If you need to work with all the cells on a worksheet (but not with any unoccupied areas of the worksheet), use the UsedRange property. UsedRange ignores empty areas of a worksheet.

From which object do you access most of Excel's options?

You access most of Excel's options from the Application object. However, you can access the workbook-specific properties that appear in the Excel Options dialog box through the appropriate Workbook object.

What object is used in a procedure to represent an embedded chart?

VBA uses the ChartObject object to represent an embedded chart on a worksheet.

Does the NewWindow method take any arguments?

No, the NewWindow method takes no arguments. For example, the following statement opens a new window showing the contents of the first window open on the workbook identified by the object variable myWorkbook:

myWorkbook.Windows(1).NewWindow

What is persistence, and why should it concern you?

The LookIn, LookAt, SearchOrder, and MatchByte arguments of the Range object's Find method persist. This means that Excel retains their settings from one search to the next (until this session with Excel ends and you shut it down). So, if you don't know that the settings used in the last search (either programmatically in a procedure or by the user) are suitable for your current needs, you should set these arguments explicitly in each search to avoid getting unexpected results. Format settings such as font and subscript also persist.

Name one of the objects or collections that are creatable in PowerPoint procedures.

Objects or collections that are creatable in PowerPoint procedures include the ActivePresentation object, the Presentations collection, the ActiveWindow object, and the SlideShowWindows collection.

How do you prevent a newly created presentation from being visible so that you can create and manipulate it in your code without the user seeing the flickering effect onscreen?

WithWindow is an optional Long argument of the Add method of the Presentations collection. Set WithWindow to msoFalse to hide the presentation so that the user doesn't have to endure the irritating flickering effect that PowerPoint tends to exhibit while creating presentation objects programmatically. The default value is msoTrue, making the new presentation visible.

PowerPoint uses two types of windows. What are they?

PowerPoint uses document windows and slide show windows.

Why would you want to assign names to slides rather than using the default index numbers that are automatically assigned to the slides?

Assigning names to slides is useful because if you add slides to, or delete them from, the presentation, the index numbers of the slides will change. You don't want to have to keep track of these readjusted index numbers as you manipulate the collection. If they have names, you can access the slides directly, without worrying that their index numbers might have changed.

How do you find out whether a presentation has a title master?

Check the HasTitleMaster property. If the presentation already has a title master, VBA returns an error when you try to add a title master. So first check, like this:

If ActivePresentation.HasTitleMaster Then
        'take further action based on the If...Then test

Describe what the following line of code does:

ActivePresentation.Slides(2).Shapes(1).Delete

The code example deletes the first Shape object on the second slide in the active presentation.

In this chapter, you worked with several examples showing how to manipulate footers for slides. Why were there no examples illustrating how to manipulate headers for slides?

Slides can't have headers, only footers. Notes pages or handouts can have headers.

The syntax when using the Add method of the NamedSlideShows collection is

expression.Add(Name, SafeArrayOfSlideIDs)

Explain what the four components of this line of code are and do.

The components are as follows:

One of these objects represents a window that displays the contents of a folder. The other represents a window displaying an Outlook item, such as an email message or an appointment. What are the names of these two objects?

An Inspector object is an object that represents a window displaying an Outlook item, such as an email message or an appointment.

An Explorer object represents a window that displays the contents of a folder.

Name one of the most widely useful properties employed when manipulating the contents of a message in a procedure.

The most commonly useful properties when accessing a message in VBA are To, CC, BCC, Subject, Body, BodyFormat, and Importance.

To create a new calendar item, you use a particular method of the Application object and specify olAppointmentItem for the ItemType argument. What is the method?

To create a new calendar item, you use the CreateItem method of the Application object. This example creates an AppointmentItem object variable named myAppointment and assigns to it a new appointment item:

Dim myAppointment As AppointmentItem
Set myAppointment = Application.CreateItem(ItemType:=olAppointmentItem)

What methods do you use to assign, add, and send a task to others?

To assign, add, and send a task to others, use the Assign, Add, and Send methods.

What is the efficient, quick way to create the proper syntax for the Filter argument?

Outlook itself can generate the Filter argument for you, using the built-in Filter utility as described in this chapter.

Event handler procedures are unlike ordinary macro procedures in several ways. Name one of the differences.

Both the construction of an event handler procedure and its testing differ somewhat from the techniques you've been employing throughout this book when creating and testing ordinary macro procedures:

What are the two types of events in Outlook? And how do they differ?

The two types of events in Outlook are application-level and item-level events. Application-level events apply to Outlook as a whole (for example, an event that triggers when the application is closed). Item-level events apply to individual items within Outlook, such as a contact or an email message in the Inbox.

Name one of the ways that Outlook's bar events can be employed by the developer.

You can customize the Outlook bar or constrain the user's navigation capabilities. For an example of the latter, you could use the BeforeNavigate event to check the shortcut to which the user is attempting to navigate. If the user is not permitted to access the folder associated with the shortcut, you can cancel the navigation by inserting appropriate code in the BeforeNavigate event.

The term macro is used in a special way in Access (referring to only one of the two types of custom procedures Access permits you to construct: VBA and Macro Designer). This usage of macro is unlike the way the term macro is used in other Office applications, not to mention all other forms of computing. Describe what Access means by the term macro.

Instead of defining macros as VBA procedures, Access uses the term macro to describe a technology unique to Access. You create these Access "macros" by clicking the Macro button on the Create tab (in the Macros And Code section of the Ribbon) to open the Macro Designer window

An Access "macro" is an historical entity—a holdover from the early days of this database system. Access macros are limited to a subset of the available programming statements.

Using the Macro Designer, you enter a list of actions that you want to perform. You choose these actions from a list and then type in arguments in the next cell in a table displayed by the Macro Designer.

Two common commands that open a database in Access are OpenCurrentDatabase and OpenDatabase. What is the difference between these two commands?

Instead of using the OpenCurrentDatabase method to open a database as the current database, you can use the OpenDatabase method of the Workspace object to open another database and return a reference to the Database object representing it. Using this method, you can open multiple databases. The OpenCurrentDatabase method, by contrast, can open only a single database at a time.

The Screen object represents the screen object that currently has the focus in Access (that is, the object that is receiving input or ready to receive input). Three types of common Access objects can have the focus when you employ the Screen object. What are they?

The object can be a form, a report, or a control.

The DoCmd object has 67 methods in Office 2010. Describe the purpose of the DoCmd object's Beep method.

The Beep method makes the computer emit a sound. This can be used in conjunction with an error message to alert the user that an error has occurred.

One way to open an ADO recordset is to provide an argument list following the Open method. What is the other way to open an ADO recordset, which doesn't involve using arguments? Some people say that this second approach makes their code easier to read.

Instead of specifying arguments for the Open method, you can set the Source, ActiveConnection, CursorType, and LockType properties of the RecordSet object you're opening and then use the Open method without arguments. You may agree with those who feel this approach makes the code easier to read.

One method you can use to traverse a recordset is the MoveFirst method. It takes you to the first record in the recordset. What does the first record mean in a recordset in a relational database? Is it the record that's the lowest numerically, the lowest alphabetically, or what?

The concept of "first record" within a relational database is essentially meaningless, unless you sort the records in some fashion. You can sort them by any of their fields and also either ascending (the default) or descending (specify DESC). Which record is first depends on the field by which you sort the recordset. But you must sort them. They can't be assumed to be sorted in any way in the database itself.

ADO offers a Find method. How many methods does DAO offer, and what are they?

There are four Find methods for DAO: FindFirst, FindNext, FindPrevious, and FindLast.

After you have made a change to a value in a record, what method do you use to save this change to make it part of the database?

You use the Update method to save the changes you made to a recordset.

To insert a new record into a recordset, what method do you use before you can assign data to the fields in the new record?

You use the AddNew method of the RecordSet object to create a new, empty record.

Of the various ways to communication between applications, which is generally the most effective?

Automation is the most powerful and efficient way to communicate with another application.

Describe the timing issues that the Shell function raises, and name a good solution to this problem.

The Shell function runs other programs asynchronously rather than synchronously. In other words, Shell doesn't halt all other activity until it has finished with its job. So when VBA executes a Shell statement, it registers the statement as an action to be performed—but that action may not necessarily be finished before the next statement in the procedure executes.

This asynchrony can cause errors in your procedures if subsequent commands depend on the Shell statement having already been executed. If you run into this type of problem, a crude but often effective fix is to just allow extra time for the Shell function to execute before taking any dependent action. You can employ the Sleep function to pause execution of your procedure to allow any necessary commands to be carried out.

How is the data object technology special as a way of storing and retrieving information; what can a data object do that's unique?

The data object has the ability to return information from and write information to the Windows Clipboard.

Not all applications support DDE. Which Office 2010 applications don't support DDE communication?

PowerPoint and Outlook do not support DDE.

SendKeys was historically most often employed to open an application's menus and select an option from the menus. Because Vista and Windows 7 applications largely do away with traditional menus, is SendKeys of even more limited use in Vista and Windows 7 than in previous versions of Windows?

No, SendKeys must simply send some different keystrokes to access Vista and Windows 7 applications' features. Many of the features of the Ribbon, for example, are accessible via key combinations. For example, pressing the sequence Alt, W, Q, 2, and the Enter key in Word will switch to the View tab on the Ribbon, select the Zoom option, and switch to a 200% zoom. The difference here is that instead of employing the traditional approach of simultaneously pressing the Alt key while pressing other keys (such as Alt+V to open a View menu), in Vista or Windows 7 you press and release Alt by itself, then you press the W key to switch to the View tab on the Ribbon. At this point, additional keystrokes are possible to activate the various options on the View tab.

Some Ribbon-related attributes include the suffix Mso. Examples include idMso and imageMso. What does the Mso mean, and what kind of attributes' names are appended with Mso?

Mso stands for Microsoft Office, and it means that a tab, icon, or other element is built in—such as a tab that is, by default, visible on the Ribbon.

What XML attribute of a group do you set to false to remove that group from the Ribbon?

You can set any group's visible attribute to false, thereby hiding it from the user.

What XML attribute do you use to create a callback?

You use the onAction attribute to create a callback.

Where can you store the XML code when programming the Access Ribbon?

You store the XML code when programming the Access Ribbon in a special table named USysRibbons.

What is XML validation?

XML validation is a feature built into XML code editors that checks to ensure that the XML is well formed (free of certain kinds of errors). A validation looks for tags that are missing, out of order, improperly punctuated, use the wrong capitalization, are not part of the schema (aren't listed as attributes or elements in the document that defines the particular XML version being used), and so on. Some errors can't be detected during validation (such as pointing an onAction attribute to a VBA procedure that doesn't exist). But many common errors—such as typos—can be spotted by validation, which is an automatic scan of XML code.