Each of The Bottom Line sections in the chapters suggest exercises to deepen skills and understanding. Sometimes there is only one possible solution, but often you are encouraged to use your skills and creativity to create something that builds on what you know and lets you explore one of many possible solutions.

Chapter 1: Recording and Running Macros in the Office Applications

• Record a macro. The easiest way to create a macro is to simply record it. Whatever you type or click—all your behaviors—are translated into VBA automatically and saved as a macro.
  • • Master It 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 test the new macro.
    • Solution Click the Developer tab on the Ribbon (if that tab isn't visible, press Alt+F, I and select Show Developer Tab in the Ribbon). Click the Record Macro button on 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 box. 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 on 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. The macro code should look something like this:

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

• Assign a macro to a button or keyboard shortcut. You can trigger a macro using three convenient methods: clicking an entry on the Ribbon, clicking a button in the Quick Access Toolbar, or using a keyboard shortcut. You are responsible for assigning a macro to any or all of these methods.
  • • Master It Assign an existing macro to a new Quick Access Toolbar button.
    • Solution Press Alt+F, I. Click Customize in the left pane of the Word Options dialog box. 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 box. Now you see a new button on the Toolbar that, when clicked, launches your macro.
• Run a macro. Macros are most efficiently triggered via a Ribbon entry, or by clicking a button on the Quick Access Toolbar, or by pressing a shortcut key combination such as Alt+N or Ctrl+Alt+F. When you begin recording a macro, the Record Macro dialog has buttons that allow you to assign the new macro to a shortcut key or toolbar button. However, if you are using the Visual Basic Editor, you can run a macro by simply pressing F5.
  • • Master It Execute a macro from within the Visual Basic Editor.
    • Solution 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 the macro.
• Delete a macro. It's useful to keep your collection of macros current and manageable. If you no longer need a macro, remove it. Macros can be directly deleted from the Visual Basic Editor or by clicking the Delete button in the Macros dialog (opened by choosing Developer ➢ Macros).
  • • Master It Temporarily remove a macro, then restore it, using the Visual Basic Editor.
    • Solution 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 an 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 box 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 box. Restore the macro by pressing Alt+F11 to reopen the Editor. Then click a blank line at the very top of the right pane where the macro code is. 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, you can copy and paste all the code examples from this book at the book's website: www.sybex.com/go/masteringvbaoffice2013

Chapter 2: Getting Started with the Visual Basic Editor

• Open the Visual Basic Editor. When you want to create a new macro by hand‐programming (as opposed to recording) or need to modify or test a macro, the Visual Basic Editor is a powerful tool.
  • • Master It Open the Visual Basic Editor in Word and create a simple macro.
    • Solution Press Alt+F11.
• Open a Macro in the Visual Basic Editor. You edit and test macro code in the Code window of the Visual Basic Editor.
  • • Master It Open the Visual Basic Editor and display a particular macro in the Code window.
    • Solution 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.

      The Project Explorer window displays a tree of current projects. You can choose between viewing only the files or the folders and files.

• Understand the Project Explorer's two views. The Project Explorer window displays a tree of current projects. You can choose between viewing only the files or the folders and files.
  • • Master It Switch between folder and contents view in the Project Explorer.
    • Solution Click the icon on the right side (a picture of a folder) just under the Project Explorer's title bar.
• Set properties for a project. You can specify a project's name, an associated Help file, and other qualities of a project.
  • • Master It Lock a project so others can't modify or even read its contents.
    • Solution 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.
• Customize the Visual Basic Editor. The Visual Basic Editor can be customized in many ways, including personalizing classic menus and toolbars.
  • • Master It Undock the Properties window and change its size. Then redock it.
    • Solution 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.

Chapter 3: Editing Recorded Macros

• Test a macro in the Visual Basic Editor. When you need to modify or debug a macro, the Visual Basic Editor is your best friend. It's filled with tools to make your job easier.
  • • Master It Open a macro; then step through it to see if anything goes wrong.
    • Solution Press Alt+F8 to open the Macros dialog box. Select the macro's name that you want to test, 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 breakpoints and use comments. Setting breakpoints allows you to press F5 to execute a macro, but forces the Editor to enter Break mode when execution reaches the line where the breakpoint resides. Comments help you understand the purpose of code—they describe it but are ignored during execution of the macro's code. “Commenting out” a line of code allows you to temporarily render it inactive to see what effect this has during execution. This is sometimes a good way to see if that line is causing the bug you're tracking down.
  • • Master It Set a breakpoint in, and add a comment to, a macro.
    • Solution 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, the line will be ignored when the Editor executes the macro.
• Edit a recorded macro. Make some changes to a Word macro.
  • • Master It With the Visual Basic Editor open, choose a macro and modify it.
    • Solution Click a line between the Sub and End Sub lines that envelop 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.

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

• Set up the Visual Basic Editor to create macros. How you arrange the various components of the Visual Basic Editor is your personal choice, but while using this book, it's easiest if you set up the Editor to resemble the way it appears in the book's figures. Besides, this arrangement is quite close to the default layout, which has proven to be the most effective one for the majority of programmers (according to various focus groups and polls) for the decades that Visual Basic has been used.
  • • Master It Press a single key to toggle (to display and then hide) the Properties window.
    • Solution Press F4 to display the Properties window.
• Create a macro for Word. Using the Help feature in any VBA‐enabled application allows you to find code examples that you can copy and paste into your own code.
  • • Master It Open the Code window and use Help to find a code example.
    • Solution 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 envelope. 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.

• Create a procedure for Excel. Certain procedure names are special. In a previous Excel exercise, you added line numbering and gave that procedure a name of your own choice. But some procedure names have a special meaning—they are triggered by an event in Excel itself. They will execute automatically when that event takes place (you don't have to run events by choosing Run from the Macro dialog box or by assigning the macro to a keyboard shortcut or Quick Access Toolbar button). One such event is Excel's Auto_Open procedure.
  • • Master It Display a message to the user when Excel first executes.
    • Solution 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 highlighted code into the procedure:

              Private Sub Workbook_Open()
              MsgBox  “Opened"
              End Sub
      

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

• Create a macro for PowerPoint. As you type a procedure, the Visual Basic Editor provides you with lists of objects' members (the Auto List Members feature) and with syntax examples, including both required and optional arguments (the Auto Quick Info feature). These tools can be invaluable in guiding you quickly to the correct object and syntax for a given command.
  • • Master It Use the Auto List Members and Auto Quick Info features to write a macro that saves a backup copy of the currently active presentation.
    • Solution Create a new presentation based on a template of your choosing. Press Alt+F11 to open the Visual Basic Editor. Choose Tools ➢ Options and ensure that the Auto List Members and Auto Quick Info check boxes are selected.

      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, and 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 Documents folder, where it is saved by default.

      Your macro should look like this:

              Sub SaveTemp()
       
              Application.ActivePresentation.SaveCopyAs “temporary"
       
              End Sub
      

• Create a procedure for Access. Although Access includes a variety of macro‐related features that are unique (such as its Macro Builder/Designer), its Visual Basic Editor is quite similar to the Visual Basic Editors in the other Office applications.
  • • Master It Open the Visual Basic Editor in Access and write a macro that displays today's date using the Date function rather than the Now function. Use the Access Visual Basic Editor Help system to understand the difference between these two functions.
    • Solution In Chapter 4, you wrote a macro in Access that displays today's date and time. Here you will display the date only.
      1. Start Access.
      2. Click the Blank Database button, and then click the Create button.
      3. Press Alt+F11 to open the Visual Basic Editor.
      4. Right‐click the database name in the Project Explorer, then choose Insert Module to open a new module in the Code window.
      5. In the Code window, type the following macro:

                 Sub ShowDateOnly()
          
                 MsgBox Date
          
                 End Sub
         

      6. Press F5 to execute the macro. You should see a message box that displays the current date.

Chapter 5: Understanding the Essentials of VBA Syntax

• Understand the basics of VBA. VBA includes two types of procedures, used for different purposes.
  • • Master It Name the two types of procedures used in VBA (and indeed in most computer languages), and describe the difference between them.
    • Solution 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.

• Work with subs and functions. A procedure (a sub or function) is a container for a set of programming statements that accomplish a particular job.
  • • Master It Write a sub in the Visual Basic Editor that displays a message to the user. Then execute that sub to test it.
    • Solution 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.

• Use the Immediate window to execute individual statements. When you're writing code, you often want to test a single line (a statement) to see if you have the syntax and punctuation right or if it produces the expected result.
  • • Master It Open the Immediate window, type in a line of code, and then execute that line.
    • Solution Press Ctrl+G to open the Immediate window, then type a line of VBA code. Press the Enter key when you've finished to execute that statement.
• Understand objects, properties, methods, and events.

  Object‐oriented programming (OOP) means working with objects in your programming. OOP has become the fundamental paradigm upon which large programming projects are built. Generally speaking, macros are not large and therefore they usually don't profit from the clerical and security benefits that OOP offers—these features particularly benefit people who write large applications as a team.

  However, in your programming, you'll make frequent use of code libraries. All VB's commands are part of these libraries, such as the vast VBA set of objects and their members (not to mention the even vaster .NET libraries that tap into the power of the operating system itself). These libraries are huge. So, there needs to be a clerical way to organize the objects and functions within the libraries—to categorize the objects and commands, and allow you to execute the methods and manage their properties and arguments in your own macros. As a result, the most useful aspect of OOP to us VBA macro programmers—taxonomy—is quite valuable even when writing brief macros. It's a way to quickly locate the members you're interested in.

  • • Master It Look up the Document object in the Visual Basic Editor's Help system; then look at its methods.
    • Solution With the Visual Basic Editor the active window, choose Help ➢ Microsoft Visual Basic Help. Click the Word Object Model Reference link in the Help dialog box. 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.

Chapter 6: Working with Variables, Constants, and Enumerations

• Understand what variables are and how you use them. Variables are a cornerstone of computer programming; they are extremely useful for the same reason that files are useful in the real world. You give a name to a variable for the same reason that you write a name to identify a file folder. A file can, over time, contain various different papers, just as the value contained in a programming variable can vary. In both cases, the contents vary; the name remains the same. It's good practice to always specifically name a variable before using it in your code. This is called explicit declaration.
  • • Master It Explicitly declare a variable named CustomersAge.
    • Solution 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
      

• Create and use variables. When creating (declaring) a new variable, you should avoid using words or commands that are already in use by VBA, such as Stop or End. There are other restrictions too, such as not using special characters.
  • • Master It The following variable name cannot be used, for two reasons. Fix it so it is a legitimate variable name:

              Dim 1Turn! as Integer

• • • Solution 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.

• Specify the scope and lifetime of a variable. Variables have a range of influence, depending on how you declare them.
  • • Master It Create a variable named AnnualSales that will be available to any procedure within its own module but not to other modules.
    • Solution Constants, like variables, are named locations in memory that contain a value. Unlike variables, however, the value in a constant does not change during program execution.
• Work with constants. Constants, like variables, are named locations in memory that contain a value. Unlike with variables, however, the value in a constant does not change during program execution.
  • • Master It Define a string constant using the Dim command. Name your constant FirstPrez, and assign it the value George Washington.
    • Solution This code line defines a constant, and assigns a value to it:

              Const FirstPrez As String = “George Washington&"

• Work with enumerations. Enumerations provide a handy name for each item in a list, often a list of properties.
  • • Master It In the Project Explorer, click the ThisDocument object to select it. Then locate the JustificationMode property in the Properties window, and choose one of that property's enumerated constants by clicking the small down‐arrow that appears, then clicking one of the constants in the drop‐down list.
    • Solution You'll see the drop‐down list of enumerated values for the JustificationMode property, as illustrated in the following screenshot.

Chapter 7: Using Array Variables

• Understand what arrays are and what you use them for. Arrays play an important role in computer programming. In some ways they resemble a mini‐database, and organized data is central to computing. Computers are sometimes called data processors for good reason, and arrays make it easier for you to manipulate variable data.
  • • Master It. What is the difference between an array and an ordinary variable?
    • Solution An ordinary (scalar) variable can contain only a single value; an array can contain multiple values, identified by index numbers.
• Create and use arrays. When you create a new array, you declare it and, optionally, specify the number of values it will contain.
  • • Master It There are four keywords that can be used to declare arrays. Name at least three of them.
    • Solution Arrays can be declared using the same keywords that are employed to declare ordinary variables: Dim, Private, Public, or Static.
• Redimension an array. If you want to resize an existing dynamic array, you can redimension it.
  • • Master It 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?
    • Solution To preserve values when redimensioning an array, use the Preserve command, like this:

              ReDim Preserve arrTestArray(5)

• Erase an array. You can erase all the values in a fixed‐size array or completely erase a dynamic array.
  • • Master It Write a line of code that erases the contents of an array named arrMyArray.
    • Solution 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

• Find out whether a variable is an array. An array is a type of variable, and you may occasionally need to query in your code whether a particular variable name denotes an array or an ordinary scalar variable (a variable that isn't an array).
  • • Master It Which built‐in function can you use in VBA to find out whether a variable is an array or an ordinary, single‐value variable?
    • Solution 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

• Sort an array. Visual Basic .NET includes array objects with built‐in search and sort methods. In VBA, however, you must write a bit of code to search and sort the values in an array.
  • • Master It Name a popular, understandable, but relatively inefficient sorting technique.
    • Solution The bubble sort is easy to visualize, but relatively inefficient.
• Search an array. Searching through an array can be accomplished in two primary ways. If you have a relatively small array, you can use the simpler, but less efficient technique. With large amounts of data, though, it's best to use the more robust approach.
  • • Master It Name two common search algorithms.
    • Solution You can use either the simple linear search or the binary search, which requires that an array be sorted first.

Chapter 8: Finding the Objects, Methods, and Properties You Need

• Understand and use objects, properties, and methods. Contemporary programming employs a hierarchical method of organization known as object‐oriented programming (OOP). At the very top of the hierarchy for any given application is the Application object. You go through this object to get to other objects that are lower in the hierarchy.
  • • Master It By using creatable objects, you can often omit the Application object when referencing it in code. What are creatable objects?
    • Solution 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

• Use collections of objects. Collections are containers for a group of related objects, such as the Documents collection of Document objects.
  • • Master It Are collections objects? Do they have their own methods and properties?
    • Solution 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.
• Find objects, properties, and methods. The Visual Basic Editor offers several ways to locate objects' members and add them to your programming code. There's an extensive Help system, the Object Browser, a List Properties/Methods tool, and the Auto List Members tool.
  • • Master It How do you employ Auto List Members to find out which properties and methods are available for Word's Document object?
    • Solution Type Document. in the Code window, and as soon as you type the period, a list of the Document object's members appears.
• Use Object variables to represent objects. You can create variables that contain objects rather than typical values like strings or numbers.
  • • Master It What keywords do you use to declare an Object variable?
    • Solution 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"

    • 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

Chapter 9: Using Built‐in Functions

• Understand what functions are and what they do. A function is a unit of code, a procedure, that performs a task and returns a value.

  You can write your own functions by writing code between Function and End Function in the VBA Editor. Chapter 10, “Creating Your Own Functions,” explores how to write such custom functions. In addition to functions you might write, there are many functions already prewritten in VBA—ready for you to call them from your macros to perform various tasks.

  • • Master It A function in VBA is quite similar to a subroutine, but there is a significant difference. What is it?
    • Solution Subroutines don't return a value; functions do.
• Use functions. In a macro, you can call a built‐in function by merely typing in its name and providing any required arguments.
  • • Master It 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.
    • Solution MsgBox Now

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

• Use key VBA functions. VBA offers the services of hundreds of built‐in functions. You'll find yourself using some of them over and over. They are key to programming.
  • • Master It What built‐in function is used quite often to display information in a dialog box to the user while a procedure runs?
    • Solution Both the MsgBox and InputBox functions are used to display information to the user in a dialog box.
• Convert data from one type to another. It's sometimes necessary to change a value from one data type to another. Perhaps you used an input box to ask the user to type in a String variable, but then you need to change it into an Integer type so you can do some math with it. (You can't add pieces of text to each other.)
  • • Master It 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?
    • Solution The built‐in function CInt transforms other data types into an Integer type. Here's an example:

              intMyVar = CInt(varMyInput)

• Manipulate strings and dates. VBA includes a full set of functions to manage text and date data.
  • • Master It 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"

• • • Solution Use the Trim function.

Chapter 10: Creating Your Own Functions

• Understand the components of a function statement. Arguments can be passed from the calling code to a function in one of two ways: by reference or by value. Which is the default?
  • • Master It Describe the difference between passing data by reference and passing data by value.
    • Solution The memory address of the actual value is passed to the function when passed by reference. This means that the value can be changed by the function. When 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 generic function. You can write, and save (File ⇨ Export File) sets of generic functions that work in any VBA‐enabled application.
  • • Master It Create a function that displays the current year in a message box. This function will require no arguments, nor will it return any value.
    • Solution       Function ShowYear()

       
                  MsgBox (Year(Now))
       
                End Function

• Create a function for Word. Word contains a whole set of objects and members unique to word‐processing tasks. Functions that are specific to Word employ one or more of these unique features of the Word object model.
  • • Master It Write a function that displays the number of hyperlinks in the currently active document. Use Word's Hyperlinks collection to get this information.
    • Solution       Function FindHyperCount()

       
                  MsgBox (ActiveDocument.Hyperlinks.Count)
       
              End Function

• Create a function for Excel. Excel uses an ActiveWorkbook object to represent the currently selected workbook. You can employ a full set of built‐in methods to manipulate the features of any workbook.
  • • Master It Using the Sheets collection of Excel's ActiveWorkbook object, write a function that displays the number of sheets in the current workbook.
    • Solution       Function SheetsCount()

       
              MsgBox (ActiveWorkbook.Sheets.Count)
       
              End Function

• Create a function for PowerPoint. PowerPoint's object model includes an ActivePresentation object, representing the currently selected presentation. Functions can make good use of this object and its members.
  • • Master It Write a function that returns how many slides are on a presentation. Pass the ActivePresentation object as an argument to this function; then display the number of slides the presentation contains. Call this function from a subroutine.
    • Solution       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.

• Create a function for Access. Access often works a little differently from other VBA‐enabled Office applications. For example, some common tasks are carried out by using methods of the special DoCmd object rather than methods of a Form or Table object.
  • • Master It 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.
    • Solution This function closes Access with the DoCmd object's Quit method:

              Function QuitApp()
       
              DoCmd.Quit (acQuitSaveAll)
       
              End Function

Chapter 11: Making Decisions in Your Code

• Use comparison operators. Comparison operators compare items using such tests as greater than or not equal to.
  • • Master It Write a line of code that uses a less than comparison to test whether a variable named Surplus is less than 1200.
    • Solution Here's an example of the less than comparison operator:

              If Surplus < 1200 Then

• Compare one item with another. You can compare strings using less than and more than comparison operators.
  • • Master It What symbol do you use to determine if VariableA is lower in the alphabet than VariableB?
    • Solution You use the less than symbol <, like this:

              If VariableA < VariableB Then

• Test multiple conditions. To test multiple conditions, you use VBA's logical operators to link the conditions together.
  • • Master It Name two of the most commonly used logical operators.
    • Solution 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

• Use If blocks. If blocks are among the most common programming structures. They are often the best way to allow code to make decisions. To test two conditions, use If… Else… EndIf.
  • • Master It 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.
    • Solution 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

• Use Select Case blocks. Select Case structures can be a useful alternative to If blocks.
  • • Master It When should you use a Select Case structure?
    • Solution 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.

Chapter 12: Using Loops to Repeat Actions

• Understand when to use loops. Loops come in very handy when you need to perform a repetitive task, such as searching through a document for a particular word.
  • • Master It What is the alternative to looping if you are carrying out repetitive tasks in a macro?
    • Solution You can copy the repeated code and 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.
• Use For… loops for fixed repetitions. For… loops are the most common loop structures in programming. You specify the number of iterations the loop must make, and the loop is exited when that number is reached.
  • • Master It Write a For…Next loop that counts up to 100, but use the Step command to increment by twos.
    • Solution This code increments its counter variable (i) by twos:

              For i = 1 To 100 Step 2
       
              Next

• Use Do… loops for variable numbers of repetitions. A Do… loop iterates until or while a condition exists, then exits from the loop when the condition no longer exists.
  • • Master It There are two categories of Do… loops. Do While… Loop and Do Until… Loop loops test a condition before performing any action. What is the other category?
    • Solution 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.
• Nest one loop within another loop. You can put loops inside other loops.
  • • Master It Think of a programming task where nested loops would be useful.
    • Solution 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.
• Avoid infinite loops. An infinite loop causes your macro to continue execution indefinitely—as if the macro had stopped responding and was “frozen.”
  • • Master It How can you avoid creating an infinite loop?
    • Solution Be sure that it is possible for your loop to terminate at some point. Ensure that a condition will occur that ends the looping.

Chapter 13: Getting User Input with Message Boxes and Input Boxes

• Display messages on the status bar. The information bar at the bottom of the window in many applications is a useful, unobtrusive way of communicating with the user. The status bar is frequently used by applications to indicate the current page, zoom level, active view (such as datasheet in Access), word count, and so on. However, you, too, can display information on the bar.
  • • Master It Write a small sub in the Visual Basic Editor that displays the current date and time in the status bar.
    • Solution This procedure shows how to display information in the status bar:

              Sub Experimentation_Zone()
       
              Application.StatusBar = Now
       
              End Sub

• Display message boxes. Message boxes are commonly used to inform or warn the user. By default, they appear in the middle of the screen and prevent the user from interacting with the host application until a button on the message box is clicked, thereby closing it.
  • • Master It Write a small sub in the Visual Basic Editor that displays the current date and time using a message box.
    • Solution Here's how to display information in a message box:

              Sub Experimentation_Zone()
       
              MsgBox Now
       
              End Sub

• Display input boxes. An input box is similar to a message box, except the former can get more information from the user. An input box allows the user to type in a string, which is more data than the simple information provided by which button the user clicked in a message box.
  • • Master It 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 Cher or they have typed in only one of their names—so display a second input box telling them to provide both their first and last names.
    • Solution 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

• Understand the limitations of message boxes and input boxes. For even moderately complex interaction with the user, message and input boxes are often too limited. They return to the VBA code, for example, only a single user response: a button click or a single piece of text. So you can't conveniently use an input box to ask for multiple data—such as an address and a phone number—without displaying multiple input boxes. That's ugly and disruptive.
  • • Master It In addition to the limitations on the amount of information you can retrieve from the user, what are the two other major limitations of message boxes and input boxes?
    • Solution You are limited in the formatting and the amount of information you can display to the user.

Chapter 14: Creating Simple Custom Dialog Boxes

• Understand what you can do with a custom dialog box. Custom dialog boxes—user interfaces you design as forms in the Visual Basic Editor—are often needed in macros and other kinds of Office automation. You might, for example, want to display a dialog box that allows the user to specify whether to let a macro continue beyond a certain point in its code or cease execution. Perhaps your macro is searching through a document for a particular phrase. Then when it finds that phrase, it displays a dialog box to users asking if they want to continue further.
  • • Master It Which VBA statement would you use to stop a macro from continuing execution?
    • Solution The End command halts execution.
• Create a custom dialog box. You use the Visual Basic Editor to both design a custom dialog box (form) and write code for macros. You can attach the various controls to a form and then enter code behind the dialog box.
  • • Master It How do you switch between the form‐design window (sometimes called the object window) and the Code window in the Visual Basic Editor?
    • Solution 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).
• Add controls to a dialog box. It's easy in the Visual Basic Editor to add various controls—such as command buttons and text boxes—to a user form (a custom dialog box).
  • • Master It How do you add a command button to a custom dialog box?
    • Solution If the Visual Basic Editor Toolbox isn't visible, click the form. Or you can choose View ➢ Toolbox. Or click the Toolbox button on the Standard toolbar to display the Toolbox. Then click the command‐button icon and click the form. If you want to add more than one command button, double‐click the command‐button icon in the Toolbox. Move your mouse cursor (notice that it now displays a small button icon) to the location in the dialog box where you want to place the command button. Click the form to place the new button on the form.
• Link dialog boxes to procedures. Buttons, check boxes, option buttons—displaying various controls to the user is fine, but unless you write some code behind these various user‐interface objects, what's the point? Your macro's user shouldn't discover that clicking a button does nothing.

  Dialog boxes often display objects with which users can communicate their wants to your code. Therefore, you write code that reads the values the user enters into controls, and responds.

  • • Master It 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.
    • Solution After you've placed the label with its Caption property set to “Would you like to know the current date and time?” and 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:
      • Line 2 closes the dialog box using the End statement. You could also use the UnLoad statement.
      • Line 7 tests the value in the label's Caption property. This is how the procedure knows if this is the first time the user has clicked the OK button. If the label displays the original caption (“Would you like to see the current date and time?”), then you know the user has not previously clicked OK. However, if this original caption is not displayed (because the date and time are), that means the user is clicking OK a second time and wants to close the dialog box.
      • Line 8 displays the date and time in the label.
      • Line 10 exits the procedure, which also has the effect of removing the dialog box.

        There are other ways to test whether the user has clicked OK twice. You could create a Static 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

• Retrieve the user's choices from a dialog box. A major task of most dialog boxes is retrieving values that the user has specified in various controls by selecting check boxes, and so on. Then you write code to carry out the user's wishes based on these retrieved values. This interaction via dialog box is the typical way that a user communicates with your procedures, and vice versa.
  • • Master It 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.
    • Solution 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

• With code, there are always various ways to achieve a given result. 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

Chapter 15: Creating Complex Forms

• Understand what a complex dialog box is. Simple dialog boxes tend to be static, but complex dialog boxes are dynamic—they change during execution in response to clicks or other interaction from the user.
  • • Master It Describe two types of dynamic behavior typical of complex dialog boxes.
    • Solution The following types of dynamic behavior are typical of complex dialog boxes:
      • The application changes the information in the dialog box to reflect choices that the user has made.
      • The dialog box displays a hidden section of secondary options when the user clicks a button in the primary area of the dialog box.
      • The application uses the dialog box to keep track of a procedure and to guide the user to the next step by displaying appropriate instructions and by activating the relevant control.
• Reveal and hide parts of a dialog box. Dialog boxes need not display everything at once. Word's Find And Replace dialog box illustrates how useful it can be to display an abbreviated dialog box containing the most common tasks and expand the box to reveal less‐popular options if the user needs access to them.
  • Master It Name the two most common techniques you can use to display additional options in a dialog box.
  • Solution The two most common techniques for displaying additional options in a dialog box are as follows:
    • Set the Visible property to False during design time to initially hide a control on a form. Then set its Visible property to True when you want to display the control.
    • Increase the height or width (or both) of the dialog box to reveal an area containing further controls.
• Create multipage dialog boxes. VBA includes the MultiPage control, which enables you to create multipage dialog boxes. Word's Font dialog box is an example of one. You can access any page (one at a time) by clicking its tab at the top of the page.
  • • Master It How does the TabStrip control differ from the MultiPage control? What are the typical uses for each?
    • Solution 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.

• Create modeless dialog boxes. A modeless dialog box can be left visible onscreen while the user continues to work in an application. For example, the Find And Replace dialog box in Access, Word, and Excel is modeless, as is the Replace dialog box in PowerPoint. A modal dialog box, by contrast, must be closed by users before they can continue to interact with the application.
  • • Master It How do you make a user form modeless?
    • Solution Set its ShowModal property to False. The default is True.
• Work with form events. Events are actions that happen to controls (or the form itself) while a program is executing. By using events, you can monitor what the user does and take action accordingly or even prevent the user from doing something that doesn't seem like a good idea.
  • • Master It Name two of the three most commonly used events in VBA programming.
    • Solution The three most commonly useful events for VBA programming are Click, Initialize, and Change.

Chapter 16: Building Modular Code and Using Classes

• Arrange your code in modules. Rather than use a single lengthy, complex procedure that accomplishes many tasks at once, programmers usually subdivide their code into smaller, self‐contained procedures—dedicated to a single, discrete task.
  • • Master It Shorter, self‐contained, single‐task procedures offer the programmer several advantages. Name three.
    • Solution The advantages of shorter, self‐contained, single‐task procedures are as follows:
      • Modular code is often easier to write because you create a number of short procedures, each of which performs a specific task.
      • You can usually debug shorter procedures relatively easily.
      • Short procedures are more readable because you can more easily follow what they do.
      • By breaking your code into procedures, you can repeat their tasks at different points in a sequence of procedures without needing to repeat the lines of code.
      • By reusing whole procedures, you reduce the amount of code you have to write.
      • If you need to change an item in the code, you can make a single change in the appropriate procedure instead of having to make changes at a number of locations in a long procedure.
      • You can easily reuse short, dedicated, single‐task procedures in other code in the future.
• Call a procedure. You execute a procedure by calling it from within your programming code.
  • • Master It How do you call a procedure?
    • Solution 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

• Pass information from one procedure to another. Sometimes a procedure requires that you pass it some information. For example, a procedure that searches text and makes some style changes to it will require that you pass the text you want to modify.

  Sometimes a procedure passes back information to the procedure that called it. For example, it might pass back a message describing whether the actions taken in the procedure were (or were not) accomplished successfully.

  • Master It What kind of procedure can pass back information to the caller?
  • Solution 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.
• Understand what classes are and what they're for. Contemporary computer programs employ classes for various reasons—to help organize large programs, to prevent programmers working in a group from stepping on each other's toes, to make code more easily reusable, to provide certain kinds of security, or as a substitute for the frowned‐upon public variables. But beginners sometimes have a hard time wrapping their minds around OOP concepts, particularly the relationship between classes and objects.
  • Master It What is the difference between a class and an object?

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

    1. A class is like a cookie and an object is like a cookie cutter.
    2. A class is like a programmer and an object is like a module.
    3. A class is like a blueprint and an object is like a house built from that blueprint.
  • Solution The answer is 3. A class is like a blueprint and an object is like a house built from that blueprint.
• Create a class. The VBA Editor employs a special kind of module for containing classes.
  • Master It How do you create a class module in the VBA Editor?
  • Solution There are three ways to create a class module:
    • Right‐click the name of the target project in the Project Explorer (or right‐click one of the items contained within the project). Then choose Insert ➢ Class Module from the context menu.
    • Click the Insert button on the Standard toolbar and choose Class Module from the drop‐down list.
    • Choose Insert ➢ Class Module from the menu bar.

Chapter 17: Debugging Your Code and Handling Errors

• Understand the basic principles of debugging. A major aspect of programming is testing your code. Debugging can be enjoyable if you think of it as a puzzle you can solve. But whether or not you enjoy it, debugging is essential if you want to preserve a reputation as a professional.
  • Master It When testing your code, try to imagine ways that the code could fail. Describe a situation that can produce unanticipated results.
  • Solution 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.

• Recognize the four different types of errors you'll create. Experts have concluded that there are four primary categories of error in programs.
  • Master It Name two of the four basic types of programming errors.
  • Solution Here are the four basic types of programming errors:
    • Language errors
    • Compile errors
    • Runtime errors
    • Program logic errors
• Employ VBA's debugging tools. The VBA Editor and VBA include a generous assortment of debugging tools to help you track down and remove bugs from your procedures. The main windows you'll employ for debugging are the Immediate window, the Locals window, and the Watch window.
  • • Master It The Watch window is especially useful because you can set watch expressions (also known as conditional breakpoints). Describe this debugging tactic.
    • Solution 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.

• Deal with runtime errors.
  • • Master It Error handlers are special statements and sections of code that detect and then manage runtime errors. What VBA statement detects a runtime error?
    • Solution VBA's On Error statement triggers when there is a runtime error, allowing you to write code that responds to the error.

Chapter 18: Building Well‐Behaved Code

• Understand the characteristics of well‐behaved procedures. Well‐behaved procedures don't annoy or alarm the user either during or after their execution.
  • Master It Name two ways programmers can write procedures that don't annoy users.
  • Solution Here are some ways programmers can avoid annoying users by their procedures' actions:
    • Make no durable or detectable changes to the user environment—other than changes the procedure is designed to make. In other words, restore the previous settings.
    • Present the user with relevant choices for the procedure and relevant information once the procedure has finished running.
    • Show or tell the user what is happening while the procedure is running.
    • Make sure whenever possible that conditions are appropriate for the procedure to run successfully—before the procedure takes any actions.
    • Anticipate or trap errors to avoid a crash. But if the procedure does crash, handle the situation as gracefully as possible and minimize damage to, or loss of, the user's work.
    • Leave users in the optimal position to continue their work after the procedure finishes executing.
    • Delete any scratch documents, folders, or other debris that the procedure created in order to perform its duties but that are no longer needed.
• Retain and restore the user environment. Users quite rightly don't appreciate it if your macro leaves the state of their application's or operating system's environment modified. Find ways to restore the user environment before your procedure finishes execution.
  • Master It 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?
  • Solution To store such information, you can use private variables, public variables, or custom objects.
• Let the user know what's happening. Particularly when a procedure is doing a lengthy “batch job” such as updating dozens of files, it's important to let the user know that the computer hasn't frozen. People need to be told that execution is continuing as expected even though nothing appears to be happening.
  • Master It Describe a way to let the user know that a procedure isn't frozen—that activity is taking place during execution.
  • Solution 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.
• Check that the procedure is running under suitable conditions. Another important element of creating a well‐behaved procedure is to check that it's running under suitable conditions. This ideal is nearly impossible to achieve under all circumstances, but you should take some basic steps.
  • Master It If a procedure accesses data from a file, name an error that could occur and, thus should be trapped.
  • Solution 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.
• Clean up after a procedure. A well‐behaved procedure avoids leaving unneeded files or other temporary items behind. In other words, a procedure should clean up after itself.
  • Master It Cleaning up involves three major tasks. Name one.
  • Solution The three main ways that a procedure cleans up after itself are as follows:
    • Undoing any changes that the procedure had to make to enable itself to run
    • Closing any files that no longer need to be open
    • Removing any scratch files or folders that the procedure has created to achieve its effects

Chapter 19: Exploring VBA's Security Features

• Understand how VBA implements security. Microsoft takes a multipronged approach to protecting users from malicious VBA code embedded in documents and capable of launching itself when the user simply opens the document.
  • • Master It Name two ways that users are protected from malicious VBA code.
    • Solution Users are protected from malicious VBA code in the followings ways:
      • The default file type for Office documents simply cannot contain any embedded macros at all (these files' filename extensions end in x, such as .docx).
      • Macro‐enabled documents can be stored in a trusted area on the hard drive.
      • The user can specify various trust settings for both macros and other executables, such as add‐ins and ActiveX controls. For example, the user can forbid the execution of any controls unless the user is first notified. Another setting prompts the user for permission before allowing a control to be loaded.
      • The user can modify a list of “trusted publishers”—companies whose documents are considered safe.
      • Developers can digitally sign their own projects, thereby making themselves “trusted publishers.”
      • The types of files that an application can access can be more specifically controlled via file blocking.
      • A Trusted Documents feature allows users to specify individual documents as reliable.
      • Files are scanned before being opened.
      • Files can be opened in a sandbox called Protected View.
• Sign a macro project with a digital signature. You can add a digital signature to your projects by creating your own certification, getting it from your company, or getting it from certification authorities such as VeriSign.
  • Master It Describe the limitations of self‐certifying a VBA macro project—without obtaining a certificate from your company or a commercial certification authority.
  • Solution The quickest and easiest way of getting a digital certificate is to create one yourself. However, this kind of certification works only 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.
• Get a digital certificate. Commercial certification authorities provide the greatest level of security, but their certification is also more difficult to attain than self‐certification or certification from your company.
  • Master It Name some of the ways you may be required to prove your identity when obtaining a digital signature from a commercial certification authority.
  • Solution 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.
• Choose the appropriate security level. When choosing the right security level to use VBA macros safely, you or a user of your code must achieve a balance. The security level must be set high enough to avoid malicious or incompetent code but low enough that it doesn't prevent you from running useful, safe code.
  • Master It 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:
    • Disable All Macros Without Notification
    • Disable All Macros With Notification
    • Disable All Macros Except Digitally Signed Macros
    • Enable All Macros With Notification
    • Enable All Macros
  • Solution There is no Enable All Macros With Notification option.
• Lock your code You can protect your source code in the VBA Editor from others. You can add a password to a project so that others can't open your VBA procedures for reading or modifying. (Although this feature might still be broken in Office 2019.)
  • Master It What is one drawback to locking your code?
  • Solution The lock requires an extra step to access the modules and forms in the project 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.

Chapter 20: Understanding the Word Object Model and Key Objects

• Understand Word's creatable objects. Word contains a set of creatable objects that VBA programmers will frequently employ in their code.
  • • Master It What is a creatable object?
    • Solution A creatable object is simply one that doesn't require you to use the term Application when invoking it. It's a kind of shorthand. 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.
• Work with the Documents collection and the Document object. The Documents collection represents all the currently open documents. Using VBA, you can manipulate this collection in a variety of ways.
  • Master It Here is the syntax for adding a new document to 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?
  • Solution The code is as follows:

            Documents.Add

• Work with the Selection object. The Selection object represents the current selection in the active document in Word. A zone can be selected by the user by dragging the mouse or by using various key combinations (such as pressing Shift and an arrow key). A selection can include one or more objects—one or more characters, one or more words, one or more paragraphs, a graphic, a table, and so on. Or, it can include a combination of these objects.
  • • Master It One kind of selection is described as a collapsed selection. What is that?
    • Solution 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.
• Create and use ranges. In Word, a range is a named area of a document with a defined starting and ending point. The typical use of ranges in Word VBA is similar to how you use bookmarks when working interactively with Word: to mark a location in a document that you want to be able to access quickly or manipulate easily.
  • • Master It Although a range is similar to a bookmark, what is the significant difference between them?
    • Solution 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 accessed at any time (whether or not a procedure is running).
• Manipulate options. Word contains many options that can be manipulated from within VBA.
  • • Master It In Word, one object controls many of the options. This object has dozens of properties but no methods. Name this object.
    • Solution The Options object controls many of the options in Word.

Chapter 21: Working with Widely Used Objects in Word

• Use Find and Replace via VBA. Word's Find and Replace utilities are frequently valuable to the VBA programmer. You'll want to master them and also some subtleties associated with their use.
  • Master It Sometimes when replacing, you need to go through a document more than once—using a loop structure. Why would you ever need to repeatedly search and replace the same document? Doesn't the Replace All setting in fact replace all?
  • Solution 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 or more spaces.) 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 through a loop would reduce it to a two‐space string, requiring yet a third pass through the loop to achieve the desired single space. This same situation can apply to multiple‐paragraph spacing (multiple blank lines) and tabs.
• Work with headers, footers, and page numbers. All Word documents contain headers and footers, even if they are empty. In addition, you can insert various types of headers and footers.
  • Master It Name two types of headers you can use in a Word document.
  • Solution 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.
• Manage sections, page setup, windows, and views. Among the various ways you can view a document, you sometimes want to have the document automatically scroll to a particular table, graphic, or other target.
  • Master It What method of the Window object can be used to easily accomplish this task?
  • Solution The ScrollIntoView method of the Window object moves the view to a target you specify.
• Manipulate tables. When you need to manage tables in Word documents, you can employ VBA to work with the Table object to represent a single table. If there is more than one table, they are referenced by a collection of Table objects.
  • Master It Name two important and useful objects within the Tables collection or the Table object.
  • Solution Some of the most useful objects within a Table object or a Tables collection are as follows:
    • The Rows collection contains the rows in the table. Each row is represented by a Row object.
    • The Columns collection contains the columns in the table. Each column is represented by a Column object.
    • The Cell object provides access to a specified cell directly from the Table object. You can also reach the cells in the table by going through the row or column in which they reside.
    • The Range object provides access to ranges within the table.
    • The Borders collection contains all the borders for the table.
    • The Shading object contains all the shading for the table.

Chapter 22: Understanding the Excel Object Model and Key Objects

• Work with workbooks. You often need to create a new, blank workbook in a macro (mimicking a user clicking the File tab on the Ribbon, then clicking the New button). And writing code that accomplishes this is not difficult. It requires only two words.
  • • Master It What code would you write to create a new, blank notebook?
    • Solution To create a blank workbook, omit the Template argument, like this:

               Workbooks.Add

• Work with worksheets. Most workbooks you access via VBA will contain one or more worksheets, so most procedures will need to work with worksheets—inserting, deleting, copying, or moving them, or simply printing the appropriate range from them.
  • • Master It Name the object you use in VBA code to represent a worksheet.
    • Solution Each worksheet is represented by a Sheet object. The Sheet objects are contained within the Sheets collection.
• Work with the active cell or selection. In a procedure that manipulates a selection that the user has made, you'll typically work with either the active cell or the current selection.
  • • Master It What is the difference between the active cell and a selection?
    • Solution The active cell is always a single cell, but the selection can either be a single cell or encompass multiple cells or other objects.
• Work with ranges. Within a worksheet, you'll often need to manipulate ranges of cells. Excel includes a special kind of range—represented by the UsedRange property.
  • • Master It What is unique about UsedRange?
    • Solution 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.
• Set options. Word employs an Options object to contain most of the options that you find in the Word Options dialog box (click the File tab on the Ribbon, then click Options). Excel uses a different object to contain its options.
  • • Master It From which object do you access most of Excel's options?
    • Solution 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.

Chapter 23: Working with Widely Used Objects in Excel

• Work with charts. You can create either full chart sheets or embedded charts within an ordinary Excel worksheet.
  • • Master It What object is used in a procedure to represent an embedded chart?
    • Solution VBA uses the ChartObject object to represent an embedded chart on a worksheet.
• Work with windows. To open a new window on a workbook, you use the NewWindow method of the appropriate Window object.
  • • Master It Does the NewWindow method take any arguments?
    • Solution 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

• Work with Find and Replace. When working with the Find and Replace features in Excel, you need to be aware of a phenomenon known as persistence.
  • • Master It What is persistence, and why should it concern you?
    • Solution 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.

Chapter 24: Understanding the PowerPoint Object Model and Key Objects

• Understand PowerPoint's creatable objects. Creatable objects are commonly used objects that can be employed in VBA code without requiring that you qualify them with the Application object. You can leave that word out of your code; it's optional, and rarely used.
  • • Master It Name one of the objects or collections that are creatable in PowerPoint macros.
    • Solution Objects or collections that are creatable in PowerPoint procedures include the ActivePresentation object, the Presentations collection, the ActiveWindow object, and the SlideShowWindows collection.
• Work with presentations. You can create a new presentation programmatically, but PowerPoint generates an annoying flicker on most systems while it brings the new presentation into view. You can block this unpleasant, strobe‐like effect to avoid disturbing your audience.
  • • Master It 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?
    • Solution 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.
• Work with windows and views. To get the PowerPoint window into the state you want, you'll often need to work with the window and with the view.
  • • Master It PowerPoint uses two types of windows. What are they?
    • Solution PowerPoint uses document windows and slide‐show windows.
• Work with slides. Once you have created or opened the presentation you want to manipulate, you can access the slides it contains by using the Slides collection. This collection contains a Slide object for each slide in the presentation. Each slide is identified by its index number, but you can also use object variables to refer to slides or to assign names to slides.
  • • Master It Why would you want to assign names to slides rather than using the default index numbers that are automatically assigned to the slides?
    • Solution 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 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.
• Work with masters. Before attempting to manipulate a master in your code, you should determine whether the master actually exists in the presentation.
  • • Master It How do you find out whether a presentation has a title master?
    • Solution 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 check, like this:

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

Chapter 25: Working with Shapes and Running Slide Shows

• Work with shapes. PowerPoint VBA provides many ways to access and manipulate shapes.
  • • Master It Describe what the following line of code does:

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

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

• Work with headers and footers. Using PowerPoint headers and footers can be a convenient way to provide continuity for presentations as well as to identify each element.
  • • Master It 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?
    • Solution Slides can't have headers, only footers. Notes pages or handouts can have headers.
• Set up and run a slide show. To create a custom slide show, you use the Add method of the NamedSlideShows collection.
  • • Master It 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 what they do.
  • Solution The components are as follows:
    • expression is a required expression that returns a NamedSlideShows object.
    • Add is the method (of the NamedSlideShows object) that adds the slides to the new show.
    • Name is a required String argument that specifies the name to assign to the new custom slide show.
    • SafeArrayOfSlideIDs is also a required argument. It's a Variant argument that specifies the numbers or names of the slides to include in the custom show.

Chapter 26: Understanding the Outlook Object Model and Key Objects

• Work with the Application object. VBA uses two major Outlook objects that most users wouldn't recognize from working with the Outlook user interface alone.
  • • Master It 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?
    • Solution 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.

• Work with messages. To work with the contents of a message in VBA, you set or get various properties.
  • • Master It Name one of the most widely useful properties employed when manipulating the contents of a message in a procedure.
    • Solution The most commonly useful properties when accessing a message in VBA are To, CC, BCC, Subject, Body, BodyFormat, and Importance.
• Work with calendar items. You can create new calendar appointment items via VBA.
  • • Master It 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?
    • Solution 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)

