在多个文件中替换VBA代码的最佳方法

Question

我曾经这样使用过:

Dim vbaComponent As Variant
For Each vbaComponent In inputWorkbook.VBProject.VBComponents
    vbaComponent.CodeModule.DeleteLines 1, vbaComponent.CodeModule.CountOfLines
    vbaComponent.CodeModule.AddFromFile importComponentFileName
Next vbaComponent

这在一段时间内工作得很好，但现在当保存Excel文件时它崩溃了。我猜文件太大了还是怎么的。

有更好的方法吗?

编辑:

问题似乎是frm和cls文件。替换bas文件效果很好

EDIT2:

在某些机器上，甚至bas文件也不工作。

EDIT3(我目前的解决方案):所以我目前的解决方案是简单地手工做一次，记录所有的鼠标和键盘输入，然后一遍又一遍地重放。

如果没有合适的解决方案，我计划为此创建一个AutoIt脚本

Answer 1

你将不得不导出/导入组件，因为不是所有的行都暴露给CodeModule，下面是示例

Private Sub exportImportComponent(Project1 As VBIDE.VBProject, Project2 As VBIDE.VBProject)
    Dim i As Long, sFileName As String
    With Project1.VBComponents
        For i = 1 To .Count
            sFileName = "C:Temp" & .Item(i).Name
            Select Case .Item(i).Type
                Case vbext_ct_ClassModule
                    .Item(i).Export sFileName & ".cls"
                    Project2.VBComponents.Import sFileName & ".cls"
                Case vbext_ct_StdModule
                    .Item(i).Export sFileName & ".bas"
                    Project2.VBComponents.Import sFileName & ".bas"
                Case vbext_ct_MSForm
                    .Item(i).Export sFileName & ".frm"
                    Project2.VBComponents.Import sFileName & ".frm"
                Case Else
                    Debug.Print "Different Type"
            End Select
        Next
    End With
End Sub

Answer 2

我可以向大家保证，因为我已经在这个主题上工作了好几年了(我放弃了几次)。当以编程方式修改代码时，无论是基于行还是-我喜欢的方法是1。重命名,2。删除重命名的，3。从导出文件重新导入，工作簿保存将崩溃，将显示Excel关闭工作簿。事实上，我的方法大多数时候都是有效的，但由于它是不可预测的，我学会了接受它。在大多数情况下，代码更改已经成功完成。所以我只是重新打开工作簿然后继续。

我使用的代码。我只是删除了所有的执行跟踪和执行日志代码行，但有些行可能仍然看起来有点神秘:

    With rn_wb.VBProject
    
    '~~ Find a free/unused temporary name and re-name the outdated component
    If mComp.Exists(wb:=rn_wb, comp_name:=rn_comp_name) Then
        sTempName = mComp.TempName(tn_wb:=rn_wb, tn_comp_name:=rn_comp_name)
        '~~ Rename the component when it already exists
        .VBComponents(rn_comp_name).Name = sTempName
        .VBComponents.Remove .VBComponents(sTempName) ' will not take place until process has ended!
    End If
   
    '~~ (Re-)import the component
    .VBComponents.Import rn_raw_exp_file_full_name
    
    '~~ Export the re-newed Used Common Component
    Set Comp = New clsComp ' class module provides the export files full name
    With Comp
        Set Comp.Wrkbk = rn_wb
        .CompName = rn_comp_name
    End With
    .VBComponents(rn_comp_name).Export Comp.ExpFileFullName
    
    '~~ When Excel closes the Workbook with the subsequent Workbook save it may be re-opened
    '~~ and the update process will continue with the next outdated Used Common Component.
    '~~ The (irregular) Workbook close however may leave the renamed components un-removed.
    '~~ When the Workbook is opened again these renamed component may cause duplicate declarations.
    '~~ To prevent this the code in the renamed component is dleted.
    ' EliminateCodeInRenamedComponent sTempName ' this had made it much less "reliablele" so I uncommented it
    
    SaveWbk rn_wb ' This "crahes" every now an then though I've tried a lot
End With

---

Private Sub SaveWbk(ByRef rs_wb As Workbook)
Application.EnableEvents = False
DoEvents ' no idea whether this helps. coded in desparation. at least it doesn't harm 
rs_wb.Save
DoEvents ' same as above, not executed when Excel crashes
Application.EnableEvents = True

结束子