Ambiguous Name Detected VBA
There are a number of different reasons why this error can occur in VBA. This is usually due to the fact that a variable and a procedure (sub or function) are called the same within the same scope in your code; of you have 2 procedures that have the same name within the same scope.
Variable and Procedure with Same Name
This error also occur if you have a variable and procedure in the same module with the same name.
To resolve the issue, (1) rename either the the variable and then, (2) in the menu, click Debug > Compile VBA Project to make sure your code will compile.
You will know your code is compiled correctly if the compile option is then greyed out.
Duplicate Functions or Sub-Procedures
This error will also occur if you have 2 procedures with the same name in the same module, when you try and compile your code, you will get the compile error – Ambiguous name detected.
Removing one of the procedures or amending the name, and then recompiling the code will once again fix the error.
VBA Code Examples Add-in
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
Why is VBA saying that it has found an 'ambiguous name'?
When compiling some code (declarations shown below) I get the error message ‘Compile Error: Ambiguous name detected. SixTables’. I have looked here and elsewhere but cannot find anything that matches my problem. What appear to be the most common causes of this error, declaring two variables with identical names or giving the same name to a function and the sub that it is called from, do not apply. And yes, I know I could just change the name to something the system was happy with, but (1) I wouldn’t learn what I’m doing wrong, and (2) I chose that name for a reason — it fits its purpose exactly 🙂
EDIT: I’m so embarrassed that I can hardly bring myself to explain what the problem was. I knew I hadn’t duplicated a name, since I was only using it once — as a function! Thanks all for your help, I’m now going to go and hide my face in shame.
2 Answers 2
- More than one object in the same scope may have elements with the same name.
Module-level identifiers and project-level identifiers (module names and referenced project names) may be reused in a procedure, although it makes programs harder to maintain and debug. However, if you want to refer to both items in the same procedure, the item having wider scope must be qualified. For example, if MyID is declared at the module level of MyModule , and then a procedure-levelvariable is declared with the same name in the module, references to the module-level variable must be appropriately qualified:
- An identifier declared at module-level conflicts with a procedure name.
For example, this error occurs if the variable MyID is declared at module level, and then a procedure is defined with the same name:
Having had this issue many times, and not fully understanding why, I think there is an important fact that I have tested, and someone can confirm. It may be obvious to professional programmers, but I place this here for those that seek these answers on discussions, who are usually not professional programmers.
A variable declared within a sub() is not «declared» (memory assigned) until that sub() is executed. And when the execution of that sub() is complete, the memory is released. Until now, I thought Public variables acted in similar way; available to any module that used it, —BUT only existing as long as the module where they were declared was still executing.
However, for any Public variable who’s declaration line is in any module in the workbook, that variable is declared and available at the execution of any module within the workbook, even if the module where the Public declaration exists is never called or executed.
Therefore, if you have a Public variable declaration in one module, and then again in a separate module that you plan to run independent of the first, Excel still sees 2 declarations for the same variable, and thus it is ambiguous.
To prove it, you can create a blank module within a workbook project, and add absolutely nothing except two Public declaration lines,
Public VariableX as String
Public VariableY as Integer
and those variables will be declared and available throughout the project, for any sub() executed. This fact is also probably the reason for the tip about minimizing the use of public variables.
Again, this may have been kindergarten level information for professional programmers, but most on this are not pros, and have to learn these lessons the hard way, or through discussion boards like this. I hope this helps someone.
Name already in use
VBA-Docs / Language / Reference / User-Interface-Help / ambiguous-name-detected.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
Ambiguous name detected
The identifier conflicts with another identifier or requires qualification. This error has the following causes and solutions:
More than one object in the same scope may have elements with the same name.
Qualify the element name by including the object name and a period. For example:
Module-level identifiers and project-level identifiers (module names and referenced project names) may be reused in a procedure, although it makes programs harder to maintain and debug. However, if you want to refer to both items in the same procedure, the item having wider scope must be qualified. For example, if MyID is declared at the module level of MyModule , and then a procedure-level variable is declared with the same name in the module, references to the module-level variable must be appropriately qualified:
An identifier declared at module-level conflicts with a procedure name. For example, this error occurs if the variable MyID is declared at module level, and then a procedure is defined with the same name:
In this case, you must change one of the names because qualification with a common module name would not resolve the ambiguity. Procedure names are Public by default, but variable names are Private unless specified as Public.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
How to Solve «Ambiguous Name Detected» VBA Error
While working with VBA you may get this error popup when you run the procedure. This error says «Ambiguous name detected: procedure name «. In this article, we will learn why this error occurs and how we can solve it.
Why this error?
Well as the pop up says, VBA engine has detected one or more procedures with the same name in the same module. This has led to confusion for the VBA engine. Hence the VBA reports the error.
How to solve this problem?
It’s easy. Don’t have two same procedures with the same name in a single module. Either change the name of the procedure, delete it or take it to some other module.
This error typically pops up while working with events in Excel.
Let’s say you are working on sheet1 with worksheet events. Now you want to trigger a message as soon as the user changes the cell selection. In that case you will use the Worksheet_SelectionChange() Event .
Now again you want to ask some questions to the users as the user changes the cell selection. Then you will have to use the same event. Now you have written two event handling procedures for different tasks.
Now when you change the selection on the sheet, this is what you get.
Now when you change the selection on the sheet, this is what you get.
See, the thing is that Excel doesn’t allow this. You can’t have two or more same names of functions in a module. Not even in Events. It leads to ambiguity.
Now if you want to do a different task on the same event, then you have to put that code in the same one event procedure. Use conditions to control the events.
The below event will work perfectly fine. When the user changes the cell selection on the sheet1, it will pop up a hi message and after that it will ask the user a question with an input box.
Now that the ambiguity is gone, the event will work perfectly fine and you will not get the error ambiguous name detected error again.
So yeah guys, this is how you can solve the ambiguity error in Excel VBA. I hope this was helpful. If you have any doubts regarding this article or any other article ask me in the comments section below.
Related Articles:
Reference Isn’t Valid Excel Error and How to Solve It? : This error occurs when we refer to an object or location that doesn’t exist or have changed. To solve this problem we track down the reference.
The Events in Excel VBA | There are seven types of Events in Excel. Each event deals in a different scope. Application Event deals with workbook level. Workbook on sheet level. Worksheet Event at Range level.
The Worksheet Events in Excel VBA |The worksheet events are really useful when you want your macros run when a specified event occurs on the sheet.
Workbook events using VBA in Microsoft Excel | The workbook events work on the entire workbook. Since all the sheets are part of the workbook, these events work on them too.
Chart object events using VBA in Microsoft Excel | The Charts are complex objects and there are several components that you attached to them. To make the Chart Events we use the Class module.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don’t need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.