How to Create Custom Function in Excel VBA

Here’s an overview of a custom function that was made via VBA code. As you can see, the function has lowercase letters, unlike traditional Excel functions.

What Is a Custom Function or a User-Defined Function in Excel VBA?

A Custom Function (also known as a User-Defined Function) is a function made by users according to their needs. It is a collection of commands written in VBA code and returns the desired output.

How to Launch and Insert Code in the Visual Basic Editor in Excel

• Click on the Developer tab, then select Visual Basic in the Code group.

This opens a new window.

• Click the Insert tab and choose Module from the list.
• Copy and paste the VBA code in the Module window.
• Press the Run button or F5 key to run the code. Alternatively, save the file as an .xlsm.

How to Create a Custom Function in Excel VBA: Step-by-Step Process

We have a text string in one cell. We want to format this text to uppercase and return the result in the C14 cell using a custom function.

Step 1 – Defining and Naming the Function

The first line of the VBA code defines the function using the Function keyword. The function is given the name “Get_text_with_format”. The name of the function should describe what the function does.

Function Get_text_with_format

Step 2 – Specifying Function Parameters

The function parameters are specified within the parenthesis (open and close brackets). In this case, there are two parameters: “cell_reference”, and “text_case”.

The data types are mentioned after the parameters. Here, the “cell_reference” argument is of the data type Range. If we do not define the type then the function returns a Type not defined error.

The optional “text_case” parameter takes the default value of False. Although, the user can also enter the value of True.

Function Get_text_with_format(cell_reference As Range, _

Optional text_case = False)

Step 3 – Setting the Return Value of the Function

We declare the return value of the “Get_text_with_format” function using the “As String” keyword. This means that the function gives us a text value which is a String data type. An important thing to note, a custom function always ends with the End Function statement.

Function Get_text_with_format(cell_reference As Range, _

Optional text_case = False) As String


End Function

Step 4 – Completing the Custom Function and Getting the Output

Within the Function and End Function keywords, we define the commands that the function will perform to return the end result.

Function Get_text_with_format(cell_reference As Range, _

Optional text_case = False) As String

Dim str_len As Integer

Dim output As String

str_len = Len(cell_reference)

For i = 1 To str_len

If Not (IsNumeric(Mid(cell_reference, i, 1))) Then _

output = output & Mid(cell_reference, i, 1)

Next i

If text_case = True Then output = UCase(output)

Get_text_with_format = output

End Function

Once we have completed writing the custom function we can return to the Excel spreadsheet and type the function to get the results shown in the image below.

Dissecting the Excel VBA Custom Function

If we dissect our VBA custom function, we can better understand its structure and how it works.

• We defined the function name “Get_text_with_format”.
• We specified its parameters and their data types “cell_reference As Range” inside the parenthesis.
• There are two types of arguments in a function; mandatory and optional arguments.
• Inside the parenthesis, we have to write the mandatory arguments first. These arguments must be provided for the function to work. In our case, “cell_reference” is the mandatory argument.
• The second type of argument is called the optional argument. It is not mandatory to write these arguments. Excel automatically considers a default value for the optional arguments if it is not provided. But, we can specify the optional arguments to get the desired output. Here, the “text_case” is the optional argument.
• We defined the return parameter of the function. In this case, the function returns a text value as a String data type.
• We declared additional variables “str_len” and “output” and use the Len function to count the length of the text.
• We used the For Next loop and If Then statements to check for text value.
• If the condition is satisfied, then we format the result according to the user’s input.
• We assigned the “output” to the function name. This means that the function returns the value stored in the “output” variable.

How to Call a Custom Function in Excel VBA

Let’s consider the “Transaction List” dataset which contains the “Transaction ID”, “Date”, “Amount”, and “Type” columns. We want to enter the starting date, ending dates, and transaction type as the input arguments in our custom function. The function will calculate the total amount.

Using the Function in a Cell

• Copy and paste the code into the Module window as shown below.

Function TransactionTotal(start_date As Date, end_date As Date, _

tran_type As String) As Double

Dim total As Double

Dim date_val As Date

For i = 5 To Range("C5").End(xlDown).Row

date_val = Range("C" & i).Value

If date_val >= start_date And date_val <= end_date Then

If Range("E" & i).Value = tran_type Then

total = total + Range("D" & i).Value

End If

End If

Next i

TransactionTotal = total

End Function

• Type the function name and enter the three arguments and function. For example, we can calculate the total “Debit” transactions between the dates “01/01/2022” and “01/31/2022”.

The B17, C17, and D17 cells refer to the “start_date”, “end_date”, and “tran_type” arguments.

