Personal website of Gregory K. Maxey, Commander USN (Retired)
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."
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.
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.
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.
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:
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:
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.
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
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!