The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

• Quarterdeck
• What's New
• Microsoft Word Tips
• Consulting
• Chain Locker
• Lucky Bag
• Contact
• Search

Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.

This Microsoft Word Tips & Microsoft Word Help page is provided for aspiring VBA (Visual Basic for Applications) enthusiasts as a collection of VBA Basics.

Disclaimer: I have had no formal training in computer programming or VBA, so if you close and exit this page now I will completely understand. However, I have had years of hands-on use and experience developing VBA solutions across a range of complexity. In the Navy, on submarines, we called that kind of experience School of the Boat, where hard lessons learned were usually remembered.

In my experience I found that I had written some fairly complex solutions before I had any real understanding of the tools that I was using. In this tips page, I hope to discuss and explain some of those tools in a manner that may save you some hair pulling and help you to avoid tripping over the same stumbling blocks that I have fallen over . Perhaps it will even help you write more efficient code

Acknowledgments: Much of what I have learned and share here is the product the generous help and support that I have received over the years from some of the Titans of Word VBA such as Jay Freedman, Jonathan West, Doug Robbins, Tony Jollans and many others. As is often the case, friend and Word MVP Graham Mayor has been untiring and merciless in checking my work and offering suggestions for improvement. Thanks All!!

What is VBA anyway?

VBA is a programming language included as part of Microsoft Office Word (and other Office suite applications) that provides the tools needed to create a solution to virtually any task or objective you face using Word. It can take over when the "Macro Recorder" available from the Word user interface (UI) falls short. It is such a powerful programming tool that, with the appropriate skill, you can completely customize Word and the Word UI.

VBA is a structured programming language, where individual statements are defined using the various building blocks of VBA such as objects, methods, and properties. These VBA statements are grouped in larger blocks called procedures. A procedure is a named group of statements that run as a unit to perform a specific task or calculate a specific result.

Note: The term "macro" is sort of tired slang for "VBA procedure." While the terms "Macro" and "Macros" are used exclusively in the Word UI, a macro is more accurately defined as a recording of VBA code statements which are used to automate a whole series of actions carried out directly from Word.

Accordingly all macros are procedures, but all procedures are not really macros. A macro is generally less effective than defining and using a properly constructed VBA procedure. For more on using the Macro recorder and its shortcomings, see the: Create a Macro Using the Recorder and: How to Modify a Recorded Macro.

If you looked at those articles, then some of the terms in this next block may be familiar. I recommend that you try to grasp and fully understand these basics now rather than later. A little more review and discussion can’t hurt.

VBA Building Blocks

The first step to learning how to create procedures is to learn about the building blocks.

Objects

VBA is an object-oriented programming language, which means the statements you create in VBA act on specific objects rather than being general commands. The Word application, and individual Word documents, are made of objects that you can manipulate through VBA statements. In fact a document is an object itself, as are individual paragraphs, sentences and words. There are many more types of objects defined in the Word object model. In many cases an object (e.g., Document) is a container for the collection of other objects (e.g., paragraphs, tables, etc.).

Collections

A collection is the group of all like objects contained in a parent object. For example, a document object functions as the container object for the collection of many other objects such as section objects, paragraph objects, or table objects. As paragraph object contains collections of sentence and words. A VBA statement that makes reference to an object in a collection uses the objects name or index. For example you might reference a named document object or an indexed paragraph object:

'Return the path of an open document in the documents collection named "VBA Basics.doc"
MsgBox Documents("VBA Basics.doc").Path
'Return the text of a first paragraph object in the ActiveDocument object
MsgBox ActiveDocument.Paragraphs(1).Range.Text

Methods

A method is an action that can be performed on an object. VBA objects are separated from their methods by periods. For example, if you wanted to save a particular file as part of a VBA program you could include the following statement in the code:

• Documents("VBA Basics.doc").Save

Remember, a method is an "action" that you perform on an object. The following screen shot shows some of methods and properties (discussed later) that are associated with the ActiveDocument object (using the Auto List Members feature). Notice the symbol for a method looks a bit like the "action" of hurling a green brick.

Note: I recommend that you keep "Auto List Members" in the VB Editor options checked. "Auto List Members" shows the various methods and properties that can be associated with objects after you type the period "." following an object when writing your code statements.

Properties

