VBA Hacker

Do-It-Yourself IntelliSense

Part I: Adding Your Own "Replace as You Type" Magic to Word

By Romke Soldaat

With the arrival of Microsoft's IntelliSense technology in Office 95, software suddenly started to get brains. Word's smart cut-and-paste feature guarantees that drag-and-drop operations don't mess up your spaces and punctuation marks. AutoCorrect and AutoFormat are great time savers, and on-the-fly spell checking is a godsend. On the other hand, I consider AutoSummarize (introduced in Word 97) still too dumb; and automatic language detection (Word 2000) too annoying to be useful.

Over the next few Office generations, the performance and reliability of these IntelliSense features will undoubtedly increase. But that doesn't mean that we have to wait for our friends in Redmond to fulfill our wishes. In this three-part series, I'll introduce a few methods and mechanics to add your own touch of IntelliSense to Word 95, 97, and 2000.

AutoFormat as You Type

Most IntelliSense features in Word can be turned on and off in the AutoCorrect dialog box (accessible from the Tools menu). FIGURE 1 shows the options in Word 2000. The Replace as you type section in the dialog box offers some really useful options, such as smart quotes, superscripted ordinals, and automatic hyperlinks.

FIGURE 1: AutoCorrect features in Word 2000

On the other hand, the Fractions (1/2) with fraction character (1/2) feature on the AutoFormat As You Type page is too limited. It only replaces the character combinations 1/4, 1/2, and 3/4 with the standard ANSI characters 1/4, 1/2, and 3/4. In Part II, I'll introduce a way to auto format any fraction, e.g. typing 11/32 automatically becomes ¹¹/₃₂. First we need to look at the basics of writing "intelligent" macros that "sense" what you're trying to type and correct your text on the fly.

Word's IntelliSense features usually kick in when you type a space, a bracket, or a punctuation mark. Word then looks at what you just wrote, checks it against your AutoCorrect and AutoFormat settings, and changes your text if appropriate. If automatic proofing is enabled, Word also checks the spelling and grammar of your typing - all at the speed of light.

Being a fast, compiled application, Word has all the time it needs to look over your shoulder and watch what you're doing. In a VBA application, however, you don't have that luxury. As far as I can tell, there's no workable method to monitor Word continuously and execute a command or macro when a certain string is typed. But there's another way.

You probably know that you can assign a macro, command, style, AutoText entry, font, or symbol to a shortcut key or key combination of your choice using the Add method of the KeyBindings collection. For example, the following code assigns the Arial font to the [Ctrl][Shift][A] key combination:

KeyBindings.Add 
      KeyCategory:=wdKeyCategoryFont, _
        Command:="Arial", KeyCode:= 
      _
        BuildKeyCode(wdKeyControl, 
      wdKeyShift, wdKeyA) 

This instruction uses the BuildKeyCode method to create a numeric value by adding one, two, or three values, which are categorized in the Object Browser under the wdKey list of constants. Normally, you would use this option with key combinations that involve the [Alt], [Ctrl], and/or [Shift] modifier key, but nothing forbids you from linking a macro with an unmodified key, such as the key that produces the letter "f" or the slash character. For example, the following code assigns the lowercase "f" key to the Intercept_F macro:

KeyBindings.Add KeyCode:=wdKeyF, 
      _
        KeyCategory:=wdKeyCategoryMacro, _

        Command:="Intercept_F"

Here, wdKeyF (decimal value 70) is one of the wdKey constants that you can look up in the Object Browser. Because the constant values for alphabetical characters in the range a-z and A-Z are always the same, regardless of the keyboard layout, it's safe to use them. Later in this article, you'll find out that, for many other keyboard characters, it's dangerous to rely on wdKey constants, and you'll have to use another way to get the correct key codes.

First Exercise: AutoDegrees

Let's assume you want to automate the formatting of Fahrenheit and Celsius (Centigrade) degrees. You decide that typing the uppercase letter F or C immediately after one or more numeric characters should insert the degrees character (° - ANSI value 176) between the numerals and the F and C characters. So, when you type 125F, Word automatically changes your typing into 125°F, and 90C becomes 90°C. What you need is one macro that's triggered by typing the letter F and another one that runs when you type the letter C. The routine in FIGURE 2 creates these keyboard assignments.

