Excel VBA Worksheet Functions: What You Can Use

Excel VBA can call many worksheet functions through Application.WorksheetFunction, including Sum, Average, CountIf, VLookup, Index, Match, Round, Max, and Min. There is no single short list that covers every function in every Excel version: availability depends on the function and the version, so check the function in your Excel object library or use an alternative calling method when it is not exposed.

What the WorksheetFunction list means

Application.WorksheetFunction is the Excel VBA object through which you call worksheet functions as VBA methods. The object exposes a large collection of familiar functions, but it is not a separately maintained, fixed-size catalog that applies to every Excel release. Some newer worksheet functions may not be available as methods in older versions, and functions that are available on a worksheet are not necessarily callable through this object in every version.

For example, a worksheet formula is entered in a cell and recalculated by Excel, while VBA can call a corresponding method and assign its returned value to a variable. In VBA, you write the method name without the leading equals sign used by a worksheet formula. A call also uses VBA syntax: values and ranges are passed as arguments, and the result is returned to the code.

Use the object when you want Excel to perform a familiar calculation without writing the calculation logic yourself. It can be especially useful for statistical calculations, conditional totals, lookups, and rounding. It does not mean that the function is a VBA language feature: it is Excel providing access to worksheet-function behavior.

Common functions by category

The following names are useful starting points, not a guarantee that every method is available in every installation. Excel’s supported function set changes over time, and the VBA object model can differ by version.

Totals and counts: Sum, Average, Count, CountA, CountBlank, CountIf, CountIfs, SumIf, and SumIfs.
Basic statistics: Max, Min, Median, Mode, StDev, and Var. Some statistical functions have newer or revised variants, so confirm which one fits the intended calculation.
Lookups and references: VLookup, HLookup, Index, Match, Choose, and Offset. Newer lookup functions may require a suitable Excel release and may not be exposed through the same VBA interface in every version.
Numbers and rounding: Round, RoundUp, RoundDown, Int, Mod, Abs, Power, and Sqrt.
Text and dates: Left, Right, Mid, Len, Trim, Substitute, Date, Year, Month, Day, Weekday, and NetworkDays.
Logic and error handling: And, Or, IfError, and IsNumber. VBA also has its own conditional and error-handling statements, which may be more suitable in code.

This list combines frequently used examples rather than promising a complete inventory. Function names and availability should be checked against the Excel version where the macro will run, especially when a workbook is shared with people using older releases.

How to call a function from VBA

Use the Application.WorksheetFunction object, then provide the function’s arguments. For a range argument, pass a Range object. The example below assumes the worksheet named Sheet1 has the values 10, 20, and 30 in cells B2:B4; the sum returned is 60. Save a copy of the workbook before running a macro: actions performed by VBA generally cannot be reversed with Ctrl+Z.

Public Sub ShowTotal()
    Dim ws As Worksheet
    Dim total As Double

    ' Use the values in B2:B4 on Sheet1.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    total = Application.WorksheetFunction.Sum(ws.Range("B2:B4"))

    MsgBox total
End Sub

The method’s arguments follow the worksheet function’s meaning, but VBA’s rules still apply. A function may return a number, text, a date serial, or another value, so choose a variable type that can hold the result. A Variant is useful when the result might be an Excel error value; a Double is suitable for many numeric results.

WorksheetFunction versus Application calls

A common point of confusion is the difference between Application.WorksheetFunction.VLookup and Application.VLookup. Both can perform a lookup, but their error behavior differs. If a lookup using WorksheetFunction.VLookup cannot find a match, it raises a VBA runtime error. Calling Application.VLookup instead returns a Variant that can contain an Excel error value, which the code can check with IsError.

The following example assumes the lookup key North is in A2:A10 and the corresponding return values are in B2:B10 on Sheet1. It uses an exact match and checks the returned value before displaying it.

Public Sub FindRegionValue()
    Dim ws As Worksheet
    Dim lookupResult As Variant

    ' Search the first column of A2:B10 for an exact match.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lookupResult = Application.VLookup("North", ws.Range("A2:B10"), 2, False)

    If IsError(lookupResult) Then
        MsgBox "No matching value was found."
    Else
        MsgBox lookupResult
    End If
End Sub

Use the method that matches your error-handling plan. If a missing match is expected and should lead to a message or a fallback value, returning an error value and checking it can be convenient. If a missing match should stop the macro, the WorksheetFunction call can be appropriate, provided the code handles the resulting runtime error deliberately.

When a function is not exposed

