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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Hi"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.InputBox ("who are you?")
End Sub
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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Hi"
Application.InputBox ("who are you?")
End Sub
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.
Prevent an auto macro/event macro executes using VBA in Microsoft Excel | To prevent the run of auto_open macro use the shift key.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
