When you enter HTML in Word, it can become very tedious typing certain tags over and over again after each new line of script. For example, when you create hyperlinks, you use the <A> tag at the beginning of the link and </A> at the end. Or, when you create a table, you bracket each row of data with the <TR> and </TR> tags. If you find yourself adding a certain tag more than others, you can use the following Word macro to help alleviate some of the repetition.

Sub InsertTRTag()
  Selection.InsertBefore "<TR>"
  Selection.InsertAfter "</TR>"
End Sub

To create this macro, select Tools/Macro/Macros from the menu bar. Enter InsertTRTag as the name, then click Create. Word opens the Visual Basic Editor and provides the macro's beginning and ending Sub statements. At the insertion point, enter the remaining two lines of code.

Automating Office with VBA

Often, you'll want to delete entire rows in Excel spreadsheets if one or more key cells are blank. To accomplish this, create a loop that iterates through the Cells collection in the column or range you want to check for blank values. Then, use the Row property to delete the entire row. For example, if you want to delete rows that have empty cells in column D, use the following macro

For Each cell In ActiveSheet.UsedRange.Columns(4).Cells
     If IsEmpty(cell) Then
          Rows(cell.Row).Delete
     End If
Next cell

ZD Journals' Automating Office with VBA

On VBA UserForms, inserting an item into a listbox or combobox control, is fairly straightforward -- you simply refer to the control by name and execute the AddItem method, such as:

MyListbox.AddItem "Value1"

This code would add the text "Value1" to the list of selections in the control. Unfortunately, in Outlook, the above code returns an error. That's because in VBScript you have to explicitly tell Outlook where the control resides. For instance, say you added a listbox, named MyListbox, to the General page on Outlook's default Contact form. To add a new item, place the following code in the Contact form's Open event:

GetInspector.ModifiedFormPages("General").MyListbox.AddItem("Value1")

The GetInspector property returns the current Inspector for the Contact form, while the ModifiedFormPages refers to the collection of individual pages in that form. To use this code with controls on other pages, simply replace "General" with the name of the alternate page. You can apply this same technique to comboboxes as well as listboxes.

Peter Dietrich

As you probably know, the Recipients collection of an Outlook MailItem represents the list of addressees indicated in the To field of an email message. When you use Automation from another Office application to add a new email Recipient, use the Add method, as in

Set olApp = New Outlook.Application
Set itmMail = OlApp.CreateItem(olMailItem)
itmMail.Recipients.Add "automating_w_vba@zdjournals.com"

This code sample would add our email address to the mail item.

How you refer to a Recipient by name depends on whether you've already entered it as a resource in Outlook or not. For example, say we had already entered Mike D. Jones as a resource in the Address Book. In the previous example, then, instead of the email address, we could use

Set itmMail = OlApp.CreateItem(olMailItem)
itmMail.Recipients.Add "Robert Price"

ZD Journal

Not all InputBoxes are created equal. You're probably familiar with VBA's InputBox function. With it, you prompt a user for information to use in a procedure. For example,

MyResponse = InputBox(Prompt:="Enter a number:", Default:=0)

sets MyResponse equal to the numeric value you enter in a VBA generated InputBox.

However, the Excel InputBox method is a little different. It allows you to automatically validate the entered data. This method has all the InputBox settings you're familiar with, as well as an additional argument, Type. With this argument, you specify what type of value you want entered into the InputBox. Indicate one of the following values: 0 for formulas, 1 for numbers, 2 for text, 4 for logical values (True or False), 8 for cell references, 16 for error values, and 64 for array values. Unlike VBA's general InputBox function, the Excel method verifies that the value you enter into the InputBox complies with the required type. If it doesn't, Excel won't accept it. For instance, if you declared the Type as a number then entered text into the resulting InputBox, Excel informs you that you've entered an inappropriate value.

To take advantage of Excel's InputBox method, you must execute it in conjunction with a valid Excel application object. That's how VBA knows which InputBox to use. So, in an Excel macro, you'd enter something like this:

MyResponse = Application.InputBox(Prompt:="Enter a number:", Default:=0, Type:=1)

or, via Automation from another application, you'd enter this:

MyResponse = appExcel.InputBox(Prompt:="Enter a number:", Default:=0, Type:=1)

where appExcel is an Excel object variable.

Automating Office with VBA

When you declare a variable to hold a count of rows in an Excel spreadsheet, your first instinct may be to use an Integer type variable. However, an Integer variable only holds numbers up to 32,767, whereas Excel spreadsheets can have up to 65,536 rows. If you use an Integer variable, say, to count the number of rows in a database range, and the database exceeds 32,767 records, your results will be inaccurate. A better variable type is the Long Integer, or Long as it appears in a declaration statement, which extends up to 2,147,483,648.

Dana DeLouis

In addition to coding assistance from the new Intellisense features, the Visual Basic Editor provides a quick syntax check when you enter a new line. VBE formats each word in a line of code when you hit [Enter]. By default, all REM statements turn green, Keywords blue, and lines with errors red. With a simple glance, you can tell if your code is correct. Also, even though objects, properties, functions, and methods remain black, they are converted to proper case. Type your code in lowercase, and if the VBE converts it, you'll know that you've entered it correctly. Of course, you'll still need to compile your code to see if you've used the commands properly, but the VBE's reformatting option will at least help you avoid syntax errors.

ZD Journals' Automating Office with VBA

To disable the Close button on a UserForm, use the form's QueryClose event. This event passes two arguments: Cancel and CloseMode. If you've done any programming with Access events, you're no doubt familiar with the Cancel argument. Set it equal to True to cancel the particular event--in this case the QueryClose event. The CloseMode argument indicates what caused the UserForm to close in the first place and can be one of the built-in constants explained in the online Help topic "QueryClose Event." For purposes of disabling the Close button, test for the vbFormControlMenu constant, which test whether you chose the Close command from the UserForm's Control menu, (the X button). So, to bring this all together, insert the following code into a UserForm's QueryClose event:

If CloseMode = vbFormControlMenu Then Cancel = True

ZD Journals' Automating Office with VBA

One quick way in VBA to format various components of a Word document is to loop through the document's collections, formatting each object as you go. To do so, use the For Each...Next loop statement. For example, if you wanted to bold every sentence in an open document, you would use

Sub MyLoop()
  For Each Sentence In ActiveDocument.Sentences
    Sentence.Bold = True
  Next Sentence
End Sub

A statement like

  For Each Wrd In Selection.Words
    If  Trim(Wrd) = "The" Or Trim(Wrd) = "the" Then Wrd.Bold = True
  Next Wrd

would bold each "The" or "the" in any highlighted portion of text.

One advantage to using a loop structure like this is that you never have to worry about where a document or selection ends. Word takes care of that for you, because it knows exactly how many objects are in the collection.

And don't forget that even though we used Word for our example, the same technique can be applied to any collection in any Office application.

ZD Journal

When you record a macro in Office, it duplicates your actions exactly in VBA code. As a result, you'll often see statements similar to

Range("C11:E15").Select
Selection.Font.FontStyle = "Bold"

or, in PowerPoint for example,

ActiveWindow.Selection.SlideRange.Shapes("Rectangle 2").Select
ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Font.Bold = msoTrue

However, in VBA, you can manipulate objects directly without selecting them first. So, unless you want to specifically show what the macro's doing as it executes, in most cases there's no need to select an object before performing some action on it. For instance, both of the examples above could be condensed into single lines of code, as in

Range("C11:E15").Font.FontStyle = "Bold"

or

ActiveWindow.Selection.SlideRange.Shapes("Rectangle 2"). & _
TextFrame.TextRange.Font.Bold = msoTrue

ZD Journal