Properties are used to describe an object. Some properties are read-only, while others are read/write. For example, the document VBA Basics.doc is saved to a particular path on my computer. That path is a property of the document object. The .Path property is read-only as it cannot be changed, without saving the file to a different location. Properties are separated from objects by periods just as methods are. The following statement will display the current path of "VBA Basics.doc", as it is defined on my computer, in an onscreen message box:

• MsgBox Documents("VBA Basics.doc").Path

If the property is read/write and you set the property equal to something, it changes the current value of that particular property and therefore changes the description of the object. Otherwise VBA can tell you the properties current value. For example, the following statements change the .Name property of the selected text to “Times New Roman” then reports the .Name property applied:

• Selection.Font.Name = "Times New Roman"
• MsgBox Selection.Font.Name

Note: Sometimes a property returns an object. In the example above the "Selection.Font" property returns or sets a "Font" object that represents the character formatting of the specified object.

Also, in the example above, "Selection" is a global property of the global application property of a document object. It returns a selection object that represents the "selected range" or the insertion point in a document. Global objects or properties are top level and do not need to be preceded by the parent object as the following code statements should illustrate:

• MsgBox Selection.Font.Name
• MsgBox Application.Selection.Font.Name
• MsgBox ActiveDocument.Application.Selection.Font.Name

Each of the statements return the same value. As the "Selection" property is Global it does not require a reference to the "Application" property or the "ActiveDocument" object.

For a structured exercise demonstrating most of the material covered so far see my: VBA Exercise Tutorial

VBA Built-In Functions

Functions provide information or perform calculations that are useful in building VBA procedures. In the previous examples, the VBA MsgBox function was used to display information on the screen. Other examples of functions include returning the current date or time, or converting data types:

'Date function returns current system date
MsgBox Date 
'Time function returns current system time
MsgBox Time
'Val function returns numbers in a string as a numeric _
 value of appropriate type
MsgBox Val("1") + Val("1") 

Notes:
    1. VBA functions should not be confused with Function procedures. Function procedures will be discussed later.

    2. You can use the underscore character to split a single code statement (or comment) over two or more lines. For more on this see: Break VBA Code Lines Using Underscore

Events

An "Event" is an action initiated either by user action or by other VBA code. An "Event Procedure" is a Sub procedure that you design according to the specification of the event. The procedure is called automatically by Word when the event occurs.

For example, a Document object has an "Open" and "Close" event. If you have properly programmed the event procedure for the Open event, Word will automatically call that procedure, always named Document_Open and always located in the "ThisDocument" module of the project, whenever the document is opened.

The following code provides a simple demonstration of the Document_Open and Document_Close events:

• The Document_Open event stores a value of the current time.
• The Document_Close event procedure compares the stored value with the current time and reports the time the document was opened

Option Explicit
'Module level declaration
Dim tStart As Date 

Private Sub Document_Open()
  tStart = Now
lbl_Exit:
  Exit Sub
End Sub

Private Sub Document_Close()
Dim tStop As Long
  tStop = DateDiff("s", tStart, Now)
  MsgBox "This document has been open for: " & ConvertTimeString(tStop, True) & "."
lbl_Exit:
  Exit Sub
End Sub

Notes:
    1. Event procedures must be coded in Class modules. The "ThisDocument" and MSForm modules are special types of class modules. Modules and module types are discussed later.

    2. The function "ConvertTimeString" used in the example above is included in the demonstration document that you can download and the end of this tips page.

    3. A very experience coder once told me that as a matter of practice he did not like to let his procedures run to the "End Sub" statement and he always provided an "Exit Sub" statement.  While I see no harm in running to an End Sub statement, I typically include a label "lbl_Exit:" and Exit Sub statement in my code templates and these examples.

For more on Events and Event procedures see Bill Coan's: Take Control of Microsoft Word Through Events

Storage and Organization

A "Project" (document or template) is the top level VBA container for storage and organizing your VBA solutions. A project consists of one or more modules.

Modules

A module can be one of four types: Code module (or Standard module), Document module (i.e., ThisDocument), MSForm module (or userform module), or Class module.

Note: The document module (ThisDocument) and MSForm modules are actually special purpose class modules.

Code within a module consists of individual lines of code (or statements). There are three types of statements:

• Declaration
• Assignment
• Executable

Module level statements appear at the top of the module before any procedures (discussed later).

'Declaration statement
Dim oRng as Word.Range 
'Assignment statement
Set oRng = ActiveDocument.Range
'Executable statement
oRng.Delete 

Note:  In addition to the three statement types shown above, you can add "comments" to your code lines. I've already provided several examples of comments.  Comments consist of text preceded by the apostrophe character. They may be placed in lines at the end of code statements or in their own lines before or after code statements.

Procedures

Procedures are the meat and potato organization and storage units of a VBA solution. They are the blocks that get things done.

There are three types of VBA procedures:

• Sub Procedure
• Function Procedure
• Property Procedure

Procedures are executed or run (same meaning) in order to apply their statements. When a procedure is run, its statement are processed in a top-down line by line fashion performing the defined operations.

Procedure names can contain a combination of as many as 254 letters, numbers, and the underscore character ( _ ). However, variable names cannot begin with a number, nor can you use reserved keywords that have special meaning to the VBA compiler

Sub Procedure - The most commonly used procedure is the Sub. A Sub procedure is a series of one or more Visual Basic statements enclosed by the Sub and End Sub statements that performs an action or actions but doesn't return a value.

[Private | Public] [Static] Sub name ([Arglist])
[instructions]
[Exit Sub]
[instructions]
End Sub

• Private (optional) indicates that the procedure is accessible only to other procedures in the same module. Public (optional) indicates that the procedure is accessible to any procedures in any modules. All procedures are public by default. Static (optional) indicates that the procedure's variables are preserved when the procedure ends.
• Sub (required) indicates the beginning of a procedure.
• Arglist (optional) defines arguments passed to the procedure by a calling procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.
• Instructions (optional) represents valid VBA code.
• Exit Sub (optional) a statement that forces an immediate exit from the procedure.
• End Sub (required) indicates the end of a procedure.

The following example illustrates a basic Sub procedure:

'The Sub statement (Note: All procedures are public by default)
Sub SubExample()
  'The instructions
  'On Error GoTo Err_Hanlder
  With Selection.Font 
    'Set font color property
    .Color = wdColorRed 
    'Set font size property
    .Size = 14 
  End With
  'The Exit statement
  Exit Sub
Err_Handler:
'The End statement
End Sub 

Note: The Exit Sub statement typically precedes any error handling code statements. For more on error handling see my: Error Handling 101

A Sub procedure can take "parameters," such as constants, variables, or expressions that are passed to it as "arguments" by another "calling" procedure. The following is an example of a Sub that takes parameters passed as arguments from a calling procedure:

Sub Main()
  'Other code could go here
  'Call and pass arguments to another sub
  'Property values for font color _
  and size are passed as arguments
  FormatFontAtSelection Selection.Range, wdColorRed, 14 
  'Other code could go here
lbl_Exit:
  Exit Sub
End Sub

Sub FormatFontAtSelection(ByRef oRng As Range, oColor As Long, oSize As Long) 'Parameters
  With oRng.Font
    .Color = oColor
    .Size = oSize
  End With
lbl_Exit:
  Exit Sub
End Sub

Function Procedure - A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements.

• It is similar to a Sub procedure, but a function can also return a value.
• A function can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure.
• If a Function procedure has no arguments, its Function statement must include an empty set of parentheses.
• A function returns a value by assigning a value to its name in one or more statements of the procedure.

The following is an example of a Function procedure called from a Sub procedure. In the example the Function procedure returns a value declared with data type "Double" to the calling procedure:

Sub ConvertTemp()
Dim dblFahTemp As Double 'Variable declaration
Dim dblCelTemp As Double 'Variable declaration
  'Use the Inputbox and CDbl functions to get and convert a user input to a double _
variable data type
  dblFahTemp = CDbl(InputBox("Enter the temperature in degrees Fahrenheit", "Temperature"))
  'Get the converted temperature value by passing the Fahrenheit temperature _
   as a variable to a Function procedure
  dblCelTemp = Celsius(dblFahTemp)
  'Use the MsgBox function to report the results
  MsgBox dblFahTemp & " degrees Fahrenheit is " & dblCelTemp & " degrees Celsius."
lbl_Exit:
  Exit Sub
