Compile error ambiguous name detected

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.

«Ambiguous name detected» error when you run a procedure in Access

Original KB number: В 817411

Requires basic macro, coding, and interoperability skills.

Symptoms

When you run a procedure in Microsoft Access 2000 or later, you may receive the following error message:

The expression Event_Name you entered as the event property setting produced the following error: Ambiguous name detected: EventProcedure_Name.

Cause

This error occurs when there are multiple procedures with the same name in a module.

Workaround

To work around this problem, delete the unwanted duplicate procedure. To do this, follow these steps:

Open the form in Design view.

In Microsoft Office Access 2003, click Code on the View menu.

In Microsoft Office Access 2007, click View Code in the Tools group on the Design tab.

On the Debug menu, click Compile Database Name.

You receive the following error message:

Compile error: Ambiguous name detected: Procedure Name.

Notice the procedure name, and then click OK.

In the code, find the other occurrences of the Procedure Name procedure, where Procedure Name produced the compile error mentioned in step 3.

Select and then delete the unwanted procedure.

On the Debug menu, click Compile Database Name.

On the File menu, click Close.

Источник

Обнаружено неоднозначное имя

Идентификатор конфликтует с другим идентификатором или требует квалификации. Эта ошибка имеет следующие причины и способы решения:

Элементы с одинаковыми именами могут принадлежать нескольким объектам в одной и той же области видимости.

Квалифицируйте имя элемента, указывая имя объекта и точку. Пример:

Идентификаторы уровня модуля и идентификаторы уровня проекта (имена модуля и имена связанных проектов) могут повторно использоваться в процедуре, хотя это усложняет сопровождение и отладку программы. Но, если нужно сослаться в одной процедуре на оба элемента, необходимо квалифицировать элемент с более широкой областью видимости. Например, если переменная MyID объявлена на уровне модуля MyModule , а затем в модуле на уровне процедуры объявлена переменная с тем же именем, ссылки на переменную уровня модуля должны быть соответствующим образом квалифицированы:

Идентификатор, объявленный на уровне модуля, конфликтует с именем процедуры. Например, эта ошибка возникает, если переменная MyID объявлена на уровне модуля, а затем определена процедура с таким же именем:

В этом случае необходимо изменить можно из имен, так как квалификация с общим именем модуля не разрешит неоднозначность. Имена процедур по умолчанию являются общедоступными (Public), а имена переменных — частными (Private), если они не объявлены как Public.

Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh).

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Источник

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).

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник

Ambiguous Name Detected VBA

This article will explain the reason that an ambiguous name is detected in 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.

Источник

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.

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.

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.

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.

Источник

Hi guys,

I’m just having a little problem with some code. A colleague and I wrote two separate pieces of code and we’re
now trying to combine it into the one worksheet. 
I believe the problem lies with the fact that we have Two «Private Sub Worksheet_Change(ByVal Target As
Range)» events in the same worksheet. 
When I remove one of the «Private Sub Worksheet_Change(ByVal Target As Range)» — I still can’t get
things to work.
Does anyone have a solution? If so it would be much appreciated.

Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
‘Hard-coded ranges may be a maintenance issue

On Error GoTo ErrHandler

‘Create a dialog which appears when Fail or Retest is selected for a test case and the corresponding JIRA column
is blank
If Not Intersect(Target, Range(«$M$2:$M$1048576»)) Is Nothing Then
Application.EnableEvents = False
If (Target.Value = «Fail» Or Target.Value = «Retest») And IsEmpty(Range(«$R$»
& Target.Row)) Then
MsgBox «A JIRA must be present for a test of status Fail or Retest»
‘Range(«$R$» & Target.Row).Font.Color = vbRed
‘Range(«$R$» & Target.Row).Font.Bold = True
‘Range(«$R$» & Target.Row).Value = «A JIRA number is required»
End If
Application.EnableEvents = True
End If

‘Update the number of test cases without priority
If Not Intersect(Target, Range(«$I$2:$I$1048576»)) Is Nothing Then
Application.EnableEvents = False
Dim numberWithoutPriority As Integer
‘Number test cases without priority = (number of non-blank test case ids) — (number of non-blank priorities)
numberWithoutPriority = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(«02. Test Cases»).Range(«$A$2:$A$1048576»))
_
— Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(«02. Test Cases»).Range(«$I$2:$I$1048576»))
ThisWorkbook.Sheets(«01. Document Info»).Range(«$B$10»).Value = numberWithoutPriority
Application.EnableEvents = True
End If

‘Update the number of test cases without status and number of test cases missing JIRAs
If (Not Intersect(Target, Range(«$M$2:$M$1048576»)) Is Nothing) Or (Not Intersect(Target, Range(«$R$2:$R$1048576»))
Is Nothing) Then
Application.EnableEvents = False
Dim numberWithoutStatus As Integer
Dim numberMissingJIRAs As Integer
‘Number test cases without status = (number of non-blank test case ids) — (number of non-blank statuses)
numberWithoutStatus = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(«02. Test Cases»).Range(«$A$2:$A$1048576»))
_
— Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(«02. Test Cases»).Range(«$M$2:$M$1048576»))
‘Number test cases missing JIRA
numberMissingJIRAs = 0
For Each c In ThisWorkbook.Sheets(«02. Test Cases»).Range(«$M$2:$M$1048576»).Cells
If IsEmpty(ThisWorkbook.Sheets(«02. Test Cases»).Range(«$A$» & c.Row)) Then
Exit For
ElseIf ((c.Value = «Retest» Or c.Value = «Fail») And IsEmpty(ThisWorkbook.Sheets(«02.
Test Cases»).Range(«$R$» & c.Row))) Then
numberMissingJIRAs = numberMissingJIRAs + 1
End If
Next
ThisWorkbook.Sheets(«01. Document Info»).Range(«$B$9»).Value = numberWithoutStatus
ThisWorkbook.Sheets(«01. Document Info»).Range(«$B$11»).Value = numberMissingJIRAs
Application.EnableEvents = True
End If

ErrHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Activate()
Application.CommandBars.FindControl(ID:=847).Enabled = False
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars.FindControl(ID:=847).Enabled = True
Me.Name = «02. Test Cases»
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Does the validation range still have validation?
If Not Intersect(Range(«ValidationRange»), Target) Is Nothing Then
Application.EnableEvents = False
If HasValidation(Intersect(Range(«ValidationRange»), Target)) = False Then
Application.Undo
MsgBox «Your last operation was canceled.» & vbCrLf & _
«It would have deleted data validation rules.», vbCritical
End If
End If
Application.EnableEvents = True
End Sub

Private Function HasValidation(r As Range) As Boolean
‘ Returns True if every cell in Range r uses Data Validation
Dim c As Range
Dim x As XlDVType
On Error Resume Next
For Each c In r.Cells
x = r.Validation.Type
If Err Then
HasValidation = False
Exit Function
End If
Next c
HasValidation = True
End Function

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.