Flushing changes made to VBProject.VBComponents in Excel using VBA

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 IfEnd SubFunction 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 FunctionSub 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 WithEnd SubSub 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 WithEnd Sub

OP here... I managed to work around this weird issue, but I haven't found a true solution. Here's what I did.

1. My first attempt after posting the question was this (spoiler: it almost worked):

   Keep removing separate from importing, but in the same procedure. This means that I had 3 loops - one to store a list of the module names (as plain strings), another to remove the modules, and another to import the modules from files (based on the names that were stored in the aforementioned list).

   The problem: some modules were still in the project when the removal loop ended. Why? I cannot explain. I'll mark this as stupid problem no. 1. I then tried placing the Remove call for every module inside a loop that kept trying to remove that single module until it couldn't find it in the project. This got stuck in an infinite loop for a certain module - I can't tell what's so special about that particular one.

   I eventually figured out that the modules were only truly removed after Excel finds some time to clear its thoughts. This didn't work with Application.Wait(). The currently running VBA code actually needed to end for this to happen. Weird.

2. Second work-around attempt (spoiler: again, it almost worked):

   To give Excel the required time to breathe after removals, I placed the removing loop inside a button click handler (without the "call Remove until it's gone" loop), and the importing loop in the click handler of another button. Of course, I needed the list of module names, so I made it a global array of strings. It was created in the click handler, before the removal loop, and it was supposed to be accessed by the importing loop. Should have worked, right?

   The problem: The aforementioned string array was empty when the importing loop started (inside the other click handler). It was definitely there when the removal loop ended - I printed it with Debug.Print. I guess it got de-allocated by the removals (??). This would be stupid problem no. 2. Without the string array containing the module names, the importing loop did nothing, so this work-around failed.

3. Final, functional workaround. This one works.

   I took Work-around number 2 and, instead of storing the module names in a string array, I stored them in a row of an auxiliary sheet (I called this sheet "Devel").

This was it. If anyone can explain stupid problem no. 1 and stupid problem no. 2, I beg you, do so. They're probably not that stupid - I'm still at the beginning with VBA, but I have solid knowledge of programming in other (sane and modern) languages.

I could add the code to illustrate stupid problem no. 2, but this answer is already long. If what I did was not clear, I will place it here.

To avoid duplicate when importing, I modified the script with following strategy :

• Rename existing module
• Import module
• Delete renamed module

I don't have anymore duplicate during import.

Sub SaveCodeModules()'This code Exports all VBA modulesDim i As Integer, name As StringWith ThisWorkbook.VBProjectFor i = .VBComponents.Count To 1 Step -1    name = .VBComponents(i).CodeModule.name    If .VBComponents(i).Type = 1 Then        ' Standard Module        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".module"    ElseIf .VBComponents(i).Type = 2 Then        ' Class        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".classe"    ElseIf .VBComponents(i).Type = 3 Then        ' Form        .VBComponents(i).Export Application.ThisWorkbook.Path & "\trunk\" & name & ".form"    Else        ' DO NOTHING    End IfNext iEnd WithEnd Sub

Sub ImportCodeModules()Dim i As IntegerDim delname As StringDim modulename As StringWith ThisWorkbook.VBProjectFor i = .VBComponents.Count To 1 Step -1    modulename = .VBComponents(i).CodeModule.name    If modulename <> "VersionControl" Then        delname = modulename & "_to_delete"        If .VBComponents(i).Type = 1 Then            ' Standard Module            .VBComponents(modulename).name = delname            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".module"            .VBComponents.Remove .VBComponents(delname)        ElseIf .VBComponents(i).Type = 2 Then            ' Class            .VBComponents(modulename).name = delname            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".classe"            .VBComponents.Remove .VBComponents(delname)        ElseIf .VBComponents(i).Type = 3 Then            ' Form            .VBComponents.Remove .VBComponents(modulename)            .VBComponents.Import Application.ThisWorkbook.Path & "\trunk\" & modulename & ".form"        Else            ' DO NOTHING        End If    End IfNext iEnd WithEnd Sub

Code to be pasted in a new module "VersionControl"