• Work with tasks and task requests. You can assign a task to a colleague and then add one or more recipients. You can then send the task to your colleague and, optionally, the additional recipients.
  • • Master It What methods do you use to assign, add, and send a task to others?
    • Solution To assign, add, and send a task to others, use the Assign, Add, and Send methods.

Chapter 27: Working with Events in Outlook

• Create event handlers. Event handlers are procedures that contain code that responds to an event. In other words, if a user modifies one of their contacts, an event can detect this modification and execute code you've written to respond to the modification.
  • • Master It Event‐handler procedures are unlike ordinary macro procedures in several ways. Name one of the differences.
    • Solution 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:
      • An event handler must be located within a class module, not an ordinary macro module.
      • An object variable must be declared that can point to the event.
      • The object variable must be initialized (connected to an object).
      • You cannot simply test the event handler by pressing F5 to run it directly (you must instead run it indirectly by triggering the event it's designed to service—for example, by modifying a contact in the Contacts folder).
• Work with application‐level events. Application‐level events happen to Outlook as a whole.
  • • Master It Name an important Outlook application‐level event.
    • Solution The Startup and Quit events are common application‐level events.
• Work with item‐level events. Outlook has two primary kinds of events.
  • • Master It What are the two types of events in Outlook? And how do they differ?
    • Solution 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.
• Understand the Quick Steps tool. With Outlook's Quick Steps feature, you can build a set of actions the user can later trigger to automate a frequently repeated task. This is quite similar to the nature and purpose of macros.
  • • Master It What is the key difference between a Quick Step and a macro?
    • Solution Quick Steps are not nearly as flexible and powerful as macros. To create a Quick Step you simply choose from a list of common actions to combine two or more of them into a little “program.”

Chapter 28: Understanding the Access Object Model and Key Objects

• Become familiar with how VBA works in Access. Access allows you to write macros in a VBA Editor using VBA code. But it also features a legacy Macro Designer utility (formerly known as the Macro Builder) with which you create an entirely different kind of macro, what we've been calling an Access‐style macro.
  • • Master It 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.
    • Solution 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 a 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.

• Open and close databases. Access permits you to open a database in several ways.
  • • Master It Two common commands that open a database in Access are OpenCurrentDatabase and OpenDatabase. What is the difference between these two commands?
    • Solution 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.
• Work with the Screen object. You became familiar with using ActiveDocument objects in Word to access the document that currently has the focus. Or, you used the ActivePresentation object to work with whichever presentation happened to be active in PowerPoint. Access, however, employs the Screen object as the parent of whatever object has the focus.
  • • Master It 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?
    • Solution The object can be a form, a report, or a control.
• Use the DoCmd object to run Access commands. Many of the tools that Access makes available to users, such as printing a report or maximizing a window, are also available to the programmer via the methods of the DoCmd object.
  • • Master It The DoCmd object has 66 methods in Office 2016. Describe the purpose of the DoCmd object's Beep method.
    • Solution 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.

Chapter 29: Accessing One Application from Another Application

• Use Automation to transfer information. Automation sets up communication between two applications, designating one of them as the server and the other as the client.
  • • Master It Of the various ways to communicate between applications, which is generally the most effective?
    • Solution Automation is the most powerful and efficient way to communicate between applications.
• Use the Shell function to run an application. Although the Shell function can prove useful in a variety of inter‐application communication situations, Shell can also present the programmer with a timing problem.
  • • Master It Describe the timing issues that the Shell function raises, and describe a good solution to this problem.
    • Solution 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.

• Use data objects to store and retrieve information. This book has described a variety of ways to store and retrieve information when working with the VBA language. Using data objects is one of these useful techniques.
  • • Master It How is the data‐object technology special as a way of storing and retrieving information? What can a data object do that's unique?
    • Solution The data object has the ability to copy information to and retrieve information from the Windows Clipboard.
• Communicate via DDE. Dynamic Data Exchange (DDE) is a technology introduced back in May 1990, with Windows 3.0. Use it if other, more efficient communication technologies are unavailable to the applications you are working with.
  • • Master It Not all applications support DDE. Which Office 2019 applications don't support DDE communication?
    • Solution PowerPoint and Outlook do not support DDE.
• Communicate via SendKeys. Using SendKeys is a fairly simple, but rather awkward and limited, way to communicate between applications. It imitates typing in keystrokes, thereby allowing your code to manipulate an application by accessing some of its features using, for example, Alt+key combinations, such as Alt+F to open the File tab on the Ribbon.
  • • Master It SendKeys was historically most often employed to open an application's menus and select an option from the menus. Since Vista, Windows applications have largely done away with traditional menus, so is SendKeys of even more limited use now than in the past?
    • Solution No, SendKeys must simply send some different keystrokes to access recent Office applications' features. Many of the features of the Ribbon, 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 older approach of simultaneously pressing the Alt key while pressing other keys (such as Alt+V to open a View menu), in today's applications you press and release Alt by itself to activate the Ribbon, then you press the W key to switch to the View tab on the Ribbon. At this point, additional keystrokes can be sent to trigger the various options on the View tab.