Home / Publications / Macros & Customization / Enhancing the Accessibility and Usability of Microsoft Office Applications Using VBA

Enhancing the Accessibility and Usability of Microsoft Office Applications Using Visual Basic

Copyright © Alan Cantor 2004. All rights reserved.

Introduction

When a person with a disability finds it inefficient, difficult, or impossible to perform a software task, the application itself can sometimes be tailored to meet the needs of the individual. Application customization is an indispensable technique for repairing accessibility problems and enhancing usability. The benefits of application customization for persons with disabilities include increased efficiency, reduced physical and mental effort, fewer errors, and greater user satisfaction.

In this paper I outline customization techniques that involve Visual Basic for Applications (VBA). VBA is a programming environment built into all Microsoft Office programs. It can be used to sculpt enhancements in Word, Excel, PowerPoint, Outlook, and Access.

About Visual Basic for Applications

Objects

Underlying all Microsoft Office applications are objects. Objects are its fundamental building blocks. Almost everything you do in an Office program involves manipulating objects. Each document, worksheet, dialog box, or range of text is an object that can be controlled programmatically in Visual Basic.

All applications consist of content and functionality. Content refers to words, numbers, or graphics. Content also refers to attributes or properties, such as the size of a window, the typeface of a word, or the thickness of a line. Functionality refers to the ways you can work with content, such as opening, closing, copying, deleting, pasting, or formatting. All objects are constructed of discrete units of related content and functionality.

Object model

The objects that make up an application are arranged hierarchically. The way that content and functionality are organized is called the object model. The content and functionality of applications are divided among the objects in the object model.

Properties and methods

A property is a characteristic of an object, such as its colour, size or caption. You set a property to specify a characteristic or behaviour of an object. A method is as a way to access the functionality of an object. In general, properties act on content, and methods act on functionality.

The object model and the User Interface

There are two ways to interact with objects: manually, using the User Interface (UI); or programmatically, using VBA. In the UI, you use the keyboard or mouse, or both, to navigate to the part of the application that controls the data you want to change or the commands you want to use. For every action you take in the UI, there is a Visual Basic equivalent. In Visual Basic statements, you navigate through the object model from the top-level object to the object that contains the content and functionality you want to manipulate.

Customization techniques

Built-in commands

Before starting to program in VBA, become familiar with preexisting commands. Built-in commands appear as UI menu items and toolbar icons. For example, "FileOpen" is the Word command that appears in the "File" menu as "Open...," and as an icon on the "Standard" toolbar. The "FileOpen" command can also be invoked by pressing Ctrl+O or Ctrl+F12.

Many built-in commands do not appear in menus or on toolbars, and do not have keyboard equivalents. Of the approximately 1200 Word commands, the majority are not available by default. Before creating new commands, check for existing commands that do the same thing. Here are techniques for exploring built-in commands:

“Tools | Customize | Commands”

To examine built-in commands, launch the application, choose "Tools | Customize," and select the "Commands" tab. Lists of commands are organized by category, i.e., by menu or function. Word includes supplementary categories including "Fonts," "Styles," and "All Commands." The "All Commands" list is especially useful because it shows actual command names. In other categories, "colloquial" command names are given instead, i.e., the text that appears in menus or as toolbar icon labels. For example, "Close" is the colloquial name for "FileClose" command.

“ListCommands” (Word only)

Use the "ListCommands" command to generate a table of Word commands and their key, menu, and toolbar assignments. The table lists colloquial command names, hotkeys, and the menu name or toolbar number on which commands appear.

  1. Select "Tools | Macro | Macros..."
  2. In the "Macro Name:" edit box, type "ListCommands" and press Enter.
  3. When prompted, choose "All Word Commands."
  4. Word will open a new document and generate a table of commands.

“ToolsCustomizeKeyboardShortcut” (Word only)

Use the "ToolsCustomizeKeyboardShortcut" command to identify actual names of menu, toolbar, and keyboard commands in Word.

  1. Press Ctrl+Alt+keypad-plus.
  2. Use the mouse or keyboard to perform any task. For example, click on a toolbar icon, press a hotkey, or choose a menu item.
  3. The "Customize Keyboard" dialog box will appear, indicating the name of the command, its category, and its hotkey(s). It is also possible to assign new hotkeys and choose a template file in which key assignments will be saved.

