Introduction to VBA Programming

Programming fundamentals

Returning information

Working with tables

Miscellaneous routines

Click a topic:

Create a macro with no previous experience.

Assign a macro to a hot-key.

Assign a macro to the TAB key.

Change the behavior of the TAB key inside a table cell.

Force a keybinding to be stored in a particular template, using vba code.

Run a macro when a user double-clicks a button in the document.

Enable a user to double-click a "Y" to change it to an "N" and then to a "?" and then back to a "Y".

Add a menu to the menu bar and populate it with commands.

Add a toolbar button to launch an executable file.

Force a command bar to display all of its commands, even if this forces the command bar to wrap to a second or third line.

Protect command bars against changes.

Remove protection from command bars so they can be changed.

Create a module-level variable that can be used by any procedure (sub or function) within a module.

Prevent a user from interrupting a macro with Ctrl+Break.

Intercept the Close button (X) in the title bar of a user form.

Force the cursor back to its normal state at the end of a macro.

Save a preview image for a template, so the user can see the preview in the File New dialog box.

List in a text file all the code from a code module.

Making the transition from WordBasic to VBA.

Create a macro with no previous experience. <Top of Page>

Solution:
Word's macro recorder can help you acquaint yourself with macros and with Office 97's vba programming language.

Let's assume that you create several documents from scratch on a daily basis. After
composing the text, you press Ctrl+Home to position the cursor at the start
of the document. Then you click the Center tool on the Formatting toolbar to
center the first paragraph of your document. Eventually, it occurs to you
that you should be able to record a macro for these last actions. That is, a
macro that will automatically position the cursor at the start of the
document and center the first paragraph.

Technically, this scenario may call for a solution involving paragraph
styles. But for now let's create a macro solution and let's use the macro
recorder to create it.

