Specifying the Number of Sheets in a New Workbook
When you create a new workbook in Excel, the file comes with three worksheets by default. Most people just use one worksheet, but leave the other two sheets in the workbook, just in case. If you use several sheets in many or all of your workbooks, you should consider increasing the default number of sheets that Excel includes in new workbooks. Follow these steps 1. Choose Office, Excel Options to open the Excel Options dialog box. 3. Use the Include This Many Sheets spin box to set the number...
Inserting Text
If you need to insert text into a document, Word offers several Range object methods. In most cases, you start by inserting a paragraph into the document, which you do by running the InsertParagraphAfter method. For example, the current cursor position is given by the Selection object which I discuss in detail later . To insert a new paragraph after the current cursor position, you'd use the following statement You can also run the InsertParagraphBefore method to insert a paragraph before the...
Digitally Signing a VBA Project
The macro virus situation is even worse if you want to distribute your VBA applications, because many people are loath to accept files that have macros. Even if your reputation is such that people won't worry about files that come from you, how can they be sure that the code they have really was created by you The Visual Basic Editor enables you to use a certificate to digitally sign your projects. A certificate is your iron-clad guarantee that you created a project. You get a certificate from...
Using the Words Object
The Words object is a collection that represents all the words in whatever object is specified. For example, ActiveDocument.Words is the collection of all the words in the active document. Other objects that have the Words property are Paragraph, Range, and Selection. You refer to individual words by using an index number with the Words collection. As I mentioned earlier, however, this doesn't return a Word object there is no such thing in Microsoft Word's VBA universe. Instead, individual...
Listing Using the Find Method
Sub SearchRecordsWithFind Dim rs As ADODB.Recordset Dim strCriteria As String Set rs .Source Employees .ActiveConnection Northwind .CursorType adOpenKeyset .Open End With strCriteria City 'Seattle' rs.Find strCriteria ' Loop to find other records that meet the criteria Do While Not rs.EOF MsgBox rs First Name amp amp rs Last Name 1 Search again, but skip a row rs.Find strCriteria, 1 After opening the Employees table as the recordset, this code uses the strCriteria variable to hold the criteria...
Moving the Insertion Point
The insertion point is the simplest form of the Selection object. If your code needs to move the insertion point, the easiest way to do this is to use the Move method, which collapses the current selection and moves the insertion point by a specified number of units. You can also use the MoveEnd method to move the insertion point to the end of the specified unit such as a paragraph , or the MoveStart method to move the insertion point to the beginning of the specified unit. Note that all three...
Using the Immediate Window
The Watch window tells you the current value of an expression, but you'll often need more information than this. You also might want to plug in different values for an expression while in break mode. You can perform these tasks with VBA's Immediate window, which you display by activating the View, Immediate Window command. Printing Data in the Immediate Window You can use the Print method of the special Debug object to print text and expression values in the Immediate window. There are two ways...
Recording a VBA Macro
As I mentioned earlier, only Word and Excel come with the Record Macro command, which is a shame. In Office 2003, you could record macros in PowerPoint, too. However, these two programs are the most suited to recording macros, so it's not all that surprising that Microsoft has restricted the Office macro recording capabilities. The next two sections show you how to record a macro in Word and Excel. Before getting started, make sure that Word is set up so that it's ready to record. If you want...
Saving Application Settings in the Registry
In a VBA procedure, you use variables to store values you need to use while you're running the procedure. When the procedure finishes, the values of those variables are wiped from memory. What do you do if you have values that you want to preserve from one VBA session to another You could store the values somewhere in the document, but this isn't a great idea because those values could be easily changed or even deleted. A better idea is to use the Registry. Windows uses the Registry to store...
Monitoring Procedure Values
Many runtime and logic errors are the result of or, in some cases, can result in variables or properties assuming unexpected values. If your procedure uses or changes these elements in several places, you need to enter break mode and monitor the values of these elements to see where things go awry. The Visual Basic Editor offers a number of methods for monitoring values, and I discuss them in the next few sections. Most of the values you'll want to monitor will be variables. Although watch...
Checking the Selection Type
The Selection object has a number of properties, including many that you've seen already with the Document and Range objects. The few properties that are unique to the Selection object aren't particularly useful, so I won't discuss them here. The lone exception is the Type property, which returns the type of selection Nothing is selected. A column in a table is selected. The selection is the insertion point. Some text is selected. A row in a table is selected.
Some Shape Object Properties
PowerPoint VBA comes equipped with more than three dozen Shape object properties that control characteristics such as the dimensions and position of a shape, whether or not a shape displays a shadow, and the shape name. Let's take a quick look at a few of these properties property returns an AnimationSettings object that represents the animation effects applied to the specified Shape. AnimationSettings contains various properties that apply special effects to the shape. Here's a sampler see the...
Inserting Slides from a File
Rather than create slides from scratch, you might prefer to pilfer one or more slides from an existing presentation. The InsertFromFile method lets you do this. It uses the following syntax Index , SlideStart , SlideEnd The Presentation object in which you want to add the slides. The name of the file including the drive and folder that contains the slides you want to insert. The index number of an existing slide in Presentation. The slides from FileName are inserted after this slide. The index...
Selecting Controls
Before you can work with a control, you must select it. For a single control, you select it by clicking it. If you prefer to work with multiple controls, the Visual Basic Editor gives you a number of techniques Hold down the Ctrl key and click each control. You also can lasso multiple controls by clicking and dragging the mouse. Move the mouse pointer to an empty part of the form, hold down the left button, and then click and drag. The VBE displays a box with a dashed outline, and any control...
Touring the Visual Basic Editor
The idea behind the Visual Basic Editor is simple It's a separate program that's designed to do nothing else but help you create and edit VBA macros. In professional programming circles, the Visual Basic Editor is called an integrated development environment or IDE. When you open the Visual Basic Editor for the first time, you don't see much. The left side of the editor has two windows labeled Project and Properties. The latter you don't need to worry about right now. I'll talk about it in...
Using the Selection Object
The Selection object always references one of two things The position of the insertion point cursor. Because much of what you do in Word involves one of these two items formatting text, inserting text at the insertion point, and so on , the Selection object is one of the most important in Word. I'm simplifying things a bit for this discussion because the Selection object can also refer to a selected shape, inline shape, or block. I'll deal only with text-related selections in this section. You...
Text Boxes
Text boxes are versatile controls that let the user enter text, numbers, and, in Excel, cell references and formulas. To create a text box, use the TextBox button in the Toolbox. Here are a few useful properties of the TextBox object EnterFieldBehavior Determines what happens when the user tabs into the text box. If you select 0 fmEnterFieldBehaviorSelectAll , the text within the field is selected. If you select 1 fmEnterFieldBehaviorRecallSelect , only the text that the user selected the last...
Returning a Range Object
you omit Worksheet, VBA assumes the method applies to the ActiveSheet object. A range reference or name entered as text. For example, the following statements enter a date in cell B2 and then create a data series in the range B2 E10 of the active worksheet Range B2 .Value 01 01 2008 Range B2 B13 .DataSeries Type xlChronological, Date xlMonth For information on the Value property and DataSeries method, see Inserting Data into a Range, p. 162. The Range method also works with named ranges. For...
Listing A Procedure That Uses Select Case to Convert a Raw Test Score into a
Function LetterGrade rawScore As Integer As String Select Case rawScore Case Is lt 0 LetterGrade ERROR Score less than 0 Case Is lt 60 LetterGrade F Case Is lt 70 LetterGrade D Case Is lt 80 LetterGrade B Case Is lt 100 LetterGrade A Case Else LetterGrade ERROR Score greater than 100 The rawScore argument is an integer value between 0 and 100. The Select Case structure first checks to see whether rawScore is negative and, if so, the function returns an error message. The next Case statement...
Adding a Form to Your Project
Forms are separate objects that you add to your VBA projects. To do this, open the Visual Basic Editor and either choose Insert, UserForm or drop down the Insert toolbar button the second from the left and click UserForm. As you can see in Figure 12.1, VBA performs the following tasks in response to this command It adds a Forms branch to the project tree in the Project Explorer. It creates a new UserForm object and adds it to the Forms branch. It displays the form in the work area. Selecting...
Outgoing Handling the ItemSend Event
To process outgoing mail, you need to add code to handle another Application object event ItemSend. As with the ItemAdd event that I discussed in the previous section, you set up the event handler by using the ThisOutlookSession object, which is part of the default Outlook VBA project. In the Visual Basic Editor, click ThisOutlookSession and then click Application in the Object list. Outlook adds the following stub to the module Private Sub Application_ItemSend ByVal Item As Object, Cancel As...
Extending the Selection
When you use the Selection object, it's important to know how to manipulate the currently selected text by extending the selection. The simplest way to extend the selection is with the various Move commands that either move the insertion point or extend the selection in a particular direction Selection.MoveDown Unit , Count , Extend Selection.MoveLeft Unit , Count , Extend Selection.MoveRight Unit , Count , Extend Selection.MoveUp Unit , Count , Extend Unit optional Specifies the unit by which...
Step One Create a Reference
This may sound strange, but the ability to program a database with ADO is not built into Access VBA by default As I mentioned at the top of the chapter, Access VBA is set up to program forms and reports among other things it just can't work with the data that's in those forms and reports, not to mention the tables where the data actually resides. It's weird, I know. So why did Microsoft set things up this way In simplest terms, there are actually several different ways to program data, and...
Formatting Text
The Range object's properties also include many of the standard text formatting commands. For example, the Bold property returns True if the Range object is formatted entirely as bold, returns False if no part of the range is bold, and returns wdUndefined if only part of the range is formatted as bold. You can also set this property by using True for bolding , False to remove bolding , or wdToggle to toggle the current setting between True and False . A similar property is Italic, which returns...
Closing a Workbook
To close a Workbook object, use the Close method, which uses the following syntax The Workbook object you want to close. If the workbook has been modified, this argument determines whether or not Excel saves those changes True Saves changes before closing. Omitted Asks the user whether changes Save the workbook under this filename. Routes the workbook according to the following Sends the workbook to the next recipient. Doesn't send the workbook. Asks the user whether the workbook should be...
Spin Buttons
A spin button is similar to a scrollbar in that the user can click the button's arrows to increment or decrement a value. To create a spin button, use the SpinButton tool in the Toolbox. The properties for a SpinButton object are the same as those for a ScrollBar except that there is no LargeChange property . Most spin buttons have a text box control beside them to give the user the choice of entering the number directly or selecting the number by using the spin button arrows. You have to use...
Some MAPiFolder Methods
Although you probably won't use them very often, the MAPIFolder object does come with a few methods MAPIFolder .CopyTo Copies the specified MAPIFolder to another folder MAPIFolder The MAPIFolder object you want to copy. DestinationFolder The MAPIFolder object to which you want the folder MAPIFolder .Delete Deletes the specified MAPIFolder. MAPIFolder .MoveTo Moves the specified MAPIFolder to another folder MAPIFolder The MAPIFolder object you want to move. DestinationFolder The MAPIFolder...
Listing A Procedure That Checks the Spelling of an Entered Word
' Get the word from the user word2Check InputBox Enter a word 1 Spell-check it result MsgBox amp word2Check amp 111 is spelled correctly MsgBox Oops ' amp word2Check amp ' is spelled incorrectly.1 End If End Sub
Accessing the File System Through VBA
If your applications need to work with the file system, VBA boasts quite a few features that make it easy. These features include a number of statements that return information about files and folders, as well as a number of functions with which you can manipulate files and folders. There are also powerful functions that give you direct access to files. This section examines all VBAs file-related statements and functions. Returning File and Folder Information If you need information about the...
MailItem Object Properties
The MailItem object boasts dozens of properties that cover everything from the message recipients to the assigned sensitivity. Here's a list of the most useful MailItem properties MailItem .BCC Returns the display names separated by semicolons of the addresses listed as blind courtesy or carbon copy recipients for the specified MailItem. MailItem .Body Returns or sets the body text for the specified MailItem. MailItem .BodyFormat Returns or sets the format of the body text for the specified...
Listing A Function Procedure That Uses Various Date Functions to Calculate a
Function CalculateAge birthDate As Date As Byte Dim birthdayNotPassed As Boolean birthdayNotPassed CDate Month birthDate amp amp _ Day birthDate amp amp _ Year Now gt Now CalculateAge Year Now - Year birthDate birthdayNotPassed End Function ' Use this procedure to test CalculateAge. MsgBox CalculateAge 8 23 59 End Sub The purpose of the CalculateAge function is to figure out a person's age given the date of birth as passed to CalculateAge through the Date variable named birthDate . You might...
Creating a Split Button
A split button is a control that has a regular button control on the top half and a menu control on the bottom half. The idea here is that the button control represents the default choice, and if you want some other choice, you pull down the menu to select it. You create the split button control by using the lt splitButton gt element lt splitButton id value label value imageMso value size normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible true...
Defining a Range Name
In Excel VBA, range names are Name objects. To define them, you use the Add method for the Names collection which is usually the collection of defined names in a workbook . Here is an abbreviated syntax for the Names collection's Add method this method has eleven arguments see the VBA Reference in the Help system Text The text you want to use as the range name. RefersTo The item to which you want the name to refer. You can enter a constant, a formula as text such as Sales-Expenses , or a...
Checking Spelling
When used with the Word or Excel Application object, the CheckSpelling method checks the spelling of a single word using the following syntax note that Word's method has a few extra arguments word The word you want to check. customDictionary The filename of a custom dictionary that the application can search if word wasn't found in the main dictionary. ignoreUppercase Set to True to tell the application to ignore words entirely in For example, the code shown in Listing 5.5 gets a word from the...
Listing Continued Qms
If InStr c.Text, Home Cell lt gt 0 Then End Sub The SelectHomeCells procedure is similar to the SelectAlOnAllSheets procedure from Listing 8.8. That is, the main loop runs through all the sheets in the active workbook and activates each worksheet in turn. In this case, however, another loop runs through each worksheet's Comments collection. The Text property of each Comment object is checked to see whether it includes the phrase Home Cell. If so, the cell containing the comment is stored in the...
Tab Strips and MultiPage Controls
I mentioned earlier that you can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job. The first situation is when you need the form to show multiple sets of the same or similar data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to...
Option Buttons
Option buttons are controls that usually appear in groups of two or more the user can select only one of the options. To create an option button, use the OptionButton tool. You can determine whether an option button starts off activated or deactivated by setting the Value property If it's True, the option is activated if it's False, the option is deactivated. For option buttons to work effectively, you need to group them so that the user can select only one of the options at a time. VBA gives...
The RecentFiles Object
Another way to open Word documents is to use the RecentFiles object, which is the collection of the most recently used files displayed on Word's Office menu. Each item on this list is a RecentFile object. You specify a RecentFile object by using RecentFiles Index , where Index is an integer that specifies the file you want to work with. The most-recently used file is 1, the second most-recently used file is 2, and so on. The maximum value of Index is given by the RecentFiies.Maximum property....
Step Create a Text File and Add the Custom XML Markup
You're now ready to create the custom XML markup for the Ribbon tabs, groups, and controls you want to display. In the same folder that you're using to store the macro-enabled document or template, create a text document named for example MyRibbon.xml. Open the file in Notepad or some other text editor and then begin the XML markup as follows j If you're working with Word's Normal template,you can open its folder by pressing Windows Logo R,entering the following path in the Run dialog box,and...
Collapsing the Selection
When you no longer want text selected, you can use the Move, MoveEnd, or MoveStart methods to automatically collapse the selection and move the insertion point. Sometimes, however, you might prefer to place the insertion point either at the beginning or the end of the current selection. To so that, use the Collapse method Direction optional Specifies where you want the insertion point to end up. Use wdCollapseStart to position the cursor at the beginning of the Selection this is the default ....
Working with the Application Window
The application's window contains the interface elements such as the Ribbon and the status bar, as well as an area for displaying a document. In Word, PowerPoint, and Access the application window can have multiple instances one for each open document, presentation, or database , whereas in Excel there is always just one application window possibly with multiple open workbooks . You can wield seven Application object properties to control the application window or sets the height, in points, of...
Note the use of the underscore in Listing This is VBAs code continuation
The purpose of this procedure is to take a name first and last, separated by a space, as shown in the TestIt procedure and extract the last name. The full name is brought into the function as the fullName argument. After declaring an Integer variable named spacePos, the procedure uses the InStr function to check fullName and find out the position of the space that separates the first and last names. The result is stored in spacePos spacePos InStr fullName, The real meat of the function is...
MailItem Object Methods
With the methods available to the MailItem object, you can send messages, as well as reply to and forward messages. See Sending a Message, later in this chapter, to learn how to use these methods that send messages. Otherwise, you can also open messages, move them to another folder, delete them, and more. Here's a summary of some of these more useful MailItem object methods MailItem.Close Closes the window in which the specified MailItem object is displayed see the Display method, later in this...
Creating a Toggle Button
A toggle button control is very similar to a check box in that it switches between one of two states pressed and not pressed. Therefore, as with a check box, you can use it to run a macro that toggles some setting on and off. You create a toggle button by using the lt toggleButton gt element lt toggleButton id value label value imageMso value size normal large InsertAfterMso value InsertBeforeMso value onAction value enabled true false visible true false screentip value supertip value keytip...
Listing A Procedure That Creates a Message Dialog Box
Dim msgPrompt As String, msgTitle As String Dim msgButtons As Integer, msgResult As Integer msgPrompt Are you sure you want to display amp vbCrLf amp the worksheet names msgButtons vbYesNo vbQuestion vbDefaultButton2 msgTitle Display Worksheet Names msgResult MsgBox msgPrompt, msgButtons, msgTitle The dialog box that's displayed when you run the code in Listing 3.3. The dialog box that's displayed when you run the code in Listing 3.3. Getting Return Values from the Message Dialog Box A message...