How to Create Custom Function in Excel VBA

Excel VBA custom functions allow users to create their own formulas. This means users can perform calculations that are not available through built-in Excel functions. This is useful for users who frequently perform repetitive calculations. Custom functions can help simplify workflows and improve efficiency. In this article, we’ll explore how to create Excel VBA custom function.

The image below compares how we can count words using a custom function vs. Excel’s built-in functions. Clearly, by using the custom function we can avoid writing complex formulas.

Moreover, custom functions can be shared with others. This makes it easier for teams to work together and ensure consistency.

Throughout this article, we have used Microsoft Excel 365; You can use any other version of Excel according to your preference.

Download Practice Workbook

You can download the following practice workbook to practice yourself.

What Is Custom Function or 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.

Sometimes, we need to create manual controls that aren’t built-in functions of Excel. In those cases, we need to use the VBA custom function. Otherwise, Excel will show ambiguous name errors or other errors. So, we will try to learn how to create an Excel VBA custom function.

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

The Developer tab contains the VBA applications like creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc.

📝 Note: The Developer tab remains hidden by default. You can learn to enable the Developer tab by following this linked article.

After enabling, click the Developer tab >> Visual Basic button in the Code group.

This opens a new window where we can run our VBA codes.

Now, click the Insert tab >> choose Module from the list >> copy and paste the VBA codes in the Module window >> press the Run button or F5 key to run the code.

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

In this section, we’ll learn to make a custom function in Excel VBA. Suppose, 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 our custom function.

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

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)

3. Setting the Return Value of the Function

Next, 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

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. Let’s dissect the VBA custom function from the previous section:

• First of all, define the function name “Get_text_with_format”.
• Next, specify 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.
• Later, define the return parameter of the function. In this case, the function returns a text value as a String data type.
• Afterward, we declare additional variables “str_len” and “output” and use the Len function to count the length of the text.
• Then we use 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.
• Eventually, we assign 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

In this portion, we’ll discuss how to call the custom function in a cell, in a formula, and within another VBA macro.

Let’s consider the “Transaction List” dataset which contains the “Transaction ID”, “Date”, “Amount”, and “Type” columns. Here, 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

Simply copy and paste the codes 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”.

Here, 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

Follow the steps shown before to insert a Module >> paste this code into the Module >> click Run.

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”, “Starting-Ending” dates. and the “Total” amount.

5 Examples of Arguments in Excel VBA Custom Functions

In this section, we’ll demonstrate five useful examples of arguments in Excel VBA custom functions.

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.

2. Making a Custom Function with One Argument

Assuming the “List of Best Sellers” dataset which contains the “ISBN”, “Book Name”, and “Price” columns. Here, 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”.

3. Custom Function with Multiple Arguments

In this example, we’ll make a custom function with multiple arguments to look up 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.

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.

In this case, the B14 cell indicates the string “Why Nations Fail” and FALSE tells the function to keep the original formatting.

5. Make a Custom Function with Array as Argument

Lastly, we can define a custom function that takes an array argument. Let’s suppose the “List of Numbers” dataset has a group of even and odd numbers in column B. Now, 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

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

In this formula, 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 a VBA custom function can help identify and fix syntax and runtime errors. Moreover, we can also implement error-handling techniques to prevent errors.

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 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. Then, we can use the F8 key to step through the code line by line. Clicking the F8 key again moves us to the next line of code.

Using Error Handling to Prevent Errors

In this portion, 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

In this updated code, 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.

Similarly, entering an invalid transaction type also results in an error message.

Finally, 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:

Excel’s built-in functions have several advantages over creating custom functions in VBA.

First, inbuilt functions in Excel are faster than VBA functions.

Second, 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.

Lastly, 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:

While there are disadvantages to using custom functions, here are their benefits.

Custom functions help to shorten and simplify complex formulas by combining Excel’s built-in functions. Simple and short formulas are easy to understand.

Another advantage of custom functions is that they can be easily copied to other workbooks.

Moreover, custom functions can be used inside other VBA macros.

Frequently Asked Questions

1. 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.

2. 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.

3. How do I debug a custom function in VBA?

We 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.

Conclusion

To sum up, VBA custom functions can perform various tasks, like calculations, data manipulation, and formatting. In this article, we’ve discussed in detail how we can create a custom function in Excel VBA. Following this, we learned how to call a custom function in a worksheet cell, combine it with an Excel formula and apply it in another VBA macro.

We also showed 3 quick and useful applications and debugging of the custom function. Moreover, we talked about error handling to make sure our function can handle invalid user inputs.

Lastly, we discussed the benefits and drawbacks of custom functions and shared helpful tips and tricks. We hope this article has provided a solid understanding of how to create custom functions in Excel VBA. If you have any suggestions or comments, don’t forget to share them with us.