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.

The purpose of this Microsoft Word Tips & Microsoft Word Help supplements my VBA Basics tips page and provides examples showing how you can cut repetition and write less code using sub-routines or functions that take arguments."

Note: It is my understanding that "technically," a calling procedure "passes" arguments while a called procedure (sub-routine or function) "accepts" parameters. In some VBA text you may see "argument" and "parameter" used interchangeably.  In this discussion, I will refer to the variables defined in a Sub or Function statement as parameters, but I use the term "take arguments."

Repetitive Code Statements/Blocks

When you write routines, you will often find yourself performing a repetitive process.  For example in the routine below I need to write values to three document bookmarks titled "A, B and C"

Sub DemoMain()
Dim oRng As Range
  With ActiveDocument
    Set oRng = .Bookmarks("A").Range
    oRng.Text = "Apples"
    .Bookmarks.Add "A", oRng
    Set oRng = .Bookmarks("B").Range
    oRng.Text = "Blueberries"
    .Bookmarks.Add "B", oRng
    Set oRng = .Bookmarks("C").Range
    oRng.Text = "Cherries"
    .Bookmarks.Add "C", oRng
  End With
End Sub

As you can see, the process to write a value to a named bookmark takes three steps (three lines of code) and those steps are then repeated in three blocks of code of similar code.

Routines That Take Arguments

In many cases, you can eliminate repetitive code and make your routines shorter and easier to follow by moving the repetitive steps to a separate routine (Sub or Function) that takes arguments.

The following can be used as a Sub-routine to perform the actual task of writing text to a bookmark.  It is simply a modified version of the code blocks used above for the same purpose:

Sub SR_WriteToBookmark(strBMName as String, strValue as String)
Dim oRng As Range
  With ActiveDocument
    Set oRng = .Bookmarks(strName).Range
    oRng.Text = strValue
    .Bookmarks.Add strName, oRng
  End With
End Sub

Notice that two variables have been declared in the Sub statement between the open and closing parenthesis. Variables declared is Sub or Function statements in this manner are called parameters. In this case, strBMName and strValue are parameters declared using a string data type.

You can define parameters in any Sub or Function statement.  I call Sub and Function statements with declared variables as parameters "Sub or Functions that take arguments."

The Sub SR_WriteToBookmark takes arguments.  It can be called from other Sub or Functions   as follows:

Sub DemoMain()
  SR_WriteToBookmark strBMName:="A", strValue:="Apples"
  SR_WriteToBookmark strBMName:="B", strValue:="Blueberries"
  'Or simply
  SR_WriteToBookmark "C", "Cherries"
End SubEnd Sub

Note: In the calling procedure shown above, I have used two methods to pass arguments to the called procedure. The first uses named arguments and leaves no doubt what the argument is. The second uses indexed (positional) arguments.  Which method you use is really up to you.  I typically use indexed arguments because they are easier to write.
However, I sometimes regret that decision because using indexed arguments in more complex routines can make it harder for people reviewing my code to read.  Worse still, when I look at my own code months or years later, I often have to scratch my head and try to figure out what I did.

Regardless of the method used, the efficiency of moving repetitive code to a separate routine that takes arguments is readily apparent.

Functions That Take Arguments

A function is similar to Sub in it can take arguments; but different in that it can 1) return a value of a declared data type and 2) be used as a variable within itself. The following is an example of a function.  While not very practical, it illustrates how a function can return a value and be a variable within itself:  

Sub DemoMain()
Dim dblArgA As Double, dblArgB As Double
A = 5.8: B = 11.2
MsgBox dblArgA & " + " & dblArgB & " = " & fcnBasicAddition(dblArgA, dblArgB)
End Sub

Function fcnBasicAddition(dblParamA As Double, dblParaB As Double) As Double
   fcnBasicAddition = dblParamA + dblParamB
End Function

Here we have a main (calling) procedure that passes arguments to a called a function. The main procedure has two declared variables, dblArgA and and dblArgB . Both are passed as arguments to the called function. The function has two parameters, the declared variables dblParamA and dblParamB.  The parameters take arguments from the called procedure.

