0 votes
in VBA by Beginner (2 points)
Hello,

I designed a complex Excel workbook with several userforms, and more than 20,000 lines of code.
This workbook is a template for my colleagues, so there are many existing copies of this workbook.

If I made design changes in the template it takes a long time to export/import this change to all copies, so I'm looking for a solution to update all related copies by a VBA macro.

Can anybody help me to find such a macro?

Greetings
Philip

4 Answers

+1 vote
by Skilled (376 points)

Why not put all VBA and user-forms in an Add-In? See Create an Excel Add-In. You must remove duplicated VBA and user-forms from existing templates and workbooks, then use VBE Tools > References to include the Add-In instead. If the Add-In is not located on a network, you will need to distribute the Add-In to users after each update but they only need to replace the old Add-In file; they do not need to change their workbooks.

0 votes
by Expert (798 points)
Have a look at this thread

https://ask.wellsr.com/817
by Beginner (2 points)
Thanks for this quick reply, but this is not what I want, because I only want to update the VBA code of all obects and all changes of the userforms, all user input data in the worksheets of each workbook should not be changed.
by Expert (798 points)
May be as an idea to have a code that exports all the modules to files .. Then to have another macro that the users run that deletes all the codes and imports the VBA modules that have been exported earlier.
by Beginner (2 points)
yes, this is the way what will solve my request, but I didn't know how I can write the needed VBA macros.
0 votes
by Beginner (15 points)
edited by

You have many solutions, i have this one in one of my VBA projects:

Example for Modules :

put in userform

Private Sub CommandButton23_Click()
'******STEP.0******'
If Me.TextBox17 = "" Then
    MsgBox "Access denied: " & vbCrLf & " No data entered in module name field ... " & vbCrLf & vbCrLf & " All directives have been canceled ...", vbCritical, "Atention"
'   ERROR STEP.0
    Exit Sub
End If

Dim xQuestion

'******confirm directives...
xQuestion = MsgBox("Warning: Are you sure you want to replace the module named " & Me.TextBox17.Text & "?" & vbCrLf & vbCrLf & "Do you trust the codes in this module?" & vbCrLf & vbCrLf & "All data from the current module will be lost and cannot be recovered ...", vbYesNo + vbQuestion, "Atention")
If xQuestion = vbNo Then
' cancel is true...
    Exit Sub
End If

'******STEP.1******'
'''''' to check if VBA project is protected ... cancel continuation of codes ...
Dim wkbTarget As Excel.Workbook
Dim txtTargetWorkbook As String

txtTargetWorkbook = ActiveWorkbook.Name
Set wkbTarget = Application.Workbooks(txtTargetWorkbook)

If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "Access is denied:" & vbCrLf & "The VBA project is protected, cannot be continued ..." & vbCrLf & "All directives have been canceled ...", vbCritical, "Atention"
'   ERROR STEP.1
    Exit Sub
End If

'******STEP.2******'
''''''to find out how many files are in the source folder (update) ...
Call maxfiles
Call maxfiles_2

If Me.TextBox12.Text = "2" Then
    MsgBox "Access is denied:" & vbCrLf & "Only one (1) file is allowed, and in the destination folder are: " & Me.TextBox21.Text & " files ..." & vbCrLf & vbCrLf & "All directives have been canceled ... ", vbCritical, "Atention"
'   ERROR STEP.2
    Exit Sub
End If

'******STEP.3******'
'''''' to know the link and file names in PC folder ... (excel) ... (MACRO) ...
Call ListAllFilesInAllFolders_602

'******STEP.4******'
Call verificarFilePC
If Me.TextBox24.Text = "Error" Then
    MsgBox "The file name in the source folder does not match..." & vbCrLf & vbCrLf & "All directives have been canceled ...", vbExclamation, "Atention"
'   ERROR STEP.4
    Exit Sub
End If

'******STEP.5******'
'''''' to see if module exists in vbproject ... (1 of 3) ...
Call modul_exist_program

If Me.TextBox25.Text = "Error" Then
    MsgBox "Access denied:" & vbCrLf & "Name entered does not exist as a program module name ..." & vbCrLf & vbCrLf & "All directives have been canceled ...", vbCritical, "Atention"
'   ERROR STEP.5
    Exit Sub