Just like normal windows, sometimes you may want to provide a maximize button on your custom user form. Unfortunately, there isn't a VBA command that does this. To get the same results, however, you can set the form's width, height, top, and left settings to those of the host application, (the Office application that's running the user form). Of course, this assumes that the host application is maximized already.

Sub MaxButton_Click()
   With UserFormName
     .Height = Application.Height
     .Width = Application.Width
     .Top = Application.Top
     .Left = Application.Left
   End With
End Sub

ZD Journals' Automating Office with VBA

You may not be aware of it, but the Visual Basic Editor offers a way to protect your code from anyone who doesn't know its password. To safeguard your projects, open the VBE, along with the project you want to protect. In the Project Explorer, collapse the project's branches so that only the name shows. Next, right-click on this item and select Project Properties from the shortcut menu. When the VBE displays the Project Properties dialog box, select the Protection tab. Select the Lock The Project For Viewing check box, and enter a password in the Password and Confirm Password text boxes. Click OK, then save and close the associated Office file. Now reopen the document, spreadsheet, or slideshow, and press [Alt][F11] to open the VBE. The project appears in the Project Explorer, but this time when you click on it, the VBE prompts you for a password.

Note, however, that password-protecting a project doesn't prevent someone from halting code execution. For example, if you display a modal UserForm in Word, then toggle to the VBE and click the Reset button on the toolbar, the UserForm still shuts down.

ZD Journals' Automating Office with VBA Journal

When developing VBA solutions in Office applications you're not familiar with, the Object Browser (OB) in the Visual Basic Editor is a good reference tool. In it, you'll find a complete list of the application's classes, methods, properties, events, and constants. To gain access to the OB from any Office program (aside from Access), press [Alt][F11] to launch the VBE, then click the Object Browser button or press [F2]. You'll notice, though, that at first glance the resulting lists can be rather confusing. That's because when you open the Object Browser, it contains items from all the host application's reference libraries, which at the very least will include MSForms, Office, and VBA, as well as items from the host program itself. You can reduce the number of items you have to deal with at one time by selecting a specific library from the Project/Library dropdown list in the Object Browser window's upper right corner.

While doing so hones the number of items considerably, both panes are still a little cluttered. The items are in alphabetical order, with objects, methods, properties, and constants all mixed together. Most of the time, you probably don't know the item's exact name, but you do know the item's category. In which case, the alphabetical list isn't much help. Fortunately, the Object Browser gives you the option to group both the Class list and the Members list into their respective categories. To do so, right-click in either pane and select the Group Members option from the resulting shortcut menu. When you do, the Object Browser clusters the items together by category. Now, all you need to do is search one group at a time.

ZD Journals' Automating Office with VBA

If you're familiar with Access forms and controls, you may have been a little perplexed when confronted with an MS Forms listbox for the first time. These controls work a little differently than they do in Access. For example, MS Form listboxes have the RemoveItem method, which uses the following syntax:

object.RemoveItem(index)

where, of course, object is the listbox's name, and index is the row you want to delete. For example,

Listbox1.RemoveItem(3)

would remove the fourth item in the list. To delete all the selected items in the control, use

With Listbox1
  For x = .ListCount to 0 Step -1
    If .Selected(x) = True Then
      .RemoveItem(x)
    End If
  Next x
End With

Notice, that to properly delete items, you start at the bottom of the list and work your way up.

ZD Journal

Not only can you launch the Visual Basic Editor from within a host application by pressing [Alt][F11], but you can also use the same key combination to toggle from the VBE back to the app.

Automating Office with VBA

Often, when you either create a VBA procedure directly in Excel, or access Excel through Automation, you'll want to open a workbook without triggering any of its event procedures. For example, you may want to read data from a named range from one workbook and place it on another. If the source workbook has an Open event procedure, chances are you won't want Excel to execute it. To circumvent a workbook's event procedures, set the EnableEvents property to False before you open the workbook. This property allows you to enable and disable Excel events, as in the sample code below:

Dim wrk As Workbook
Application.EnableEvents = False
Set wrk = Workbooks.Open("C:\Examples\CapRange.xls")
     '...Code to get data from named range goes here
     wrk.Close
Application.EnableEvents = True

Automating Office with VBA

You may have noticed that when you turn off the ScreenUpdating property directly after hiding a UserForm that the UserForm doesn't really disappear. Instead, the UserForm gets locked into a limbo state--not completely gone, but with part of it missing, or many of its controls blank. This behavior is the result of VBA's habit of executing code one line after another without waiting for Windows to complete an operation. In other words, when you execute the Hide method on a UserForm, Windows begins to conceal the form, but VBA doesn't wait for it to complete the job before turning off the screen updating, which in turn locks the UserForm in place on the screen.

To give the operating system the time it needs to hide the form, use the DoEvents command between execution of the Hide method and setting the application's ScreenUpdating property. The DoEvents command temporarily yields code execution to the operating system until it completes the appropriate events. So, to truly hide a UserForm when you turn off screen updating, use code similar to the following:

MyUserForm.Hide
DoEvents
ScreenUpdating = False

Jason Woods

If a property or expression evaluates to a Boolean value, you can simply use the property or expression in a conditional If...Then statement. For example,

If MyControl.Visible = True Then  'Do stuff...

can also be stated as

If MyControl.Visible Then  'Do stuff...

ZD Journals' Automating Office with VBA

Often in VBA, you may want to use a specific Excel Range that you select from a worksheet during code execution. However, it's not always convenient to build an entire UserForm with a RefEdit control just to get this user-selected range. Excel's InputBox method is one quick alternative. When you set this method's Type argument equal to 8 -- the Range type -- Excel lets you select the appropriate cells from a worksheet and enters the cell references into the InputBox, just like the RefEdit control. So, to set a Range variable equal to a user-selected area, use

MyRange = Application.InputBox(Prompt:= "Select a range of cells.", Type:=8)

Automating Office with VBA

You may or may not have been exposed to the problem mentioned in the title--you create an Automation object to Word from another Office application, then make the Word instance visible. And wallah! Word appears stripped of everything but its title bar. While you can still manipulate the object with code, if you want to manually interact with it, this barren Word instance doesn't do you much good. No doubt, if you've encountered this problem, you wondered how to correct it. In a nutshell, this behavior results when you try to set the Word object's window state before you make it visible, as shown in the following Sub Procedure.

Sub WordBug()
Dim objWord As Word.Application
Set objWord = New Word.Application
With objWord
    .WindowState = wdWindowStateMaximize
    .Visible = True
    .Quit
End With
Set objWord = Nothing
End Sub

To test this yourself, simply launch Excel or Access. In Access, select the Insert/Module option from the menu bar. In Excel, press [Alt][F11] to open the Visual Basic Editor. In both applications, choose Tools/References..., add a reference to the MS Word 8.0 Object Library, then enter the above code. When you've finished, press [F8] to step through the procedure. After Visual Basic executes the

Visible = True

statement, Word appears on the Taskbar. When you select it, Word appears as we mentioned above-totally bare. Press [Alt][Tab] to toggle back to your host application, and step through the rest of the code so that Word closes properly. To correct Word's odd behavior, simply switch the order of

WindowState = wdWindowStateMaximize

and

Visible = True

Now, if you run the procedure again, the proper menus and toolbars clothe Word once more.

ZD Journals' Automating Office with VBA

In many cases, VBA gives you the option of using a function or method with or without parenthesis around the command's arguments. When you want a VBA function or method to return a value, enclose the arguments in parenthesis. For instance, when you use the MsgBox() function you often want to test to see which button the user clicked. To do so, you enclose the MsgBox() arguments in parenthesis, such as

intResponse = MsgBox("Click Yes.", vbYesNo)

This statement returns the MsgBox button value that the user clicks and places it in the intResponse variable. However, if you simply want the function or method to perform an action without returning a value, remove the parenthesis, like we did in this code snippet:

If intResponse = vbNo Then
  MsgBox "I said click Yes!", vbCritical
End If

Unlike the previous example, here, the MsgBox function displays a message box with the Critical icon, but doesn't return a value for the button clicked.

Automating Office with VBA