Imagine Excel as a versatile toolbox. It comes equipped with a wide array of tools, from simple screwdrivers to complex power drills, each designed for specific tasks. These tools are Excel’s built-in functions, like SUM, AVERAGE, and VLOOKUP. While they’re incredibly useful, there might be times when you need a specialised tool that doesn’t exist in the standard kit.

That’s where custom functions in Excel come in. They’re like creating your own unique tool, tailored to the exact specifications of your task. You can design them to perform complex calculations, manipulate text in specific ways, or automate repetitive processes.

Think of it like this: If Excel’s built-in functions are the standard tools in a hardware store, custom functions are the bespoke tools crafted by a skilled artisan. They offer unparalleled flexibility and precision, allowing you to tackle data analysis challenges with ease.

In the following sections, we’ll delve deeper into the world of custom functions, exploring their creation, applications, and best practices. Whether you’re a novice Excel user or a seasoned data analyst, understanding custom functions can elevate your data analysis skills to new heights. Here’s our free practice sheet that you must download to follow along: Custom_User_Defined_Functions_In_Excel.xlsm

Kindly note that these functions can be created only in Microsoft Excel Desktop version.

Understanding Custom / User-Defined Functions (UDFs)

User-Defined Functions (UDFs) are custom functions created using Visual Basic for Applications (VBA). While VBA might seem intimidating at first, the process of creating UDFs is relatively straightforward, especially for those with basic programming knowledge.

Components of UDF

Here’s an overview of the key components and rules for creating custom functions:

1. Function Declaration

• Syntax: Function FunctionName(argument1 As Type, argument2 As Type, …) As ReturnType
• Components:
  • FunctionName: The name of your custom function. Must follow naming rules:
    • Cannot exceed 255 characters.
    • Cannot start with a number, contain spaces, or use special characters (except underscores).
    • Cannot use reserved Excel/VBA words (e.g., Sum, If).
  • argument1, argument2, …: These are inputs for your function. Each argument has:
    • A name (e.g., number1) that follows the naming rules above.
    • A type (e.g., As Double, As String) that defines the expected data type.
  • ReturnType: The data type your function returns (e.g., As Double, As String).

2. Function Body

• The body contains the code, calculations, and logic.
• Components:
  • Variables: Store values used in calculations.
  • Operators: Used for arithmetic (e.g., +, -), comparisons (e.g., =, >, <), and logic (e.g., And, Or).
  • Loops and Conditionals: If…Then, For…Next, or Do…Loop structures are used for decision-making or iteration.
  • Assignment Statements: Set values for variables or calculations.
  • Return Statement: The function must assign a value to its name (e.g., FunctionName = result) before it ends to indicate what it should return.

3. End Statement

• End Function signals the end of the function code.

4. Error Handling (Optional)

• Use On Error statements to manage runtime errors.
• On Error Resume Next can allow the function to continue on error, while On Error GoTo specifies an error-handling routine.

In Excel custom functions, the Public and Private keywords define the scope of a function or variable within VBA (Visual Basic for Applications):

Public: Makes the function or variable accessible to all modules and allows it to be called directly from a worksheet. Custom functions declared as Public can be used just like built-in Excel formulas.

Ex: 

Public Function AddNumbers(a As Double, b As Double) As Double

    AddNumbers = a + b

End Function

This can be used in Excel as =AddNumbers(3, 4).

Private: Limits the function or variable to the module in which it is declared. It cannot be called from the worksheet but is useful for helper functions used internally in VBA code.

Ex:

Private Function SquareNumber(x As Double) As Double

    SquareNumber = x * x

End Function

This function can only be used by other procedures in the same module.

Why Use Them?

• Public: To make functions available in the worksheet or across multiple modules.
• Private: To encapsulate logic, avoid naming conflicts, and keep the code modular and secure.

Steps to Create a UDF

Step 1 : By default, the Developer tab is hidden in Excel. Here’s how to enable it:

Go to File → Options → Excel Options  → Customize Ribbon. 

Check the box next to “Developer” and click OK.

Step 2: Open the VBA Editor:

Press Alt+F11 to open the VBA editor. Or go to Developer > Visual Basic.

Step 3: Under the Modules section on the left, right click and select Insert a new module. In case, there is no module already present, right click on Microsoft Excel Objects under your project and select Insert a new module.

Step 4: Define the Function:

• Use the Function keyword to declare the function.
• Specify the function name and parameters.
• Write the code to perform the desired calculations.
• Use the Function name to assign the result.

Step 5: Use the UDF in Your Worksheet:

Type the function name in a cell after adding a “=”, followed by parentheses.

Input the required arguments.

**

Examples of a Custom Function in Excel

Example 1 (Basic)- Find area of a rectangle 

Here’s an example function to calculate the area of a rectangle:

Data:

Code in VBA editor

Result:

Once you’ve defined the function in VBA, you can use it in Excel like any other formula by typing =RectangleArea(12, 10) into a cell, which would return 120.

**

Example 2: Statistics Example – Coefficient of Variance

A company wants to analyze the consistency of sales performance across different regions. For this, the management uses the Coefficient of Variation (CV), a measure of relative variability that compares the standard deviation of sales to the mean sales. A higher CV indicates greater variability, while a lower CV suggests more consistent performance.

This metric helps businesses:

• Evaluate performance stability across regions, departments, or products.
• Identify regions with inconsistent sales to focus improvement efforts.
• Compare variability between datasets with different scales.

Data:

Code in VBA editor

Result:

**

Example 3: Text Manipulation UDF: Custom Text Cleaning

Imagine an HR department managing employee feedback forms that contain inconsistent formatting: extra spaces, mixed-case names, and special characters like \ or _. A custom CleanText function can quickly standardise these entries, ensuring data consistency across reports and improving readability.

Data:

Custom Function Code:

• The function is named CleanText and takes one argument, inputText, which is the text to be cleaned. It returns a String.
• Trim(inputText) removes any extra spaces at the beginning and end of the input text.
• Replace functions are used to strip out \ , _ ,  . , and !. These lines can be modified to remove any other specific characters as needed.

Result:

**

Example 4: Date and Time UDF: Custom Date Calculations

Imagine you are managing a workforce where employees are paid based on the type of day they work. For instance:

1. Weekdays (Monday to Friday): Employees are paid a fixed rate of $20 per day.
2. Weekends (Saturday and Sunday): Employees are paid a higher rate of $30 per day due to increased demand or overtime rates.

This scenario applies to:

• Part-time or contract-based workers: You need to calculate their pay over specific date ranges.
• Shift-based industries like retail, logistics, or hospitality where weekend work attracts higher pay.
• HR departments: Automating payroll calculations for specific time periods.

For example, if you’re managing payroll for a temporary workforce working on a project, you need to calculate their expected total pay based on the start and end dates for each employee’s contract if they work on weekends.

Data:

Project Milestones data in column B,C in above data

Custom Function Code:

• Loop through date range:
  The code iterates through each day between StartDate and EndDate using For d = StartDate To EndDate.
• Determine day type:
  The Weekday function identifies whether a day is a weekday or weekend: vbSaturday (7) and vbSunday (1) are weekends and paid $30/day. All other days are weekdays, paid $20/day.

• Accumulate total pay:
  For each day, the function adds the respective pay to a running total using Total = Total + Pay.
• Return total pay:
  After looping through all days, the function returns the calculated total using TotalPay = Total.
• Weekday function in VBA:
  The Weekday function assigns numbers (1 to 7) to days:

Sunday = 1, Monday = 2, …, Saturday = 7.
This helps differentiate weekends from weekdays for pay calculation.

Result:

**

Example 5: Sentiment Analysis using Custom VBA Function (Nested Function Within Same Module)

Imagine you’re a blogger working for a eduTech chain, and the company collects readers’ feedback to gauge overall satisfaction. The feedback is given on the blogsite itself where readers can leave a comment. You have a column of comments that need to be analysed to determine how positive or negative is the sentiment expressed by customers.

Data:

Code (Nested Functions in the same Module):