End If

'******STEP.7******'
''''''to delete / remove vbproject module ... (MACRO) ...
Call DeleteModule_602

'******STEP.8******'
''''''to import pc folder module ... (MACRO) ...
Call ImportModules_602

MsgBox "The module indicated was successfully replaced ...", vbInformation, "MyProgram"

End Sub

Sub verificarFilePC()
'''''' to check with the database if module name is there ...
userformsNomes.Visible = xlSheetVisible
userformsNomes.Select
userformsNomes.Unprotect "0000" ' your code...

Dim Coluna_filesdados As Long
Dim linha As Long

On Error Resume Next
Coluna_filesdados = Sheets("Files2").Range("B:B").CurrentRegion.Rows.Count

For linha = 1 To Coluna_filesdados

    If Sheets("Files2").Cells(linha, 2) = Me.TextBox23.Text Then
            Me.TextBox24.Text = "OK..."
'            MsgBox "The name you entered was found in the database ...", vbInformation
            Exit Sub
        Exit For
    End If
Next linha

Me.TextBox24.Text = "Error"
'MsgBox "The name you entered is not in the database ...", vbExclamation

End Sub

Sub maxfiles()
'''''' to know how many files are in the update folder...(textbox)...
Dim StrFile As String
Dim objFSO, destRow As Long
Dim mainFolder, mySubFolder

Set objFSO = CreateObject("Scripting.FileSystemObject")
mFolder = "C:\Users\migue\OneDrive\Área de Trabalho\Condominio3\Actualizações\UpdateVBAProjectFiles"

Set mainFolder = objFSO.GetFolder(mFolder)
StrFile = Dir(mFolder & "\*.*")

Do While Len(StrFile) > 0
    Count = Count + 1
    StrFile = Dir
Loop

Me.TextBox21.Text = Count

End Sub

Sub maxfiles_2()
Dim maximtxt As Double

On Error Resume Next
maximtxt = Me.TextBox21.Value

On Error Resume Next

If maximtxt > 1 Then
    Me.TextBox22.Text = "2"
    Else
    Me.TextBox22.Text = "1"
End If

End Sub

Sub modul_exist_program()
'''''' to see if module exists in vbproject ... (2 of 3) ...
Dim modulname As String

modulname = Me.TextBox17.Text

    Debug.Print modul_exist2(ThisWorkbook, modulname)
'    Debug.Print modul_exist(ActiveWorkbook, modulname)

End Sub

Public Function modul_exist2(wb As Workbook, Name As String) As Boolean
'''''' to see if module exists in vbproject ... (3 of 3) ...
Dim module As Object

On Error Resume Next
Set module = wb.VBProject.VBComponents(Name).CodeModule
On Error GoTo 0

modul_exist2 = Not module Is Nothing

If Not modul_exist2 Then
    Me.TextBox25.Text = "Error"
    Else
    Me.TextBox25.Text = "OK"
End If

End Function

in codes Module

Sub ListAllFilesInAllFolders_602() ' excel...
''''''to get separate link & name of files in PC folder ...
Dim MyPath As String, MyFolderName As String, MyFileName As String
Dim i As Integer, F As Boolean
Dim objShell As Object, objFolder As Object, AllFolders As Object, AllFiles As Object
Dim MySheet As Worksheet

On Error Resume Next

''''''to select source folder...
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "", 0, 0)
If Not objFolder Is Nothing Then
    MyPath = objFolder.self.path & "\"
    Else
    Exit Sub
       'MyPath = "C:\...\"
End If

Set objFolder = Nothing
Set objShell = Nothing

'''''' for folder list...
Set AllFolders = CreateObject("Scripting.Dictionary")
Set AllFiles = CreateObject("Scripting.Dictionary")

