使用VBA对Excel中的VBProject.VBComponents进行刷新更改 [英] Flushing changes made to VBProject.VBComponents in Excel using VBA

问题描述

我在Excel中遇到了一些奇怪的怪癖，而在程序上删除模块，然后从文件中重新导出它们。基本上，我有一个名为VersionControl的模块，它应该将我的文件导出到预定义的文件夹，并根据需要重新导入。这是重新导入的代码（它的问题描述如下）：

I've been experiencing some strange quirks in Excel while programatically removing modules then reimporting them from files. Basically, I have a module named VersionControl that is supposed to export my files to a predefined folder, and reimport them on demand. This is the code for reimporting (the problem with it is described below):

Dim i As Integer
Dim ModuleName As String
Application.EnableEvents = False
With ThisWorkbook.VBProject
    For i = 1 To .VBComponents.Count
        If .VBComponents(i).CodeModule.CountOfLines > 0 Then
            ModuleName = .VBComponents(i).CodeModule.Name
            If ModuleName <> "VersionControl" Then
                If PathExists(VersionControlPath & "\" & ModuleName & ".bas") Then
                    Call .VBComponents.Remove(.VBComponents(ModuleName))
                    Call .VBComponents.Import(VersionControlPath & "\" & ModuleName & ".bas")
                Else
                    MsgBox VersionControlPath & "\" & ModuleName & ".bas" & " cannot be found. No operation will be attempted for that module."
                End If
            End If
        End If
    Next i
End With

运行这个之后，我注意到一些模块不再出现，有些模块有重复（例如mymodule和mymodule1）。在逐步完成代码之后，很明显，在 Remove 调用之后，一些模块仍然停留，而在项目中仍然会被重新导入。有时，这只会导致模块后缀为 1 ，但有时我同时拥有原始和副本。

After running this, I've noticed that some modules don't appear anymore, while some have duplicates (e.g. mymodule and mymodule1). While stepping through the code, it became obvious that some modules still linger after the Remove call, and they get to be reimported while still in the project. Sometimes, this only resulted having the module suffixed with 1, but sometimes I had both the original and the copy.

有没有办法将调用冲洗到删除和导入所以他们自己应用？我想在每个应用程序之后调用保存函数，如果Application对象中有一个函数，虽然导入过程中出现问题时可能导致损失。

Is there a way to flush the calls to Remove and Import so they apply themselves? I'm thinking to call a Save function after each, if there's one in the Application object, although this can cause losses if things go wrong during import.

想法？

编辑：更改标签同步至版本控制。

推荐答案

这是一个现场阵列，您正在添加和删除项目在迭代期间改变指数。尝试向后处理数组。这是我的解决方案，没有任何错误处理：

This is a live array, you are adding and removing items during iteration thereby changing the index numbers. Try processing the array backwards. Here is my solution without any error handling:

Private Const DIR_VERSIONING As String = "\\VERSION_CONTROL"
Private Const PROJ_NAME As String = "PROJECT_NAME"

Sub EnsureProjectFolder()
    ' Does this project directory exist
    If Len(Dir(DIR_VERSIONING & PROJ_NAME, vbDirectory)) = 0 Then
        ' Create it
        MkDir DIR_VERSIONING & PROJ_NAME
    End If
End Sub

Function ProjectFolder() As String
    ' Ensure the folder exists whenever we try to access it (can be deleted mid execution)
    EnsureProjectFolder
    ' Create the required full path
    ProjectFolder = DIR_VERSIONING & PROJ_NAME & "\"
End Function

Sub SaveCodeModules()

    'This code Exports all VBA modules
    Dim i%, sName$

    With ThisWorkbook.VBProject
        ' Iterate all code files and export accordingly
        For i% = 1 To .VBComponents.count
            ' Extract this component name
            sName$ = .VBComponents(i%).CodeModule.Name
            If .VBComponents(i%).Type = 1 Then
                ' Standard Module
                .VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
            ElseIf .VBComponents(i%).Type = 2 Then
                ' Class
                .VBComponents(i%).Export ProjectFolder & sName$ & ".cls"
            ElseIf .VBComponents(i%).Type = 3 Then
                ' Form
                .VBComponents(i%).Export ProjectFolder & sName$ & ".frm"
            ElseIf .VBComponents(i%).Type = 100 Then
                ' Document
                .VBComponents(i%).Export ProjectFolder & sName$ & ".bas"
            Else
                ' UNHANDLED/UNKNOWN COMPONENT TYPE
            End If
        Next i
    End With

End Sub

Sub ImportCodeModules()
    Dim i%, sName$

    With ThisWorkbook.VBProject
        ' Iterate all components and attempt to import their source from the network share
        ' Process backwords as we are working through a live array while removing/adding items
        For i% = .VBComponents.count To 1 Step -1
            ' Extract this component name
            sName$ = .VBComponents(i%).CodeModule.Name
            ' Do not change the source of this module which is currently running
            If sName$ <> "VersionControl" Then
                ' Import relevant source file if it exists
                If .VBComponents(i%).Type = 1 Then
                    ' Standard Module
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".bas"
                ElseIf .VBComponents(i%).Type = 2 Then
                    ' Class
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".cls"
                ElseIf .VBComponents(i%).Type = 3 Then
                    ' Form
                    .VBComponents.Remove .VBComponents(sName$)
                    .VBComponents.Import fileName:=ProjectFolder & sName$ & ".frm"
                ElseIf .VBComponents(i%).Type = 100 Then
                    ' Document
                    Dim TempVbComponent, FileContents$
                    ' Import the document. This will come in as a class with an increment suffix (1)
                    Set TempVbComponent = .VBComponents.Import(ProjectFolder & sName$ & ".bas")

                    ' Delete any lines of data in the document
                    If .VBComponents(i%).CodeModule.CountOfLines > 0 Then .VBComponents(i%).CodeModule.DeleteLines 1, .VBComponents(i%).CodeModule.CountOfLines

                    ' Does this file contain any source data?
                    If TempVbComponent.CodeModule.CountOfLines > 0 Then
                        ' Pull the lines into a string
                        FileContents$ = TempVbComponent.CodeModule.Lines(1, TempVbComponent.CodeModule.CountOfLines)
                        ' And copy them to the correct document
                        .VBComponents(i%).CodeModule.InsertLines 1, FileContents$
                    End If

                    ' Remove the temporary document class
                    .VBComponents.Remove TempVbComponent
                    Set TempVbComponent = Nothing

                Else
                    ' UNHANDLED/UNKNOWN COMPONENT TYPE
                End If
            End If
            Next i
        End With

End Sub

这篇关于使用VBA对Excel中的VBProject.VBComponents进行刷新更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！