Proceed as follows:
1. Choose Macro|Record New Macro on the Tools menu (or simply double-click
the REC button in the Status bar.)
2. Enter a macro name (sorry, no spaces or punctuation allowed).
3. Click the Assign Macro To Keyboard button.
4. Press Ctrl+F8, then click Assign and click Close.
Word will display the Stop Recording toolbar, which lets you know that Word
is recording your actions and will continue recording them until you click
the Stop Recording button on this toolbar. (Don't click it yet!)
5. Press Ctrl+Home to position your cursor at the start of your document.
6. Click the Center tool on the Formatting toolbar.
7. Click the Stop Recording tool on the Stop Recording toolbar.
8. Open some other document.
9. Position the cursor in the middle of the document (doesn't matter where).
10. Press Ctrl+F8.
11. Smile with satisfaction as Word moves the cursor to the start of the
document and centers the first paragraph.

So far, so good. Now to see what the macro looks like under the hood:
1. Choose Macro|Macros on the Tools menu.
2. Select the macro that you just created.
3. CLick Edit.

Word will open up the vba editor and display something like the following
macro:
Sub MyFirstRecordedMacro()
'
' MyFirstRecordedMacro Macro
' Macro recorded 10/31/98 by Elgin
'
Selection.HomeKey Unit:=wdStory
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
End Sub

4. To learn more about the code, position the cursor somewhere in
Selection.HomeKey and press F1 to view a related help topic.
5. Close the help window and repeat Step 4 with the cursor in other
locations.
6. On the vba editor File menu, choose Close and Return to Microsoft Word.

If you have the patience to try these steps, you'll be well on your way. The
rest is just details. There are lots of them but you can always search the
vba help system and return to the newsgroups for additional information.

Assign a macro to a hot-key. <Top of Page>

Solution:
1. Choose Customize on the Tools menu, then click Keyboard...
2. In the Categories list, choose Macros.
3. In the Macros list, choose a macro.
4. Enter a key combination in the space provided.
5. Before clicking Assign, select the template where you want the assignment
to be stored.
6. Click Assign.
7. Click Close.

Assign a macro to the TAB key. <Top of Page>

Solution:
Since the TAB key isn't accepted as a legitimate choice in the Customize dialog, use a macro to make the assignment.

Notes:
The following macro assigns the tab key to a macro called MacroNameGoesHere. Simply replace
"MacroNameGoesHere" with the name of the macro that you want to run and you'll be all set, except for the limitations noted afterward.

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
Sub AssignTabKey()
KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyTab), KeyCategory:= _
wdKeyCategoryMacro, Command:="MacroNameGoesHere"
End Sub

Limitation #1:
When the cursor is situated in a table cell, the tab key isn't interpreted
as a tab key. Instead, it is interpreted as a NextCell command. If you want
to control the action of the tab key in this situation, you must create a
subroutine called NextCell. For example:
Sub NextCell()
'Code listed here will control tab key inside a table cell
End Sub

Limitation #2:
When the cursor is situated in a form field in a protected section of a
protected document, the tab key isn't interpreted as a tab key. Instead, it
executes a built-in routine that lies beyond vba's reach.

Change the behavior of the TAB key inside a table cell. <Top of Page>

Note:
When the cursor is inside an unprotected table and you press the Tab key, Word runs a built-in routine called NextCell. If you create a custom version of this routine, Word will run your version instead of the built-in version. If the table is in a section of a document protected for forms, Word runs a built-in routine that lies beyond the reach of Visual Basic for Applications.

Solution:
Use a macro to move the cursor downward through each column when the tab key is pressed. Design the macro so that, when the cursor reaches the bottom of a column, it moves to the top of the next column.

Caveat:
The following routine has been tested only on tables where all rows have the same number of columns and all columns have the same number of rows.

1. Click Macro on the Tools menu, then click Macros... on the submenu.
2. Under Macro Name, enter NextCell, then click Create.
Word will show the following code, which represents the built-in NextCell routine:
Sub NextCell()
'
' NextCell Macro
' Moves to the next table cell
'
Selection.MoveRight Unit:=wdCell

End Sub

3. Replace the built-in code with the following Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
Sub NextCell()
While Selection.Information(wdStartOfRangeColumnNumber) > _
Selection.Information(wdMaximumNumberOfColumns)
Selection.MoveLeft
NeedToSelect = 1
Wend
If NeedToSelect = 1 Then
Selection.Cells(1).Select
Selection.MoveEnd unit:=wdCharacter, Count:=-1
Exit Sub
End If
CurrentRow = Selection.Information(wdStartOfRangeRowNumber)
CurrentColumn = Selection.Information(wdStartOfRangeColumnNumber)
If CurrentRow < Selection.Information(wdMaximumNumberOfRows) Then
Selection.Tables(1).Cell(CurrentRow + 1, CurrentColumn).Select
Selection.MoveEnd unit:=wdCharacter, Count:=-1
ElseIf CurrentColumn < Selection.Information(wdMaximumNumberOfColumns) Then
Selection.Tables(1).Cell(1, CurrentColumn + 1).Select
Selection.MoveEnd unit:=wdCharacter, Count:=-1
Else
Selection.Tables(1).Cell(1, 1).Select
Selection.MoveEnd unit:=wdCharacter, Count:=-1
End If
End Sub
4. Select Save Normal on the File menu.
5. Select Close and Return to MS Word on the File menu.
6. Position the cursor in a table and press the TAB key.
7. If not satisfied with the results, modify the code in the NextCell macro.
8. To return the TAB key to its normal functionality, delete or rename the NextCell macro.

Force a keybinding to be stored in a particular template, using vba code. <Top of Page>

Solution for normal.dot:
CustomizationContext = NormalTemplate
KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyControl, wdKeyAlt, wdKeyW), _
KeyCategory:=wdKeyCategoryCommand, Command:="FileClose"

Solution for other templates:
'run this code while viewing a document attached to the template
'where you want the keybinding to be stored
CustomizationContext = ActiveDocument.AttachedTemplate
KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyControl, wdKeyAlt, wdKeyW), _
KeyCategory:=wdKeyCategoryCommand, Command:="FileClose"

Run a macro when a user double-clicks a button in the document. <Top of Page>

Solution: Insert a macrobutton field into the document and format it to look like a button.

1. Press Control+F9 to insert a set of Field Code Braces. { } DO NOT SIMPLY TYPE THE BRACES. YOU MUST USE Control + F9.
2. Type the following between the braces:
MACROBUTTON MyMacroName Double-Click To Run Macro
NOTE: Use the name of an actual macro, instead of MyMacroName
You should end up with something like this:
{ MACROBUTTON MyMacroName Double-Click To Run Macro }
3. Right-click the area between the braces and select Toggle Field Codes.
4. Format the macro button to look like a button, using Format Font, Format
Paragraph, and Format Borders and Shading.