Note: I used "Arg" (for argument) in declaring variables in the calling routine and "Param" (for parameter) in declaring parameters in the called function simply to illustrate this point. In some VBA text you may see "argument" and "parameter" used interchangeably.

Let's demonstrate a significantly more complex example.  This time we are going write code in the main routine to find and replace text in the active document and report the number of replacements performed.

Sub DemoMain()
Dim oRng As Range
Dim lngFound As Long
  Set oRng = ActiveDocument.Range
  With oRng.Find
   .ClearFormatting
   .Text = "A"
   .MatchWholeWord = True
   .Wrap = wdFindStop
   While .Execute
     oRng.Text = "Apples"
     oRng.Collapse wdCollapseEnd
     lngFound = lngFound + 1
    Wend
  End With
  MsgBox "There were " & lngFound & " instances found and replaced"
End Sub

That is a significant chunk of code for just one item and to make matters worse, unlike a Find and Replace process that you run from the user interface, a simple VBA Find and Replace routine like this only processes the main text storyrange of the document. It ignores everything else (headers, footers, footnotes, endnotes etc.).

 A VBA routine that performs find and replace in all storyranges can look like a real hairball and certainly not something you would want in your main routines even once let alone multiple times.

To deal with the relatively complex process of finding and replacing multiple find/replace text pairs anywhere it a document, I am going to start by showing you the main routine:

Sub DemoMain()
Dim lngIndex As Long
Dim lngFound As Long
Dim arrFind() As String, arrReplace() As String
  'Four terms we want to find.
  arrFind = Split("A,B,C,D", ",")
  'The four replacement terms.
  arrReplace = Split("Apples,Blueberries,Cherries,Dates", ",")
  For lngIndex = 0 To UBound(arrFind)
    lngFound = lngFound + fcnProcessStories(arrFind(lngIndex), arrReplace(lngIndex))
  Next lngIndex
  MsgBox "There were a total of " & lngFound & " intances found and replaced."
End Sub

You should already see that the main routine has less lines of code than the original. Notice that I have used the more simplified index method of passing arguments to the function fcnProcessStories. 

The following provides two functions and a sub-routine that take arguments.  Together with the calls in the main routine they perform to process the entire document and enable us to have a clean and compact main routine:   

Function fcnProcessStories(strFind As String, strReplace As String) As Long
Dim rngStory As Word.Range
Dim lngValidator As Long
Dim oShp As Shape

  'Fix the skipped blank Header/Footer problem
  lngValidator = ActiveDocument.Sections(1).Headers(1).Range.StoryType
  ResetFRParams Selection.Range
  'Iterate through all story types in the current document
  For Each rngStory In ActiveDocument.StoryRanges
    'Iterate through all linked stories
    Do
      fcnProcessStories = fcnProcessStories + fcnFRInStory(rngStory, strFind, strReplace)
      On Error Resume Next
      Select Case rngStory.StoryType
        Case 6, 7, 8, 9, 10, 11
          If rngStory.ShapeRange.Count > 0 Then
            For Each oShp In rngStory.ShapeRange
              If Not oShp.TextFrame.TextRange Is Nothing Then
                fcnProcessStories = fcnProcessStories + 
                                    fcnFRInStory(oShp.TextFrame.TextRange, _
                                    strFind, strReplace)
              End If
            Next
          End If
        Case Else 'Do Nothing
      End Select
      On Error GoTo 0
      'Get next linked story (if any)
      Set rngStory = rngStory.NextStoryRange
    Loop Until rngStory Is Nothing
  Next
End Function
Function fcnFRInStory(rngStory As Word.Range, _
                      strSearch As String, _
                      strReplace As String) As Long
  With rngStory.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = strSearch
    While .Execute
      rngStory.Text = strReplace
      rngStory.Collapse wdCollapseEnd
      fcnFRInStory = fcnFRInStory + 1
    Wend
  End With
End Function
Private Sub ResetFRParams(oRng As Range)
  With oRng.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = True
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute
  End With