Excel can have a worksheet function that is absent from the WorksheetFunction methods available to VBA in a particular version. One alternative is Application.Evaluate, which asks Excel to interpret an expression. Another is to write a formula to a cell and read its result. These approaches are not interchangeable in every situation: they have different requirements for references, formula context, and error handling.

For newer functions such as XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, and LET, version support matters. These functions require Excel 2021 or Microsoft 365. LAMBDA, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, and HSTACK require Microsoft 365 or Excel 2024. Even when Excel supports a function in cells, verify that the particular VBA call you intend to use is supported in the target environment.

For a function that is not available as a VBA method, an evaluated expression can be useful, but it should be tested with the workbook’s actual sheet names, references, and Excel version. If the calculation is simple, native VBA logic can be clearer and more portable than constructing a formula string. If it is complex, a cell formula may be easier for another workbook maintainer to inspect.

Choosing between a worksheet function and VBA logic

Calling a worksheet function is not automatically better than writing the calculation in VBA. Choose based on readability, compatibility, and how the result will be used. A function such as SumIfs can express a multi-criteria total compactly, while a loop may be easier to customize when each row needs a different action or validation.

Use a worksheet function call when Excel already has a suitable function and its standard behavior is what the macro needs.
Use VBA logic when the calculation involves workflow, row-by-row decisions, or custom rules that would make a formula difficult to understand.
Use a cell formula when users should see, audit, or update the calculation directly in the worksheet.
Check compatibility first when the workbook will run on older Excel versions or on computers with different update channels.

Also consider calculation volume. Repeatedly calling a worksheet function for each cell in a large loop can be slower than reading a range into an array, processing the values in memory, and writing results back in one operation. For modest workloads, clarity is often more important than optimizing prematurely; for large workbooks, measure the actual macro and consider reducing calls between VBA and the worksheet.

Common problems and how to avoid them

One frequent problem is assuming every Excel error will behave like a normal return value. A WorksheetFunction call can raise a runtime error rather than return a value VBA can test with IsError. Use the Application form for functions where that returned-error pattern is useful, and still check the result before using it.

Another issue is passing the wrong data type or range shape. A lookup function may expect its lookup column in a particular position; a conditional function expects criteria and sum ranges with compatible dimensions. A function may also return a value that does not fit the chosen VBA type. Check its documented arguments and return type, and use Variant where the result can legitimately vary.

Confirm that the function is available in every Excel version that must run the macro.
Use explicit worksheet references instead of relying on the active sheet.
Check for missing lookup results, blank cells, and invalid input before using a returned value.
Test on a copy of the workbook, especially when the macro also changes cells or other workbook content.

Excel for Mac supports VBA, but macro availability and some surrounding features can differ from Windows. A macro based only on ordinary worksheet functions and workbook ranges is generally more portable than one that relies on Windows-specific components. Excel for the web does not run VBA macros; users need desktop Excel to execute them.

Frequently Asked Questions

Is there a complete Excel VBA WorksheetFunction list?
Excel exposes many worksheet functions through Application.WorksheetFunction, but the available methods vary with Excel version. The VBA Object Browser and the documentation for the target Excel release are better references for checking a specific method than relying on an old copied list.

Can I use XLOOKUP in VBA?
Excel 2021 and Microsoft 365 support XLOOKUP in worksheets. Whether it can be called directly as a WorksheetFunction method depends on the Excel version and object model available to the macro. If direct method access is unavailable, consider Application.Evaluate or a formula in a worksheet cell, then test the approach in the target version.

Why does WorksheetFunction.VLookup stop my macro?
A failed lookup through Application.WorksheetFunction.VLookup can raise a runtime error instead of returning a value for IsError to inspect. Use Application.VLookup when you want to receive a Variant error result and branch on it, or add appropriate VBA error handling.

Do VBA worksheet functions use an equals sign?
No. The equals sign begins a formula entered in an Excel cell. In VBA, call the method by name and pass its arguments, such as Application.WorksheetFunction.Sum followed by a range argument.

Can Excel for the web run VBA?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA macros. Run the macro in desktop Excel for Windows or Mac, subject to the workbook’s and organization’s macro security settings.

Where to verify a particular method

When you need a specific function, check its official Excel documentation and the VBA Object Browser in the desktop application. In the VBA editor, press F2 to open the Object Browser, select the Excel library, and search for the method name. Confirm both that the method exists in the target version and that its arguments and return behavior fit your code. Then test cases that could fail, such as a missing lookup key or an empty range, before relying on the macro in a working workbook.

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.