VBA动态创建UserForm报‘Ambiguous name detected: Test’错误的解决方法

问题背景 #

动态创建名为Test的UserForm，首次运行正常，但第二次运行时在Set Label1 = Test.Controls.Add("Forms.Label.1", "Label1")行触发“Ambiguous name detected: Test”错误。即使调用删除窗体的代码，问题依然存在。

错误原因 #

1. 删除VBComponent后，UserForms集合中可能仍残留该窗体的实例引用，导致名称冲突
2. 直接使用窗体名称Test访问控件，依赖VBA编译时的名称绑定，残留实例会让系统无法区分新创建的组件和旧实例
3. 原代码中创建窗体后直接用名称操作，未使用返回的对象变量，放大了冲突风险

解决方案 #

1. 始终通过对象变量操作动态创建的窗体

创建窗体后，用返回的myForm对象替代直接引用Test名称，避免名称绑定冲突。

2. 彻底清理窗体实例和组件

在创建新窗体前，先销毁所有同名窗体实例，再删除VBComponent（如果存在），确保内存和项目中无残留。

修改后的完整代码 #

动态创建UserForm的代码

Sub Crt_UserForm(ByVal count23 As Long, ByVal Temp As String, shts As Variant)
    Dim myForm As Object
    Dim Textbox1 As MSForms.TextBox
    Dim Textbox2 As MSForms.TextBox
    Dim Label1 As MSForms.Label
    Dim Label2 As MSForms.Label
    Dim Button1 As MSForms.CommandButton
    Dim Button2 As MSForms.CommandButton
    Dim shtchk() As MSForms.CheckBox
    
    ' 创建前先清理可能存在的Test窗体
    CleanUpUserForm "Test"
    
    ' 创建新的UserForm组件
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)
    
    With myForm
        .Properties("Name") = "Test"
        .Properties("Caption") = "Выберите листы для защиты паролем"
        .Properties("Width") = 300
        .Properties("Height") = 150
    End With
    
    ' 使用myForm对象添加控件，避免直接引用Test名称
    Set Label1 = myForm.Controls.Add("Forms.Label.1", "Label1")
    Set Label2 = myForm.Controls.Add("Forms.Label.1", "Label2")
    Set Textbox1 = myForm.Controls.Add("Forms.Textbox.1", "Textbox1")
    Set Textbox2 = myForm.Controls.Add("Forms.Textbox.1", "Textbox2")
    Set Button1 = myForm.Controls.Add("Forms.CommandButton.1", "Button1")
    Set Button2 = myForm.Controls.Add("Forms.CommandButton.1", "Button2")
    
    ' 控件布局设置
    With Label1
        .Top = 10
        .Left = 10
        .Width = 50
        .Height = 20
        .Caption = "Текущий пароль"
    End With
    With Label2
        .Top = 10
        .Left = 70
        .Width = 50
        .Height = 20
        .Caption = "Новый пароль"
    End With
    With Textbox1
        .Top = 40
        .Left = 10
        .Width = 50
        .Height = 20
    End With
    With Textbox2
        .Top = 40
        .Left = 70
        .Width = 50
        .Height = 20
    End With
    With Button1
        .Top = 70
        .Left = 10
        .Width = 50
        .Height = 20
        .Caption = "确认"
    End With
    With Button2
        .Top = 70
        .Left = 70
        .Width = 50
        .Height = 20
        .Caption = "取消"
    End With
    
    ' 通过UserForms集合添加并显示实例
    VBA.UserForms.Add(myForm.Name).Show
End Sub

清理窗体的辅助函数

Sub CleanUpUserForm(formName As String)
    ' 销毁所有同名窗体实例
    Dim uf As UserForm
    For Each uf In UserForms
        If uf.Name = formName Then
            Unload uf
            Set uf = Nothing
        End If
    Next uf
    
    ' 删除VBProject中的窗体组件（忽略不存在的错误）
    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents.Remove _
        ThisWorkbook.VBProject.VBComponents(formName)
    On Error GoTo 0
End Sub

' 替代原RemoveUserForm的调用
Sub RemoveUserForm()
    CleanUpUserForm "Test"
End Sub

关键修改说明 #

• 用myForm对象操作控件：创建窗体后直接使用返回的对象变量，彻底避免名称冲突问题
• 前置清理步骤：每次创建新窗体前先清理旧实例和组件，确保环境干净
• 通过集合显示窗体：用UserForms.Add替代直接调用Test.Show，避免残留实例干扰

内容的提问来源于stack exchange，提问作者IvanSTV