End Sub

Function Celsius(ByRef dblFahTemp As Double) As Double
  'Perform calculations to convert the Fahrenheit value to a Celsius value
  Celsius = (dblFahTemp - 32) * (5 / 9)
  'Apply appropriate number formatting
  Select Case True
    Case Celsius = Int(Celsius)
      Celsius = Format(Celsius, "0;-0")
    Case Else
      Celsius = Format(Celsius, "0.00;-0.00")
  End Select
lbl_Exit:
  Exit Function
End Function

Note: The example above included a declaration and extra executable statements for clarity. In practice you can nest the call to the function within the MsgBox function:

Sub ConvertTemp()
Dim dblFahTemp As Double 'Variable declaration
  dblFahTemp = CDbl(InputBox("Enter the temperature in degrees Fahrenheit", "Temperature"))"
  MsgBox dblFahTemp & " degrees Fahrenheit is " & Celsius(dblFahTemp) & " degrees Celsius."
lbl_Exit:
  Exit Sub
End Sub

Property Procedure - A procedure that creates and manipulates properties for a class module. A Property procedure begins with a Property Let, Property Get, or Property Set statement and ends with an End Property statement.

Note: A detailed discussion property procedures, class and MSForm modules are beyond the scope of this tips page, but I have include working examples in the demonstration document that you can download and the end of this tips page.

Variables/Constants

Variables/Constants are used to store information temporarily. As a procedure is executed, it holds values temporarily in memory. Variables/constants define the name and data type that the procedure associates with specific locations in memory. Sometimes this information will change during the execution of the code (variable) and sometimes it will be static (constant).

Each variable/constant has a specific type that indicates how much memory the data requires and the operations that can be performed on that kind of data.

Note: There will be more on using variables, including declaration statements, data types and scope later in this tips page.

Dim (stands for dimension) and Const (stands for Constant) statements are used to declare variables and constants and allocate storage space. They can appear in a "General Declarations" section at the top of a code module -or- immediately following a procedure declaration. For example:

Sub ConstantVariableDemo()
'Constant value that doesn't change during execution
Const pStr As String = "ABCD" 
'Variable value that changes during execution (from 1 through 4)
Dim i As Long 
  For i = 1 To Len(pStr) 
    MsgBox Mid(pStr, i, 1)
  Next i
  For i = 1 To Len(pStr)
    MsgBox Left(pStr, i)
  Next i
lbl_Exit:
  Exit Sub
End Sub

Declaring Variables/Constants

In almost all cases, you should make it point to declare all variables and constants used in your code and include in the declaration an explicit "data type" clause (i.e., ... as Integer, ... as Long , ... as String, etc.) for the following reasons:

Note: Range is an object (object data type) in the Word object model.

• Undeclared variables can be confusing or lead to unexpected results.
• Declared variables use less memory and enhance efficiency.
• You will have access to intelli-sense (Auto List Members) associated with the data type.  For instance if you declare oRng as Range and then type a . (dot), you will see the Auto List Members associated with the Range object.

• It makes debugging easier, because the VBA Editor will then be able to “spot” errors it would otherwise miss.  For example, if you accidentally misspell a variable name somewhere, this will be picked up as an undeclared variable if you compile your project using the Debug menu.

To explicitly declare multiple variables on a single line, you must include the data type for each variable:

• Dim strName as String, strAddr as String, strEmail as String

To help (or prod) you to use declared variables, I strongly recommend that you include the Module level statement "Option Explicit" as the first statement in all of your project modules. Using the Option Explicit statement forces you to declare every variable using a Dim, Private, Public, ReDim, or Static statement before you can use it. This may appear to add additional lines of code to your solution, but the benefit in manageable code far outweighs the effort involved.

Note: You can ensure that the Option Explicit statement will be added automatically to each new module that you create by checking "Require Variable Declaration" in the VB Editor options.

Note: The compiler will default to the type Variant for a variable that does not have a type specified. A Variant behaves like a chameleon, as it can become whatever type is required for the data assigned to it. This is usually undesirable because it is not memory efficient, it slows performance as VBA has to determine what type of data the Variant represents, and it can result in problematic type conflict errors. However, variables of data type variant can and do serve a useful purpose when it is known that the variable type cannot be determined - such as when capturing freeform entry by users