Sub 
      ActivateAutoDegrees()
        ' Define 
      where the customization is stored. 
        CustomizationContext = 
      NormalTemplate
        ' Assign 
      the uppercase F character. 
        KeyBindings.Add _
          KeyCategory:=wdKeyCategoryMacro, 
      _
          Command:="Intercept_F", _
          KeyCode:=BuildKeyCode(wdKeyShift, 
      wdKeyF) 
        ' Assign 
      the uppercase C character. 
        KeyBindings.Add _
          KeyCategory:=wdKeyCategoryMacro, 
      _
          Command:="Intercept_C", _
          KeyCode:=BuildKeyCode(wdKeyShift, 
      wdKeyC) 
      End Sub

Once you've executed this macro, typing the uppercase letter F triggers the Intercept_F macro, and the letter C runs the Intercept_C macro. Both macros do essentially the same job; FIGURE 3 shows the listing of the macro that intercepts the F key.

Sub 
      Intercept_F()
        
      
        On 
      Error Resume Next
        
      
        With Selection
           ' If the selection isn't an insertion point, 
           ' type the letter F and jump out. 
           If . Type <> 
      wdSelectionIP Then
            .TypeText "F" 
             Exit Sub
           End If
        
           ' Create a range object for the current selection. 
      
           Dim TextBeforeIP As Range
           Set TextBeforeIP = .Range
           ' Extend the start position of the range to the
           ' beginning of the word before the insertion point. 
      
          TextBeforeIP.StartOf Unit:=wdWord, 
      Extend:=wdExtend
           ' If the entire word consists of digits and does 
      not
           ' end with a space, treat it as an 
      AutoDegree
          ' candidate. 
           If IsNumeric(TextBeforeIP.Text) And _
             Right(TextBeforeIP.Text, 
      1) <> " " Then
            .TypeText "ºF" 
           Else
            .TypeText "F" 
           End If
        End 
      With
      End Sub

The With block in this macro contains the code that works on the current selection. First, the macro checks to see whether the selection is something other than an insertion point. If that's the case, it assumes you want to replace the selected text with the letter F (which is done with the TypeText method), and the macro exits. Otherwise, a Range object with the name TextBeforeIP is created for the insertion point. The StartOf method is used to extend the start position of that range to the beginning of the word at the left of the insertion point. At this point, the TextBeforeIP range contains the last typed word. If this word can be evaluated as a number (using the IsNumeric function) and doesn't end with a space character (meaning that the letter F wasn't intended to be typed immediately after the number), the macro inserts the string °F, otherwise the letter F is typed.

Problems!

Although the AutoDegree macros will work properly in most cases, there are a few potential problems. Because you have redefined the behavior of the uppercase C and F characters, you'll end up with situations where typing these characters doesn't have the desired effect.

One example is when you're typing text in the header of an e-mail message. If you type an uppercase C or F in either the To:, Cc:, or Subject: fields, the AutoDegree macros will generate error 4605: "This method or property is not available because the current selection is in the mail header." The version of the macros in Listing One intercepts this error and prompts you to use the [CapsLock] key to type an uppercase C or F. It's not an elegant solution, but it's workable.

The blocked [C] and [F] keys are also incompatible with some of Word's dialog boxes. There's no problem with dialog boxes in which you type unformatted text, such as the Open, Properties, and Find dialog boxes; both characters can be typed without a hitch. But some other dialog boxes contain so-called "rich" textboxes where you can bold, italicize, etc. the text. The ones I found are the dialog boxes associated with the Caption command on the Insert menu, and the Envelopes and Labels command on the Tools menu. Typing an uppercase C or F in these dialog boxes is impossible, unless you use the [CapsLock] key.

The problem is that you can't trap these occurrences programmatically; macros simply don't run while dialog boxes are displayed. So, the only way to tackle this problem is by resetting the [C] and [F] keys to their default behavior before displaying the dialog box and reassigning them to the AutoDegrees macros afterwards. But how?

Rewriting Commands

Ever since the good (or bad?) old days of WordBasic, you were able to replace a built-in command by writing a macro with the same name. But how do you find out which command displays a certain dialog box? In pre-2000 versions of Word, you had to guess. Usually you can combine the name of the menu that contains the command with the name of the command itself. For example, the Save As command on the File menu is linked with the FileSaveAs macro. Sometimes you may have to fiddle a bit with the concatenation of the menu and command name; for example, the command that inserts the Date and Time isn't called InsertDateAndTime, but InsertDateTime.

If you have Word 2000, your life is much easier thanks to the new CommandName property of the Dialogs object, which returns the name of the procedure that displays a specified built-in dialog box. The following instruction assigns the string FileSaveAs to the MacroName variable:

MacroName = 
      Dialogs(wdDialogFileSaveAs).CommandName

This way you'll discover that the commands associated with the Caption, Envelopes, and Labels dialog boxes are InsertCaption and ToolsEnvelopesAndLabels, respectively. All you have to do is write macros with the same names that temporarily disable the keyboard assignments, display the appropriate dialog box, and then recreate the key bindings. Listing Two contains these macros.

Second Exercise: AutoMetrics

So far, so good, but there are more clouds on the horizon. Let's assume you also want an automatic correction of metric expressions. This may not be very relevant for most US readers, but the rest of the world abolished the imperial system a long time ago. (There's a scattered probe on Mars that illustrates this conflict rather painfully.) An AutoMetrics application should be able to convert strings like km1, m2, and dm3 into km¹, m², and dm³, respectively. (The superscripted characters have the ASCII values 185, 178, and 179, respectively.)