Enable a user to double-click a "Y" to change it to an "N" and then to a "?" and then back to a "Y". <Top of Page>

Solution:
Use a macrobutton field.

Notes:
A macrobutton field is a placeholder that displays a value (such as "Y", "N", or "?") and runs a macro when you double-click it. The macro can do anything you want it to do, including change the displayed value.

1. Before creating the macrobutton, create the macro that you want to run when the field is double-clicked. The following macro will exactly fit the needs described above:
Sub SymbolCarousel()
Select Case Selection.Fields(1).Code.Characters(29)
Case "Y"
Selection.Fields(1).Code.Characters(29) = "N"
Case "N"
Selection.Fields(1).Code.Characters(29) = "?"
Case "?"
Selection.Fields(1).Code.Characters(29) = "Y"
Case Else
End Select
End Sub

2. After creating the SymbolCarousel macro, create the macro button by positioning
the cursor where you want the N/Y/? value displayed. Then press Ctrl+F9 to
insert a set of field braces { }. (Don't just type the braces. You must insert
them using Ctlr+F9.)

3. Within the braces, type very carefullly the exact character string shown
between braces below (but don't type the braces). Be sure to leave a space before the word
MACROBUTTON, before SymbolCarousel, and before N. Do NOT put a space after
the N.
{ MACROBUTTON SymbolCarousel N}

4. Right-click the field expression and choose Toggle Field codes.
5. Double-click and see if the N changes to a ?. Double-click again and see if it changes to a Y. Again and it should change back to N.

Add a menu to the menu bar and populate it with commands. <Top of Page>

Solution:
Add a menu called "MyMenu" to the menu bar, then add several commands to the menu. In this case, all of the commands do the same thing: they run a macro called TestMacro.

**Before running this code, take a minute to make a macro called TestMacro. Otherwise, the commands on the new menu will generate an error when Word realizes that the commands are trying to run a non-existent macro.**

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
'specify where new menu will be stored
CustomizationContext = ActiveDocument.AttachedTemplate
'add a menu to the Menu Bar
With CommandBars("Menu Bar").Controls
.Add(Type:=msoControlPopup, Before:=3).Caption = "&MyMenu"
End With
'add commands to the new menu
With CommandBars("Menu Bar").Controls("MyMenu").Controls
Set myButton = .Add(Type:=msoControlButton)
myButton.FaceId = 18
myButton.Caption = "Run Test Macro"
myButton.OnAction = "TestMacro"
Set myButton = .Add(Type:=msoControlButton)
myButton.FaceId = 23
myButton.Caption = "Run Test Macro"
myButton.OnAction = "TestMacro"
Set myButton = .Add(Type:=msoControlButton)
myButton.FaceId = 201
myButton.Caption = "Run Test Macro"
myButton.OnAction = "TestMacro"
myButton.BeginGroup = True
Set myButton = .Add(Type:=msoControlButton)
myButton.FaceId = 208
myButton.Caption = "Run Test Macro"
myButton.OnAction = "TestMacro"
End With

Add a toolbar button to launch an executable file. <Top of Page>

Solution:
1. Create a macro to launch the executable file.
2. Assign the macro to a toolbar.

To create the macro, proceed as follows:
1. Choose Macro|Macros... on the Tools menu.
2. Enter a name for the macro, such as LaunchMyProgram
3. Click Create
4. In the vba macro editing window, enter the Shell statement, something like:
Shell c:\MyProgram.Exe

(Place the filename within double quotes if it contains any spaces.)
5. Choose Save Normal on the File menu.
6. Choose Close and Return To Word on the File menu.

To assign the macro to a toolbar:
1. Rightclick any toolbar and select Customize.
2. Click the Commands tab at the top of the dialog box.
3. In the Categories list, choose Macro.
4. In the Macros list, choose LaunchMyProgram and drag it up to the toolbar
of your choice.
5. Right-click the resulting button and choose Change Button Image and
select an image.
6. Right-click the button again and choose Default Style.
7. Click Close to close the Customize dialog box.

Force a command bar to display all of its commands, even if this forces the command bar to wrap to a second or third line. <Top of Page>

Notes:
When a command bar has too many controls to fit on one line, the command bar first drops any controls that don't have priority. If the command bar still doesn't fit on one line, then the command bar wraps to the next line.

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
For Each oControl In CommandBars("Standard").Controls
oControl.Priority = 1
Next oControl

Protect command bars against changes. <Top of Page>

Solution:
Protect the command bars.

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
'NOTE: enable exactly one CustomizationContext statement:
'CustomizationContext = NormalTemplate
'CustomizationContext = ActiveDocument.AttachedTemplate
For Each oCommandBar In CommandBars
If oCommandBar.Visible = True Then
oCommandBar.Protection = msoBarNoChangeDock + _
msoBarNoChangeVisible + msoBarNoCustomize + _
msoBarNoMove + msoBarNoResize
End If
Next oCommandBar

Remove protection from command bars so they can be changed. <Top of Page>

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
'NOTE: enable exactly one CustomizationContext statement:
'CustomizationContext = NormalTemplate
'CustomizationContext = ActiveDocument.AttachedTemplate
For Each oCommandBar In CommandBars
oCommandBar.Protection = msoBarNoProtection
Next oCommandBar

Create a module-level variable that can be used by any procedure (sub or function) within a module. <Top of Page>

Notes:
Any code outside a procedure is referred to as module-level code.

Solution:
Place the following code at the start of a code module (say, NewMacros) and run each subroutine in turn.

Dim myString As String
Sub Test()
myString = "Hello"
End Sub
Sub Test2()
myString = "Goodbye"
End Sub
Sub Test3()
MsgBox myString
End Sub

Prevent a user from interrupting a macro with Ctrl+Break. <Top of Page>

Solution:
Application.EnableCancelKey = wdCancelDisabled

Intercept the Close button (X) in the title bar of a user form. <Top of Page>

Solution:
Place some code into the user form's QueryClose event.

Code:
This code is provided for illustrative purposes only and is not warranted to be suitable for any particular business purpose. The code may be freely copied for any lawful business purpose.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
End Sub

Force the cursor back to its normal state at the end of a macro. <Top of Page>

Solution:
Add one of the following lines to the end of the macro:
System.Cursor = wdCursorIBeam
System.Cursor = wdCursorNormal

Save a preview image for a template, so the user can see the preview in the File New dialog box. <Top of Page>

Solution:
Choose Properties on the File menu and click the Summary tab. Then place a
checkmark next to Save Preview Picture.

List in a text file all the code from a code module. <Top of Page>

Solution:
Go to the Project Explorer window of the Visual Basic Editor, right-click the module you want to export and select Export File.

Making the transition from WordBasic to VBA. <Top of Page>

The most difficult part about making the transition from WordBasic to VBA is learning to use Word's object model (instead of Word's user interface) as an organizing principle.

As a WordBasic programmer, you could view macros as silent, behind-the-scenes users of Word. Indeed, if you read through a WordBasic macro, you find a series of commands that might just as well have come from a user. When Word 6 or 7 executed a macro, it behaved exactly as it would have behaved if a user were sitting at the keyboard and executing the commands manually.

Sure, WordBasic offered looping and branching, but these were merely tools for managing the order in which commands were to be executed. The commands themselves might just as well have come from a user.

As a Visual Basic programmer, you can view your macros as object manipulators. You can drill down to a particular object and then manipulate it. In the end, the types of manipulations that you can perform aren't all that different from what the user can accomplish via the user interface. But your macros can "reach" objects in more powerful and flexible ways than user interface commands could ever do. Moreover, your macros can reach those objects without regard to the current position of the selection. Plus, you can branch and loop in new, more powerful ways than you could with WordBasic.

Consider the following statement, which allows you to manipulate the entire range of text found in the second row of the first table of the active document:

ActiveDocument.Tables(1).Rows(2).Range.Bold = True

Now consider this looping structure, which allows you to manipulate the same range within ALL tables in the document:

Dim oTable as Table
For each oTable in ActiveDocument.Tables
oTable.Rows(2).Range.Bold = True
Next oTable