User-Defined Procedures and Functions

Most VBA macros consist of a single procedure. However, VBA lets you create additional procedures and functions that you can call from within the macro's main procedure. Procedures and functions are useful when you have a series of VBA commands or a complex calculation that you need to call on several times in a macro. By placing these commands in a procedure or function, you can code them once and call on them as many times as needed.

Using procedures

To create a procedure, use a Sub/End Sub command pair outside the Sub/ End Sub command pair for the macro's main procedure. The statements that make up the procedure go between the Sub and End Sub commands, and the Sub command supplies the name of the procedure and any arguments that are passed to the subroutine. For example:

Sub SayHello
    BeepMsg "Hello World!"
End Sub
Sub BeepMsg(Message As String)
    Beep
    MsgBox Message
End Sub

In this example, the BeepMsg procedure displays a message and sounds a tone to get the user's attention. In the macro's main procedure (named SayHello), you can use BeepMsg as though it were a built-in VBA command.

Using functions

A function is similar to a subroutine, with one crucial difference: A function returns a value. Here's an example:

Sub GetAnAnswer
    If GetYesNo("Yes, or no?") Then
	BeepMsg "You said yes."
    Else
	BeepMsg "You said no."
    End If
End Sub
Sub BeepMsg(Message As String)
    Beep
    MsgBox Message
End Sub

Function GetYesNo(Message As String) As Boolean
    If MsgBox(Message, vbYesNo) = vbYes Then
	GetYesNo = True
    Else
	GetYesNo = False
    End If
End Function

In this example, the GetYesNo function uses a MsgBox function to display a message box with Yes and No buttons. The return value from the MsgBox function determines which button the user pressed, and the return value of the GetYesNo function is set to True if the user clicked Yes or False if the user clicked No. Back in the Main routine, the GetYesNo function is used in an If statement to display You said yes if the user clicks Yes or You said no if the user clicks No.