The macro recorder

Use the macro recorder to show how actions performed in the UI translate into Visual Basic instructions. Checking the code will reveal which objects, properties, and methods are needed to perform tasks.

  1. Choose a task to perform. Practice it before you record.
  2. Select "Tools | Macro | Record New Macro..."
    • Name the macro.
    • Assign a keyboard or toolbar shortcut. (To assign a toolbar shortcut, drag the macro name to a toolbar or menu.) The "Stop Recording" toolbar will appear.
  3. Perform the task.
  4. Click the "Stop" button, or choose "Tools | Macro | Stop Recording."

The macro recorder has limitations. You cannot record instructions to select text with a mouse, but you can record instructions to select text by keyboard. Another limitation is that you may not be able to capture exactly what you intend unless you edit Visual Basic instructions.

Revising recorded macros

It is normal for recorded macros to fail under certain circumstances. One of the best ways to learn Visual Basic is to repair recorded macros that almost work.

A recorded macro often fails because it contains superfluous code. Macros created with the recorder usually contain unneeded instructions. Strip away extraneous code so that the macro does exactly what you intended, and nothing more.

Code editing aids

VBA has features to assist in learning the language. These include:

Context-sensitive help

Position the insertion point within a property or method, or select it, and press F1. This will bring up a help screen with code samples.

Auto List Member

This feature displays a list containing code that would logically complete the statement at the current insertion point. Activate by pressing Ctrl+spacebar.

Auto Quick Info

When this feature is enabled, information about functions and their parameters displays as you type.

Run a macro

To run a macro within VBA, position the insertion point anywhere within the body of the macro and press F5. To run it line-by-line, press F8.

The Object Browser

Press F2 to obtain information about objects, properties, methods, events, and built-in constants.

Changing the User Interface

After you have discovered built-in commands or created your own, incorporate the commands into the UI. (Note: only the third technique is keyboard and screen reader accessible.)

Adding commands to menus and toolbars

  1. Choose "Tools | Customize." This dialog box must be open throughout this operation!
  2. Go to the "Commands" page tab.
  3. Choose a "Category." (For newly-created commands, choose "Macros.")
  4. Locate a "Command."
  5. Drag the command icon to a menu or toolbar.
  6. Drop the icon where you want the item to appear.
  7. Right-click the command to rename it, change its accelerator key, or modify its appearance.
  8. When finished, close the Customize dialog box.

Reorganizing menus and toolbars

  1. Choose "Tools | Customize."
  2. To move a command between and within menus and toolbars, drag it to its new destination.
  3. To copy a command, hold down Ctrl and drag it to its new destination on a menu or toolbar.
  4. To delete a command, drag the command from its menu or toolbar, and drop it anywhere where this is no menu or toolbar.
  5. When finished, close the Customize dialog box.

Assigning a hotkey to a command

  1. Choose "Tools | Customize."
  2. Click on the "Keyboard..." button.
  3. Choose a category from the left combo box.
  4. Choose a command, macro, etc. from the right combo box.
  5. Go to the "Press new shortcut key" box.
  6. Press a new shortcut key.
  7. Choose "Close" to close the "Customize Keyboard" dialog box.
  8. Choose "Close" to close the "Customize" dialog box.

Conclusion

Mastering VBA takes considerable time and effort, yet the payback is significant. When a software feature is hard to use, difficult to learn, or not accessible to a person with a disability, assistive technology professionals have two recourses: report the problem and hope for an eventual bug fix; or craft a solution that eliminates or minimizes the problem immediately.

References

No Author. Microsoft Office 97 Visual Basic Programmer's Guild. Microsoft Press. 1997.

Alan Cantor. Enhancing the Accessibility of Word 97 Using Built-in Commands, Macros and Visual Basic Procedures. Handout for pre-conference course at RESNA 1999, Long Beach, CA.