Excel VBA Function Examples: Useful Worksheet UDFs

Excel VBA function examples show how to create custom worksheet functions for tasks that built-in formulas do not handle conveniently. Add a function to a standard VBA module, save the workbook as an Excel macro-enabled workbook, and call it from a cell just like a built-in function; the examples below cover names, word counts, grades, and overdue days.

What an Excel VBA function does

A VBA function, often called a user-defined function (UDF), accepts inputs, performs calculations or transformations, and returns one result. Once its code is in the workbook, a worksheet formula can use it. For example, a function can standardize a repeated rule or make a complicated calculation easier to read.

A function is not the same thing as a macro in the everyday sense. In VBA, a Function returns a value, while a Sub carries out an action, such as formatting a range or creating a report. Worksheet UDFs are intended to calculate values in cells. They generally should not edit other cells, change formatting, or perform actions such as displaying dialogs. Use a Sub procedure for those jobs.

Set up a VBA function in Excel

Save a backup copy of the workbook before adding or running VBA. Macros cannot be undone with Ctrl+Z.
Save the working file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As.
Open the VBA editor on Windows with Alt+F11. On a Mac, use Option+F11; some keyboards require Fn+Option+F11. You can also open the Developer tab and choose Visual Basic.
In the editor, choose Insert > Module, then paste the function code into the new standard module. A function placed only in a worksheet code module or ThisWorkbook may not be available in the same way as a standard-module UDF.
Return to Excel and enter the function name in a cell. If Excel asks about macros when the file opens, enable them only if you trust the workbook and its source.

If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. Ribbon wording can vary slightly by platform or update. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use the desktop app for these steps. Excel for Mac supports VBA, although some Windows-specific objects and automation features are not available there.

Example: join first and last names

This small UDF joins two text values and trims spaces at the ends of the result. Paste it into a standard module:

Public Function FullName(ByVal firstName As String, ByVal lastName As String) As String
    Dim combined As String

    ' Join the inputs and remove leading or trailing spaces.
    combined = Trim$(firstName & " " & lastName)
    FullName = combined
End Function

With a first name in A2 and a last name in B2, enter =FullName(A2,B2). If A2 contains “Mina” and B2 contains “Patel,” the result is “Mina Patel.” Because the function returns text, the result is a value in the formula cell; it does not overwrite either input cell. This version expects text inputs and does not attempt to handle every possible name convention, such as multiple family names or preferred names.

Example: count space-separated words

Excel has functions for many text operations, but a small UDF can make a simple counting rule reusable. This example counts words separated by ordinary spaces. It ignores leading and trailing spaces and collapses repeated spaces before counting:

Public Function WordCount(ByVal cellText As String) As Long
    Dim cleaned As String
    Dim parts() As String

    ' Treat an empty or space-only string as zero words.
    cleaned = Trim$(cellText)
    If Len(cleaned) = 0 Then
        WordCount = 0
        Exit Function
    End If

    ' Reduce repeated ordinary spaces to one separator.
    Do While InStr(1, cleaned, "  ", vbBinaryCompare) > 0
        cleaned = Replace(cleaned, "  ", " ")
    Loop

    parts = Split(cleaned, " ")
    WordCount = UBound(parts) + 1
End Function

With text in A2, use =WordCount(A2). If A2 contains “Excel VBA functions,” the result is 3. This is deliberately a basic definition of a word: punctuation remains attached, and a tab or line break is not treated as an ordinary space. Define the counting rule before using the result for a formal limit or report.

Example: assign a letter grade

A grading UDF can centralize thresholds so that every row follows the same rule. This example uses a 0–100 score scale, with A at 90 or above, B at 80–89, C at 70–79, D at 60–69, and F below 60. Values outside the scale return “Invalid.”

Public Function GradeFromScore(ByVal score As Double) As String
    ' Apply descending score thresholds.
    If score < 0 Or score > 100 Then
        GradeFromScore = "Invalid"
    ElseIf score >= 90 Then
        GradeFromScore = "A"
    ElseIf score >= 80 Then
        GradeFromScore = "B"
    ElseIf score >= 70 Then
        GradeFromScore = "C"
    ElseIf score >= 60 Then
        GradeFromScore = "D"
    Else
        GradeFromScore = "F"
    End If
End Function

With a numeric score in C2, use =GradeFromScore(C2). If C2 is 84, the result is “B”; if it is 101, the result is “Invalid.” The thresholds are examples, not a universal grading standard. Change them to match the policy that applies to your data. A text value or an error in the input cell may produce an Excel error rather than a grade.

Example: calculate days overdue

This function returns the number of whole calendar days a due date is past, or zero when the date has not passed. It accepts two date-like inputs and returns an Excel error value if either is not a date:

Public Function DaysOverdue(ByVal dueDate As Variant, _
                            ByVal asOfDate As Variant) As Variant
    Dim dueDay As Date
    Dim checkDay As Date

    ' Reject values that VBA cannot interpret as dates.
    If Not IsDate(dueDate) Or Not IsDate(asOfDate) Then
        DaysOverdue = CVErr(xlErrValue)
        Exit Function
    End If

    ' Compare calendar dates, ignoring any time portion.
    dueDay = DateValue(CDate(dueDate))
    checkDay = DateValue(CDate(asOfDate))

    If checkDay > dueDay Then
        DaysOverdue = DateDiff("d", dueDay, checkDay)
    Else
        DaysOverdue = 0
    End If
End Function

With a due date in D2 and an as-of date in E2, use =DaysOverdue(D2,E2). If D2 is 10 June and E2 is 14 June of the same year, the result is 4; if E2 is 9 June, it is 0. The example uses calendar days, not business days, and treats a date as overdue only after the due date. For business-day rules, holidays and weekends need an explicit definition.

How worksheet UDFs calculate

Excel calls the VBA function when it calculates a cell containing that function. The values supplied between the parentheses become the function’s arguments; the assignment to the function name determines the returned result. In the grade example, for instance, assigning “B” to GradeFromScore is what places that text in the formula cell.

Excel normally recalculates a UDF when a referenced input changes, just as it does for many built-in functions. A UDF that reads information not passed in as an argument, such as a value from another cell, can be harder for Excel to track reliably. Prefer passing every required input as an argument. Avoid making a UDF volatile merely to force recalculation unless there is a specific reason: recalculating often can slow a large workbook.

When copied down a column, ordinary cell references in the worksheet formula adjust in the usual way. The VBA code itself does not need to change. You can also use a UDF inside a larger formula, provided the combined formula is valid and the returned data type suits the calculation.

Choosing between a VBA function and a built-in formula

Use an existing Excel function when it already expresses the rule clearly. Built-in formulas are easier for other people to inspect, work without enabling VBA, and are generally more portable to Excel for the web. Excel 365 includes modern functions such as XLOOKUP, FILTER, and TEXTSPLIT, though availability depends on the Excel version and update channel. A VBA UDF is most useful when it makes a repeated custom rule easier to maintain or when a calculation is awkward to express with available worksheet functions.

Choose a built-in formula for common lookups, sums, text transformations, and dynamic-array tasks when a suitable function exists.
Choose a VBA UDF for a reusable, specialized calculation that returns a value and is used in a desktop Excel environment that allows macros.
Choose a Sub macro when the task must change cells, apply formatting, create sheets, or interact with a user.
Consider Office Scripts or another supported approach when the workflow must run in Excel for the web; these are not VBA functions and have different capabilities.

Portability matters. Recipients may use a platform that cannot run VBA, may have macros disabled by policy, or may not trust a workbook’s source. If others need only the calculated results, you can provide a separate copy with formulas replaced by values, while keeping an original macro-enabled file for maintenance.

Common errors and safe handling

A #NAME? result often means Excel cannot resolve the function name. Check for a spelling error, confirm the code is in a standard module in the correct open workbook, and verify that macros are available. A missing function can also occur when a workbook containing the UDF is closed or when a formula is moved to a workbook that does not contain the code.

A #VALUE! result can point to an input type the function does not accept. For example, the grade function expects a number, while the overdue function explicitly returns a value error for invalid dates. Blank cells, numbers stored as text, date settings, and error values are worth checking. Test a UDF with a few ordinary values and boundary cases before filling it down thousands of rows.

Macro security is separate from whether the code is correct. Do not enable macros in an unexpected attachment just to make a function work. Review the source, follow your organization’s security rules, and keep an untouched backup. A macro-enabled file is not automatically unsafe, but VBA can perform actions beyond returning a worksheet value, so trust and provenance matter.

Frequently Asked Questions

How do I create a custom function in Excel VBA?
Open the VBA editor, choose Insert > Module, paste a Function procedure into the standard module, save the workbook as .xlsm, and call the function from a worksheet cell. The desktop Excel app is required to create and run VBA.

Can I use a VBA function in Excel for the web?
No. Excel for the web can open a macro-enabled workbook and preserve its VBA content, but it cannot create, edit, or run VBA macros. Open the workbook in desktop Excel to calculate with its VBA UDFs.

Why does my custom function show #NAME?
Check the function spelling, module location, workbook containing the code, and macro availability. Also confirm that the workbook with the function is open and that the formula is not pointing to a copy that lacks the VBA module.

Can a VBA function change other cells?
A worksheet UDF should calculate and return a value, not edit other cells or alter workbook state. Use a Sub macro for actions such as writing to a range, changing formatting, or creating a worksheet.

Can I undo a VBA macro with Ctrl+Z?
No. Running a macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a separate copy before running a macro, especially if it modifies workbook content.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.