Hi,
I am designing a VBA tool for some number of people. In order to avoid re-distributing the file to everyone when a mistake or bug is corrected, I decided to add a module called, updateCode. This will basically remove all current modules (except updateCode ofcourse), and re-import them from shared drive with a click of a button (which everyone can do). See a working code below FYI.
'compiled from snippets of codes from internet
Public Sub updateCode_buttonClick()
'When you used the Extensibility code, the VBA Editor window will flicker. This can be reduced with the code:
' Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False
On Error GoTo Code_Error
Dim Path As String
Dim VBComp As VBIDE.VBComponent
Path = filePath
'delete all modules except this one
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule And VBComp.Name <> "UpdateCode" Then
Application.VBE.ActiveVBProject.VBComponents.Remove VBComp
End If
Next VBComp
'import all modules from specified folder
Dim basFile As Variant
basFile = Dir(Path & "*.bas")
Do While basFile <> ""
'import new updated modules
ThisWorkbook.VBProject.VBComponents.Import (Path & basFile)
basFile = Dir
Loop
Code_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure updateCode_buttonClick of Module UpdateCode. There are several possible reasons: " & vbNewLine & vbNewLine & _
"1) Unable to remove one or more modules" & vbNewLine & _
"2) Unable to find folder path: " & Path & vbNewLine & _
"3) Unable to import one or more modules" & vbNewLine, vbExclamation
End Sub
If there is a bug in other modules, i can correct that and tell people to press the button, and voila bug fixed.
HOWEVER, if a bug appears when a 100 people used it in updateCode module, i can;t really fix it.
So my question is: Am i missing anything in this code that will make it error-free and "monkey-proof"?
Thanks a lot
Bookmarks