The "intelligence" in the macros will be very much the same as in the AutoDegrees project; all they have to do is look for a metric unit (the options are mm, cm, dm, m, dam, hm, and km), preceded by a numeric expression or a space. If that's the case, the superscripted numeral is inserted; otherwise the standard size numeral is used. FIGURE 4 shows a listing that converts the character 2 into a metric square symbol (²) if the last-typed text includes a metric unit. You'll see many similarities to the listing in FIGURE 3.

Sub 
      Intercept_2()
        On 
      Error Resume Next
        With Selection
           ' If the selection isn't an insertion point, 
      
           ' type the appropriate digit. 
           If . Type <> 
      wdSelectionIP Then
            .TypeText "2" 
           Else
             ' Create a range object for the current selection. 
      
             Dim TextBeforeIP As Range
             Set TextBeforeIP = .Range
             With TextBeforeIP
               ' Extend the start position of the range to the
               ' beginning of the word before the insertion point. 
              .MoveStart 
      Unit:=wdWord, Count:=-1
               ' If the first character is a digit, move the start
               ' of the range one character at a time to the right, 
               ' until the first character is NOT a digit. 
               If IsNumeric(.Characters.First) Then
                 While IsNumeric(.Characters.First) 
                  .MoveStart 
      Unit:=wdCharacter, Count:=1
                 Wend
               End If
               ' See what text is now in the range. 
               Select Case LCase(.Text) 
                 Case "mm", "cm", "dm", "m", "dam", "hm", "km" 
                   ' A valid metric unit, so we type the
                   ' superscripted digit. 
                  Selection.TypeText "²" 
                 Case Else
                   ' Not an AutoMetric candidate.
                  Selection.TypeText "2" 
               End Select

             End With
           End If
        End 
      With
        
      
        If 
      Err.Number = 4605 Then
          MsgBox "Use the 
      numeric keypad to type a 2" 
        End 
      If
      End Sub

More Problems!

The AutoMetrics macros are designed to be associated with the keys that create the characters 1, 2, and 3 on the standard keyboard. Because these key assignments have the same drawbacks as the ones for the AutoDegrees macros (i.e. they fail to work in an e-mail header or rich-text dialog box), I decided to leave the numeric keypad untouched. This way we keep the option available to use the keypad keys (or the virtual keypad on a portable PC) to type a 1, 2, or 3 in dialog boxes that are incompatible with our macros. Listing Three contains the AutoMetrics macros.

