excel - VBProject.VBComponents(wsTarget.CodeName).Name =代码每隔一次失败。为什么？

Question

我有一个小应用程序，其中包含大约20个可以完美运行的子程序...每隔一段时间。

我在子ImportData中第一次将代码名称添加到新创建的工作表中时失败。在以下行中：
ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Customers"

下面的代码包含三个可以重新创建问题的子。重要说明：我可以连续多次运行子ImportData，而不会出现任何问题，但是如果我连续两次调用子“ Sync（）”，它将在第二次尝试中失败，但是在第三次尝试中运行良好，依此类推（也许它不喜欢奇数。）

任何关于为什么发生这种情况的想法将不胜感激。

FYT：我正在Mac的Excel中运行此代码

Public LastRow As Long
Private wks As Worksheet

Sub Sync()
     Call ImportData
     Call SyncBoth
End Sub

Public Sub ImportData()
'++++++++++++++++++++++++++++++++++++++++++++++++++++++
'+++++ 1. ImportData will allow user to select file to import data from
'+++++ 2. Copy both the Customers and Vendors data to their respective sheets
'++++++++++++++++++++++++++++++++++++++++++++++++++++++
     Dim wsSource As Worksheet
     Dim wsTarget As Worksheet
     Dim LastRow As Long
     Dim MaxDate As Date
     Dim ShCount As Integer
     Dim SourceFile As String
          SourceFile = "/Users/phild/Documents/RTPro/Customer and Vendor Raw Sync.xlsm"
     Dim SourceWb As Workbook
          Set SourceWb = Workbooks.Open(SourceFile)
     Dim TargetWb As Workbook
          Set TargetWb = ThisWorkbook
     Dim sheet As Worksheet

     For ShCount = 1 To 2
          Select Case ShCount
          Case 1
               Set wsSource = SourceWb.Worksheets("Sheet1")                 'Set Worksheet to copy data from
               ThisWorkbook.Sheets("Customers").Delete                           'Delete old Customer worksheet in this worksheet

               Set sheet = ThisWorkbook.Sheets.Add                                'Create New Customer woeksheet in this woekbook
               sheet.Name = "Customers"                                              'Name new Customer worksheet
               Set wsTarget = ThisWorkbook.Worksheets("Customers")         'Set Customers ws as the target ws
               Debug.Assert ThisWorkbook.VBProject.Name <> vbNullString              '<--Force the VBE to exist. Don't pollute the Immediate window
               ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Customers"       'Give Customers a Code name
                              'THE LINE OF CODE ABOVE RESULTS IN A Runtime error '32813"
                              ' Method 'Name' of object '+VBComponent' failed
                              ' EVERY OTHER TIME I RUN THE SUB Sync()

        Case 2
               Set wsSource = SourceWb.Worksheets("Sheet3")                'Set Worksheet to copy data from
               ThisWorkbook.Sheets("Vendors").Delete                            'Delete old Vendors worksheet in this worksheet

               Set sheet = ThisWorkbook.Sheets.Add                               'Create New Vendor worksheet in this woekbook
               sheet.Name = "Vendors"                                                'Name new Vendor worksheet
               Set wsTarget = ThisWorkbook.Worksheets("Vendors")           'Set Customers ws as the target ws
               Debug.Assert ThisWorkbook.VBProject.Name <> vbNullString          '<--Force the VBE to exist. Don't pollute the Immediate window  '
               ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "Vendors"     'Give Vendors a Code name
        End Select

        Call CleanTarget(wsTarget)

          LastRow = Find_LastRow(wsSource)
          wsSource.Range("A1:Z" & LastRow).Copy Destination:=wsTarget.Range("A1")

          Next ShCount

     SourceWb.Close
End Sub

Sub SyncBoth()
     Dim ShCount As Integer

     For ShCount = 1 To 2
          Select Case ShCount
               Case 1
                    Set wks = Customers                 'Work in sheet("Customers")
                    LastRow = Find_LastRow(wks)      'Last row of "Customers"

               Case 2
                    Set wks = Vendors                     'Work in sheet("Vendors")
                    LastRow = Find_LastRow(wks)       'Last row of "Vendors"
          End Select

          Debug.Print wks.Name

     Next ShCount

     'Normally I have about 10 subs here that are called sequentially. But this is enough the cause the errorw

End Sub```

Answer 1

您正在运行时修改宿主VBA项目-Sheet1的代码名称标识符是一个编译时的项目全局范围对象：即使未在任何地方使用它，也有合法的机会更改它需要重新编译项目。

因此，代码可以很好地运行，直到通过重命名全局对象来解决问题为止。由于编译后的代码与项目中的实际VBComponent匹配，因此下一次运行现在运行良好。

考虑将宏放在一个单独的VBA项目中，该宏提示在哪个启用宏的工作簿中重命名组件：因为该VBA项目不会是已编译和运行的VBA代码，所以它应该“正常工作”。