excel - "VBProject.VBComponents"上的自动化错误 - VBA (Excel)

Question

我有这个代码:

Sub ChangeImportedBOQCodeName(importedName As String)
    ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = "BOQ_" & importedName
End Sub

那是从

Private Sub cmdOK_Click()

    Dim TargetName As String

    TargetName = cbxSheets.Text

    Set TargetSheet = TargetWB.Sheets(TargetName)
    TargetSheet.Copy After:=SourceWB.Sheets(SourceSheet.Index)

    ChangeImportedBOQCodeName ActiveSheet.CodeName

    ' Close the User Form
    Unload Me

    ' Inform User on Successful BOQ Import
    MsgBox "The selected BOQ was successfully imported to the Analysis", vbInformation, "Import Successful"

    ' Ask User to generate codes automatically
    Dim msgTxt As String
    msgTxt = "Generate codes in the imported BOQ, automatically ?" & vbNewLine & vbNewLine & _
             "(The proccess may take a while depending on the System specs and BOQ layout and size)"

    If MsgBox(msgTxt, vbYesNo, "Auto Code BOQ") = vbYes Then
        CheckImportedBOQ ActiveSheet
    Else
        MsgBox "Auto code genrate was aborted", vbInformation, "Aborted"
    End If

End Sub

但我收到此错误:

Run-time error '-2147417848 (80010108)' Automation error The object invoked has disconnected from its client

excel突然退出。

问题是这个错误以前从未发生过，并且代码工作得很好。

我也试过评论 ChangeImportedBOQCodeName ActiveSheet.CodeName这一行和代码有效。

Answer 1

在您的 Sub ChangeImportedBOQCodeName你正在通过ActiveSheet.CodeName ，所以你需要做的就是改变:

ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = "BOQ_" & importedName

至:

ActiveWorkbook.VBProject.VBComponents(importedName).Name = "BOQ_" & importedName