AllFolders.Add (MyPath), ""
    i = 0
    Do While i < AllFolders.Count
        Key = AllFolders.Keys
        MyFolderName = Dir(Key(i), vbDirectory)
        Do While MyFolderName <> ""
            If MyFolderName <> "." And MyFolderName <> ".." Then
                If (GetAttr(Key(i) & MyFolderName) And vbDirectory) = vbDirectory Then
                    AllFolders.Add (Key(i) & MyFolderName & "\"), ""
                End If
            End If
            MyFolderName = Dir
        Loop
        i = i + 1
    Loop

''''''for file list...
For Each Key In AllFolders.Keys
''''''for files...
    MyFileName = Dir(Key & "*.*")
'''''' for PDF files only...
    'MyFileName = Dir(Key & "*.PDF")    'only PDF files
    Do While MyFileName <> ""
        AllFiles.Add (MyFileName), Key
        MyFileName = Dir
    Loop
Next

'''''' for excel file list in this workbook...
'''''' will go through looking for excel sheet named files2..
''''''if it does delete content ... if it does not exist, create an excel sheet named files2...
For Each MySheet In ThisWorkbook.Worksheets
    If MySheet.Name = "Files2" Then
        Sheets("Files2").Cells.Delete
        F = True
        Exit For
        Else
        F = False
    End If
Next
If Not F Then Sheets.Add.Name = "Files2"

'Sheets("Files").[A1].Resize(AllFolders.Count, 1) = WorksheetFunction.Transpose(AllFolders.keys)
Sheets("Files2").[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.items)
Sheets("Files2").[B1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Keys)

Set AllFolders = Nothing
Set AllFiles = Nothing

End Sub

Sub DeleteModule_602()
''''''to check if VBA project is protected ... cancel continuation of codes ...
Dim wkbTarget As Excel.Workbook
Dim txtTargetWorkbook As String

txtTargetWorkbook = ActiveWorkbook.Name
Set wkbTarget = Application.Workbooks(txtTargetWorkbook)

If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "Access is denied:" & vbCrLf & "The VBA project is protected, cannot be continued ..." & vbCrLf & "All directives have been canceled ...", vbCritical, "Atention"
    Exit Sub
End If

'''''' to delete vbproject module...
Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent
Dim modulname As String

modulname = UserForm_actualizaçoes.TextBox17.Text
On Error GoTo nãoexiste:

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(modulname)
VBProj.VBComponents.Remove VBComp

'MsgBox "The selected Module has been removed and deleted from this program ...", vbInformation

Exit Sub

nãoexiste:
'    MsgBox "not found, option canceled", vbCritical
    Exit Sub

End Sub

Public Sub ImportModules_602()
''''''to import all components in pc folder ... (1 of 2) ...
Dim wkbTarget As Excel.Workbook
Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim txtTargetWorkbook As String
Dim txtImportPath As String
Dim szFileName As String
Dim cmpComponents As VBIDE.VBComponents

'''''' so if it is not this workbook give error ...
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
    MsgBox "Access is denied:" & vbCrLf & "The name of this VBA Project does not match the name of the VBA Project, where the selected file will be imported ...", vbCritical, "Atention"
    Exit Sub
End If

''''''to folder path in Pc with the modules ... (made function for...) ...
If FolderWithVBAProjectFiles = "Error" Then
    MsgBox "Access is denied:" & vbCrLf & "Source folder does not exist ..." & vbCrLf & "Module import operation has been canceled ...", vbCritical, "Atention"
    Exit Sub
End If

txtTargetWorkbook = ActiveWorkbook.Name
Set wkbTarget = Application.Workbooks(txtTargetWorkbook)

'''''' to check if VBA project is protected...
If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "Access is denied:" & vbCrLf & "The VBA project is protected, cannot be continued ..." & vbCrLf & "All directives have been canceled ...", vbCritical, "Atention"
    Exit Sub
End If

txtImportPath = FolderWithVBAProjectFiles & "\"

    Set objFSO = New Scripting.FileSystemObject

'''''' to check if the folder is empty or not...
'    If objFSO.GetFolder(txtImportPath).Files.Count = 0 Then
'       MsgBox "Error: There are no files to import ...", vbCritical, "Atention"
'       Exit Sub
'    End If

Set cmpComponents = wkbTarget.VBProject.VBComponents

For Each objFile In objFSO.GetFolder(txtImportPath).Files

If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
    (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
    (objFSO.GetExtensionName(objFile.Name) = "bas") Then
    cmpComponents.Import objFile.path
End If

Next objFile

'MsgBox "Import of selected Module successfully completed ...", vbInformation

End Sub

Function FolderWithVBAProjectFiles() As String
'''''' to import all components into pc folder ... (2 of 2) ...
'''''' to check if there is a folder where the files to import ...
Dim WshShell As Object
Dim FSO As Object
Dim SpecialPath As String

Set WshShell = CreateObject("WScript.Shell")
Set FSO = CreateObject("scripting.filesystemobject")

SpecialPath = "C:\Users\migue\OneDrive\Área de Trabalho\Condominio3\Actualizações"

If Right(SpecialPath, 1) <> "\" Then
    SpecialPath = SpecialPath & "\"
End If

If FSO.FolderExists(SpecialPath & "UpdateVBAProjectFiles") = False Then
    On Error Resume Next
    MkDir SpecialPath & "UpdateVBAProjectFiles"
    On Error GoTo 0
End If

If FSO.FolderExists(SpecialPath & "UpdateVBAProjectFiles") = True Then
    FolderWithVBAProjectFiles = SpecialPath & "UpdateVBAProjectFiles"
    Else
    FolderWithVBAProjectFiles = "Error"
End If

End Function

There are several ways to write the code, for Userform or excel files I have other ... ;)  

by Beginner (2 points)
thanks for this example code, I'll test it tomorrow.
by Beginner (15 points)
edited by
_hello, you can adapt the code to your needs, I made a copy and past of a part that had and without changes
_if your project is not protected you can remove this part:

''''''to check if VBA project is protected ... cancel continuation of codes ...
Dim wkbTarget As Excel.Workbook
Dim txtTargetWorkbook As String

txtTargetWorkbook = ActiveWorkbook.Name
Set wkbTarget = Application.Workbooks(txtTargetWorkbook)

If wkbTarget.VBProject.Protection = 1 Then
    MsgBox "Access is denied:" & vbCrLf & "The VBA project is protected, cannot be continued ..." & vbCrLf & "All directives have been canceled ...", vbCritical, "Atention"
    Exit Sub
End If
 
_I have this part repeated because I use these lines of code combined with others and other situations, but just once is enough
_you can also remove this part:
'******STEP.2******' 
''''''to find out how many files are in the source folder (update) ... Call maxfiles 
Call maxfiles_2
 
_it only serves to execute one and only one update at a time and only for modules
 
:)

 

