Excel VBA Tutor Logo Excel VBA Classic Excel VBA 2010 Excel VBA 365 Excel VBA Examples About Us
≡ Excel VBA Tutor Logo

Excel VBA Lesson 5: Sub Procedures and Functions in Excel VBA

❮ Previous Lesson Next Lesson ❯

Table of Contents

  1. Sub Procedures
  2. Functions
  3. Best Practices
  4. Knowledge Check

5.1: Sub Procedures in VBA

A Sub procedure (or subroutine) in Excel VBA is a block of code that performs a specific task when called by the main program or another procedure. Unlike functions, sub procedures don't return a value directly, though they can modify variables or objects passed to them.

Key Characteristics of Sub Procedures:

  • Begin with Sub and end with End Sub
  • Can accept parameters (arguments)
  • Can be called from other procedures using the Call keyword
  • Don't return a value (though they can modify passed arguments)
  • Often used for tasks that don't require a return value

Basic Sub Procedure Structure

Sub ProcedureName([arguments])
    ' VBA code goes here
    ' Perform actions
End Sub

Calling a Sub Procedure

Sub procedures can be called in two ways:

' Method 1: Using Call keyword
Call ProcedureName(arguments)

' Method 2: Without Call keyword
ProcedureName arguments

Example 5.1: Simple Sub Procedure

This example demonstrates a basic sub procedure that displays a message box:

Private Sub CommandButton1_Click()
    Call ShowWelcomeMessage
End Sub

Sub ShowWelcomeMessage()
    MsgBox "Welcome to Excel VBA Programming!", vbInformation, "Greeting"
End Sub

Example 5.2: Sub Procedure with Parameters

This example shows a sub procedure that calculates and displays a salary based on hourly wage and hours worked:

Private Sub CommandButton1_Click()
    ' Call the salary sub with parameters
    Call CalculateSalary(15.5, 40) ' $15.50/hour for 40 hours
End Sub

Sub CalculateSalary(hourlyRate As Double, hoursWorked As Double)
    Dim totalSalary As Double
    totalSalary = hourlyRate * hoursWorked
    MsgBox "Total Salary: $" & Format(totalSalary, "0.00"), vbInformation, "Salary Calculation"
End Sub

Note: While the Call keyword is optional, using it makes your code more readable and clearly indicates where procedures are being called.

5.2: Functions in VBA

Functions in Excel VBA are similar to sub procedures but with one key difference: they return a value to the calling code. There are two types of functions:

  1. Built-in functions (provided by VBA, like MsgBox, InputBox, etc.)
  2. User-defined functions (created by programmers for specific needs)

Key Characteristics of Functions:

  • Begin with Function and end with End Function
  • Can accept parameters (arguments)
  • Must return a value (assigned to the function name)
  • Can be used in expressions or assigned to variables

5.2.1 InputBox Function

The InputBox function displays a dialog box that prompts the user to enter text or a value. It's useful for gathering user input during macro execution.

Syntax:

userInput = InputBox(Prompt, [Title], [Default], [XPos], [YPos])

Parameters:

Parameter Description Optional
Prompt The message displayed to the user No
Title The title of the input box Yes
Default Default text that appears in the input field Yes
XPos, YPos Screen coordinates for the dialog position Yes

Example 5.3: Using InputBox

This example prompts the user for their name and displays a personalized greeting:

Private Sub CommandButton1_Click()
    Dim userName As String
    
    ' Get user input with InputBox
    userName = InputBox("Please enter your name:", "User Information", "John Doe", 500, 300)
    
    ' Check if user entered something
    If userName <> "" Then
        MsgBox "Hello, " & userName & "! Welcome to our application.", vbInformation, "Greeting"
    Else
        MsgBox "You didn't enter your name.", vbExclamation, "Notice"
    End If
End Sub
VBA InputBox Example
Figure 5.1: InputBox in action

5.2.2 Creating User-Defined Functions

User-defined functions (UDFs) allow you to extend VBA's capabilities by creating custom functions tailored to your specific needs.

Function Declaration Syntax:

[Public|Private] Function FunctionName([Arg As DataType, ...]) As ReturnType
    ' Function code
    ' Assign return value to FunctionName
    FunctionName = returnValue
End Function

Scope Note: Use Public to make the function available throughout your project, or Private to restrict it to the current module.

Example 5.4: CubeRoot Function

This example creates a custom function to calculate the cube root of a number:

Public Function CubeRoot(number As Double) As Double
    ' Calculate cube root
    If number < 0 Then
        CubeRoot = -((-number) ^ (1 / 3))
    Else
        CubeRoot = number ^ (1 / 3)
    End If
End Function

To use this function in Excel:

  1. Open the Visual Basic Editor (Alt+F11)
  2. Insert a new module (Insert → Module)
  3. Paste the function code
  4. Return to Excel and use the function like any built-in function: =CubeRoot(27)
VBA User-Defined Function Example
Figure 5.2: Using the CubeRoot function in Excel

Example 5.5: Tax Calculation Function

Here's a more practical example that calculates tax based on income:

Public Function CalculateTax(income As Currency) As Currency
    Const taxRate As Single = 0.2 ' 20% tax rate
    
    ' Calculate tax
    If income <= 10000 Then
        CalculateTax = 0 ' No tax for low income
    Else
        CalculateTax = (income - 10000) * taxRate
    End If
End Function

Best Practices for Sub Procedures and Functions

Follow these guidelines for better VBA code:

  • Naming: Use descriptive names (e.g., CalculateMonthlyInterest instead of CalcInt)
  • Single Responsibility: Each procedure/function should do one thing well
  • Parameter Validation: Validate inputs at the start of functions
  • Error Handling: Include error handling (we'll cover this in later lessons)
  • Comments: Add comments explaining complex logic
  • Modularity: Break complex tasks into smaller procedures/functions

Knowledge Check

Test Your Understanding

  1. What's the key difference between a Sub procedure and a Function?
  2. How would you call a Sub procedure named "ProcessData" with two parameters?
  3. What happens if a user clicks Cancel in an InputBox?
  4. How do you return a value from a Function?
  5. Why might you choose to make a Function Private instead of Public?
Show Answers
  1. A Sub procedure doesn't return a value, while a Function does.
  2. Call ProcessData(param1, param2) or ProcessData param1, param2
  3. InputBox returns an empty string ("").
  4. Assign the value to the function name: FunctionName = returnValue
  5. To restrict its use to the current module and prevent accidental use elsewhere.

Ready for More?

In the next lesson, we'll explore Excel VBA's built-in Math Functions that can supercharge your calculations!

Summary

✅ In This Lesson, You Learned:

  • Sub Procedures:
    • Perform actions without returning values
    • Called using Call or directly
    • Use for tasks like formatting or data processing
  • Functions:
    • Return values to the caller
    • Can be used in Excel formulas (UDFs)
    • Must assign result to function name
  • InputBox - Gets user input during execution
  • Best Practice - Use Subs for actions, Functions for calculations

🔗 Related Resources


❮ Previous Lesson Next Lesson ❯


Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page