Resting Anchor

The Anchorage

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

VBA Basics
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


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.

Site Note IconNote: 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 FAQ articles written by MVPs Bill Coan: Creating a macro with no programming experience using the recorder and Jay Freeman: 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:

VBA Code Snippet:
'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:

Remember, a method is an "action" that you perform on an object. I like the "throwing brick" analogy that Bill uses in his article. 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 a hurled green brick.

vba basic 1

Site Note IconNote: 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:

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:

Site Note IconNote: 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:

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 Bill Coan's MVP FAQ: Getting to grips with VBA basics in 15 minutes

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:

VBA Code Snippet:
'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") 

Site Note IconNotes:
    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: What are underscores at the end of code lines there for?

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:

VBA Code Script:
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

Site Note IconNotes:
    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.

Site Note IconNote: 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:

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

VBA Code Snippet:
'Declaration statement
Dim oRng as Word.Range 
'Assignment statement
Set oRng = ActiveDocument.Range
'Executable statement
oRng.Delete 

Site Note IconNote:  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.

Site Note IconBonus Tip: By default, you are NOT required to declare variables used in your code, but undeclared variables can become very problematic. I 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.

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.

vba basics 2

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:

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.

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

The following example illustrates a basic Sub procedure:

VBA Code Script:
'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 

Site Note IconNote: 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:

VBA Code Script:
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.

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:

VBA Code Script:
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

Site Note IconNote: 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:

VBA Code Script:
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.

Site Note IconNote: 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.

Site Note IconNote: 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:

VBA Code Script:
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

You should make a point to include an explicit "type" clause (i.e., ... as Integer -or- ... as String) when declaring variables and constants.

Valid data types are Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String(variable-length text), String (fixed-length text), Object, Variant, a user-defined type, or a specific object type.

A table summarizing the available data types is include at the end of this tips page.

Site Note IconNote: 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:

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:

VBA Code Script:
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:

VBA Code Script:
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.

Site Note IconNote: 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:

VBA Code Snippet:
'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:

VBA Script:
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.

Site Note IconNote: 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.

VBA Code Script:
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:

VBA Cdoe Script:
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:

VBA Code Script:
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.

Data Types
Type Memory Type-Declaration Character Description
Byte1 bytenonePositive whole number ranging from 0 through 255 that can be represented as a binary value.
Boolean2 bytesnoneTrue or False
Integer2 bytes% Whole numbers ranging from -32,768 through 32,767.
Long (long integer4 bytes& Whole numbers ranging from -2,147,483,648 through 2,147,483,647.
Single4 bytes! Single-precision floating-point number (with decimal points) ranging from -3.402823E38 to 3.402823E38.
Double8 bytes# Double-precision floating-point number (which is more precise for very large or very small numbers) ranging from -1.79769313486232E308 to 1.79769313486232E308.
Currency8 bytes@Large numbers between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 (15 digits to left of decimal and 4 digits to the right of the decimal).
Date8 bytesnoneRepresents dates from January 1, 100 through December 31, 9999.
Object4 bytesnoneAn instance of a class or object reference.
String10 bytes + 1 byte per char$Series of any ASCII characters.
String (fixed-length)length of string noneSeries of any ASCII characters, of a pre-defined length.
Variantmin 16 bytesnoneAny kind of data except fixed-length String data and user-defined types.
Sub VariableDemo()
Dim Prompt$, varUserInput As Variant
Initialize a String variable with an instruction to appear in an InputBox
Prompt$ = "Please enter something."
' Apply your String variable as an argument for the InputBox function. Use a Variant variable to capture
' the user's entry in an InputBox
VarUserInput = InputBox(Prompt$)
' Display the user's entry by applying your variable within a message dialog
MsgBox varUserInput
End Sub

A couple of things to notice about this example are how a type-declaration character (that was the $ sign in the Prompt$ variable) can be applied to define the String variable. Also, you can see how a Variant was purposely used since the user could enter either numbers or text in the InputBox.

Using Sub-Routines and Functions That Take Arguments

For an introduction to this topic see the MVP Word FAQ: How to cut out repetition and write much less code, by using subroutines and functions that take arguments

For tips on the correct methods for using parenthesis in statement that call sub-routines or functions and pass arguments, see the MVP Word FAQ: When to use parentheses to enclose subroutine and function arguments

Considering those two helpful articles, let's look at a simple example:

VBA Code Script:
Sub Main_Procedure()
Dim lngArg As Long, Dim strArg As StringlngArg = 1
  strArg = "I am what I am."
  MsgBox "Passing: " & lngArg & " - " & strArg
  CalledSub_Routine1 lngArg, strArg
  MsgBox "After Passing: " & lngArg & " - " & strArg
lbl_Exit:
  Exit Sub
End Sub

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

Here we have a main procedure that calls a sub-procedure. The main procedure has two declared variables, lngArg and strArg . Both are passed as arguments to a called procedure. The variable values are reported before and after the call to the sub-procedure. The sub-procedure accepts the arguments as parameters and changes the parameter values.

Site Note IconNote: A calling procedure "passes" arguments while a called procedure "accepts" parameters. I used "Arg" (for argument) in declaring variables in Main_Routine (or calling procedure) and "Par" (for parameter) in CalledSub_Routine (or called procedure) simply to illustrate this point. In some VBA text you may see "argument" and "parameter" used interchangeably.

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:

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:

VBA Code Script:
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:

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:

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

VBA Code Snippet:
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

Site Note IconNote: 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.

VBA Code Snippet:
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:

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:

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.
  1. If you want to move up or down one line (as opposed to one paragraph) or want to identify the current line of text.
  1. If you want to use some items returned by the Information property.
  1. If you want to work with a column in a table. This is because a column doesn't have a range property.
VBA Code Snippet:
'Shade the 3rd column of the selected table
With Selection
.Tables(1).Columns(3).Select
.Shading.BackgroundPatternColor = wdColorGray60
End With
1 2 3 4 5
6 7 8 9 10
  1. 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).

VBA Code Snippet:
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:

VBA Code Script:
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

Share Stumbleupon

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo

Or

JustAnswerAsk a Word Expert OnlineSubmit