But there's another problem, and this time it's Microsoft's fault. If you didn't know better, you would probably use the following code to assign a macro to the [1] key:

KeyBindings.Add KeyCode:=wdKey1, 
      _
        KeyCategory:=wdKeyCategoryMacro, _
        Command:="Intercept_1" 

The wdKey1 constant has a numeric value of 49, which is indeed the code for the [1] key on many keyboards, including the US and UK versions. But on French, Belgian, and some Eastern European keyboards, all numeric characters are on shifted keys, so you have to type [Shift][1] to produce the "1" character. As a result, running the previous code will not assign the Intercept_1 macro to the [1] key, but to the ampersand (&) character on a French keyboard, and to the plus sign (+) on a Czech keyboard!

The KeyBinding Mess

Don't think this is an isolated case. If your keyboard (or that of you customers) doesn't have a US layout, you'll find out that the wdKey list of constants in the Object Browser is not only misleading, but also dangerous. The list uses constant names that are supposed to be easy to remember - which they are in many cases. You can safely assume that wdKeyA always contains the value associated with the "a" character (lowercase, not uppercase!) on any keyboard that supports the Latin alphabet. The same applies to the wdKeySpacebar, wdKeyNumeric5, and wdKeyF10 constants, to name a few. All keyboard drivers use identical "scan codes" for function keys, cursor keys, keys on the numeric keypad, and most alpha keys, even if their physical position on the keyboard isn't the same. But that's where it stops.

Take for example the / key. You would expect that you could use the wdKeyControl (512) and wdKeySlash (191) constants to create a [Ctrl]/ key binding:

KeyBindings.Add 
      KeyCategory:=wdKeyCategoryCommand, _
        Command:="WindowSplitWindow", 
      _
        KeyCode:=BuildKeyCode(wdKeyControl, wdKeySlash)

This will work on a PC with a US or UK keyboard, but fails miserably on many other configurations. Why? Because the / key on many international keyboards doesn't have 191 as the scan code. For example, on a Dutch keyboard, the slash character has a scan code of 219, which happens to be the value of the wdKeyOpenSquareBrace constant, which represents the "[" character. How about that for mnemonic constant names?

I found at least eight keyboards that have different scan codes for the / key. On each of them, the above instruction will assign the command to the key combination you do not want, and - worse - overwrite any existing key binding without warning!

Microsoft doesn't say a word about this in VBA Help, and I haven't seen a single VBA book that discusses this problem. So, if you want to write applications that must run on any keyboard, you should avoid the use of wdKey constants like the plague, and find another way to obtain keycodes.

Get the API to Do the Work VBA Can't Do

Whenever VBA fails to behave the way you expect or want it to, it pays to see if the Windows API  does a better job. In the case of solving the problem of unreliable wdKey constants, solace comes from the Windows VkKeyScan function. This function must be declared as follows:

Public Declare Function 
      VkKeyScan Lib "user32" _
         
      Alias "VkKeyScanA" (ByVal cChar As Byte) As Integer

VkKeyScan translates an ANSI character into a virtual-key code plus a shift state for the current keyboard. The function requires one parameter, cChar, which must be a Byte value, an 8-bit number ranging in value from 0-255. The return value is the sum of the virtual-key code and the shift state (256 for the [Shift] key, 512 for the [Ctrl] key, and 1024 for the [Alt] key).

You can obtain the cChar value for a specific ANSI character using the VBA Asc function. For example, to find the key code of the [1] key, regardless of the current keyboard layout, use the following syntax:

OneKey = VkKeyScan(Asc("1")) 

On most keyboards, this will return a value of 49 (the same as the wdKey1 constant), but on a French, Czech, or Slovak keyboard, you'll get 305, which is the sum of the wdKeyShift (256) and wdKey1 constants. The following code snippet uses VkKeyScan rather than the wdKey1 constant to assign the Intercept_1 macro to the [1] key:

