[VBA] How to Create and Use Functions with Arguments | Returning Values

Introduction

In VBA procedures, besides the Sub we have seen so far, there is another type called Function. The biggest difference between Sub and Function is that a Function can return a “return value” as the result of a process.

By creating your own Function, you can turn frequently used calculations or processes—like Excel’s SUM or VLOOKUP functions—into reusable “original functions.”

This article explains the basics of how to create and use a Function that receives arguments and returns a result.

Sample Code for Function with Arguments

In this example, we will create an original function called CalculatePriceWithTax that accepts a base price and a tax rate as arguments, calculates the price including tax, and returns it.

Complete Code

' === Function to calculate price with tax ===
' Receives 2 arguments and returns a value of Currency type
Function CalculatePriceWithTax(basePrice As Double, taxRate As Double) As Currency
    ' Assigning a value to the variable with the same name as the Function makes it the return value
    CalculatePriceWithTax = basePrice * (1 + taxRate)
End Function


' === Sub procedure to call the created Function ===
Sub Test_CalculateFunction()

    Dim priceA As Currency
    Dim priceB As Currency
    
    ' --- Call Function from within macro and store return value in variable ---
    priceA = CalculatePriceWithTax(1000, 0.1) ' 1000 yen, 10% tax
    priceB = CalculatePriceWithTax(basePrice:=5000, taxRate:=0.08) ' 5000 yen, 8% tax
    
    ' --- Output results to sheet ---
    With Worksheets("Sheet1")
        .Range("B2").Value = "Price A (Inc. Tax):"
        .Range("C2").Value = priceA
        
        .Range("B3").Value = "Price B (Inc. Tax):"
        .Range("C3").Value = priceB
    End With
    
End Sub

Explanation of the Code

Function CalculatePriceWithTax(...) As Currency

This is the definition of the Function.

• Function ... End Function: Indicates that the procedure type is a Function.
• (basePrice As Double, taxRate As Double): Like a Sub, it receives the values necessary for processing as arguments.
• As Currency: By specifying a data type at the end of the Function statement, you define the data type of the value the Function returns. Here, we specify Currency, which is suitable for handling money.

CalculatePriceWithTax = basePrice * (1 + taxRate)

Inside the Function, if you assign a value to a variable with the same name as the Function itself, that value becomes the return value of the Function. This is the most important rule of Functions.

The calculation result (1000 * (1 + 0.1) = 1100) is stored in the special variable named CalculatePriceWithTax and returned to the caller.

priceA = CalculatePriceWithTax(1000, 0.1)

This is the part where the Function is called from within the Sub procedure.

1. When CalculatePriceWithTax(1000, 0.1) is executed, the Function returns the calculation result 1100.
2. That return value is stored in the variable priceA via the = operator.

When to Use Sub vs. Function

If you are unsure whether to use a Sub or a Function, consider the following:

• Sub: When you want to perform an “Action”.
  • Examples: Adding a sheet, formatting cells, saving a file.
• Function: When you want to “Get a Value”.
  • Examples: Calculating a tax-included price, finding the address of a cell that meets criteria, processing a text string.

Summary

In this article, I explained how to create Functions to modularize processing and improve reusability in VBA.

• A Function is a procedure that can return a result value.
• Define the return type using Function ... As DataType.
• Set the return value using the format FunctionName = Value.

By separating complex calculations or repetitive judgment logic into independent Functions, your main code becomes simpler, and the readability of the entire program improves significantly.