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.
In this tutorial:
- Programming with VBA
- Basic Structure of VBA Macros
- Understanding the Basic Elements of VBA
- Working with Variables and Data
- Using static variables
- Using Strings
- Of Objects, Properties, and Methods
- Getting to know the object model
- Using the With statement
- Controlling Your Programs
- The ElseIf structure
- For/Next Loops
- User Input and Output
- User-Defined Procedures and Functions