Excel VBA Name Errors: Causes and Fixes

There is no single standard Excel VBA message called “Name error.” The phrase usually refers to a name-related compile error, an invalid or duplicate worksheet name, or Excel’s separate #NAME? formula error; identify which message you see before choosing a fix.

What “name error” can mean in Excel

In VBA, a name is a label used for something such as a variable, procedure, module, workbook, or worksheet. VBA reports specific errors when it cannot resolve a name or when code tries to assign a name Excel will not accept. The wording matters: “Ambiguous name detected” and “Sub or Function not defined” are compile errors, while a failed worksheet rename is commonly a run-time error, often error 1004.

People also use “name error” to describe #NAME? in a worksheet cell. That is an Excel formula error, not a VBA error. It commonly appears when Excel cannot interpret a function or name in a formula. These issues can be connected—for example, a macro may write a formula containing a misspelled function—but their causes and fixes differ.

Start by noting the exact message and when it appears: while editing or compiling code, while a macro is running, or in a cell after a formula is entered. That simple distinction usually narrows the diagnosis quickly.

Common VBA messages involving names

Several different errors may sound like a “name error,” but each points to a different problem:

“Ambiguous name detected”: VBA cannot determine which matching identifier you mean in the current context. A duplicate procedure or a conflicting name in the project may be responsible.
“Sub or Function not defined”: The code calls a procedure or function VBA cannot find under that name. A typo, a missing reference, or a procedure that is inaccessible from the calling code can cause this.
“Variable not defined”: With Option Explicit enabled, VBA flags a variable that has not been declared, often because its spelling differs from the declaration.
Run-time error 1004 when setting a sheet name: Excel rejected the requested worksheet name, for example because it contains a forbidden character or duplicates another sheet name.
#NAME? in a cell: Excel cannot interpret part of a formula. This is not the same as a VBA compile or run-time error.

Do not try to fix all of these by renaming the worksheet or the macro. First match the message to the kind of name involved.

How VBA names are resolved

VBA uses identifiers to refer to variables, constants, procedures, modules, and other objects. An identifier generally starts with a letter and can contain letters, numbers, and underscores; it cannot contain spaces or punctuation such as a hyphen. Reserved VBA words, such as Sub or Next, cannot be used as ordinary identifiers.

Names also have scope. A variable declared inside a procedure is normally available only there, while a module-level or public declaration can be available more widely. If code calls a routine whose name is duplicated or otherwise ambiguous in the relevant scope, VBA may not know which one to use. When appropriate, qualify a call with its module or object, or give one routine a distinct name.

For example, a call written as CalculateTotal may be unclear if more than one accessible procedure has that name. Renaming one routine to something specific, such as CalculateInvoiceTotal, can make the intent clear. Also check for small spelling differences: customerTotal and customersTotal are different identifiers, even if they look similar at a glance.

Worksheet names have separate restrictions

A worksheet tab name is not a VBA identifier, and Excel applies its own rules. A sheet name must not be blank, must be no more than 31 characters, and cannot contain any of these characters: colon, backslash, slash, question mark, asterisk, or square brackets. It also cannot begin or end with an apostrophe. Worksheet names are not case-sensitive for uniqueness, so Summary and summary cannot be separate tabs in the same workbook.

For example, this assignment requests an invalid tab name because it contains a slash: Worksheets("Sheet1").Name = "Sales/2026". Excel will reject it. A name such as Sales 2026 avoids that particular problem, provided the workbook does not already contain a sheet with the same name.

Names that are valid as worksheet tabs may still be unsuitable as VBA identifiers. A tab can contain spaces, as in Quarterly Sales, but a VBA variable or procedure name cannot. Keep the two naming systems distinct when debugging.

Diagnose the problem before changing code

Use the error’s timing and the highlighted line to guide the investigation. When VBA stops, the editor normally highlights the statement that failed, but that line may depend on a name assigned earlier or on an object selected by previous code.

Read the full message. Record the exact wording and any error number rather than searching only for “name error.”
Identify the object being named. Is it a VBA variable, a procedure, a module, a worksheet tab, a workbook, a defined name, or a formula function?
For compile errors, inspect spelling and declarations. In the Visual Basic Editor, choose Debug > Compile VBAProject to surface compile problems. Check that the called procedure exists and is accessible.
For a sheet rename failure, inspect the proposed text. Check its length, forbidden characters, apostrophes at either end, and whether another tab already uses the name.
For #NAME?, inspect the formula itself. Check function spelling, quotation marks around text, and whether any named range or custom function it uses is available.