KeyBindings.Add _
        KeyCode:= VkKeyScan(Asc("1")), _
        KeyCategory:=wdKeyCategoryMacro, _
        Command:="Intercept_1"

FIGURE 5 shows a generic AssignMacroToCharacter routine that calls the Windows VkKeyScan function to create a foolproof link between any macro and any keyboard character, as well as a ResetCharacter macro that removes the key binding.

Sub 
      AssignMacroToCharacter( _
        MacroName As String, Character 
      As String)
        
        CustomizationContext = 
      NormalTemplate
        KeyBindings.Add _
          KeyCategory:=wdKeyCategoryMacro, 
      _
          Command:=MacroName, 
      _
          KeyCode:=VkKeyScan(Asc(Character)) 
      End Sub
        
      Sub 
      ResetCharacter(Character As String)
        CustomizationContext = 
      NormalTemplate
        FindKey(VkKeyScan(Asc(Character))).Disable
      End Sub

The syntax is:

' Create key binding. 
      
      AssignMacroToCharacter "Intercept_1", 
      "1"   
        
      ' Delete key binding. 
      
      ResetCharacter "1" 

The listings at the end of this article use similar instructions in the AutoMetrics and AutoDegrees modules. Together with the IntelliLib module (which contains generic code and some API declarations), these modules form a complete project that adds two new IntelliSense features to Word.

A Better KeyString Function

Because of the same US bias that makes the wdKey constants unusable, you can't rely on VBA's KeyString function and method either. This function is designed to return a string that identifies a certain key combination. For example:

MsgBox KeyString(BuildKeyCode(wdKeyControl, 
      _
                                    wdKeyShift, 
      wdKeyA)) 

displays the string Ctrl+Shift+A. Again, with standard alphabetical characters, this works fine. But as soon as you go beyond the A-Z range, you can't rely on KeyString at all. For example:

KeyString(BuildKeyCode(wdKeyShift, wdKey6)) 

always returns the circumflex character (^), even though it should return a slash character (/) on a Hungarian keyboard, a question mark (?) on a Canadian multilingual keyboard, and an ampersand (&) on a Danish or German keyboard! An unforgivable blunder.

Thankfully, the Windows API is also helpful in finding the correct key string associated with a specified character. This trick requires two additional functions: MapVirtualKey and GetKeyNameText. The MapVirtualKey function translates (among other things) a virtual-key code into a scan code. The GetKeyNameText function retrieves a string that represents the name of a key. I won't discuss the details of these functions here; you can read more about them in the MSDN library, or in any book that deals with the Windows API.

In the IntelliLib module (again, refer to Listing Two), you'll find a GetKeyString function that tells you which key combination must be used to generate a certain character. Unlike VBA's KeyString function, this GetKeyString function is keyboard-aware, and will return the correct key combination for any ANSI character on any hardware configuration. For example, to find out which keys need to be used to generate the @ character, use:

KeyText = GetKeyString("@") 

Depending on the current keyboard, this will give you the key strings shown in the table in FIGURE 6.

There's no way you can get this information in VBA!

Conclusion

The purpose of this series is to demonstrate that there's a lot more to VBA programming than you may think. With a bit of hacking, you can create powerful new tools to enhance your Office environment. In this first of three articles, I demonstrate how you can extend Word's IntelliSense features with an AutoDegrees and AutoMetrics feature, and I pointed you at a way to solve a serious problem with Word's keycode constants.

In the next installment, we'll build an application that beats Word's AutoFractions by miles, and a nifty AutoCalculate function that converts any number or calculation automatically in a choice of different formats. The third part of this series will introduce a powerful tool as an alternative for Word's limited and complicated ways to create accented international characters.

The VBA source referenced in this article is available for download. This file contains a ready-to-use and unprotected Word template with the code for all three installments of this series. The README.TXT file in the zip contains full installation instructions.

Dutchman Romke Soldaat 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 created a number of successful add-ons for Office. Living in Italy, he divides his time between writing articles for this magazine, enjoying the Mediterranean climate, and steering his Land Rover through the world's most deserted areas. Romke can be contacted at romke@soldaat.com.

Begin Listing One -

AutoDegrees.bas

