Flushing changes made to VBProject.VBComponents in Excel using VBA

Question

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

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.

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.

Ideas?

Edit: changed tag synchronization to version-control.

Answer 1

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

Answer 2

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 modules
Dim i As Integer, name As String

With ThisWorkbook.VBProject
For 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 If
Next i
End With

End Sub

---

Sub ImportCodeModules()

Dim i As Integer
Dim delname As String
Dim modulename As String

With ThisWorkbook.VBProject
For 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 If
Next i

End With

End Sub

---

Code to be pasted in a new module "VersionControl"

Answer 3

I've been struggling with this issue for days now. I built a crude version control system similar to this, though not using arrays. The version control module is imported on Workbook_Open and then the startup procedure is called to import all of the modules listed in the version control module. Everything works great except Excel started creating duplicate version control modules because it would import the new module before the deletion of the existing one finished. I worked around that by appending Delete to the previous module. The problem then being that there were still two procedures with the same name. Chip Pearson has some code for deleting a procedure programmatically, so I deleted the startup code from the older version control module. Still, I ran into an issue where the procedure had not been deleted by the time the startup procedure was called. I finally found a solution on another stack overflow thread that is so simple it makes me want to put my head through a wall. All I had to do was change the way I call my startup procedure by using

Application.OnTime Now + TimeValue("00:00:01"), "StartUp"    

Everything works perfectly now. Though, I will probably go back and delete out the now redundant renaming of the module and deleting the second procedure and see if this alone solves my original problem. Here is the other thread with the solution...

Source control of Excel VBA code modules

Answer 4

Not sure if it helps somebody ... In an xlsm container (if you open it as a zip-file) you can find a file called like vbaproject. You can overwrite this in order to "update" the whole content of the VBProject (all standardmodules, classmodules, userforms, table-modules and thisworkbook-module). With this aproach you can create the "new" code etc. in a copy of your workbook and finally overwrite the vbproject file in the destination workbook. This maybe avoid the need to export / import modules and suffering of duplicates. (Sorry for posting no code due to lack of time)

Answer 5

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.

Answer 6

The rename, import and delete workaround didn't work in my case. It seems (but this is pure speculation) that Excel might save the compiled objects in its .XLMS file, and when this file is re-opened these objects are reloaded in memory before the ThisWorkbook_open function occurs. And this leads the renaming (or removal) of certain modules to fail or be delayed (even when trying to force it with the DoEvents call). The only workaround I found is to use the .XLS binary format. For some obscure reason (I suspect the compiled objects are not bundled in the file), it works for me.

You have to know that you won't be able to re-import any module being/having been used or referenced at the time your import code runs (renaming will fail with error 32813/removal of the module will be delayed until after you will try to import, adding annoying '1's at the end of your module names). But for any other module, it should work.

If all of your source code needs to be managed, a better solution would be to "build" your workbook from scratch using some script or tool, or switch to a better suited programming language (i.e. one that doesn't live inside an Office suite software ;) I haven't tried it but you could look here: Source control of Excel VBA code modules.

Answer 7

I've spent quite some time on this issue. The point is that any VBComponent deleted is in fact not deleted before the process which did it has ended. Because rename is done immediately this is the method of choice to get the new imported component out of the way.

By the way: For the versioning I use Github which is easy to use and just perfect, specifically when only changed components are exported - what I do. Those interested may have a look at the public Github repo Excel-VB-Components-Management-Services or at the related post Programmatically updating or synchronizing VBA code of Excel VB-Project Components

Answer 8

Another situation related to the title of this question:

I was adding components in a loop (from python):

for file in files_to_import:
    project.VBComponents.Import(file)

This sometimes corrupted the workbook, sometimes did not. It appears something as simple as this:

for file in files_to_import:
    project.VBComponents.Import(file)
    print(project.VBComponents.Count)

helps "flush" the vbproject and fix the issue