Use of the Function in a Formula

We can combine our custom function with the SUM function to get the total “Debit” and “Credit” transactions within a specific date.

Applying the Function in a VBA Macro

• Here’s the code we’ll use.

Sub DisplayTransactionTotal()

Dim start_date As Date

Dim end_date As Date

Dim tran_type As String

Dim total As Double

start_date = Range("B17").Value

end_date = Range("C17").Value

tran_type = Range("D17").Value

total = TransactionTotal(start_date, end_date, tran_type)

MsgBox "The amount of " & tran_type & " transactions between " & _

start_date & " and " & end_date & " is $" & total

End Sub

The VBA macro displays a MsgBox with the transaction “Type”, and “Starting-Ending” dates. and the “Total” amount.

Arguments in Excel VBA Custom Functions: 5 Examples

Example 1 – Creating a Custom Function without Any Arguments

Insert this custom function code into your Module window.

Function GetWsheetName() As Variant

Dim n As Integer

Dim sheets() As String

Dim transposed() As String

ReDim sheets(1 To ThisWorkbook.sheets.Count)

For n = 1 To ThisWorkbook.sheets.Count

sheets(n) = ThisWorkbook.sheets(n).Name

Next n

ReDim transposed(1 To UBound(sheets), 1 To 1)

For n = 1 To UBound(sheets)

transposed(n, 1) = sheets(n)

Next n

GetWsheetName = transposed

End Function

Write the function name and press the ENTER key to get the names of the worksheets. If you’re using an older version of Excel, then use CTRL + SHIFT + ENTER keys.

Example 2 – Making a Custom Function with One Argument

We have the “List of Best Sellers” dataset which contains the “ISBN”, “Book Name”, and “Price” columns. We want to make a custom function that counts the number of words in a cell. The function splits each word at the space character and counts their numbers.

Function WordCount(rng As Range) As Integer

WordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

In this function, the B14 cell represents the text “Why Nations Fail”.

Read More: How to Make VBA Function with Arguments in Excel

Example 3 – Custom Function with Multiple Arguments

We’ll make a custom function with multiple arguments to look up a cell value.

Function LookupValue(lookup_value As Variant, lookup_range As Range, _

return_column As Integer) As Variant

Dim lookup_result As Range

Set lookup_result = lookup_range.Find(lookup_value, LookIn:=xlValues, _

LookAt:=xlWhole)

If Not lookup_result Is Nothing Then

LookupValue = lookup_result.Offset(0, return_column - 1).Value

Else

LookupValue = "Not Found"

End If

End Function

In this expression, the B17 cell refers to the “lookup_value”, the B5:E14 range is the “lookup_range”, and 3 is the “return_column” argument.

Example 4 – Custom Function with Mandatory and Optional Arguments

We can create a custom function that takes a mandatory and an optional argument to return formatted text.

Function Get_text_with_format(cell_reference As Range, _

Optional text_case = False) As String

Dim str_len As Integer

Dim output As String

str_len = Len(cell_reference)

For i = 1 To str_len

If Not (IsNumeric(Mid(cell_reference, i, 1))) Then _

output = output & Mid(cell_reference, i, 1)

Next i

If text_case = True Then output = UCase(output)

Get_text_with_format = output

End Function

Here, the B14 cell indicates the string “Why Nations Fail” and TRUE confirms the formatting to uppercase.

The B14 cell indicates the string “Why Nations Fail” and FALSE tells the function to keep the original formatting.

Example 5 – Make a Custom Function with Array as Argument

We can define a custom function that takes an array argument. Suppose the “List of Numbers” dataset has a group of even and odd numbers in column B. We want our custom function to find the sum of the odd or even numbers, depending on the argument.

Function sum_even_odd_numbers(input_range As Range, _

even_odd As String) As Double

Dim arr As Range

Dim output As Double

For Each arr In input_range

If IsNumeric(arr.Value) Then

If even_odd = "even" And arr.Value Mod 2 = 0 Then

output = output + arr.Value

ElseIf even_odd = "odd" And arr.Value Mod 2 <> 0 Then

output = output + arr.Value

End If

End If

Next arr

sum_even_odd_numbers = output

End Function

The B5:B14 range is the number range, and “odd” is the condition to sum the odd numbers.

The B5:B14 range is the number range and “even” is the condition for calculating the sum of the even numbers.

Scope of a Custom Function in Excel VBA

In Excel VBA, the scope of a custom function represents the range within which the function is available for use. There are two types of scopes: Public and Private scopes.

