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.
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.
Turn on the macro recorder in Word and create a macro that moves the insertion cursor up three lines. Then turn off the macro recorder and view the code in the Visual Basic Editor.
Click the Developer tab on the Ribbon. Click the Record Macro button in the Developer tab, then give the macro a name, such as temporary and, if necessary, change the Store Macros In target to All Documents (Normal.dotm). Click OK to close the Record Macro dialog. This begins the recording process. Press the up arrow key three times. That's what you want to record.
Now click the Stop Recording button in the Developer tab or on the status bar at the bottom of the screen. Press Alt+F11 to open the Visual Basic Editor. Open Normal, then Modules, and double-click NewMacros in the left (Project) pane of the Editor. Scroll the code window until you locate your new macro (it will have been added to the bottom of the collection of macros. The macro code should look something like this:
Sub temporary() ' ' temporary Macro ' ' Selection.MoveUp Unit:=wdLine, Count:=3 End Sub
You can trigger a macro using two convenient methods: clicking a button in the Quick Access Toolbar or using a keyboard shortcut. You are responsible for assigning a macro to one or both of these methods.
Assign an existing macro to a new Quick Access Toolbar button.
Right-click the Quick Access Toolbar. Choose Customize Quick Access Toolbar. In the Choose Commands From list, select Macros. Click a macro's name to select it in the list. Click the Add button to insert this macro's name in the Customize Quick Access Toolbar list. Click OK to close the dialog. Now you see a new button on the Toolbar that when clicked launches your macro.
Macros are most efficiently triggered either 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.
Execute a macro from within the Visual Basic Editor.
Open the Visual Basic Editor by pressing Alt+F11. Click to put the insertion cursor anywhere in the code within one of your macros in the right pane (between the Sub
and End Sub
lines of code). Press F5 to execute that macro.
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 pressing Alt+F8).
Temporarily remove a macro, then restore it, using the Visual Basic Editor.
Press Alt+F11 to open the Visual Basic Editor. The code within this editor is just text, similar to Notepad. Locate a macro within the Editor. Each macro is the code that starts with Sub
and concludes with End Sub
. So drag your mouse to select the entire macro, including its Sub...End Sub
lines. (Note that the Editor displays a horizontal line between each macro, so you can easily see where each macro's code begins and ends.) Press Ctrl+C to copy the macro's code. Then press Delete to delete the macro. Close the Visual Basic Editor and press Alt+F8 to open the Macros dialog and see your list of macros. Scroll this list and notice that the macro you deleted in the Editor no longer exists.
Now close the Macros dialog. Restore the macro by pressing Alt+F11 to reopen the Editor. Then click on a blank line at the very top of the right pane where the various macros' code is displayed. However, you want to put the blinking insertion cursor outside of any other macro's Sub...End Sub
code area. Finally, press Ctrl+V to paste the macro you previously deleted. It's restored. Remember, this Visual Basic Editor merely accepts ordinary, plain text for its source code—so you can freely cut, copy, and paste code. In fact, from this book's website you can copy and paste all the code examples from the book at www.sybex.com/go/masteringvba2010
.
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.
Open the Visual Basic Editor in Word and create a macro.
Press Alt+F11.
You edit and test macro code in the Code window of the Visual Basic Editor.
Open the Visual Basic Editor and display a particular macro in the Code window.
Press Alt+F8 to open the Macros dialog box, click the name of the macro you want to work with, then click the Edit button.
The Project Explorer window displays a tree of current projects. You can choose between viewing only the files or the folders and files.
Switch between folder and contents view in the Project Explorer.
Click the icon (a picture of a folder) on the right side just under the Project Explorer's title bar.
You can specify a project's name, an associated Help file, and other qualities of a project.
Lock a project so others can't modify or even read its contents.
Right-click the project's name in the Project Explorer to open the shortcut menu. Choose <ProjectName> Properties. Click the Protection tab and select the Lock Project For Viewing text box. In the Password To View Project Properties group box, enter a password for the project in the Password text box, and then enter the same password in the Confirm Password text box. Click the OK button and close the project.
The Visual Basic Editor can be customized in many ways, including personalizing classic menus and toolbars.
Undock the Properties window and change its size.
Double-click the title bar of the Properties window to undock it. Position your mouse pointer in the lower-right corner until the pointer changes to a double arrow. Then drag the window to resize it. Restore the Properties window to its default docked position by double-clicking its title bar again.
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.
Open a macro; then step through it to see if anything goes wrong.
Press Alt+F8 to open the Macros dialog box. Select the name of the macro that you want to test, and then click the Step Into button. The Visual Basic Editor opens and the insertion cursor is located within the chosen macro, thereby making it the currently active one (the one with which the editor's features—such as the Step tool—will work). The first line is highlighted, indicating that it is the next line that will execute. Press F8 to execute the first line, then press F8 repeatedly to step down through each line of code. See if any problems occur—either problems you observe in the behavior of the macro in your application or problems that VBA notifies you of by displaying an error message box.
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.
Set a breakpoint in, and add a comment to, a macro.
Set a breakpoint by clicking in the gray margin indicator bar to the left of a line of code where you want to halt execution. The line of code on which you set a breakpoint is shaded brown by default. You can set as many breakpoints as you wish. Now type in a line such as 'The following With block describes the format for this new paragraph. Because you've started this line with a single-quote symbol, it will be ignored when the editor executes the macro.
Make some changes to a Word macro.
With the Visual Basic Editor open, choose a macro and modify it.
Click a line between the Sub
and End Sub
lines that envelope the macro you want to modify. This puts the insertion cursor where you want it. Now simply type in whatever adjustments you want to make to the code.
How you arrange the various components of the Visual Basic Editor is your personal choice, but while using this book, it's easiest if you set up the editor to resemble the way it appears in the book's figures. Besides, this arrangement is quite close to the default layout, which has proven to be the most effective one for the majority of programmers (according to various focus groups and polls) for the decades that Visual Basic has been employed an editor.
Press a single key to display, then hide, the Properties window.
Press F4 to display the Properties window. Click the close button to dismiss it.
Using the Help feature in any VBA-enabled application allows you to find code examples that you can copy and paste into your own code.
Open the Code window and use Help to find a code example.
Press F7 to open the Code window, and then press F1 to open Help. Click the Word Object Model Reference link. Scroll down until you see the Line Numbering Object link. Click it, then click the Line Numbering Object link in this newly displayed information. You'll find a code example that adds line numbers to the active document. Select and copy this code, then paste it into the Visual Basic Editor. Note that many code examples are not full procedures but merely snippets of code, so it's up to you to add the Sub...End Sub
to turn them into actual procedures. Your final procedure in the Visual Basic Editor should look like this:
Sub AddLines() With ActiveDocument.Sections(1).PageSetup.LineNumbering .Active = True .CountBy = 5 .RestartMode = wdRestartPage End With End Sub
I named it AddLines
; name it whatever you wish. But keep in mind that to be able to execute it—for it to be a formal macro—you must include the Sub...End Sub
.
Press F5 to try it out, and then look at the document and see the line numbers.
Remove the line numbers from the document by clicking the Line Numbers option in the Page Setup section of the Page Layout tab on the Ribbon and choosing None.
Certain procedure names are special. In the previous exercise, you added line numbering and gave that procedure a name of your own choice. But some procedure names have a special meaning—they represent an event. They will execute automatically when that event takes place (you don't have to run events by choosing Run from the Macro dialog box or by assigning the macro to a keyboard shortcut or Quick Access Toolbar button). One such event is Excel's Workbook_Open
procedure.
Display a message to the user when a workbook is opened in Excel.
Press F7 to open the Code window in Excel's Visual Basic Editor. Locate VBAProject (
theprojectname)
in the Project Explorer, double-click it to open its contents, and then double-click ThisWorkbook
under the project. An empty Sub
(an open
event) appears in the Code window. Type the code in boldface into the procedure:
Private Sub Workbook_Open()
MsgBox "Opened"
End Sub
Close and then reopen the workbook to see the message automatically displayed.
As you type a procedure, the Visual Basic Editor provides you with lists of objects' members (the Auto List Members feature) and with syntax examples, including both required and optional arguments (the Auto Quick Info feature). These tools can be invaluable in guiding you quickly to the correct object and syntax for a given command.
Use the Auto List Members and Auto Quick Info features to write a macro that saves a backup copy of the currently active presentation.
Create a new presentation based on a template of your choosing. Press Alt+F11 to open the Visual Basic Editor. Choose Tools
Right-click the name of the presentation in the Project Explorer, then choose Insert Module so you'll have a place to write a macro.
Type the following line of code to create a macro:
Sub SaveTemp()
When you press the Enter key, the Visual Basic Editor automatically adds End Sub
.
Now type Application. in the macro. As soon as you press the period (.), a list of properties and methods of the Application
object is displayed. Choose ActivePresentation
. Again, when you press the period key, a list of the ActivePresentation
object's members appears. Choose SaveCopyAs
. Press the spacebar to insert a space after SaveCopyAs
; you'll see that this method has only one required argument: a filename string (meaning you must provide a literal filename within quotes or a string variable or constant). In this case, just type "temporary", and then press the F5 key to execute the macro. You didn't specify a path, so you can find your file in your My Documents folder, where it is saved by default.
Your macro should look like this:
Sub SaveTemp() Application.ActivePresentation.SaveCopyAs "temporary" End Sub
Although Access includes a variety of macro-related features that are unique (such as its Macro Builder/Designer), its Visual Basic Editor is quite similar to the Visual Basic Editors in the other Office 2007 applications.
Open the Visual Basic Editor in Access and write a macro that displays today's date using the Date
function rather than the Now
function. Use the Access Visual Basic Editor Help system to understand the difference between these two functions.
In Chapter 4, you wrote a macro in Access that displays today's date and time. Here you will display the date only.
Start Access.
Click the Blank Database button, and then click the Create button.
Press Alt+F11 to open the Visual Basic Editor.
Right-click the database name in the Project Explorer, then choose Insert Module to open a new module in the Code window.
In the Code window, type the following macro:
Sub ShowDateOnly() MsgBox Date End Sub
Press F5 to execute the macro. You should see a message box that displays the current date.
VBA includes two types of procedures, used for different purposes.
Name the two types of procedures used in VBA (and indeed in most computer languages), and describe the difference between them.
A function always returns a value after it finishes executing. For example, you can display a message box, and when the user clicks a button to close that box, the value returned represents which button the user clicked. When the statement that called (invoked) the MsgBox
function gets that value, it can respond in whatever way the programmer finds appropriate. Often it's something like this: If the user clicked OK, then check the spelling, or if the user clicked Cancel, then close the document.
A subprocedure (or subroutine) does not return a value. It does a job, then quits without sending back any information to the code or action that triggered it. Events are always subprocedures.
A procedure is a container for a set of programming statements that accomplish a particular job.
Write a subprocedure in the Visual Basic Editor that displays a message to the user. Then execute that subprocedure.
In the Visual Basic Editor Code window, type code similar to this:
Sub showmessage() MsgBox "Hi, user." End Sub
Execute this code by clicking within the subprocedure to position the insertion point there and then pressing F5.
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.
Open the Immediate window, type in a line of code, and then execute that line.
Press Ctrl+G to open the Immediate window, then type a line of VBA code. Press the Enter key when you've finished executing that statement.
Object-oriented programming (OOP) means creating objects to use in your programming. OOP has become the fundamental paradigm upon which large programming projects are built. Generally speaking, macros are not large and therefore don't profit from the clerical, security, and other benefits that OOP offers—particularly for people who write large applications as a team.
However, code 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) are written by great groups of people. And these libraries themselves are huge. There must be a way to organize their objects and functions—to categorize them and allow you to execute the methods and manage their properties and arguments. As a result, another aspect of OOP—taxonomy—becomes an important issue even when writing brief macros.
Look up the Document
object in the Visual Basic Editor's Help system; then look at its methods.
With the Visual Basic Editor the active window, choose Help
Maximize the Help dialog box so you can see the large lists of objects and members. Scroll down until you see the Document
object link. Click that link, and then click the link Document Object Members. As you scroll down, you'll see the many methods, properties, and events for this object. Click any of them to get a description, syntax, and, usually, a helpful code example you can cut and paste into your Code window.
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. And a file can, over time, contain different papers, just as the value contained in a variable can vary. 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.
Explicitly declare a variable named CustomersAge
.
This code explicitly declares a variable:
Dim CustomersAge
If you decided to declare the variable as an Integer type, it would look like this:
Dim CustomersAge As Integer
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 also restrictions such as not using special characters.
This variable name cannot be used for two reasons. Fix it so it is a legitimate variable name:
Dim 1Turn! as Integer
Dim Turn as Integer
You can't begin a variable name with a digit, nor can you use an exclamation point anywhere in the name.
Variables have a range of influence, depending on how you declare them.
Create a variable named AnnualSales
that will be available to any procedure within its own module but not to other modules.
Use the Private
command to declare a module-level variable. It will be accessible from any procedure in its module but not from other modules in its project. Here is an example:
Private AnnualSales As Integer
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.
Define a string constant using the Dim
command. Name your constant FirstPrez
, and assign it the value George Washington
.
This code line defines a constant, and assigns a value to it:
Const FirstPrez As String = "George Washington"
Enumerations provide a handy name for each item in a list, often a list of properties.
In the Project Explorer, click the ThisDocument
object in the Normal project to select it. Then locate the JustificationMode
property in the Properties window, and open its list of enumerated values.
You'll see the drop-down list of enumerated values for the JustificationMode
property, as illustrated in the following screen shot.
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.
What is the difference between an array and an ordinary variable?
An ordinary variable (formally called a scalar variable) can contain only a single value. However, an array can contain multiple values, each identified by its own index number.
When you create a new array, you declare it and, optionally, specify the number of values it will contain.
There are four keywords that can be used to declare arrays. Name at least three of them.
Arrays can be declared using the same keywords that are employed to declare ordinary variables: Dim, Private, Public
, and Static
.
If you want to resize an existing dynamic array, you can redimension 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?
To preserve values when redimensioning an array, use the Preserve
command, like this:
ReDim Preserve arrTestArray(5)
You can erase all the values in a fixed-size array or completely erase a dynamic array.
Write a line of code that erases an array named arrMyArray
.
Use the Erase
statement with the name of an array you want to erase. The following statement erases the contents of the fixed-size array named arrMyArray
:
Erase arrMyArray
An array is a type of variable, and you may occasionally need to check whether a particular variable name denotes an array or an ordinary scalar variable (a variable that isn't an array).
Which built-in function can you use in VBA to find out whether a variable is an array or an ordinary, single-value variable?
Use the IsArray
function with the variable's name to see if a variable is an array. For example, the following statement checks the variable MyVariable
to see if it's an array:
If IsArray(MyVariable) = True Then
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.
Name a popular, understandable, but relatively inefficient sorting technique.
The bubble sort is easy to visualize but relatively inefficient.
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.
Name two common ways to search an array.
You can use either the simple linear search or the binary search, which requires that an array be sorted first.
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.
By using creatable objects, you can often omit the Application
object when referencing it in code. What are creatable objects?
Because you'd have to go through the Application
object to get to pretty much anything in the application, most applications include a number of creatable objects—objects that you can access without referring explicitly to the Application
object. These creatable objects are usually the most-used objects for the application, and by using them, you can access most of the other objects without having to refer to the Application
object. For example, Excel exposes the Workbooks
collection as a creatable object, so you can use the following statement, which doesn't use the Application
object:
Workbooks(1).Sheets(1).Range("A1").Select
Collections are containers for a group of related objects, such as the Documents
collection of Document
objects.
Are collections objects? Do they have methods and properties?
Yes, collections are themselves objects—in the same sense that a flower vase contains a group of flower objects but the vase, too, is an object. A collection can have its own properties and methods, though collections usually have fewer properties and methods than other objects.
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 feature, and the Auto List Members tool.
How do you employ Auto List Members to find out which properties and methods are available for Word's Document
object?
Type Document. in the Code window, and as soon as you type the period, a list of the Document
object's members appears.
You can create variables that contain objects rather than typical values like strings or numbers.
What keywords do you use to declare an Object variable?
The same keywords are used to declare Object variables as you use for any other variable. To create an Object variable, declare it using a Dim, Private
, or Public
statement. For example, the following statement declares the Object variable objMyObject
:
Dim objMyObject As Object
However, you assign a value to an ordinary variable by using the = sign:
strMyString = "Harry"
But you assign an object to an Object variable using the Set
command in addition to the = sign, like this:
Dim wksSheet1 As Worksheet
Set
wksSheet1 = ActiveWorkbook
A function is 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
. Chapter 10 explores how to write functions. But 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.
A function is quite similar to a subroutine, but there is a significant difference. What is it?
Subroutines don't return a value; functions do.
In a macro you can call a built-in function by merely typing in its name and providing any required arguments.
You can combine multiple functions in a single line of code. The MsgBox
function displays a message box containing whatever data you request. The only required argument for this function is the prompt. The Now
function returns the current date and time. Write a line of code that calls the MsgBox
function and uses the Now
function as its argument.
MsgBox Now
The MsgBox
function displays a message box, which, in this case, is the date and time returned by the Now
function.
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.
What built-in function is used quite often to display information in a dialog box to the user while a procedure runs?
Both the MsgBox
and InputBox
functions are used to display information to the user in a dialog box.
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.
What built-in function would you use to convert a string such as "12"
(which, in reality, is two text characters, the digits 1 and 2) into an integer data type, the actual number 12, that you can manipulate mathematically.
The built-in function CInt
transforms other data types into an Integer type. Here's an example:
intMyVar = CInt(varMyInput)
VBA includes a full set of functions to manage text and date data.
Which built-in function would you use to remove any leading and trailing space characters from a string. For example, you want to turn
"this "
into
"this"
Use the Trim
function.
Arguments can be passed from the calling code to a function in one of two ways: by reference or by value.
Describe the difference between passing data by reference and passing data by value?
The memory address of the actual value is passed to the function when passed the data is by reference. This means that the value can be changed by the function. When the data is passed by value, a copy of the data is sent to the function, leaving the original data unmodifiable by the called function. By reference is the default.
You can write, and save (File
Create a function that displays the current year in a message box. This function will require no arguments, nor will it return any value.
This function displays the current year:
Function ShowYear() MsgBox (Year(Now)) End Function
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.
Write a function that displays the number of hyperlinks in the currently active document. Use Word's Hyperlinks
collection to get this information.
This function displays the number of hyperlinks in a document:
Function FindHyperCount() MsgBox (ActiveDocument.Hyperlinks.Count) End Function
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.
Using the Sheets
collection of Excel's ActiveWorkbook
object, write a function that displays the number of sheets in the current workbook.
This function displays the number of sheets in a workbook:
Function SheetsCount() MsgBox (ActiveWorkbook.Sheets.Count) End Function
PowerPoint's object model includes an ActivePresentation
object, representing the currently selected presentation. Functions can make good use of this object and its members.
Write a function that returns how many slides are on a presentation. Pass the ActivePresentation
object as an argument to this function; then you display the number of slides the presentation contains. Call this function from a subroutine.
This function—called by a subroutine—displays the number of slides in a presentation:
Option Explicit Function CountSlides(objPresentation As Presentation) As Integer CountSlides = objPresentation.Slides.Count End Function Sub SeeNumber() MsgBox (CountSlides(ActivePresentation)) End Sub
Notice that your CountSlides
function is called from within the SeeNumber
sub. The ActivePresentation
object is passed as an argument. The CountSlides
function's argument list includes a variable of the Presentation
type. And the entire function is defined as an Integer type, meaning it will pass back integer data to the caller. CountSlides
is assigned the integer value provided by the Count
method and thus returns this value to the calling subroutine.
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.
Write a function that closes Access by using the DoCmd
object's Quit
method. Ensure that all data is saved by employing the acQuitSaveAll
constant as an argument for the Quit
method.
This function closes Access with the DoCmd
object's Quit
method:
Function QuitApp() DoCmd.Quit (acQuitSaveAll) End Function
Comparison operators compare items using such tests as greater than or not equal to.
Write a line of code that uses a less than comparison to test whether a variable named Surplus
is less than 1200.
Here's an example of the less than comparison operator:
If Surplus < 1200 Then
You can compare strings using less than and more than comparison operators.
What symbol do you use to determine if VariableA
is lower in the alphabet than VariableB
?
You use the less than symbol <, like this:
If VariableA < VariableB Then
To test multiple conditions, you use VBA's logical operators to link the conditions together.
Name two of the most commonly used logical operators.
The most often used logical operators are And, Or
, and Not
. Tests can be combined using these operators, like this:
If A < B AND
C = F Then
If
statements.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
.
Write an If... Else... End If
block of code that displays two message boxes. If the temperature (the variable Temp
) is greater than 80, tell the user that it's hot outside. Otherwise, tell the user that it's not that hot.
Your code may vary somewhat from this, but see if you've followed the basic structure:
Sub tempShow() Dim Temp As Integer
Temp = 66 If Temp > 80 Then MsgBox "Hey, it's hot outside!" Else MsgBox "It's not that hot." End If End Sub
Select Case
statements.Select Case
structures can be a useful alternative to If
blocks.
When should you use a Select Case
structure?
Select Case
is often more readable than a lengthy, complex If... ElseIf... ElseIf
... multiple-test decision-making block.
Use the Select Case
statement when the decision you need to make in the code depends on one variable or expression that has more than two or three different values that you need to evaluate.
Loops come in very handy when you need to perform a repetitive task, such as searching through a document for a particular word.
What is the alternative to looping if you are carrying out repetitive tasks in a macro?
You can copy the repeated code, then paste it into the Visual Basic Editor as many times as you want to repeat the task. Programmers, however, frown on repeated code because they consider it a redundancy, which it often is. If some behavior needs to be repeated, you can almost always employ some form of loop structure.
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.
Write a For...Next
loop that counts up to 100, but use the Step
command to increment by twos.
This code increments its counter variable (i) by twos:
For i = 1 To 100 Step 2 Next
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.
There are two types of Do
... loops. Do While... Loop
and Do Until... Loop
loops test a condition before performing any action. What is the other type?
The second type of Do
... loop includes loops that perform an action before testing a condition. Do... Loop While
and Do... Loop Until
fall into this category.
You can put loops inside other loops.
Think of a programming task where nested loops would be useful.
Nested loops would come in handy if you're accessing a multidimensional array or a table of data, for example. You could use one loop to search through each row in the table and another loop to search through all the columns. The example of nested loops given in this chapter is that you need to create a number of folders, each of which contains a number of subfolders. Structurally, this subfolders-within-folders example resembles a multidimensional array or a table of data.
An infinite (or endless) loop causes your macro to continue execution indefinitely—as if the macro had stopped responding and was "frozen."
How do you avoid creating an infinite loop?
Be sure that it is possible for your loop to terminate at some point. Ensure that a condition will occur that ends the looping.
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.
Write a small sub in the Visual Basic Editor that displays the current date and time in the status bar.
This procedure shows how to display information in the status bar.
Sub Experimentation_Zone() Application.StatusBar = Now End Sub
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.
Write a small sub in the Visual Basic Editor that displays the current date and time using a message box.
Here's how to display information in a message box.
Sub Experimentation_Zone() MsgBox Now End Sub
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.
Write a small sub in the Visual Basic Editor that asks users to type in their name. Use the InStr
function to see if there are any space characters in the returned string. If not, it means either they are Madonna or they have typed in only one name—so display a second input box telling them to provide both their first and last names.
You can handle this several ways, but this example code uses a Do...Loop Until
structure to repeatedly display an input box until the user types in at least two words:
Sub Get_Name() Dim response As String response = InputBox("Please type in your full name:", _ "Enter Name") If Not InStr(response, " ") Then 'found no space character Do response = InputBox _ ("You entered only one name. Please type in your full name:", _ "Enter First and Last Names Please") Loop Until InStr(response, " ") End If End Sub
For even moderately complex interaction with the user, message and input boxes are often too limited. They provide, for example, only a single answer. So you can't conveniently use an input box to ask for multiple data—such as address and phone number—without displaying multiple input boxes.
In addition to the limitations on the amount of information you can retrieve from the user, name the other major limitation of message and input boxes.
You are limited in the formatting and the amount of information you can display to the user.
Custom dialog boxes—user interfaces you design in the Visual Basic Editor—are often necessary 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.
Which VBA statement would you use to stop a macro from continuing execution?
The End
command halts execution.
You use the Visual Basic Editor to both design a custom dialog box and write code for macros. You can attach the various controls on a dialog box to code behind the dialog box.
How do you switch between the form design window (sometimes called the object window) and the Code window in the Visual Basic Editor?
The easiest way is to press F7 to display the Code window and press Shift+F7 to display the design window. However, you can also use the View menu, or double-click the module name in the Project Explorer (to switch to design mode), or double-click the form or one of its controls (to switch to the Code window).
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).
How do you add a command button to a custom dialog box?
If the Visual Basic Editor Toolbox isn't visible, click the form. Or you can choose View
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? Dialog boxes often display objects with which users can communicate their wishes to your code. Therefore, you write code that explores the values the user enters into controls and responds to whatever buttons the user might click.
Create a small custom dialog box that displays a message in a label control saying, "Would you like to know the current date and time?" Put an OK button and a Cancel button on this form. Write code that simply ends the procedure if the user presses the Cancel button but that displays the date and time in the label if the user clicks the OK button. If the user clicks OK a second time, end the procedure.
After you've placed the label with its Caption
property set to "Would you like to know the current date and time?" and also put two appropriately captioned buttons on the form, double-click the Cancel button to open the code window. Name the label lblShowTime
.
In the Cancel button's Click
procedure, type End. Now use the Object drop-down list (on the top left of the Code window) to select the OK button.
In the OK button's Click
procedure, type the code shown here:
1. Private Sub btnCancel_Click() 2. End 3. End Sub 4. 5. Private Sub btnOK_Click() 6. 7. If Label1.Caption = "Would you like to see the current date and time?" Then 8. Label1.Caption = Now 9. Else 10. End 11. End If 12. 13. End Sub
Here's how the code works:
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 procedures, 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
(or local, or procedure-scope
variable) a Boolean variable type that is set to True
the first time the OK button is clicked. Line 7 would then test the value of this variable in the following way:
If blnToggle = True Then
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. This interaction via dialog box is the typical way that a user communicates with your procedures, and vice versa.
Create a new dialog box that contains three option buttons captioned Small, Medium, and Large and named optSmall, optMedium, and optLarge. Write code in each option button's Click
procedure to change the button's caption to boldface when the button is clicked.
The following code shows how to employ the Font
object's Bold
property to turn boldface on and off in a caption on a dialog box:
1. Private Sub optSmall_Click() 2. optSmall.Font.Bold = True 3. optMedium.Font.Bold = False 4. optLarge.Font.Bold = False 5. End Sub 6. 7. Private Sub optMedium_Click() 8. optSmall.Font.Bold = False 9. optMedium.Font.Bold = True 10. optLarge.Font.Bold = False 11. End Sub 12. 13. Private Sub optLarge_Click() 14. optSmall.Font.Bold = False 15. optMedium.Font.Bold = False 16. optLarge.Font.Bold = True 17. End Sub
You could write this code a different way by creating a function that accepted small, medium
, or large
as an argument and then used a Select Case
structure to make the appropriate caption boldface. That solution would look like this:
Private Sub optSmall_Click() ChangeSize ("small") End Sub Private Sub optMedium_Click() ChangeSize ("medium") End Sub Private Sub optLarge_Click() ChangeSize ("large") End Sub Function ChangeSize(strChoice As String)
Select Case strChoice Case Is = "small" optSmall.Font.Bold = True optMedium.Font.Bold = False optLarge.Font.Bold = False Case Is = "medium" optSmall.Font.Bold = False optMedium.Font.Bold = True optLarge.Font.Bold = False Case Is = "large" optSmall.Font.Bold = False optMedium.Font.Bold = False optLarge.Font.Bold = True End Select End Function
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.
Describe two types of dynamic behavior typical of complex dialog boxes.
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.
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.
Name the two most common techniques you can use to display additional options in a dialog box.
The two most common techniques for displaying additional options in a dialog box are as follows:
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.
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.
How does the TabStrip control differ from the MultiPage control? What are the common uses for each?
A MultiPage control allows the user to switch among different virtual pages (with differing controls and varied layouts). The MultiPage control is most often employed to display a set of property pages for a feature (such as fonts) that includes many possible options and settings. You therefore can subdivide all these options among multiple pages in the form: one page for font properties and another page for character spacing, for example.
A TabStrip control contains multiple tabs but not multiple pages. In other words, the layout of the rest of the dialog box (apart from the tab strip itself), stays the same no matter which tab on the tab strip the user clicks. This is useful for displaying records from a database because the fields (such as text boxes) remain identical no matter which record the user is viewing.
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.
How do you make a user form modeless?
Set its ShowModal
property to False
. The default is True
.
Events are actions that happen while a program is executing. Many events are supported by the UserForm
object and the controls you use on it. 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.
Name two of the three most useful events available in VBA programming.
The three most commonly useful events for VBA programming are Click, Initialize
, and Change
.
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.
Shorter, self-contained, single-task procedures offer the programmer several advantages. Name three.
The advantages of shorter, self-contained, single-task procedures are as follows:
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.
You execute a procedure by calling it from within your programming code.
How do you call a procedure?
You can use the optional Call
statement, like this, to call a procedure named FormatDocument
:
Call FormatDocument
But most programmers omit the Call
keyword, using just the name of the procedure, like this:
FormatDocument
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 modified.
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.
What kind of procedure can pass back information to the caller?
Only functions can pass back information to a caller. Subroutines can accept data (arguments) like functions, but subroutines cannot pass data back to the caller.
Contemporary computer programs employ classes for various reasons—to help organize large programs, to make code more easily reusable, to provide certain kinds of security, or as a superior substitute for public variables. But beginners sometimes have a hard time wrapping their mind around the concept, particularly the relationship between classes and objects.
What is the difference between a class and an object?
Choose the correct answer (only one answer is correct):
A class is like a cookie and an object is like a cookie cutter.
A class is like a programmer and an object is like a module.
A class is like a blueprint and an object is like a house built from that blueprint.
The answer is 3. A class is like a blueprint and an object is like a house built from that blueprint.
The VBA Editor employs a special kind of module for creating classes.
How do you create a class module in the VBA Editor?
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
Click the Insert button on the Standard toolbar and choose Class Module from the drop-down list.
Choose Insert
A major aspect of programming is testing your code. Debugging can be enjoyable if you think of it as a puzzle that you can solve. But whether or not you enjoy it, debugging is essential if you want to preserve a reputation as a professional.
When testing your code, try to imagine ways that the code could fail. Describe a situation that can produce unanticipated results.
A user may try to run a document-formatting procedure without first opening a document.
Or the user might try to open a file, and trigger certain errors—perhaps the file doesn't exist; or is currently in use by another computer; or is on a network drive, floppy drive, CD-ROM drive, or removable drive that isn't available at the time
You'll also run into other peripheral-related errors if the user tries to use a printer or other remote device (say, a scanner or a digital camera) that's not present, connected, powered up, or configured correctly.
Similarly, any procedure that deals with a particular object in a document (for example, a chart in Excel) will run into trouble if that object is not present or not available.
Experts have concluded that there are four primary categories of error in programs.
Name two of the four basic types of programming errors.
Here are the four basic types of programming errors:
Language errors
Compile errors
Runtime errors
Program logic errors
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.
The Watch window is especially useful because you can set watch expressions (also known as conditional breakpoints). Describe this debugging tactic.
Watch expressions are flexible and powerful debugging tools. You can ask the VBA Editor to break on any kind of expression you can think up, such as any line that causes a variable to exceed a certain value, go below zero, change to a shorter string length, and so on.
You specify a condition (a watch expression such as X < 0
), and the VBA Editor automatically halts execution and displays the line where this occurs.
You can trap some runtime errors (errors that show up while a procedure is executing) while debugging your code. But others show up only while your user is interacting with your program—and you're probably not there to help them. There is a way, though, to soften the blow and, in some cases, even fix a problem by adding error handlers to your programs.
Error handlers are special statements and sections of code that detect and then manage runtime errors. What VBA statement detects a runtime error?
VBA's On Error
statement triggers when there is a runtime error, allowing you to write code that responds to the error.
Well-behaved procedures don't annoy or alarm the user either during or after their execution.
Name two ways programmers can write procedures that don't annoy users.
Here are some ways programmers can avoid annoying users by their procedures' actions:
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.
Users quite rightly don't appreciate it if your procedure modifies the state of their application's or operating system's environment. Find ways to restore the user environment before your procedure finishes execution.
Assume that you are writing a procedure that employs Word's Search and Replace feature. This feature retains its settings between uses so the user can repeatedly trigger the same search or replace actions. How can you temporarily store the status of the user's last search or replace so that you can restore this data after your procedure is finished executing?
To store such information, you can use private variables, public variables, or custom objects.
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.
Describe a way to let the user know that a procedure isn't frozen—that activity is taking place during execution.
You can tell users via a message box before starting a lengthy process that they should anticipate a delay. Alternatively, you can display messages on the status bar. Or you could disable screen updating for parts of a procedure and turn it back on, or refresh it, for other parts.
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.
If a procedure accesses data from a file, name an error that could occur and thus should be trapped.
You should trap errors in case a file being accessed hasn't been opened, doesn't exist (has been deleted or moved), or doesn't contain data that the procedure expects to find in it.
A well-behaved procedure avoids leaving unneeded files or other temporary items behind. In other words, a procedure should clean up after itself.
Cleaning up involves three major tasks. Name one.
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
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.
Name two ways that users are protected from malicious VBA code.
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.
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.
Describe the limitations of certifying a VBA macro project for yourself—without obtaining a certificate from your company or a commercial certification authority.
The quickest and easiest way of getting a digital certificate is to create one yourself. However, this kind of certification only works on the computer on which the certificate was created, and it's the least trustworthy type of digital signature. A digital certificate you create yourself is of little value to people other than you and those who personally trust you.
Commercial certification authorities provide the greatest level of security, but their certification is also more difficult to achieve than self-certification or obtaining certification from your company.
Name some of the ways you may be required to prove your identity when obtaining a digital signature from a commercial certification authority.
The procedure for proving your identity varies depending on the commercial certification authority and the type of certificate you want. Generally speaking, the greater the degree of trust that the certificate is intended to inspire, the more proof you'll need to supply. For example, you can get a basic certificate on the strength of nothing more than a verifiable email address, but this type of certificate is unlikely to make people trust you. Other certificate types require you to appear in person before a registration authority with full documentation (such as a passport, driver's license, or other identity documents). Such certificates carry more trust.
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.
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
There is no Enable All Macros With Notification option.
You can add publishers to the list of trusted publishers.
How do you add a publisher to the list of trusted publishers?
To add a trusted publisher, open a document or template that contains VBA code from the publisher you want to add. A Security Alert dialog box opens. When you open a file (or load an add-in) that contains code from a source with a valid digital signature, you have the option of adding this publisher to your list of trusted publishers. In the Security Alert dialog box, click the Trust All Documents From This Publisher option button.
You can protect your source code in the VBA Editor from others. You add a password to a project (the items in boldface in the Project Explorer) and others can't open your VBA procedures for reading or modifying.
What is the one drawback to locking your code?
The lock requires an extra step to accessing the modules and forms in the pro-ject because you must first provide the password. However, for the protection you gain by locking your code, this small extra effort can be well worth the trouble.
Some people find viewing a schematic of the Word object model useful as a way of visualizing the interrelationships of the various objects and collections.
When you look at the Word Object Model Map, what is the highest object in the hierarchy—the object that contains all other objects?
The highest object in the hierarchy is the Application
object.
Word contains a set of creatable objects that VBA programmers will frequently employ in their code.
What is a creatable object?
A creatable object is simply one that doesn't require you to use the term Application
when invoking it. For example, the Documents
collection object is creatable, so the word Application
is optional in this code: Application.Documents.Count
does the same thing as Documents.Count
.
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.
Here is the syntax for creating a new document in the Documents
collection:
Documents.Add Template, NewTemplate, DocumentType, Visible
If you merely want to add a new, empty document (based on the default Normal.dotm
template) to the documents currently open in Word, the code is quite simple. What is the code that you would write in VBA to accomplish this?
The code is as follows:
Documents.Add
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 a combination of these objects.
One kind of selection is described as a collapsed selection. What is that?
A collapsed selection is an insertion point (the blinking cursor). Nothing is visibly selected. The insertion point, however, is still thought of as technically a selection (pointing to a place within the document), even though this special kind of selection has no contents.
In Word, a range is a named contiguous 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.
Although a range is similar to a bookmark, what is the significant difference between them?
The main difference between a range and a bookmark involves their lifetimes. A range exists only as long as the VBA procedure that defines it is executing. A bookmark is persistent: It is saved with the document or template that contains it and can be acc-essed at any time (whether or not a procedure is running).
Word contains many options that can be manipulated from within VBA.
In Word, one object controls many of the options. This object has dozens of properties but no methods. Name this object.
The Options
object controls many of the options in Word.
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.
Sometimes when replacing, you need to go through a document more than once—using a loop structure. Why would you need to repeatedly search and replace the same document?
In some situations, the act of replacing actually generates new instances of the target of the replacement activity. Let's say you want only single-space strings in a document. (For example, you want sentences separated by only a single space character, but sometimes a typist accidentally presses two spaces following a period.) You set up a search and replace that looks for double-space character strings and replaces them with single-space characters. You have to take into account that there can also be some multiple-space-character strings. Consider a string of six adjacent space characters. During the first pass, your double-to-single search and replace reduces the six-space string to a three-space string (three instances of double spaces would reduce to three instances of single spaces). The second pass would reduce it to a two-space string, requiring yet a third pass through the loop to reach the desired single space. This same situation can apply to multiple paragraph spacing and tabs.
All Word documents contain headers and footers, even if they are empty. In addition, you can insert various types of headers and footers.
Name two types of headers you can use in a Word document.
Here are the major types of Word headers: the primary header, different first-page headers, different even-page headers, and different sets of headers for each of the sections in the document.
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.
What method of the Window object can be used to easily accomplish this task?
The ScrollIntoView
method of the Window
object moves the view to a target you specify.
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.
Name two important and useful objects within the Tables
collection or the Table
object.
Some the of most useful objects within a Table object or a Tables collection are:
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.
You often need to create a new, blank workbook in a procedure (as if you'd interactively clicked the File tab on the Ribbon, then clicked the New button). And writing code that accomplishes this is not difficult. It requires only two words.
What code would you write to create a new, blank notebook?
To create a blank workbook, omit the Template
argument, like this:
Workbooks.Add
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.
Name the object you use in VBA code to represent a worksheet.
Each worksheet is represented by a Sheet
object. The Sheet
objects are contained within the Sheets
collection.
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.
What is the difference between the active cell and a selection?
The active cell is always a single cell, but the selection can either be a single cell or encompass multiple cells or other objects.
Within a worksheet, you'll often need to manipulate ranges of cells. Excel includes a special kind of range—represented by the UsedRange
property.
What is unique about UsedRange
?
If you need to work with all the cells on a worksheet (but not with any unoccupied areas of the worksheet), use the UsedRange
property. UsedRange
ignores empty areas of a worksheet.
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.
From which object do you access most of Excel's options?
You access most of Excel's options from the Application
object. However, you can access the workbook-specific properties that appear in the Excel Options dialog box through the appropriate Workbook
object.
You can create either full chart sheets or embedded charts within an ordinary Excel worksheet.
What object is used in a procedure to represent an embedded chart?
VBA uses the ChartObject
object to represent an embedded chart on a worksheet.
To open a new window on a workbook, you use the NewWindow
method of the appropriate Window
object.
Does the NewWindow
method take any arguments?
No, the NewWindow
method takes no arguments. For example, the following statement opens a new window showing the contents of the first window open on the workbook identified by the object variable myWorkbook
:
myWorkbook.Windows(1).NewWindow
When working with the Find and Replace features in Excel, you need to be aware of a phenomenon known as persistence.
What is persistence, and why should it concern you?
The LookIn, LookAt, SearchOrder
, and MatchByte
arguments of the Range
object's Find
method persist. This means that Excel retains their settings from one search to the next (until this session with Excel ends and you shut it down). So, if you don't know that the settings used in the last search (either programmatically in a procedure or by the user) are suitable for your current needs, you should set these arguments explicitly in each search to avoid getting unexpected results. Format settings such as font and subscript also persist.
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.
Name one of the objects or collections that are creatable in PowerPoint procedures.
Objects or collections that are creatable in PowerPoint procedures include the ActivePresentation
object, the Presentations
collection, the ActiveWindow
object, and the SlideShowWindows
collection.
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, strobelike effect to avoid disturbing your audience.
How do you prevent a newly created presentation from being visible so that you can create and manipulate it in your code without the user seeing the flickering effect onscreen?
WithWindow
is an optional Long argument of the Add
method of the Presentations
collection. Set WithWindow
to msoFalse
to hide the presentation so that the user doesn't have to endure the irritating flickering effect that PowerPoint tends to exhibit while creating presentation objects programmatically. The default value is msoTrue
, making the new presentation visible.
To get the PowerPoint window into the state you want, you'll often need to work with the window and with the view.
PowerPoint uses two types of windows. What are they?
PowerPoint uses document windows and slide show windows.
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 assign names to slides.
Why would you want to assign names to slides rather than using the default index numbers that are automatically assigned to the slides?
Assigning names to slides is useful because if you add slides to, or delete them from, the presentation, the index numbers of the slides will change. You don't want to have to keep track of these readjusted index numbers as you manipulate the collection. If they have names, you can access the slides directly, without worrying that their index numbers might have changed.
Before attempting to manipulate a master in your code, you should determine whether the master actually exists in the presentation.
How do you find out whether a presentation has a title master?
Check the HasTitleMaster
property. If the presentation already has a title master, VBA returns an error when you try to add a title master. So first check, like this:
If ActivePresentation.HasTitleMaster Then 'take further action based on the If...Then test
PowerPoint VBA provides many ways to access and manipulate shapes.
Describe what the following line of code does:
ActivePresentation.Slides(2).Shapes(1).Delete
The code example deletes the first Shape
object on the second slide in the active presentation.
Using PowerPoint headers and footers can be a convenient way to provide continuity for presentations as well as identifying each element.
In this chapter, you worked with several examples showing how to manipulate footers for slides. Why were there no examples illustrating how to manipulate headers for slides?
Slides can't have headers, only footers. Notes pages or handouts can have headers.
To create a custom slide show, you use the Add
method of the NamedSlideShows
collection.
The syntax when using the Add
method of the NamedSlideShows
collection is
expression
.Add(Name, SafeArrayOfSlideIDs)
Explain what the four components of this line of code are and do.
The components are as follows:
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.
Application
object.VBA uses two major Outlook objects that most users wouldn't recognize from working with the Outlook user interface alone.
One of these objects represents a window that displays the contents of a folder. The other represents a window displaying an Outlook item, such as an email message or an appointment. What are the names of these two objects?
An Inspector
object is an object that represents a window displaying an Outlook item, such as an email message or an appointment.
An Explorer
object represents a window that displays the contents of a folder.
To work with the contents of a message in VBA, you set or get various properties.
Name one of the most widely useful properties employed when manipulating the contents of a message in a procedure.
The most commonly useful properties when accessing a message in VBA are To, CC, BCC, Subject, Body, BodyFormat
, and Importance
.
You can create new calendar appointment items via VBA.
To create a new calendar item, you use a particular method of the Application
object and specify olAppointmentItem
for the ItemType
argument. What is the method?
To create a new calendar item, you use the CreateItem
method of the Application
object. This example creates an AppointmentItem
object variable named myAppointment
and assigns to it a new appointment item:
Dim myAppointment As AppointmentItem Set myAppointment = Application.CreateItem(ItemType:=olAppointmentItem)
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.
What methods do you use to assign, add, and send a task to others?
To assign, add, and send a task to others, use the Assign, Add
, and Send
methods.
The hard part—and it's a very hard part—of using the Filter
argument to search for items with the AdvancedSearch
method is building the filter text itself. Creating the proper syntax for this argument is quite complicated. There is, however, a good shortcut that vastly simplifies generating the proper syntax.
What is the efficient, quick way to create the proper syntax for the Filter argument?
Outlook itself can generate the Filter
argument for you, using the built-in Filter utility as described in this chapter.
Event 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.
Event handler procedures are unlike ordinary macro procedures in several ways. Name one of the differences.
Both the construction of an event handler procedure and its testing differ somewhat from the techniques you've been employing throughout this book when creating and testing ordinary macro procedures:
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 by making it point to the event handler procedure.
You cannot simply test the event handler by pressing F5 to run it directly (you run it indirectly by triggering the event it's designed to service—for example, by modifying a contact in the Contacts folder).
Outlook has two primary kinds of events.
What are the two types of events in Outlook? And how do they differ?
The two types of events in Outlook are application-level and item-level events. Application-level events apply to Outlook as a whole (for example, an event that triggers when the application is closed). Item-level events apply to individual items within Outlook, such as a contact or an email message in the Inbox.
You can respond to user interaction with Outlook's bar by writing code in the bar's events.
Name one of the ways that Outlook's bar events can be employed by the developer.
You can customize the Outlook bar or constrain the user's navigation capabilities. For an example of the latter, you could use the BeforeNavigate
event to check the shortcut to which the user is attempting to navigate. If the user is not permitted to access the folder associated with the shortcut, you can cancel the navigation by inserting appropriate code in the BeforeNavigate
event.
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.
The term macro is used in a special way in Access (referring to only one of the two types of custom procedures Access permits you to construct: VBA and Macro Designer). This usage of macro is unlike the way the term macro is used in other Office applications, not to mention all other forms of computing. Describe what Access means by the term macro.
Instead of defining macros as VBA procedures, Access uses the term macro to describe a technology unique to Access. You create these Access "macros" by clicking the Macro button on the Create tab (in the Macros And Code section of the Ribbon) to open the Macro Designer window
An Access "macro" is an historical entity—a holdover from the early days of this database system. Access macros are limited to a subset of the available programming statements.
Using the Macro Designer, you enter a list of actions that you want to perform. You choose these actions from a list and then type in arguments in the next cell in a table displayed by the Macro Designer.
Access permits you to open a database in several ways.
Two common commands that open a database in Access are OpenCurrentDatabase
and OpenDatabase
. What is the difference between these two commands?
Instead of using the OpenCurrentDatabase
method to open a database as the current database, you can use the OpenDatabase
method of the Workspace
object to open another database and return a reference to the Database
object representing it. Using this method, you can open multiple databases. The OpenCurrentDatabase
method, by contrast, can open only a single database at a time.
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.
The Screen
object represents the screen object that currently has the focus in Access (that is, the object that is receiving input or ready to receive input). Three types of common Access objects can have the focus when you employ the Screen
object. What are they?
The object can be a form, a report, or a control.
DoCmd
object to run Access commands.Many of Access's interactive commands, such as printing a report or maximizing a window, are available to the programmer via methods of the creatable DoCmd
object.
The DoCmd
object has 67 methods in Office 2010. Describe the purpose of the DoCmd
object's Beep
method.
The Beep
method makes the computer emit a sound. This can be used in conjunction with an error message to alert the user that an error has occurred.
You can open an ADO recordset in two different ways.
One way to open an ADO recordset is to provide an argument list following the Open
method. What is the other way to open an ADO recordset, which doesn't involve using arguments? Some people say that this second approach makes their code easier to read.
Instead of specifying arguments for the Open
method, you can set the Source, ActiveConnection, CursorType
, and LockType
properties of the RecordSet
object you're opening and then use the Open
method without arguments. You may agree with those who feel this approach makes the code easier to read.
Both ADO and DAO technologies have methods that allow you to move around within a recordset.
One method you can use to traverse a recordset is the MoveFirst
method. It takes you to the first record in the recordset. What does the first record mean in a recordset in a relational database? Is it the record that's the lowest numerically, the lowest alphabetically, or what?
The concept of "first record" within a relational database is essentially meaningless, unless you sort the records in some fashion. You can sort them by any of their fields and also either ascending (the default) or descending (specify DESC
). Which record is first depends on the field by which you sort the recordset. But you must sort them. They can't be assumed to be sorted in any way in the database itself.
Both ADO and DAO offer methods to directly search for a particular record.
ADO offers a Find
method. How many methods does DAO offer, and what are they?
There are four Find
methods for DAO: FindFirst, FindNext, FindPrevious
, and FindLast
.
When editing a record, you first use the Edit
method, and then you can change the value in a field.
After you have made a change to a value in a record, what method do you use to save this change to make it part of the database?
You use the Update
method to save the changes you made to a recordset.
It's not difficult to insert new records or delete existing ones. In both situations, you use the Update
method when finished to save the changes to the database.
To insert a new record into a recordset, what method do you use before you can assign data to the fields in the new record?
You use the AddNew
method of the RecordSet
object to create a new, empty record.
Automation sets up communication between two applications, designating one of them as the server and the other as the client.
Of the various ways to communication between applications, which is generally the most effective?
Automation is the most powerful and efficient way to communicate with another application.
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.
Describe the timing issues that the Shell
function raises, and name a good solution to this problem.
The Shell
function runs other programs asynchronously rather than synchronously. In other words, Shell
doesn't halt all other activity until it has finished with its job. So when VBA executes a Shell
statement, it registers the statement as an action to be performed—but that action may not necessarily be finished before the next statement in the procedure executes.
This asynchrony can cause errors in your procedures if subsequent commands depend on the Shell
statement having already been executed. If you run into this type of problem, a crude but often effective fix is to just allow extra time for the Shell
function to execute before taking any dependent action. You can employ the Sleep
function to pause execution of your procedure to allow any necessary commands to be carried out.
This book has described a variety of ways to store and retrieve information when working with the VBA language. Using data objects are among these useful techniques.
How is the data object technology special as a way of storing and retrieving information; what can a data object do that's unique?
The data object has the ability to return information from and write information to the Windows Clipboard.
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.
Not all applications support DDE. Which Office 2010 applications don't support DDE communication?
PowerPoint and Outlook do not support DDE.
SendKeys
.Using SendKeys
is a fairly simple, rather awkward, and limited way to communication between applications. It imitates typing in keystrokes, thereby allowing you to manipulate an application by accessing some of its features using, for example, Alt+key combinations, such as Alt+F to open a File tab on the Ribbon.
SendKeys
was historically most often employed to open an application's menus and select an option from the menus. Because Vista and Windows 7 applications largely do away with traditional menus, is SendKeys
of even more limited use in Vista and Windows 7 than in previous versions of Windows?
No, SendKeys
must simply send some different keystrokes to access Vista and Windows 7 applications' features. Many of the features of the Ribbon, for example, are accessible via key combinations. For example, pressing the sequence Alt, W, Q, 2, and the Enter key in Word will switch to the View tab on the Ribbon, select the Zoom option, and switch to a 200% zoom. The difference here is that instead of employing the traditional approach of simultaneously pressing the Alt key while pressing other keys (such as Alt+V to open a View menu), in Vista or Windows 7 you press and release Alt by itself, then you press the W key to switch to the View tab on the Ribbon. At this point, additional keystrokes are possible to activate the various options on the View tab.
Modifying the Ribbon involves employing XML attributes—similar to methods and properties—of various Ribbon elements such as tabs, groups, and buttons.
Some Ribbon-related attributes include the suffix Mso
. Examples include idMso
and imageMso
. What does the Mso
mean, and what kind of attributes' names are appended with Mso
?
Mso
stands for Microsoft Office, and it means that a tab, icon, or other element is built in—such as a tab that is, by default, visible on the Ribbon.
You might want to make an entire Ribbon group unavailable to the user. For example, the Editing group on the Home tab includes three options that most people launch via shortcut keys: Find, Replace and Select. So what's the point of having this group take up space?
What XML attribute of a group do you set to false
to remove that group from the Ribbon?
You can set any group's visible
attribute to false
, thereby hiding it from the user.
To execute VBA code, you insert a callback in the XML code that will run whatever VBA macro you specify. When the user clicks a control, such as a button, the XML code that services this control sends a message to the Office application, telling it that a response is needed.
What XML attribute do you use to create a callback?
You use the onAction
attribute to create a callback.
Access often does things differently from the majority of Office applications, and Ribbon programming is no different. You can manipulate the Access Ribbon as freely as in the other applications, but several of the programming techniques differ.
Where can you store the XML code when programming the Access Ribbon?
You store the XML code when programming the Access Ribbon in a special table named USysRibbons.
Most Ribbon programming involves writing two types of code: XML and VBA. Strategies for fixing bugs in XML include validation.
What is XML validation?
XML validation is a feature built into XML code editors that checks to ensure that the XML is well formed (free of certain kinds of errors). A validation looks for tags that are missing, out of order, improperly punctuated, use the wrong capitalization, are not part of the schema (aren't listed as attributes or elements in the document that defines the particular XML version being used), and so on. Some errors can't be detected during validation (such as pointing an onAction
attribute to a VBA procedure that doesn't exist). But many common errors—such as typos—can be spotted by validation, which is an automatic scan of XML code.
18.119.124.49