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

Question

我有一个包含大约 20 个子程序的小应用程序，每次都能完美运行。

第一次在子 ImportData 中向新创建的工作表添加代码名称时，它失败了。在下面一行:ThisWorkbook.VBProject.VBComponents(wsTarget.CodeName).Name = "客户"

下面的代码包含三个可以重现问题的子代码。重要提示:我可以连续运行子 ImportData 多次，不会出现任何问题，但如果我连续两次调用子“Sync()”，第二次尝试会失败，但第三次尝试会正常工作，依此类推(也许它不喜欢奇数..)

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

FYT:我正在 Excel for Mac 中运行此代码

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 代码，所以它应该“正常工作”。