How to cut out repetition and write much less code, by using subroutines and functions that take arguments

Article contributed by Dave Rado

Most of us write routines that do similar operations more than once. It makes your code much less cumbersome and much easier to follow if you hive off all such repetitive chunks of code into separate subroutines or functions.

The difference between a sub and a function is that a function can return a value. Within the function itself, you can treat the function name like a variable, and give it a value and then you can call the function and get that value. Here's a ridiculously simple (and not very useful!) example:

Function GetResult As Long
GetResult = 2
End Function

Sub Test()
MsgBox GetResult
'Returns 2
End Sub

But suppose you want to do a calculation 2 + 2. You could use:

MyResult = 2 + 2.

But alternatively, you could use a function to do the operation. The advantage is: less repetitive code (not so much in this example, because it's so simple, but in general).

So you could have:

Function SumTwoValues(FirstNum As Long, SecondNum As Long) As Long

    'Any sub or function can have variables passed to it,
    'and these variables, which need to be declared as shown here, enclosed in brackets
    'are called arguments

    SumTwoValues = FirstNum + SecondNum

End Function

And you can call the function like this:

Sub MainMacro()
Dim MyResult As Long
MyResult = SumTwoValues(2, 2)
End Sub

That's exactly the same as MyResult = 2 + 2, but if the function contained more than just one line of code, and was called often, using a function like that would greatly reduce the amount of code needed and also make your code easier to follow.

Let's take a more complex example. Supposing you had a macro that needs to do many Find and Replace operations, one after another. By using a subroutine that takes arguments to do the Find and Replaces it means you only need to have a single line of code for each and Replace. Here's a simple example:

Let's suppose that all your Find & Replace operations are identical except for the find text and replacement text. Then you could have a sub or function that gets called, which looks something like this:

Sub DoFindReplace(FindText As String, ReplaceText As String)

With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting

    .Text = FindText
    .Replacement.Text = ReplaceText

    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    Do While .Execute
        'Keep going until nothing found
        .Execute Replace:=wdReplaceAll
    Loop
    'Free up some memory
    ActiveDocument.UndoClear
End With

End Sub

You can then call it like this:

Sub MainMacro()
    'Remove double spaces
    Call DoFindReplace(" ", " ")
    'Remove all double tabs
Call DoFindReplace("^t^t", "^t")
'Remove empty paras (unless they folow a table or start or finish a doc)
    Call DoFindReplace("^p^p", "^p")
    'etc etc
End Sub

So only one extra line is needed for each Find and Replace operation.

You can make it a bit more flexible by making any other parameters (such as .MatchCase) that might change from one find & replace operaration to the next into arguments that you can pass values to, instead of hard coding them.

For instance, if .MatchCase is always set to False in your macro, you can just hard code it as shown above; but if it's True for some and False for others then you could use:

Sub DoFindReplace(FindText As String, ReplaceText As String, _
bMatchCase As Boolean)

'rest of sub as before except
.MatchCase = bMatchCase

End Sub

and you could call that like this:

Sub MainMacro()
Call DoFindReplace("Ibm", "IBMI", True)
Call DoFindReplace("laserjet", "LaserJet", False)
End Sub

With Subs and Functions that take arguments – as with Word's Methods (which are actually built-in functions) – you can choose whether or not to specify the variable names when you call them. In the examples given so far I haven't used the variable names in the calling statements; and in the first few examples, there was no real point, because it's obvious what's going on in them.

But in the last example, it's not so obvious what the third variable is (without looking at
the function) – so it's better to specify the variable names in this case, which you do like this:

Sub MainMacro()
Call DoFindReplace(FindText:="Ibm", ReplaceText:="IBM", bMatchCase:=True)
Call DoFindReplace(FindText:="laserjet", ReplaceText:="LaserJet", bMatchCase:=False)
End Sub

That is much easier to follow, for anyone maintaining your code, than

Call DoFindReplace("Ibm", "IBMI", True)
Call DoFindReplace("laserjet", "LaserJet", False)

Sometimes you might want to specify an argument in a sub or function, but you might want it to be optional. Most built-in Word functions include some optional arguments (such as the Background argument of the Printout method, for instance).

To make an argument optional, you simply prefix it with the keyword Optional when you declare it, for example:

Sub DoFindOrReplace(FindText As String, Optional ReplaceText As String)

In this example, the procedure could check the value of the optional ReplaceText variable; and do a Find & Replace if it had a value, but a Find if it didn't.

With optional arguments, you can also specify what value the variable should have if no value is specified. In other words you can specify its default value. If you do that, it's not optional in quite the same sense as before; it's actually mandatory, but you just don't have to specify its value in your code. If you don't specify its value, it will use the default value. So for example, you could have:

Sub DoFindReplace(FindText As String, ReplaceText As String, _
Optional bMatchCase As Boolean = False)

Then you'd only have to specify the value of the bMatchCase argument if you wanted it to be set to True. This can save a lot of typing!

For some code examples which call subs or functions with arguments:

How to use a single VBA procedure to read or write both custom and built-in Document Properties

How to get the username of the current user

Getting names of available printers

How to find out, using VBA, how many replacements Word made during a Find & Replace All

Finding and replacing symbols

Other related tips:

The art of defensive programming

Why variables should be declared properly

How to cut out repetition and write much less code, by using subroutines and functions that take arguments

Related articles