Public scope means the function is visible to all the Modules and can be used in all the sheets in the workbook. This is useful when we have to perform a specific calculation across multiple worksheets or workbooks. By default, a custom function has a Public scope. However, we can also write the Public keyword before the function name.

Public Function WordCount(rng As Range) As Integer

WordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

In contrast, Private scope means the function is only visible within the Module where it is defined. This is beneficial when we need to calculate a value within a fixed Module. We can create a custom function with Private scope by using the Private keyword before the function name.

Private Function WordCount(rng As Range) As Integer

WordCount = UBound(Split(rng.Value, " "), 1) + 1

End Function

How to Debug a Custom Function in Excel VBA

Debugging Syntax Errors

If there is a syntax error in the code, Excel VBA displays an error message when we run the function. Here is a list of common syntax errors:

• Missing parentheses and quotes.
• Misspelled variable names or function names.
• Improperly defined function parameters.
• Improper use of operators or keywords.

Debug Runtime Errors

Excel VBA displays a runtime error at the point of the error usually by highlighting the line of code. Some common runtime errors are:

• Division by zero.
• Using a function or variable that has not been defined or declared.
• Trying to access a file or resource that does not exist.

Setting a Breakpoint to Debug Custom Function

To go through each line of code one at a time, we can set a Breakpoint. This means choosing a line of code and marking it with a red dot. We can do this by selecting that line and pressing the F9 key. This helps us analyze and debug our code more efficiently.

After setting a Breakpoint, when we run the function, it stops at this line. Use the F8 key to step through the code line by line. Pressing the F8 key again moves to the next line of code.

Using Error Handling to Prevent Errors

We’ll modify our custom function for calculating the total amount. By adding an error handling block in our code we can handle invalid user inputs and warn the user.

Function TransactionTotal_ErrHandle(start_date As Variant, _

end_date As Variant, tran_type As String) As Double

Dim total As Double

Dim date_val As Variant

Dim i As Long

If Not IsDate(start_date) Then

MsgBox "Invalid Start date entered", vbExclamation

Exit Function

End If

If Not IsDate(end_date) Then

MsgBox "Invalid End date entered", vbExclamation

Exit Function

End If

If tran_type <> "Credit" And tran_type <> "Debit" Then

MsgBox "Invalid Transaction type", vbExclamation

Exit Function

End If

For i = 5 To Range("C5").End(xlDown).Row

date_val = Range("C" & i).Value

If date_val >= start_date And date_val <= end_date Then

If Range("E" & i).Value = tran_type Then

total = total + Range("D" & i).Value

End If

End If

Next i

TransactionTotal_ErrHandle = total

End Function

The three If Then statements make sure that if the user enters an invalid date or transaction type, then the function can warn the user.

If the user enters an invalid date, the program shows an error message to the user about the date.

Entering an invalid transaction type also results in an error message.

Once we enter the correct inputs the function calculates the “Total” transaction amount.

VBA Custom Function Vs. Excel Built-in Functions

Excel Built-in Functions:

• Inbuilt functions in Excel are faster than VBA functions.
• We do not need to worry about enabling macros. Some people may get confused when they see the yellow warning bar that asks them to enable macros.
• With built-in functions, there are no worries with the file extension. If our workbook contains custom functions, we have to save it in the .xlsm format.

VBA Custom Functions:

• Custom functions help to shorten and simplify complex formulas by combining Excel’s built-in functions. Simple and short formulas are easy to understand.
• They can be easily copied to other workbooks.
• Custom functions can be used inside other VBA macros.

Read More: Difference Between Subroutine and Function in Excel VBA

Frequently Asked Questions

Can I pass an array to a custom function in VBA?

Yes, we can enter an array into a custom function in VBA by defining the argument as an array and using the array elements in the code.

Are custom functions in VBA portable across different versions of Excel?

Yes, custom functions are portable across different versions of Excel as long as the VBA code is compatible with your version of Excel.

How do I debug a custom function in VBA?

You can use the VBA editor’s debugging tools like breakpoints, watch variables, and step through the code to resolve errors.

Things to Remember

• The syntax of custom functions must start with the Function keyword and end with the End Function keyword.
• Custom functions cannot change the size of the window, modify the formula in a cell, or change the font, color, etc. in a cell.
• It is a good practice to use comments in the VBA code to explain the purpose, inputs, and outputs of custom functions.

Download the Practice Workbook

Related Articles

• Excel VBA Function to Return Multiple Values
• How to Execute VBA Function Procedure in Excel
• How to Return a Value in VBA Function
• How to Use VBA Input Function in Excel
• VBA Sub Vs Function in Excel
• How to Create and Use ColorFunction in Excel