By Romke Soldaat
One of the great benefits of the Microsoft Office suite is that its applications share a common interface. The menu bars, for instance, are nearly identical. And where menu and toolbar commands have similar functions in different applications, they display identical button images and captions.
In Office 97, the total number of shared commands is over 1,200. Of course, this doesn't mean that each command is available in all Office applications, or that each command has exactly the same functionality. For example, Excel doesn't have a grammar check, Word doesn't let you create pivot tables, and the Replace command doesn't display the same dialog box in each application.
As a user you could — in theory — use all of the available commands by placing their associated CommandBar controls on a menu or toolbar. As a VBA programmer you don't have to go that far. Because each control has a unique numeric identifier, you can access any control (and its associated function) by looking for its Id property. For CommandBar controls, this is a number between 1 and 3900; for menus the ID numbers start at 30,002.
Once you know the ID of a command, you can use the CommandBars.FindControl method to locate the command within the application, and access all properties and methods associated with that CommandBarControl object. Take, for example, the Print command on the File menu, which has command ID number 4. One of the available properties is the DescriptionText property. Simply running the following command in an Office application:
MsgBox CommandBars.FindControl(id:=4).DescriptionTextdisplays a message box with the text "Prints the active document."
Of course, not all properties and methods of a CommandBar control are very useful from a programming point of view. However, with the Caption and Enabled properties and the Execute method, you can obtain results that are fairly sensational.
Locating the Command IDAs far as I know, there's no documentation that lists all of the Office commands' IDs. (A computer-generated list of IDs covering all Word, Excel, and PowerPoint commands and their associated captions is available for download; see end of article for details.) If you want to know the IDs of a limited number of commands, create a toolbar named "Temp," and drag some command buttons onto it, all via the Customize dialog box. Then run the following macro, which lists the IDs and captions of the commands in the VBE Immediate Window:
Private Sub ListCmds()
Dim mybar As CommandBar
Dim i, n As Integer
Set mybar = CommandBars("Temp")
n = mybar.Controls.Count
For i = 1 To n
Debug.Print mybar.Controls(i).ID & _
mybar.Controls(i).Caption & vbLf
Next
End SubRecall the code in the previous example I used to display the description text of the File | Print command:
MsgBox CommandBars.FindControl(ID:=4).DescriptionTextWhat happens if the CommandBar control in question can't be found? Most, but not all, commands appear somewhere on a menu or command bar. One example of a command that doesn't appear on a command bar (and won't be found with the FindControl method) is Word's InsertSpike command (ID 2200). There's only one way to find that ID programmatically: Your macro must add the command to a temporary toolbar, and read its Id property.
Most of the examples in this article use the vbaGetCommandID function (see in FIGURE 1) to do just this. First, the function tries the FindControl method to look for the command. If that method fails, a temporary toolbar is created and populated with the control we're looking for. The temporary toolbar is then immediately deleted. The function returns a "handle" to the CommandBar control we're looking for. We can then use any property we're interested in, such as the Enabled and Caption properties.
Function vbaGetCommandID(ByVal CmdID As Long) As Object
Dim dummy As CommandBarControl
Set dummy = CommandBars.FindControl(id:=CmdID)
If dummy Is Nothing Then
On Error Resume Next
With CommandBars.Add(Name:="TempIDFinder", _
Temporary:=True)
.Controls.Add id:=CmdID
dummy = .Controls(1)
.Delete
End With
End If
Set vbaGetCommandID = dummy
End FunctionFIGURE 1: The vbaGetCommandID function returns a handle to the CommandBarControl with the specified command ID.
Command ExecutionIn previous versions of Word for Windows, you could use the WordBasic CommandValid function to check if a specified command is valid in the current context. In VBA this function isn't available. VBA Help doesn't offer an alternative, and tells you to "Use the IsObjectValid property to determine if an object variable reference is valid. Also, an object variable that returns Nothing is not valid." Rather useless!
Fortunately, you can simulate the same function in VBA if you know the ID of a command. The following vbaCommandValid function first calls the vbaGetCommandID function to search the CommandBars collection for the specified CommandBar control, and then looks at the status of that control:
' Returns True if the command associated with a
' command ID can be executed.
Function vbaCommandValid(ByVal CmdID As Long)
On Error Resume Next
vbaCommandValid = vbaGetCommandID(CmdID).Enabled
If Err Then vbaCommandValid = False
End FunctionIf the control is enabled, the function returns True; otherwise the return value is False.
The following instruction returns True if the Format/Font command (ID 253) is valid and False if not, e.g. if it's in a protected document section:
CanFormatFont = vbaCommandValid(253)To see if there's anything in the Clipboard, you can check the enabled status of the Paste command (ID 22):
CanPaste = vbaCommandValid(22)Because the Copy button (ID 19) on the Standard toolbar is automatically disabled if nothing is selected, you can use the following code to determine if there is an active selection:
UserMadeSelection = vbaCommandValid(19)And here's how you can see if a WordArt object is selected:
WordArtSelected = vbaCommandValid(2094)The ID of the Edit Text button on the WordArt toolbar is 2094. This button is grayed out if no WordArt object is selected.
The Impossible Dialog BoxIn most cases you can display a built-in Office dialog box using standard VBA commands, but sometimes the effect is not what you would expect. For example, if you use the following instruction in Word:
Dialogs(wdDialogEditFind).ShowWord displays the Find and Replace dialog box with the Replace and Go To pages disabled, preventing you from accessing these parts of the dialog box (see FIGURE 2). As another example, to display the Properties dialog box (which is not supported by VBA), Microsoft wants you to call the Help of the WordBasic object:
FIGURE 2: Using a standard VBA command displays Word's Find and Replace dialog box with the Replace and Go To pages disabled.
WordBasic.FilePropertiesBut if you know the IDs of the commands that display these dialog boxes, you can display the Edit, Replace, and Go To dialog boxes without disabled pages, and the Properties dialog box without using WordBasic. The vbaRunCommandByID function takes care of it, and returns True if the command was successfully executed (see FIGURE 3). As you can see, the code is similar to that used in the vbaGetCommandID function.
' Runs the command associated with a command ID. Returns
' True if successful, False if the command failed.
Function vbaRunCommandByID(ByVal CmdID As Long)
On Error Resume Next
Dim dummy As CommandBarControl
Set dummy = CommandBars.FindControl(id:=CmdID)
If dummy Is Nothing Then
On Error Resume Next
With CommandBars.Add(Name:="TempIDFinder", _
Temporary:=True)
.Controls.Add id:=CmdID
.Controls(1).Execute
.Delete
End With
Else
dummy.Execute
End If
vbaRunCommandByID = (Err = 0)
End FunctionFIGURE 3: The vbaRunCommandByID function.
The Execute method is used to simulate a click on the associated CommandBar control. One of the great advantages of this method is that, in most cases, you can use the same code in all Office applications. The following instructions display the Find, Replace, Go To, and Properties dialog boxes, respectively. The last three examples work in both Word and Excel. The first two examples demonstrate that Word and Excel use different IDs for the Find command:
vbaRunCommandByID(141) ' Find (Word)
vbaRunCommandByID(1849) ' Find (Excel)
vbaRunCommandByID(313) ' Replace
vbaRunCommandByID(757) ' Goto
vbaRunCommandByID(750) ' PropertiesFIGURE 4 shows that all pages in Word's Find and Replace dialog box are now available.
Creating Application-independent CodeThe vbaRunCommandByID function previously described offers the great advantage of letting you write macros that run in Word, Excel, and PowerPoint without application-specific modifications. For example, to save the current Word document, Excel worksheet, or PowerPoint presentation, you don't have to use different code for each application. All it takes is a single statement:
vbaRunCommandByID(3) ' 3 = Save command IDTo copy whatever is selected to the Windows Clipboard, use the following command in any Office application:
vbaRunCommandByID(19) ' 19 = Copy command IDTo apply boldface formatting to the selection in each Office application, use:
vbaRunCommandByID(113) ' 133 = Bold command IDAlthough most VBA commands are (and work) the same in all languages supported by Office, there are still a few left that won't work automatically in each language version. One example is the command that inserts the contents of the Spike into the active Word document. VBA Help suggests the following code:
NormalTemplate.AutoTextEntries("Spike").Insert Where:=RangeThis works with English versions of Word, but will fail in other language versions. (In the Dutch version of Word, the Spike is called "Prikker," in French it's "Pique Notes.") To write code that's safe in any language version, you're better off calling the vbaRunCommandByID function using the 2200 ID, like this:
vbaRunCommandByID(2200) ' 2200 = Insert Spike command IDSometimes you may want to retrieve the local text of a command string, especially if you write macros that should also run on Office applications in non-English languages. For example, if you want to add a "Zoom 88%" command to the View menu, you'd probably like to use the local translation of the Word "Zoom." The following vbaLocalCaption function does it for you:
' Returns caption text in the language of the application.
Function vbaLocalCaption(ByVal CmdID As Long, _
Optional ByVal NoAmp As Boolean, _
Optional ByVal NoEll As Boolean)
On Error Resume Next
vbaLocalCaption = vbaGetCommandID(CmdID).Caption
If NoAmp = True Then
vbaLocalCaption = vbaStripAmpersand(vbaLocalCaption)
End If
If NoEll = True Then
vbaLocalCaption = vbaStripEllipsis(vbaLocalCaption)
End If
End FunctionDepending on the value of the NoAmp and NoEll parameters, this function can call two other functions, which manipulate the retrieved command string. The vbaStripAmpersand function removes the ampersand, which defines the underlined character, and the vbaStripEllipsis function removes the trailing ellipses that are usually appended to command strings that display a dialog box (see FIGURE 5).
' Returns caption text without ampersand (&) character.
Public Function vbaStripAmpersand(ByVal Instring As String)
Dim test As Long
test = InStr(Instring, "&")
Select Case test
Case 0:
vbaStripAmpersand = Instring
Case 1:
vbaStripAmpersand = Mid(Instring, 2)
Case Else:
vbaStripAmpersand = Left(Instring, test-1) & _
Mid(Instring, test+1)
End Select
End Function
' Returns caption text without trailing ellipsis (...).
Public Function vbaStripEllipsis(ByVal Instring As String)
Dim test As Long
test = InStr(Instring, "...")
Select Case test
Case 0:
vbaStripEllipsis = Instring
Case Else:
vbaStripEllipsis = Left(Instring, test - 1)
End Select
End FunctionFIGURE 5: The vbaStripAmpersand and vbaStripEllipsis functions remove ampersands and trailing ellipses respectively.
The following instruction creates a "Zoom 88%" caption based on the local string for the word "Zoom," using the command ID 925. The ampersand and ellipsis are removed, and the first "8" appears underlined. Again, it works both in Word and Excel:
MyCaption = vbaLocalCaption(925, True, True) & " &88%"FIGURE 6 displays the code that retrieves the local string for "About Microsoft Word" (command ID 927). The string is then converted into an equivalent translation of "About Office Toys" (see FIGURE 7a). In the French version of Word this returns "À propos de Office Toys" (see FIGURE 7b). The German version of Word returns "Office Toys Info."
MSAbout$ = vbaLocalCaption(927, True, True)
InstrMS = InStr(MSAbout$, "Microsoft Word")
LenMS = Len("Microsoft Word")
Select Case InstrMS
' "Microsoft Word" is not in string.
Case 0
ToyAbout$ = "&Office Toys " & MSAbout$
' String starts with "Microsoft Word."
Case 1
ToyAbout$ = "&Office Toys " & Trim(Mid(MSAbout$, LenMS))
' Assuming "Microsoft Word" is at end of string.
Case Else
ToyAbout$ = "&" & Trim(Left(MSAbout$, InstrMS - 1)) & _
" Office Toys"
End SelectFIGURE 6: The code that retrieves the local string for "About Microsoft Word," converting it into an equivalent translation of "About Office Toys."
FIGURE 7a: Auto translation in action with the vbaLocalCaption function.
FIGURE 7b: The last item on the menu is automatically adapted to the language of the host application.
String Retrieval Using Command IDsMany CommandBar controls are listboxes or comboboxes that are dynamically updated while you work with the application. If you want to use this information for your own forms or macros, you can do so once you know the command ID of the listbox in which you're interested. Some worthwhile command IDs are:
Border Width, 2622
Font, 1728
Font Size, 1731
Style, 1732
Zoom, 1733
Address, 1740
Undo, 128
Redo, 129
(Again, a complete list of Microsoft Office command IDs is available for download; see end of article for details.)
The following code displays a message box with all styles available in the active Word document.
With vbaGetCommandID(1732)
For i = 1 To .ListCount
msg = msg + .List(i) + vbCr
Next
End With
MsgBox msgThe following code shows you how you can fill a listbox in a form with all the Internet addresses that are stored in the Registry by Internet Explorer (FIGURE 8 shows the results):
Private Sub UserForm_Activate()
ListBox1.Clear
With vbaGetCommandID(1740)
For i = 1 To .ListCount
ListBox1.AddItem .List(i)
Next
End With
End Sub
FIGURE 8: Using the vbaGetCommandID function takes just a few lines of code to fill a listbox with recent Internet addresses.
The following bits of code tell you how many items there are in the Undo and Redo lists:
UndoItems = vbaGetCommandID(128).ListCount
RedoItems = vbaGetCommandID(129).ListCountThis can be interesting information if you have a macro that changes the contents or formatting of the current document, and you want to have an option to undo your actions. By counting the number of undo items before and after your macro has modified a document, you can use the difference as the parameter with the Undo method.
Generating Sorted Font Lists Using Command IDsI've saved the best example for last. Using the same trick as previously demonstrated, you can also quickly obtain a sorted list of all installed fonts. Word's FontNames object doesn't offer a sorted font list, and Excel and PowerPoint don't have a FontNames object at all, but the sorted font list is right in front of your eyes on the Formatting toolbar of each Office application (see FIGURES 9a and 9b).
FIGURE 9a: Borrowing the contents of the Font list …
FIGURE 9b: … to create a VBA application that displays fonts in alphabetical order.
The following code creates an AllFonts array by reading the items in the font list on the Formatting toolbar:
Set FntSource = CommandBars.FindControl(id:=1728)
With FntSource
ListOffSet = _
IIf(.ListHeaderCount = -1, 0, .ListHeaderCount)
LastFont = .ListCount - ListOffSet - 1
ReDim AllFonts(LastFont)
For i = 0 To LastFont
AllFonts(i) = FntSource.List(i + ListOffSet + 1)
Next
End WithBecause both Word and PowerPoint (but not Excel) can have a "fontlist MRU" at the top of the list, the macro must eliminate these duplicate entries by looking at the ListHeaderCount property, which determines how many fonts appear above the separator line. These fonts are excluded from the array. You can use this routine without any modification in Word, Excel, and PowerPoint. As a matter of fact, I've done exactly that in my Visual Fonts freeware application, which you can download from www.officetoys.com.
ConclusionIn this article I've demonstrated that the VBA programmer isn't necessarily limited to the features and functions that Microsoft provides. With a bit of hacking you can add powerful new commands and functions to your VBA projects. The Id property of CommandBar controls lets you write macros that run in any language version of any Office application. It also gives you a quick and reliable way to retrieve captions and listbox contents from CommandBar controls. Watch this space for more VBA hacks!
The files referenced in this article are available for download here.
Romke Soldaat is the creator of a number of best-selling Word add-ons, such as MegaWord, Office Navigator, and Office Toys. After a career as an advertising copywriter, during which he developed and marketed a successful add-on for WordPerfect, he was hired by Microsoft in 1988 to co-found the Microsoft International Product Group in Dublin, Ireland. That same year he started working with the prototypes of WinWord, writing his first macros long before the rest of the world. In 1992 he left Microsoft and retired to France, where he now does what he likes most: writing software that makes Microsoft products better and easier to use. Romke's Web site (from where you can download a number of shareware and freeware Office 97 add-ons) is at www.officetoys.com, and he can be contacted via e-mail at romke@officetoys.com.