On Windows, press Alt+F11 to open the Visual Basic Editor; on Mac, the common shortcut is Option+F11, though keyboard settings may require Fn as well. In Excel for the web, you cannot create, edit, or run VBA macros; open the workbook in the desktop application for VBA work.

Safely rename a worksheet in VBA

Before running a macro, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a copy gives you a way back if the macro changes something unexpectedly. The example below checks common worksheet-name restrictions and an existing tab name before applying a new name.

Option Explicit

Sub RenameSheetSafely()
    Dim ws As Worksheet
    Dim otherSheet As Worksheet
    Dim newName As String
    Dim forbidden As Variant
    Dim i As Long

    ' Change these names to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    newName = "Quarterly Sales"
    forbidden = Array(":", Chr$(92), "/", "?", "*", "[", "]")

    If Len(newName) = 0 Or Len(newName) > 31 Then
        MsgBox "The sheet name must contain 1 to 31 characters."
        Exit Sub
    End If

    If Left$(newName, 1) = "'" Or Right$(newName, 1) = "'" Then
        MsgBox "A sheet name cannot begin or end with an apostrophe."
        Exit Sub
    End If

    For i = LBound(forbidden) To UBound(forbidden)
        If InStr(1, newName, CStr(forbidden(i)), vbBinaryCompare) > 0 Then
            MsgBox "The sheet name contains a forbidden character."
            Exit Sub
        End If
    Next i

    For Each otherSheet In ThisWorkbook.Worksheets
        If otherSheet.Index <> ws.Index Then
            If StrComp(otherSheet.Name, newName, vbTextCompare) = 0 Then
                MsgBox "A sheet with that name already exists."
                Exit Sub
            End If
        End If
    Next otherSheet

    ws.Name = newName
End Sub

The code assumes that a worksheet named Sheet1 exists in the workbook containing the macro. Change that reference if needed. The loop compares names without regard to case, matching Excel’s case-insensitive uniqueness rule. The macro does not rename any other sheets; it exits with a message if the proposed name fails one of its checks.

Fix identifier and formula-name problems

For “Ambiguous name detected,” search the project for the exact identifier and review procedures and declarations that share it. Rename a conflicting item or make a call explicit where that is appropriate. Do not change a built-in event procedure’s required name casually: event handlers often need a precise name and location to run.

For “Sub or Function not defined,” compare the call with the actual procedure declaration character by character. Confirm that the procedure is in the workbook or add-in you expect, and check its access level. If the code depends on an external library, a missing reference can also prevent VBA from resolving a referenced type or function; review Tools > References in the Visual Basic Editor for entries marked “MISSING.”

For #NAME?, correct the worksheet formula rather than renaming a VBA variable. Text values in formulas generally need straight double quotation marks, while a named range must exist with the spelling used in the formula. A custom VBA function may also be unavailable if macros are disabled, the function is not public in a standard module, or the workbook containing it is not open. The exact cause depends on the formula and workbook setup.

Misconceptions that slow troubleshooting

A common misconception is that every name-related message means a duplicate worksheet tab. It does not: compile errors concern VBA’s code and identifiers, while a rejected tab name is an Excel object-property problem. Another is that changing capitalization will resolve a duplicate sheet name; Excel treats sheet names as case-insensitive, so that does not create a distinct tab.

It is also easy to assume that every failed macro line is itself the root cause. A line that assigns a name may be where the problem becomes visible, but the value may have been assembled incorrectly earlier—for instance, by joining a label with a date or importing text containing a slash. Inspect the value being assigned, not only the statement that assigns it.

Frequently Asked Questions

What does “Ambiguous name detected” mean in Excel VBA?
VBA found more than one possible match for an identifier in the relevant context. Search for duplicate or conflicting names, then rename an item or qualify the reference so VBA can identify the intended one.

Why do I get Run-time error 1004 when renaming a sheet?
The requested name may be too long, blank, contain a forbidden character, begin or end with an apostrophe, or already belong to another sheet. Check the exact value before assigning it to the worksheet’s Name property.

Is Excel’s #NAME? error a VBA error?
No. It is a worksheet formula error: Excel cannot recognize a name or part of the formula. A macro can put a faulty formula into a cell, but the cell error is still produced by Excel’s formula parser.

How do I find a duplicate procedure name?
Use Find in the Visual Basic Editor to search the project for the procedure’s exact name, then inspect each declaration and call. Remember that names in different modules can have different scopes, so check how the code is referenced rather than assuming every repeated word is automatically a conflict.

Can I run or edit VBA in Excel for the web?
No. Excel for the web can preserve a workbook containing macros, but VBA editing and execution require desktop Excel. Use Excel for Microsoft 365 on Windows or the desktop Mac app for those tasks.

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.