Excel VBA Global Functions: Scope and How to Create One

VBA & macros · Updated November 21, 2026

In Excel VBA, a global function is usually a Public Function declared in a standard module, making it callable from other modules in the same VBA project and, if it meets Excel’s rules for user-defined functions, from a worksheet formula. VBA has no single function that is automatically global across every open workbook: visibility is governed by the function’s declaration and the workbook or add-in that contains it.

What global means in Excel VBA

“Global function” is common shorthand, not a special VBA function type. In practice, people mean a procedure declared Public whose name can be used beyond the module where it was written. A function calculates or returns a value; a VBA Sub performs an action and does not return a value in the same way.

There are two boundaries to keep in mind. First is module scope: a public procedure in a standard module can generally be called by other procedures in the same VBA project, while a private procedure is restricted to its containing module. Second is project scope: a function in one workbook’s VBA project does not automatically become a function in every other workbook. “Public” does not mean universally available on the computer.

That distinction resolves a common misconception. A public function can be broadly visible within its project, but it is not a built-in Excel function such as SUM, and it does not become a permanent part of Excel merely because it is declared public.

Where to put the function

For a function you want to call from worksheet cells, put it in a standard module, rather than in a worksheet code module or the ThisWorkbook module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, then place the function in the new module. You can also open the editor from the Developer tab by choosing Visual Basic.

If the Developer tab is not visible in Windows Excel, enable it through File > Options > Customize Ribbon and select Developer. On a Mac, the Visual Basic Editor is available from the Developer tab; the keyboard shortcut is commonly Option+F11, with Fn sometimes needed depending on the keyboard. Menu names and keyboard behavior can vary slightly by Mac setup.

Worksheet modules and ThisWorkbook are object modules: they contain code associated with a particular sheet or workbook. They are appropriate for event procedures, such as code that runs when a cell changes, but are not the normal home for a worksheet-callable custom function. A standard module is the straightforward choice for reusable functions.

Create a simple public function

This example counts words separated by spaces. It treats an empty cell or a string containing only ordinary spaces as zero words, and collapses repeated ordinary spaces before counting. In a standard module, add the following code:

Option Explicit

Public Function WordCount(ByVal textValue As String) As Long
    Dim normalizedText As String

    ' Treat blank input as zero words.
    If Len(Trim$(textValue)) = 0 Then
        WordCount = 0
        Exit Function
    End If

    ' Collapse repeated ordinary spaces before counting.
    normalizedText = Application.WorksheetFunction.Trim(textValue)
    WordCount = UBound(Split(normalizedText, " ")) + 1
End Function

Public makes the function available beyond its module within the project. Function begins a function procedure, and the declared return type As Long means its result is a whole number. Assigning a value to WordCount, the function’s own name, sets that result. The argument textValue receives the input, and ByVal means the function works with a value rather than changing the caller’s variable through that argument.

If the text is in cell A2, enter =WordCount(A2) in another cell. For example, if A2 contains Excel VBA global function, the result is 4. A worksheet formula that directly passes a cell reference gives Excel a dependency it can track, so a change to A2 can trigger recalculation.

This is a simple word counter, not a full linguistic parser. It counts items separated by spaces; punctuation remains attached to a word, and nonbreaking spaces or line breaks may need additional handling. The limitation is useful to understand: custom functions are only as accurate as the rules their code implements.

Calling it from another VBA procedure

A public function can also be called by another procedure in the same project. For example, a VBA procedure in a different standard module can assign the function’s result to a variable. The function name is ordinarily enough when it is unambiguous; you do not need to prefix it with its module name.

If two accessible procedures have the same name, VBA may report an ambiguous name or require you to resolve the conflict. Give reusable functions distinct, descriptive names, and avoid using a name that duplicates a built-in Excel function. A function name that resembles a built-in can confuse users even when the code compiles.

A function is not a substitute for a macro that edits a sheet. A worksheet UDF should calculate and return a result, not change other cells, formatting, or Excel’s environment. If the task is to write results into a range, delete rows, or format a report, use a Sub procedure, such as a button-triggered macro, rather than trying to make a worksheet formula perform those actions.

Using a custom function in worksheet formulas

To use a VBA function in a cell, it normally needs to be a public function in a standard module, and the workbook containing its code must be open with macros permitted. Enter it as you would a built-in function, using its name and arguments. You can also select it through Excel’s function-insertion interface, although custom functions may be grouped under user-defined functions.