When you declare variables, you should choose meaningful variable names that describe the variable's purpose. Variable names must meet the same criteria as procedure names. There are 3 levels at which we can declare or dimension (Dim) variables/constants. These are:

• Procedure-Level
• Module-Level (Private)
• Project-Level or Public Module-Level

In each of these levels the variable/constant differs in scope and lifetime. This is discussed below:

Procedure-Level - These are probably the most widely used. They are declared inside the Procedure itself using the Dim or Const statements. See example below:

Sub ProcLevelDeclaration()
Dim i As Long
  Const pText As String = " seconds and counting"
  For i = 10 To 1 Step -1
    MsgBox i & pText
  Next i
  MsgBox "Blast off"
lbl_Exit:
  Exit Sub
End Sub

Variables/constants declared at the procedure level are not available to other procedures and they only retain their values for the life of that procedure. As soon as the procedure finishes, the variable/constant and its value are destroyed. This refers to a variable's/constant's scope.

Module-Level (Private) - These are variables/constants that are declared outside the individual procedures at the top of the module. See example below:

Option Explicit
Private Cnt As Long
Private Const pStr As String = "Testing "

Sub Procedure1()
  For Cnt = 1 To 4
    MsgBox pStr & Cnt
    If Cnt = 4 Then
      Cnt = 3
      Exit For
    End If
  Next Cnt
  Procedure2
lbl_Exit:
  Exit Sub
End Sub

Sub Procedure2()
  For Cnt = Cnt To 1 Step -1
    MsgBox pStr & Cnt
  Next Cnt
  MsgBox "Test Complete"
lbl_Exit:
  Exit Sub
End Sub