• sentimentCalc receives a tweet (text input) and two ranges (posKW for positive keywords and negKW for negative keywords).
• The tweet is split into individual words.
• For each word, sentimentCalc calls another function called CheckKeywords within Module1 to check if the word exists in the list of positive or negative keywords.
• If a word is found in the positive list, the score is increased by 10 points; if it’s found in the negative list, the score is decreased by 10 points.
• After processing all words, the function returns the sentiment score, which represents the overall sentiment of the tweet.

“iferror(match(“”” & val & “””,” & r.Address & “,), 0)”:

• This is a dynamic Excel formula that combines several elements:
  • MATCH(val, r.Address, 0): The MATCH function tries to find the position of val (the word) in the range r (the list of keywords).
  • iferror(…, 0): If the MATCH function fails to find the word in the range, it would normally return an error. The IFERROR function handles this by returning 0 instead of an error, meaning the word is not found.
• The formula is constructed dynamically as a string, and the Evaluate method allows VBA to run this formula.

The result of the Evaluate method is returned directly as the output of the CheckKeywords function:

• If the word is found, MATCH returns the position of the word in the range (a non-zero value), which is treated as True.
• If the word is not found, MATCH returns an error, and IFERROR returns 0, which is treated as False.

Result:

Example 6: Net Salary Calculation (Nested Function Called From Another Module)

This example demonstrates how to calculate tax dynamically based on a gross salary using VBA. The tax rate is 15% for salaries up to $50,000 and 30% for salaries of more than $50,000. By automating this logic in a custom function, it simplifies tax computations for payroll or financial reports in Excel.

Data:

 Custom Function Code:

As you can see here, CalculateTax of Module7 is being called inside CalculateNetSalary of Module8.

Result:

**

Example 7 : Net Salary Calculation Using Google Gemini AI:

Prompt:

“Create a VBA custom function to calculate the tax amount based on a given gross salary. The tax rate should be 15% for salaries less than or equal to 50,000 and 30% for salaries greater than 50,000.

Also, create another VBA user-defined function in another module to calculate the net salary after deducting the calculated tax from the gross salary. This function should call the first function to determine the tax amount.”

Response:

Result:

As you can see, the Function’s name has been modified from Google Gemini AI’s version to maintain uniqueness for all functions in a workbook. We get the same result as the original function.

**

Rules and Best Practices for Creating Custom Functions in Excel

1. Start in the Visual Basic for Applications (VBA) Editor:
   • Press Alt + F11 to open the VBA editor.
   • Insert a new module where you’ll write the custom function.
2. Avoid Overwriting Built-In Functions:
   • Name your custom functions uniquely to prevent conflicts with existing Excel functions.
3. Use Clear, Descriptive Names:
   • This makes it easier to understand and maintain, both for yourself and others.
4. Limit Side Effects:
   • Custom functions in Excel should ideally not modify cells or the workbook directly. Excel expects functions to calculate values only (in contrast to Sub procedures, which can modify the workbook).
5. Data Type Consistency:
   • Declare data types for all arguments and the return value for reliable performance and to avoid type errors.
6. Minimise Calculation Time:
   • Custom functions should be efficient to avoid slowing down the workbook. Complex functions might recalibrate when cells or ranges are modified.
7. Debugging:
   • Use Debug.Print in the VBA editor to troubleshoot your function as you build it.

Conclusion

Custom / User-Defined Functions (UDFs) are a powerful tool to extend Excel’s capabilities and perform complex tasks. By crafting custom functions tailored to specific needs, you can streamline data analysis, improve efficiency, and enhance the overall productivity of your work.

We explored the fundamentals of creating UDFs, from defining function names and parameters to writing the core logic. We also delved into practical examples, including text manipulation, date and time calculations, statistical functions analysis, financial modelling, and data validation.

Remember that the key to effective UDF development lies in clear understanding of the problem, careful planning, and thorough testing. By following best practices and continuously refining your skills, you can unlock the full potential of UDFs and elevate your Excel expertise to new heights. Download our free practice sheet here: Custom_User_Defined_Functions_In_Excel.xlsm