Worksheet UDFs have important limitations. They cannot safely perform actions that change the workbook as a side effect of recalculation. They should return a value based on their inputs. They also do not automatically recalculate merely because some unrelated external condition has changed. Excel tracks ordinary cell references supplied as arguments, but it cannot infer every dependency hidden inside VBA code.

Application.Volatile can make a function recalculate whenever Excel recalculates, even if its explicit arguments have not changed. That can be appropriate for a function that depends on a changing condition Excel does not track, but it can also slow calculation when used widely. It is not necessary for the word-count example, because the input cell is an explicit argument.

Public versus Private and other scope choices

Use Public Function when callers elsewhere in the project, or a worksheet formula, need to use the function. Use Private Function when the procedure is an internal helper that should only be called from within its own module. A private helper keeps implementation details out of the wider project interface and reduces the chance that another procedure will depend on code you later change.

VBA also has public variables, sometimes declared with Public at module level. A global variable is not a global function: the variable stores state, while a function calculates and returns a result. Global variables can make code harder to understand because their values may be changed from many places. Prefer passing needed values as function arguments unless shared state is genuinely required.

Keep scope aligned with use. A small utility used by several modules can be public. A helper used by one larger function can be private. For a worksheet function, public visibility and standard-module placement are the usual combination.

Saving, sharing, and workbook boundaries

VBA code requires a macro-enabled file format. If the function is stored in a workbook, save the file as an Excel Macro-Enabled Workbook (.xlsm); saving it as a standard .xlsx file removes the VBA project. An Excel add-in (.xlam) is another way to distribute reusable VBA functions, though installing and enabling an add-in is a separate setup step.

For a function kept in a regular workbook, users generally need that workbook open and its macros enabled to use the function. If another workbook uses the function, the code does not simply transfer into that workbook. Sharing the macro-enabled workbook or distributing a properly installed add-in is more reliable than assuming a public function is available globally.

Macro security matters because VBA can do much more than calculate a result. Open code only from a source you trust, and follow your organization’s security policy. Before running a macro that changes workbook data, save a separate copy: macro actions cannot be undone with Ctrl+Z. The sample function only returns a value, but the precaution applies to macros generally.

Common problems and how to diagnose them

  • The worksheet shows #NAME?. Check that the function name is spelled correctly, the code is in a standard module, the workbook containing it is open, and macros are enabled. Also check that the file was saved in a macro-enabled format.
  • Another module cannot call the function. Confirm its declaration is Public Function, not Private Function, and that the caller is in the same VBA project. Public scope does not automatically cross workbook-project boundaries.
  • The code compiles but the cell function is unavailable. Confirm that the procedure is in a standard module and is a function that returns a value. A function in a sheet’s code module is not the usual worksheet-UDF arrangement.
  • The result does not update as expected. Ensure the relevant cell is passed as an argument. Consider whether calculation is manual or the function depends on something Excel cannot track. Use volatility only when its broader recalculation behavior is appropriate.
  • The function conflicts with another name. Rename it to something distinctive, then update formulas that use the old name. Compile the VBA project to catch naming and syntax issues.

Alternatives to a VBA global function

For a calculation that can be expressed with built-in worksheet functions, a normal Excel formula is usually easier to share: it does not require VBA macros to be enabled and is more transparent to other spreadsheet users. Excel’s built-in functions also have a wider range of compatibility across platforms and file-sharing workflows.

For repeatable data cleaning and importing, Power Query may be a better fit than a VBA UDF. For automation that changes cells or runs a sequence of actions, use a VBA Sub or consider Office Scripts where available. Choose VBA when custom logic is needed and the workbook’s users can work with macro-enabled files; choose the least complex tool that meets the requirement.

Frequently Asked Questions

Is there a Global Function keyword in VBA?
No. Use Public Function to make a procedure broadly accessible within its VBA project. “Global function” is an informal description of scope, not a separate VBA keyword.

Can a public VBA function be used in any workbook?
No. Public visibility does not automatically extend to every open workbook. The workbook or add-in containing the function must be available, and another project may need an explicit way to access it.

Why can’t Excel find my VBA function in a cell?
Check that it is a public function in a standard module, that the file is macro-enabled, and that macros are allowed. A typo in the formula or a closed workbook that contains the code can also prevent Excel from resolving it.

Should I use Public Function or Private Function?
Use Public when a worksheet formula or code outside the module needs the function. Use Private for a helper that should remain usable only inside its own module.

Can a worksheet UDF change other cells?
A worksheet UDF should return a value rather than modify other cells or workbook settings as a side effect. Use a macro procedure such as a Sub when the job is to change the workbook.

Related

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.