End Sub

Don't be overwhelm by the amount of code. Instead, be relieved that by using sub-routines and functions that take arguments; we don't have to have all this code cluttering up our main routine!

The first function fcnProcessStories, takes the find and replacement terms  as arguments then simply performs the tasks of looping through all the document storyranges (and other difficult text ranges).  It passes each range and find and replacement terms as arguments to the second function fcnFRInStory.  Then it returns the number of replacements performed in all storyranges to the main routine.

The second function fcnFRInStory, is called from the first function.  It takes a range object and the find and replacement terms as arguments and performs the actual task of finding and replace text.  It returns the number of replacements performed in each range back to the calling procecure.

The sub-routine ResetFRParams is called from the first function and simply ensures any sticky parameter from a user define find and replace is reset to the parameters required by the operation.

Optional Arguments

In the sub-routine ResetFRParams used in the complex example above, I passed a single argument oRng.  All of the parameters oRng.Find in that routine are set according my preferences when performing find and replace with VBA.

 Let's suppose that you are not so set in your ways and while most of the time those are your preferences, but occasionally you want to set the .MatchWholeWord property to false instead of true.  This is where optional arguments are so handy.  Modify ResetFRParams as follows:

Private Sub ResetFRParams(oRng As Range, Optional bWholeWord As Boolean = True)
  With oRng.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = bWholeWord
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute
  End With
End Sub

Here we have added the optional argument bWholeWord and set it default value to True.

Now on those rare occasions you want to that value to false you simply pass "False" to the optional argument:

• Sub DemoMain()
•   ResetFRParams Selection.Range, False
• End Sub

Pass Arguments to a Parameter Array (ParamArray)

Occasionally you may need to pass an variable/unknown number of arguments to a called Sub or Function.  A good example might be a simple calculation solution where you add X numbers or multiply X numbers.  You don't want to have to change your code every time the number of terms is changed!  In this situation you can use a ParamArray.  When using a ParamArray remember:

• It must be the last parameter in the declared parameters.
• It must be declared as a variant.
• It can't be associated with ByVal, ByRef or Optional.

For example, the calling procedure in the code below can pass any number of variable terms to the called function: 

Sub DemoMain()
  MsgBox fcnBasicCalc("A", 1, 2, 3, 4)
  MsgBox fcnBasicCalc("M", 1, 2, 3, 4, 5)
End Sub

Function fcnBasicCalc(strRouter As String, ParamArray varTerms() As Variant) As Single
Dim lngIndex As Long
  For lngIndex = 0 To UBound(varTerms)
    Select Case strRouter
      Case "A": fcnBasicCalc = fcnBasicCalc + varTerms(lngIndex)
      Case "M"
        If lngIndex = 0 Then
          fcnBasicCalc = varTerms(lngIndex)
        Else
          fcnBasicCalc = fcnBasicCalc * varTerms(lngIndex)
        End If
    End Select
  Next
End Function

Since the ParamArray is (and must be) a variant data type you are free to pass any combination of data type arguments.

Sub DemoMain()
  AnyNumberArgs "Longs and text", 1, 2, "buckle my shoe"
  AnyNumberArgs "Doubles and text", 1.88 + 1.12, 1.5 + 2.5, "close the door"
  AnyNumberArgs "Arrays", Split("A|B|C", "|"), Split("1|2|3", "|")
End Sub

Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant)
Dim lngIndex As Long, lngItem As Long
 
  Debug.Print strName
  'Use UBound function to determine upper limit of array.
  For lngIndex = 0 To UBound(intScores())
   If Not IsArray(intScores(lngIndex)) Then
     Debug.Print " "; intScores(lngIndex)
    Else
      For lngItem = 0 To UBound(intScores(lngIndex))
        Debug.Print " "; intScores(lngIndex)(lngItem)
      Next
    End If
  Next lngIndex
End Sub

See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

That's it! I hope you have found this tips page useful and informative.

This page was last updated on March 09, 2021

Website Privacy Statement

Back to top     Back to Word Tips