Updating VBA code without replacing the entire workbook.

Hi there,

See if the following approach gets you moving in the right direction - it requires a Reference to be set to "Microsoft Visual Basic for Applications Extensibility 5.3".

Use the following code in your master workbook to export all of its VBA code to a shared directory (e.g. H:\VBA Modules):

Sub ExportModules()

    Const sFOLDER_PATH  As String = "H:\VBA Modules"

    Dim sExtension      As String
    Dim sFileName       As String
    Dim sFullName       As String
    Dim cmp             As VBComponent
    Dim vbp             As VBProject

    Set vbp = ThisWorkbook.VBProject

    For Each cmp In vbp.VBComponents

        If cmp.Type <> vbext_ct_Document Then

            sExtension = vbNullString

            Select Case cmp.Type

                   Case vbext_ct_ClassModule

                        sExtension = ".cls"

                   Case vbext_ct_MSForm

                        sExtension = ".frm"

                   Case vbext_ct_StdModule

                        sExtension = ".bas"

            End Select

            If sExtension <> vbNullString Then
                sFullName = sFOLDER_PATH & "\" & cmp.Name & sExtension
            End If

        End If

    Next cmp

End Sub

Create a VBA CodeModule named M99_ImportVBA in each of the User workbooks whose VBA you wish to update regularly, and insert the following code into it:

Option Private Module
Option Explicit


'=========================================================================================
'=========================================================================================


Public Sub UpdateVbaComponents()

    Call DeleteModules

    Call ImportModules

End Sub


'=========================================================================================
'=========================================================================================


Private Sub DeleteModules()

    Const sIMPORT_VBA   As String = "M99_ImportVBA"

    Dim cmp             As VBComponent
    Dim vbp             As VBProject

    Set vbp = ThisWorkbook.VBProject

    For Each cmp In vbp.VBComponents

        If cmp.Name <> sIMPORT_VBA Then

            If cmp.Type <> vbext_ct_Document Then
                vbp.VBComponents.Remove VBComponent:=cmp
            End If

        End If

    Next cmp

End Sub


'=========================================================================================
'=========================================================================================


Private Sub ImportModules()

    Const sFOLDER_PATH  As String = "H:\VBA Modules"

    Dim sExtension      As String
    Dim objFolder       As Object
    Dim objFile         As Object
    Dim objFSO          As Object
    Dim cmp             As VBComponents

    Set cmp = ThisWorkbook.VBProject.VBComponents

    Set objFSO = CreateObject(Class:="Scripting.FileSystemObject")

    Set objFolder = objFSO.GetFolder(sFOLDER_PATH)

    If objFolder.Files.Count > 0 Then

        For Each objFile In objFolder.Files

            sExtension = objFSO.GetExtensionName(objFile.Name)

            If sExtension = "cls" Or _
               sExtension = "frm" Or _
               sExtension = "bas" Then

                cmp.Import Filename:=objFile.Path

            End If

        Next objFile

    End If

End Sub

Finally, insert the following code into the "ThisWorkbook" VBA CodeModule:

Option Explicit


Private Sub Workbook_Open()

    Call UpdateVbaComponents

End Sub

Using the above approach means that your updated VBA CodeModules need to be exported only to the shared folder. Each time a User workbook is opened, all of its VBA CodeModules will be deleted, and those from the shared folder will be imported to replace them.


Hope this helps - please let me know how you get on.

Regards,

Greg M