Variables/Constants declared using the Private or Private Const statements at the module-level (or within a form's General Declarations section) are available to all procedures within that module or form and they retain their assigned values, unless the Document closes or the End statement is used. However, these variables are not available to procedures outside the module in which they are declared.

Note: Variables and constants declared at the module level are Private by default. You could omit "Private" in the declaration statements shown above.

You can declare multiple variables in a single code statement but be sure to declare each variable explicitly. For example:

'OK
Dim pStr1 as String, pStr2 as String, pStr3 As String
'Don't use:
Dim pStr1, pStr2, pStr3 As Sting.
'In the case only pStr3 is explicitly declared as a string data _
  type. pStr1 and pStr2 are treated as variant data type

Project-Level, Document Level, or Public Module-Level - These are variables/constants that are declared "Public" at the module-level (or within a form's General Declarations section). See example below:

Option Explicit
Public Counter As Long
Public Const pString As String = "Testing "

Sub ThisModProcedure1()
  For Counter = 1 To 4
    MsgBox pString & Counter
    If Counter = 4 Then
      Counter = 3
      Exit For
    End If
  Next Counter
  'Call procedure in another module
  OtherModule.Procedure1
lbl_Exit:
  Exit Sub
End Sub

Variables/Constants declared as Public at the module level are available to all procedures, in all modules within the same project the variables are declared in. Their values are retained unless the Document closes or the "End" statement is used.

Note: Take care in declaring your variables and constants as it is a better practice to use the narrowest possible scope.

Remember, the "End" statement and "End Sub" or "End Function" statements are not the same. If the first procedure in the example above was split into two procedures as shown below, you could run the first procedure and then run the second procedure and the variable/constant values are preserved.

Sub ThisModProcedure2()
  For Counter = 1 To 4
    MsgBox pString & Counter   
    If Counter = 4 Then
      Counter = 3
      Exit For
    End If
   Next Counter
lbl_Exit:
  Exit Sub
End Sub

Sub ThisModuleProcedure2()
  OtherModule.Procedure1
End Sub

However, if an "End" statement is used in the first procedure then the variable value is not retained when you run the second procedure. You can see this for yourself in the in the examples document that you can download at the end of this tips page.

Static Declaration - Procedures and variables/constants may also be declared using the "Static" statement.

When the Static statement is used to declare a procedure, the procedure's variable remain in scope and retain their values until the document closes or the End statement is used:

Sub RunStaticProcDemo()
  'Attempt call to Demo 9 times 
  Demo
  Demo 
  Demo
  Demo
  Demo
  Demo
  Demo
  Demo
  Demo
lbl_Exit:
  Exit Sub
End Sub

Static Sub Demo()
'Each time run the value the variable i is preserved
Dim i As Long
  i = i + 1
  MsgBox i
  'When i grows to a value > 6 stop all code execution
  If i > 6 Then End
lbl_Exit:
  Exit Sub
End Sub

When the Static statement is used to declare a variable, the variable remains in scope and retains its values until the document closes or the End statement is used. Run the following procedure several times as an example:

Sub CostOfPurchase()
'Static variables remain in scope and retain their values after procedure is run.
'Declare variables
Static sngTotal As Single
Dim sngCostThisItem As Single
  sngCostThisItem = CSng(InputBox("Enter the cost of a purchase:"))
  sngTotal = sngTotal + sngCostThisItem
  'Display results
  MsgBox "The cost of a new purchase is: " & sngCostThisItem
  MsgBox "The running cost is: " & sngTotal
lbl_Exit:
  Exit Sub
End Sub

The following table summarizes variable/constant data types.

Using Sub-Routines and Functions That Take Arguments

See: Cut Repetition with Sub-Routines or Functions That Take Arguments.

For tips on the correct methods for using parenthesis in routines that call and pass arguments to sub-routines or functions, see: Using Parentheses In Code Statements and Calls

ByRef/ByVal

When you ran the example, did you notice how the variable values in the main procedure were altered by the changes made to the parameters by the called procedure?

This is the default "ByRef" behavior when passing arguments where a reference to the memory address storing the variable data is passed and changes made to the parameter result in changes to data in the memory address. Since "ByRef" is the default behavior you don't have to explicitly use "ByRef" in your code, but it is a good practice. The following statements are functionally the same:

• Sub CalledSub_Procedure(lngPar As Long, strPar As String)
• Sub CalledSub_Procedure(ByRef lngPar As Long, ByRefstrPar As String)

When ByVal is used the actual value of the argument is passed and changes made to the parameter in the called procedure are not reflected in the variable value of the calling procedure as the following should illustrate:

Sub Main_Procedure2()
Dim lngArg As Long, Dim strArg As String
  lngArg = 1
  strArg = "I am what I am."
  MsgBox "Passing: " & lngArg & " - " & strArg
  CalledSub_Routine2 lngArg, strArg
  MsgBox "After Passing: " & lngArg & " - " & strArg
lbl_Exit:
  Exit Sub
End Sub

Sub CalledSub_Procedure2(ByVal lngPar As Long, ByVal strPar As String)
  lngPar = lngPar + 1
  strPar = "I ain't what I used to be."
  MsgBox "lngPar = " & lngPar & " - strPar = " & strPar
lbl_Exit:
  Exit Sub
End Sub

For additional information and examples for passing arguments ByRef or ByVal see the module "Passing_Arguments" in the VBA Demonstration Document.

Best Practices

Now I would like to move past the basics tools of VBA and discuss just a few points that I feel are best practices.

Learn to Love Ranges

A range object represents a contiguous area in a document and is defined using a starting and ending character position.

Unlike the Selection object, where you can only have one at time, you can have as many range object variables declared and assigned as you like, in one or many different open documents. A range object has many of the attributes of the Selection object and most (but not all) of the methods that can used with the Selection object can also be applied to a range object.

To start out on this, take a relatively small macro that you have written using the Selection. Put the following line at the start:

• Dim ORng as Range
• Set ORng = Selection.Range

You should see that from the material presented earlier that those statements declare a range object variable and assign it so that it is located at the same position as the current selection (i.e., the start and end character position of the range object = the start and end character position of the selection.)

Then, wherever you have a line of code that says Selection.something, replace it with ORng.something.

Remember most, but not all, of the methods that you can use with the selection object can also be used with a range object. You may get some compile errors when you first attempt to execute your modified macro, so take a look and see what the problem is. It is very likely that you have used the HomeKey method of the Selection object to move you to the top of the document, or used some other method that can't be done with a range object.

There is almost always one or more ways of doing the same thing using a method appropriate for a range object. In the case of Selction.HomeKey, you could use:

• ORng.Move Unit:=wdStory, Count:=-1

Once you have gotten used to the idea of using a range object instead of the Selection, you can start getting more ambitious.

Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
'Other lines of code would go here.
oRng.Move Unit:=wdStory, Count:=-1
oRng.Select
Set oRng = Nothing

Note: It is always a good practice to explicitly destroy variable values when you are finished with their value as shown in the final assignment statement in the example above.

If you want to transfer unformatted text between two places (or even between two documents), then you can set a range object equal to the two places and use the properties and methods of the range object to perform the task.

Dim oRng1 As Word.Range
Dim oRng2 As Word.Range
  Set oRng1 = ActiveDocument.Paragraphs(1).Range
  Set oRng2 = ActiveDocument.Range
  oRng2.InsertAfter oRng1.Text
  Set oRng1 = Nothing
  Set oRng2 = Nothing

Almost everything within a document has a range, so you don't necessarily need to define a range variable. If you know that you want to set the contents of the second cell of the third row of the first table in the document, then you can do it this way:

• ActiveDocument.Tables(1).Cell(3, 2).Range.Text = "My new text"

You can even copy *formatted* text within and between documents without the clipboard, by making use of the FormattedText property.

This example copies the text and formatting from the selection into a new document:

• Set oRng = Selection.FormattedText
• Documents.Add.Content.FormattedText = oRng
• Set oRng = Nothing

Ranges have a number of advantages over the Selection. They typically require fewer lines of code to accomplish as task and manipulating a range object does not incur the overhead associated with Word having to move or change the selection "highlight" in the active document. This usually results in faster execution.

There are a few occasions there the selection has an advantage, where a range won't really do:

1. If you want to use the predefined bookmarks such as \Page or \HeadingLevel. These are always defined relative to the Selection.

• Selection.Bookmarks("\Page").Select

2. If you want to move up or down one line (as opposed to one paragraph) or want to identify the current line of text.

• Selection.MoveDown wdLine, 1

3. If you want to use some items returned by the Information property.

• MsgBox Selection.Information(wdActiveEndPageNumber)

4. If you want to work with a column in a table. This is because a column doesn't have a range property.

'Shade the 3rd column of the selected table
With Selection
.Tables(1).Columns(3).Select
.Shading.BackgroundPatternColor = wdColorGray60
End With

5. I also use Selection.InsertFile because if you use Range.InsertFile, the range ends up at the beginning of what you've just inserted, instead of at the end, which isn't ideal, and Selection.InsertFile is a little faster.

Other than these 5 specific circumstances, I try to use a range object whenever possible.

MsgBox Function

As shown earlier, a MsgBox function can be used to display information on the screen. It can also return a user response to a query and when used with other code statements direct the course of execution.

Select MsgBox in a VBA code statement and press F1. The VBA Help file will present a help topic on the MsgBox function with an example. Part of that example is shown below (I have removed the part associated with a help file because I don't have a help file builder to work with).

Dim Msg, Style, Title, Response, MyString
'Define message.
Msg = "Do you want to continue?" 
'Define buttons.
Style = vbYesNo + vbCritical + vbDefaultButton2 
'Define title.
Title = "MsgBox Demonstration" 
Response = MsgBox(Msg, Style, Title)
'User chose Yes.
If Response = vbYes Then 
  'Perform some action.
  MyString = "Yes" 
'User chose No.
Else 
  'Perform some action.
  MyString = "No" 
End If

While the example works to illustrate the use and purpose of the MsgBox function, I see several things in this example that I don't agree with. First all of the variables are declared as variants. Secondly there really isn't any need for variables at all.

A VBA function can be used with other statements to streamline coding. A more efficient way to use the MsgBox function is shown below where the MsgBox function is combined with the If ... Then … Else statement:

Sub MsgBoxDemo()
  'All together in one statement.
  If MsgBox("Do you want to continue?", vbCritical + cbYesNo + vbDefaultButton2, _
      "MsgBox Demonstration") = vbYes Then 
    'Perform some action.
  Else
   'Perform some other action or do nothing.
  End If
lbl_Exit:
  Exit Sub
End Sub

That's it now! Keep an eye on this page for future additions.  I hope you have found this tips page useful and informative.

You can download a Word document containing the examples shown in this tips page plus a working Class and MSForm module: VBA Basics Demonstration Document. For more on userforms see my: Create and Employ a Userform

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top    Back to Word Tips