Be efficient and recycle your macro code

When you type the same code multiple times, consider reusing your code instead of recreating it over and over. To do this, place the code in a separate procedure and call that procedure whenever you need it.

Here, we’ll look at doing just that and how to pass values to the procedure as you call it.

Create the Solution

step1This macro creates a dummy document complete with headings and paragraphs. A UserForm gets data from the user and the code behind it creates the document.

Start with an empty Word document and, in the Visual Basic Editor, choose Insert > UserForm. Add three labels, two textboxes, two SpinButtons and two CommandButtons.

Arrange the first label, textbox and SpinButton side by side and then the second set. Set both textboxes’ Enabled property to False and their Value to 1. Add captions to the labels using the image as a guide.

Set the Commandbutton1 caption to “Make Document” and the CommandButton2 caption to “Quit” and its Cancel property to “True”.

Set both SpinButton Min properties to 1 and Max to 10. Double click CommandButton1 and type this:

Dim numheads, numsentences, i As Integer
On Error Resume Next
numheads = TextBox1.Text
numsentences = TextBox2.Text
For i = 1 To numheads
Call textline(1, True)
Call textline(numsentences, False)
Next i
Unload Me

Double click Quit and type this:

Unload Me

Double click SpinButton1 and type this:

TextBox1.Text = SpinButton1.Value

Double click SpinButton2 and type this:

TextBox2.Text = SpinButton2.Value

Type this into the UserForm1 code dialog:

Sub textline(lines As Variant, headvalue As Boolean)
Dim mytext As String
mytext = “The quick brown fox jumps over the lazy dog. “
For i = 1 To lines
Selection.TypeText (mytext)
If headvalue Then
Selection.Style = ActiveDocument.Styles(“Heading 1”)
End If
Next i
Selection.TypeParagraph
End Sub

Choose Insert > Module, add this code and save the file as a .docm document.

Sub MakeMyDocument()
UserForm1.Show
End Sub

How it works

step2In the code behind CommandButton1 are calls to the sub procedure textline, which also pass arguments to that procedure.

The arguments are a number and a Boolean variable (true/false) and they allow the one procedure to respond differently depending on the values in the variables passed to it.

The arguments have to be in the same order in the calling statement and in the procedure declaration but they don’t have to have the same name. The variable types have to match or at least be compatible.

Calling a procedure and passing values to it is a great way to use one piece of code in multiple ways.

Once the called procedure has finished execution, control is handed back to the calling procedure, which then continues to execute.