End Listing One

Begin Listing Two - IntelliLib.bas

Public Declare Function 
      VkKeyScan Lib "user32" _
         
      Alias "VkKeyScanA" (ByVal cChar As Byte) As Integer
      Public Declare Function 
      GetKeyNameText Lib "user32" _
         
      Alias "GetKeyNameTextA" (ByVal lParam 
      As Long, 
      _
         
      ByVal lpBuffer As String, ByVal nSize 
      As Long) As Long
      Public Declare Function 
      MapVirtualKey Lib "user32" _
         
      Alias "MapVirtualKeyA" (ByVal wCode 
      As Long, 
      _
         
      ByVal wMapType As Long) As Long
        
      Sub 
      AutoExec()
        ActivateAutoDegrees
        ActivateAutoMetrics
      End Sub
        
      Sub 
      AutoExit()
        DeactivateAutoDegrees

        DeactivateAutoMetrics
      End Sub
        
      Sub 
      AssignMacroToCharacter(MacroName As String, _
        Character As String, Optional ShiftState As 
      Integer)
        
      
        ' 
      ShiftState values. 
        ' 
      Shift=256. 
        ' Ctrl=512. 
      
        ' Alt=1024. 
      
        CustomizationContext = 
      NormalTemplate
        KeyBindings.Add _
          KeyCategory:=wdKeyCategoryMacro, 
      _
          Command:=MacroName, 
      _
          KeyCode:=VkKeyScan(Asc(Character)) + ShiftState

      End Sub
        
      Sub 
      ResetCharacter(Character As String, _
         
      Optional ShiftState As Integer)
        
        CustomizationContext = 
      NormalTemplate
        FindKey(VkKeyScan(Asc(Character)) + ShiftState).Disable
      End Sub
        
      Sub 
      InsertCaption()
        ' Disable 
      the key assignments. 
        DeactivateAutoDegrees

        DeactivateAutoMetrics

        ' Display 
      the dialog box. 
        Dialogs(wdDialogInsertCaption).Show

        ' Re-enable 
      the key assignments. 
        ActivateAutoDegrees
        ActivateAutoMetrics
      End Sub
        
      Sub 
      ToolsEnvelopesAndLabels()
        ' Disable 
      the key assignments. 
        DeactivateAutoDegrees

        DeactivateAutoMetrics

        ' Display 
      the dialog box. 
        With 
      Dialogs(wdDialogToolsEnvelopesAndLabels) 
           ' Mimick the behavior of the original command. 
      
          .ExtractAddress = 
      True
          .Show
        End 
      With
        ' Re-enable 
      the key assignments. 
        ActivateAutoDegrees
        ActivateAutoMetrics
      End Sub
        
      Function 
      GetKeyString(Char As String)
        Dim 
      KeyName As String 
      * 256
        Dim 
      ShiftState As String
        Dim 
      KeyCode As Long, 
      ScanCode As Long, 
      lReturn As Long
        
      
        ' VkKeyScan 
      returns the scan code for the character. 
        KeyCode = VkKeyScan(Asc(Char)) 
        ' Look at 
      the keycode part that identifies the shift
        ' state, 
      using VBA constants for the Alt, Ctrl, 
        ' and Shift 
      key. 
        ShiftState = _
          IIf(KeyCode And wdKeyAlt, "Alt+", vbNullString) & 
      _
          IIf(KeyCode And wdKeyControl, "Ctrl+", vbNullString) & 
      _
          IIf(KeyCode And wdKeyShift, "Shift+", vbNullString) 
        ' Translate 
      the virtual-key code into a scan code. 
        ScanCode = 
      MapVirtualKey(KeyCode, 0) 
        ' 
      GetKeyNameText retrieves the name of a key. 
        ' The 
      return value is the lengh of the string. 
        lReturn = 
      GetKeyNameText(ScanCode * 2 ^ 16, KeyName, 255) 
        GetKeyString = ShiftState & 
      Left(KeyName, lReturn) 
      End Function

End Listing Two

Begin Listing Three -

AutoMetrics.bas

End Listing Three