by Beginner (2 points)
thanks again for this update, but I can't adapt this code for my case, so I'll contact a VBA specialist who can do this for me.
0 votes
by Skilled (330 points)

Hi Philip,

Here is another way to look at your challenge:

1. Create an updated version of your workbook by changing the UserForms and/or the VBA code that you maintain.

2. Omit all worksheets that have colleagues' user input data

3. Write a small macro that imports the colleagues' user input data from the older version

Pros and Cons

One benefit is eliminating the need for code import routines, which usually requires colleagues to change Excel settings to "trust" access to the VBA Project Object Model.

Another benefit is the speed of importing data, vs. the speed of deleting and then importing code modules.

The drawbacks vary, depending on your user input data:

  • Do your colleagues have hundreds of worksheets?
  • Is the input data very large?
  • Would importing the data "break" existing connections?
The last drawback is the most frustrating, because calculations would refer to the old workbook. Unexpected results or outright errors could make this method a non-starter.
 
One other drawback exists if your UserForms are embedded in worksheets (ActiveX). If the user inputs reside on the same worksheet as such embedded forms, this method fails.
 
Commentary
The underlying problem appears to be that you don't separate the data layer from the presentation layer. I blame Excel for this tight integration. (This integration exists in their other products, too, even Access, which has all the pieces needed for separating database coontent from database manipulation.)
 
By definition, an Excel template, whether you use the XLTM file format or not, is supposed to consist solely of a presentation layer and a processing layer. The user should be free to add, update and delete data as required.
 
Cheers,
 
Mitch
 
by Beginner (2 points)
Hi Mitch,

thanks for this alternate idea, but to restore all user data is not so easy, in the workshhets we have signature buttons wicht are disabled/enabled (depending on signature is given) and activeX option-buttons wich are selected or not and inserted small icon with hyperlinks. So this ist to much and complicated to transfer all this details to the updated workbook (there are over 30 workbooks).

Thanks
Philip
by Skilled (330 points)
Hi Philip,

Thanks for sharing. Yes, ActiveX would make the method problematic. Good luck with the VBA specialist!

Cheers,

Mitch

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...