Subroutines & Arguments in VBA
Learn how to create reusable Sub procedures, pass arguments, use ByVal and ByRef, work with optional parameters, and build practical Excel automation programs.
1. What Is a Subroutine?
A Subroutine is a named block of VBA code
that performs a particular task. In VBA, a Subroutine is
declared using the Sub keyword and ends with
End Sub.
Instead of writing the same instructions repeatedly, you can create a Sub procedure once and call it whenever required. Subroutines are useful for formatting reports, calculating totals, processing rows, and preparing worksheets.
2. Syntax of a Sub Procedure
Sub ProcedureName()
' Statements to execute
End Sub
Example 1: Simple Subroutine
Sub WelcomeMessage()
MsgBox "Welcome to VBA Programming!"
End Sub
The procedure is defined with the name
WelcomeMessage. When it runs, VBA displays
a message box.
How to execute it
- Open the Visual Basic Editor using Alt + F11.
- Insert a standard module.
- Write the Sub procedure.
- Place the cursor inside the procedure.
- Press F5 to run it.
3. Calling One Subroutine from Another
A Sub procedure can execute another Sub procedure. This helps divide a large task into smaller, reusable operations.
Example 2: Calling another Sub
Sub MainProcedure()
MsgBox "Starting the program."
DisplayMessage
MsgBox "Program completed."
End Sub
Sub DisplayMessage()
MsgBox "This message comes from another Sub."
End Sub
Execution sequence
MainProcedurestarts.- The first message box appears.
DisplayMessageexecutes.- Control returns to
MainProcedure. - The final message box appears.
Using Call
Sub MainProcedureWithCall()
Call DisplayMessage
End Sub
The Call keyword is optional when calling a Sub.
If you use Call, enclose the argument list in
parentheses when arguments are supplied. Without
Call, arguments to a Sub are normally written
without parentheses.
4. What Are Arguments?
Arguments are values supplied to a procedure when it is called. They allow one reusable Sub to work with different input values.
A parameter is the variable declared in the procedure definition. An argument is the actual value or variable supplied in the call.
| Term | Meaning | Example |
|---|---|---|
| Parameter | A variable declared in the procedure definition. | studentName As String |
| Argument | A value supplied when calling the procedure. | "Rahul" |
Example 3: Subroutine with one argument
Sub DisplayStudent(ByVal studentName As String)
MsgBox "Student Name: " & studentName
End Sub
Sub RunStudentExample()
DisplayStudent "Rahul"
DisplayStudent "Priya"
DisplayStudent "Amit"
End Sub
The same Sub procedure can display different student names because the caller supplies a different argument each time.
5. Passing Multiple Arguments
A Sub procedure can accept two or more arguments. Separate the parameters using commas in the procedure declaration.
Example 4: Calculate total price
Sub CalculateTotal(ByVal quantity As Long, _
ByVal unitPrice As Currency)
Dim totalAmount As Currency
totalAmount = quantity * unitPrice
MsgBox "Total Amount: " & Format(totalAmount, "0.00")
End Sub
Sub RunTotalExample()
CalculateTotal 5, 250
CalculateTotal 3, 1000
End Sub
| Quantity | Unit Price | Total |
|---|---|---|
| 5 | 250 | 1,250 |
| 3 | 1,000 | 3,000 |
Explanation: The calling procedure passes quantity and unit price. The reusable Sub calculates the total for whichever values are supplied.
6. ByVal in VBA
ByVal passes an argument's value to the parameter.
Reassigning the parameter does not change the caller's original
variable.
Example 5: Demonstrating ByVal
Sub ChangeValueByVal(ByVal number As Long)
number = 100
MsgBox "Value inside Sub: " & number
End Sub
Sub TestByVal()
Dim x As Long
x = 10
ChangeValueByVal x
MsgBox "Value outside Sub: " & x
End Sub
| Location | Value |
|---|---|
| Inside Sub | 100 |
| Outside Sub | 10 |
7. ByRef in VBA
ByRef passes an argument by reference. When a
compatible variable is passed, assigning a new value to the
parameter can change the caller's variable.
Example 6: Demonstrating ByRef
Sub ChangeValueByRef(ByRef number As Long)
number = 100
End Sub
Sub TestByRef()
Dim x As Long
x = 10
ChangeValueByRef x
MsgBox "Value outside Sub: " & x
End Sub
ByVal vs ByRef
| ByVal | ByRef |
|---|---|
| Passes the argument's value. | Passes access to the argument variable. |
| Reassigning the parameter does not reassign the caller's variable. | Reassigning the parameter can change the caller's variable. |
| Useful when the procedure should not update the caller's variable. | Useful when the procedure must update a variable supplied by the caller. |
ByRef if neither ByVal nor
ByRef is specified. Declare the intended
passing method explicitly for clearer code.
8. Optional Arguments
An Optional parameter allows a procedure to be
called without supplying that argument. If the caller omits
the argument, VBA uses the declared default value when one
is provided.
Example 7: Optional discount rate
Sub CalculateDiscount( _
ByVal price As Currency, _
Optional ByVal discountRate As Double = 0.1)
Dim discountAmount As Currency
Dim finalPrice As Currency
discountAmount = price * discountRate
finalPrice = price - discountAmount
MsgBox "Original Price: " & Format(price, "0.00") & vbCrLf & _
"Discount: " & Format(discountAmount, "0.00") & vbCrLf & _
"Final Price: " & Format(finalPrice, "0.00")
End Sub
Sub RunDiscountExamples()
CalculateDiscount 1000
CalculateDiscount 1000, 0.2
End Sub
| Call | Rate | Discount | Final Price |
|---|---|---|---|
CalculateDiscount 1000 |
10% | 100 | 900 |
CalculateDiscount 1000, 0.2 |
20% | 200 | 800 |
Optional parameters must appear after required parameters. In this example, the default discount rate is 10%, but the caller can provide a different rate.
9. Named Arguments
Named arguments let you identify a parameter by its name instead of relying only on its position in the call. They are especially useful when a procedure has several optional parameters.
Example 8: Using named arguments
Sub DisplayEmployee( _
ByVal employeeName As String, _
Optional ByVal department As String = "General", _
Optional ByVal location As String = "Pune")
MsgBox "Name: " & employeeName & vbCrLf & _
"Department: " & department & vbCrLf & _
"Location: " & location
End Sub
Sub RunNamedArgumentExample()
DisplayEmployee "Rahul"
DisplayEmployee "Priya", _
location:="Mumbai"
DisplayEmployee employeeName:="Amit", _
department:="Finance", _
location:="Pune"
End Sub
Named arguments make calls easier to read and allow you to specify an optional argument without supplying every optional argument before it.
10. Practical Example: Reusable Worksheet Formatting
Suppose you regularly prepare reports. Instead of writing formatting instructions for every report, create a reusable Sub that accepts a worksheet range as an argument.
Example 9: Format a report heading
Option Explicit
Sub FormatHeading(ByVal headingRange As Range)
With headingRange
.Font.Bold = True
.Font.Size = 16
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(13, 110, 253)
.HorizontalAlignment = xlCenter
End With
End Sub
Sub CreateReports()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Employee Report"
ws.Range("A1:D1").Merge
FormatHeading ws.Range("A1:D1")
ws.Range("A3").Value = "Employee Name"
ws.Range("B3").Value = "Department"
ws.Range("C3").Value = "Salary"
ws.Range("D3").Value = "Status"
FormatHeading ws.Range("A3:D3")
ws.Columns("A:D").AutoFit
MsgBox "Report formatting completed.", vbInformation
End Sub
Explanation
FormatHeadingaccepts a Range object.- The Sub applies bold text, font size, font color, background color, and alignment.
CreateReportsprepares the worksheet headings.- The formatting Sub is called twice for different ranges.
- The same formatting logic can be reused in other report procedures.
11. Passing Worksheet Objects to a Sub
A procedure can receive a worksheet object as a parameter. This makes the code reusable across different worksheets without hardcoding a worksheet name inside the procedure.
Example 10: Add a heading to a worksheet
Option Explicit
Sub AddWorksheetHeading(ByVal ws As Worksheet, _
ByVal headingText As String)
With ws.Range("A1")
.Value = headingText
.Font.Bold = True
.Font.Size = 15
End With
End Sub
Sub RunWorksheetHeading()
AddWorksheetHeading _
ThisWorkbook.Worksheets("Sheet1"), _
"Monthly Sales Report"
AddWorksheetHeading _
ThisWorkbook.Worksheets("Sheet2"), _
"Employee Report"
End Sub
12. Validate Arguments Before Processing
A reusable procedure should check whether the supplied arguments meet its requirements. For example, a discount rate should not be negative or greater than 100%.
Example 11: Validate discount rate
Option Explicit
Sub ApplyValidatedDiscount( _
ByVal price As Currency, _
ByVal discountRate As Double)
Dim finalPrice As Currency
If price < 0 Then
MsgBox "Price cannot be negative.", vbExclamation
Exit Sub
End If
If discountRate < 0 Or discountRate > 1 Then
MsgBox "Discount rate must be between 0 and 1.", vbExclamation
Exit Sub
End If
finalPrice = price * (1 - discountRate)
MsgBox "Final Price: " & Format(finalPrice, "0.00"), vbInformation
End Sub
Sub RunValidatedDiscount()
ApplyValidatedDiscount 2000, 0.15
End Sub
13. Understanding Procedure Scope
Scope determines where a procedure can be called from. In a standard module, a Sub declared Public is generally accessible throughout the VBA project, while a Private Sub is restricted to its declaring module.
Public procedure
Public Sub ShowPublicMessage()
MsgBox "This is a public procedure."
End Sub
Private procedure
Private Sub ShowPrivateMessage()
MsgBox "This is a private procedure."
End Sub
14. Common Mistakes to Avoid
- Wrong argument count: Supplying fewer arguments than required or supplying too many arguments.
- Wrong argument type: Passing text when a numeric parameter is expected.
- Incorrect ByVal/ByRef assumption: Understanding whether a parameter can change a caller's variable.
- Incorrect Call syntax: Using parentheses incorrectly when calling a Sub.
- Optional parameters in the wrong order: Optional parameters must follow required parameters.
- Hardcoded worksheet names: Pass worksheet or range objects when that makes a procedure more reusable.
- No input validation: Validate values before calculations or worksheet modifications.
15. Practice Exercises
Create separate procedures for these tasks and test them using different arguments.
- Create a Sub that displays a welcome message.
- Create a Sub that accepts a student name and displays it.
- Create a Sub that accepts two numbers and displays their sum.
- Create a Sub that accepts quantity and unit price and calculates the total.
- Demonstrate the difference between ByVal and ByRef.
- Create a Sub with an optional discount rate.
- Use named arguments to pass an employee name and department.
- Create a reusable Sub that formats a specified Range object.
- Pass a Worksheet object to a procedure and add a heading.
- Validate a price and discount rate before calculating the final price.
- Create one main procedure that calls three smaller Sub procedures.
- Create a Private helper Sub and call it from another procedure in the same module.
Important Points to Remember
- A Sub procedure begins with
Suband ends withEnd Sub. - Parameters are declared in the procedure definition; arguments are supplied when calling it.
- One Sub can call another Sub.
ByValprevents reassignment of the caller's ordinary variable through the parameter.ByRefcan allow a procedure to change the caller's variable.- Use
Optionalfor parameters that do not always need to be supplied. - Use named arguments to make calls clearer when several parameters are available.
- Pass worksheet or range objects to create reusable Excel procedures.
- Validate arguments before performing important calculations or updates.
Summary
Subroutines organize VBA instructions into reusable procedures. Arguments allow a procedure to receive different values, while ByVal and ByRef control how parameters interact with variables supplied by the caller. Optional and named arguments make procedures more flexible and readable.
Passing worksheet and range objects allows the same procedure to work with different parts of a workbook. These techniques help create maintainable VBA automation for reports, calculations, formatting, and data processing.