Lesson 6: Subroutines and Functions in Excel VBA 365

6.1: Subroutines in Excel VBA

A Subroutine (or Sub procedure) in Excel VBA 365 is a block of code that performs specific tasks when called. Unlike event procedures tied to controls, subroutines are standalone procedures that can be executed from multiple places in your code.

Key Characteristics of Subroutines:

• Modular: Break complex tasks into manageable pieces
• Reusable: Can be called multiple times from different parts of your program
• Organized: Help maintain clean and structured code
• Can accept parameters: Receive input values when called

Creating a Subroutine:

To create a subroutine in Excel VBA 365:

1. Open the Visual Basic Editor (Alt+F11)
2. Insert a new module (Insert > Module)
3. Define your subroutine using the Sub keyword

Sub YourSubroutineName(parameters)
    ' Your code here
End Sub

Example 6.1: Basic Subroutine

This example demonstrates a simple subroutine that displays a message:

Sub GreetUser()
    MsgBox "Welcome to Excel VBA 365!", vbInformation, "Greeting"
End Sub

Example 6.2: Subroutine with Parameters

This example shows how to pass parameters to a subroutine:

Sub CalculateArea(length As Double, width As Double)
    Dim area As Double
    area = length * width
    MsgBox "The area is: " & area, vbInformation, "Area Calculation"
End Sub

Calling Subroutines

You can call subroutines in several ways:

' Method 1: Using Call keyword
Call GreetUser

' Method 2: Without Call keyword
CalculateArea 10, 5

' Method 3: From another subroutine
Sub MainProcedure()
    Call GreetUser
    CalculateArea 15, 20
End Sub

6.2: Functions in Excel VBA

Functions in Excel VBA are similar to subroutines but with one key difference - they return a value. Functions can be either built-in (provided by VBA) or user-defined (created by you).

6.2.1 Built-in Functions

Excel VBA provides numerous built-in functions. Two commonly used ones are:

MsgBox Function

result = MsgBox(Prompt, Buttons, Title)

InputBox Function

The InputBox function creates a dialog box for user input:

userInput = InputBox(Prompt, Title, DefaultText, XPos, YPos)

Example 6.3: Using InputBox

Sub GetUserName()
    Dim userName As String
    userName = InputBox("Please enter your name:", "User Identification")
    
    If userName <> "" Then
        MsgBox "Hello, " & userName & "!", vbInformation, "Greeting"
    Else
        MsgBox "You didn't enter a name.", vbExclamation, "Notice"
    End If
End Sub

6.2.2 Creating User-Defined Functions

User-defined functions (UDFs) are custom functions you create to perform specific calculations or operations. They can be used in VBA code or directly in Excel worksheets.

Function Syntax

Public Function FunctionName(parameters) As ReturnType
    ' Code
    FunctionName = returnValue
End Function

Example 6.4: Cube Root Function

Function CubeRoot(num As Double) As Double
    If num < 0 Then
        CubeRoot = -((-num) ^ (1 / 3))
    Else
        CubeRoot = num ^ (1 / 3)
    End If
End Function

Example 6.5: Advanced Math Function

Function CalculateHypotenuse(a As Double, b As Double) As Double
    ' Calculate hypotenuse using Pythagorean theorem
    CalculateHypotenuse = Sqr(a ^ 2 + b ^ 2)
End Function

Example 6.6: Business Function

Function CalculateSalary(basePay As Double, hoursWorked As Double, _
                       Optional overtimeRate As Double = 1.5) As Double
    Const normalHours As Double = 40
    Dim regularPay As Double, overtimePay As Double
    
    If hoursWorked <= normalHours Then
        CalculateSalary = basePay * hoursWorked
    Else
        regularPay = basePay * normalHours
        overtimePay = (hoursWorked - normalHours) * basePay * overtimeRate
        CalculateSalary = regularPay + overtimePay
    End If
End Function

6.3: Differences Between Subs and Functions

Feature	Subroutine	Function
Returns value	No	Yes
Can be used in worksheet	No	Yes
Calling syntax	Call SubName or SubName	result = FunctionName()
Declaration	Sub...End Sub	Function...End Function

6.4: Best Practices

• Naming conventions: Use descriptive names (CalculateTax rather than Tax)
• Single responsibility: Each procedure should do one thing well
• Parameter validation: Validate inputs at the start of functions
• Error handling: Include error handling in critical functions
• Comments: Document purpose, parameters, and return values

Summary

• Subroutines perform tasks without returning values, while functions return values
• Both subs and functions promote code reusability and organization
• Built-in functions like MsgBox and InputBox provide user interaction
• User-defined functions extend Excel's built-in function library
• Functions can be used directly in Excel worksheet cells
• Proper naming and structure make your code more maintainable
• Parameter validation and error handling create robust procedures

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial