Marty: Thanks for your response. At least I'm not crazy or alone -- well, maybe we both are.
I really like your idea of the Master Workbook to hold code and sheet templates. I can see that as being a great way to store a documentation sheet for the workbook, constants as named ranges, conversion tables, and hyperlinks to the web as applicable. I wonder if content of some of those sheets could also be linked to master versions (i.e. you never know when they are going to change the value of Pi or something). The idea of something equivalent to Custom.dic for code is also really attractive. I hope Microsoft is taking notes.
I'm a personal user and don't have access to SharePoint that I'm aware of. I do love using OneNote to supplement my aging memory and recording solutions to various problems I've had. In one of my last pre-retirement projects, I worked on a coding project that I intended for a prospective client and I kept that code in its own Master Workbook and Master Document that would likely have ended up being a pair of Add-ins. I think I may go back with that approach rather than the Personal.xlsb and its Word equivalent. After all, who am I to mess around with someone's Personal.xlsb etc.
I still use the VBA editor to write and maintain my code to gain access to IntelliSense. As you may know, a lot of editing can introduce instability into code and can be re-stabilized by Exporting and Importing the VBA. Ron de Bruin had a couple of routines for doing this that he posted at
Import and Export VBA code. I found this to be really effective in solving problems that get introduced by the compiler or some other ghost in the machine. I also found it to be a means for maintaining the same code in Excel and Word procedure libraries.
I adapted Ron's code for use with Word or Excel and generalized the code so it works with both applications without any real-time changes. I tend to work heavily in Word or heavily in Excel. Between bouts of heavy editing and after doing a RefreshModules on the newly edited code, I re-import all the code into the version for Word or Excel that has been superseded by the editing. I keep the following code in its own module that is stored independently from my procedure libraries. Redundantly keeping it in the procedure libraries for Excel/Word assures it is always available if my work migrates to another machine. I tried to make sure it is well documented and robust enough to work on a machine other than my own. YMMV.
Ron: Thanks for all you do. I hope this is in keeping with accepted use of your generous contributions.
VBA Code:
Option Explicit
' This code was derived and enhanced from VBA published by Ron de Bruin.
' Enhancements by Bruce Volkert circa 2018
' The Original Code, an explanation of its function, and usages is found at: _
https://www.rondebruin.nl/win/s9/win002.htm
' Function: _
Copies all modules in the Active Document to and/or from a Target Folder on the user's computer. _
Typically, this code creates and uses the VBAProjectFiles folder in the user's MyDocuments folder. _
If this target folder does not exist it will be created. _
If this target folder exists, the, Export will delete all files in the Target Folder _
before Exporting modules from the Active Document.
' Enhancements: _
Generalized for use with Excel and Word. _
Configured for early or late binding as indicated by bLateBinding = True or False. _
Refactored for consistency with personal naming convention.
' Conditions for use: _
This code must be run from outside of the Active Document.
' Export: _
If not present, creates the Target Folder (i.e. szExportPath). _
Identifies the application of associated with the Active Document. _
Exports all Modules (standard, class, and form) to the Target Folder.
' Import: _
Verifies there are files to import in the Target Folder. _
Deletes ALL Modules (standard, class, and form) from the Active Document. _
Copies all Modules (standard, class, and form) from the Target Folder into the Active Document. _
' RefreshModules: _
Runs Export _
Runs Import
' Note: _
Although a copy of this module may be included in the Active Document, it must be executed from another document. _
Simply copy the module from the Active Document into any other document that is open (surrogate docuent). _
Execute the Refresh/Export/Import procedures as warranted. _
You can removed the module from the surrogate document if you wish; but, this is not necessary.
' Plans: _
Add code and procedures to perform this function for additional MS Office Applications.
' Compiler Directives: _
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/const-directive
#Const bLateBinding = True ' True means Use Late Binding. False means use Early Binding.
' References Required for EarlyBinding:
' Microsoft Scripting Runtime _
Microsoft Visual Basic for Applications Extensibility 5.3 library _
Requires a reference to Microsoft Excel 14.0 Object library _
Requires a reference to Microsoft Scripting Runtime
Private Sub RefreshModules()
Export
Import
End Sub
Private Sub Export()
Select Case Application
Case "Microsoft Excel"
ExportModulesFromExcelWorkbook
Case "Microsoft Word"
ExportModulesFromWordDocument
End Select
End Sub
Private Sub Import()
Select Case Application
Case "Microsoft Excel"
ImportModulesToExcelWorkbook
Case "Microsoft Word"
ImportModulesToWordDocument
End Select
End Sub
Private Sub ExportModulesFromWordDocument()
#If bLateBinding Then
Dim appWord As Object
Dim MySource As Object
Dim cmpComponent As Object
#Else
Dim appWord As Word.Application
Dim MySource As Word.Document
Dim cmpComponent As VBIDE.VBComponent ' Requires reference to _
Microsoft Visual Basic for Applications Extensibility 5.3 library
#End If
On Error Resume Next
Set appWord = GetObject(, "Word.Application")
If Err Then
MsgBox "Word is not running." & vbCr & _
"There is nothing to Export from Word.", vbOKOnly
Exit Sub
End If
On Error GoTo 0
Dim bExport As Boolean
Dim szSourceDocument As String
Dim szExportPath As String
Dim szFileName As String
''' The code modules will be exported in a folder named.
''' VBAProjectFiles in the Documents folder.
''' The code below create this folder if it not exist
''' or delete all files in the folder if it exist.
If FolderWithVBAProjectFiles = "Error" Then
MsgBox "Export Folder not exist"
Exit Sub
End If
On Error Resume Next
Kill FolderWithVBAProjectFiles & "\*.*"
On Error GoTo 0
''' NOTE: This Document must be open in Word.
szSourceDocument = appWord.ActiveDocument.Name
Set MySource = appWord.Application.Documents(szSourceDocument)
If MySource.VBProject.Protection = 1 Then
MsgBox "The VBA in this Document is protected," & _
"not possible to export the code"
Exit Sub
End If
szExportPath = FolderWithVBAProjectFiles & "\"
For Each cmpComponent In MySource.VBProject.VBComponents
bExport = True
szFileName = cmpComponent.Name
''' Concatenate the correct filename for export.
Select Case cmpComponent.Type
Case vbext_ct_ClassModule
szFileName = szFileName & ".cls"
Case vbext_ct_MSForm
szFileName = szFileName & ".frm"
Case vbext_ct_StdModule
szFileName = szFileName & ".bas"
Case vbext_ct_Document
''' This is a worksheet or Document object.
''' Don't try to export.
bExport = False
End Select
If bExport Then
''' Export the component to a text file.
cmpComponent.Export szExportPath & szFileName
''' remove it from the project if you want
'''MySource.VBProject.VBComponents.Remove cmpComponent
End If
Next cmpComponent
MsgBox "Export is ready"
End Sub
Private Sub ExportModulesFromExcelWorkbook()
#If bLateBinding Then
Dim appExcel As Object
Dim wkbSource As Object
Dim cmpComponent As Object
#Else
Dim appExcel As Excel.Application
Dim wkbSource As Excel.Workbook
Dim cmpComponent As VBIDE.VBComponent
#End If
On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If Err Then
MsgBox "Excel is not running." & vbCr & _
"There is nothing to Export from Excel.", vbOKOnly
Exit Sub
End If
On Error GoTo 0
Dim bExport As Boolean
Dim szSourceWorkbook As String
Dim szExportPath As String
Dim szFileName As String
''' The code modules will be exported in a folder named.
''' VBAProjectFiles in the Documents folder.
''' The code below create this folder if it not exist
''' or delete all files in the folder if it exist.
If FolderWithVBAProjectFiles = "Error" Then
MsgBox "Export Folder not exist"
Exit Sub
End If
On Error Resume Next
Kill FolderWithVBAProjectFiles & "\*.*"
On Error GoTo 0
''' NOTE: This workbook must be open in Excel.
szSourceWorkbook = appExcel.ActiveWorkbook.Name
Set wkbSource = appExcel.Application.Workbooks(szSourceWorkbook)
If wkbSource.VBProject.Protection = 1 Then
MsgBox "The VBA in this workbook is protected," & _
"not possible to export the code"
Exit Sub
End If
szExportPath = FolderWithVBAProjectFiles & "\"
For Each cmpComponent In wkbSource.VBProject.VBComponents
bExport = True
szFileName = cmpComponent.Name
''' Concatenate the correct filename for export.
Select Case cmpComponent.Type
Case vbext_ct_ClassModule
szFileName = szFileName & ".cls"
Case vbext_ct_MSForm
szFileName = szFileName & ".frm"
Case vbext_ct_StdModule
szFileName = szFileName & ".bas"
Case vbext_ct_Document
''' This is a worksheet or workbook object.
''' Don't try to export.
bExport = False
End Select
If bExport Then
''' Export the component to a text file.
cmpComponent.Export szExportPath & szFileName
''' remove it from the project if you want
'''wkbSource.VBProject.VBComponents.Remove cmpComponent
End If
Next cmpComponent
MsgBox "Export is ready"
End Sub
Private Sub ImportModulesToExcelWorkbook()
#If bLateBinding Then
Dim appExcel As Object
Dim wkbTarget As Object
Dim objFSO As Object
Dim objFile As Object
Dim cmpComponents As Object
#Else
' Requires a reference to Microsoft Excel 14.0 Object library
Dim appExcel As Excel.Application
Dim wkbTarget As Excel.Workbook
' Requires a reference to Microsoft Scripting Runtime
Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim cmpComponents As VBIDE.VBComponents
#End If
On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If Err Then
' Excel was not running
Set appExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0
Dim szTargetWorkbook As String
Dim szImportPath As String
Dim szFileName As String
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "Select another destination workbook" & _
"Not possible to import in this workbook "
Exit Sub
End If
'Get the path to the folder with modules
If FolderWithVBAProjectFiles = "Error" Then
MsgBox "Import Folder not exist"
Exit Sub
End If
''' NOTE: This workbook must be open in Excel.
szTargetWorkbook = appExcel.ActiveWorkbook.Name
Set wkbTarget = appExcel.Application.Workbooks(szTargetWorkbook)
If wkbTarget.VBProject.Protection = 1 Then
MsgBox "The VBA in this workbook is protected," & _
"not possible to Import the code"
Exit Sub
End If
''' NOTE: Path where the code modules are located.
szImportPath = FolderWithVBAProjectFiles & "\"
Set objFSO = New Scripting.FileSystemObject
If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
MsgBox "There are no files to import"
Exit Sub
End If
'Delete all modules/Userforms from the ActiveWorkbook
Call DeleteVBAModulesAndUserFormsFromExcelWorkbook
Set cmpComponents = wkbTarget.VBProject.VBComponents
''' Import all the code modules in the specified path
''' to the ActiveWorkbook.
For Each objFile In objFSO.GetFolder(szImportPath).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 is ready"
End Sub
Private Sub ImportModulesToWordDocument()
#If bLateBinding Then
Dim appWord As Object
Dim cmpComponents As Object
Dim MyTarget As Object
Dim objFSO As Object
Dim objFile As Object
#Else
Dim appWord As Word.Application
Dim cmpComponents As VBIDE.VBComponents
Dim MyTarget As Word.Document
Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
#End If
On Error Resume Next
Set appWord = GetObject(, "Word.Application")
If Err Then
' Word was not running
Set appWord = CreateObject("Word.Application")
End If
On Error GoTo 0
Dim szMyTarget As String
Dim szImportPath As String
Dim szFileName As String
#If Application = "Word.Application" Then
If appWord.ActiveDocument.Name = ThisDocument.Name Then
MsgBox "Select another destination Document" & _
"Not possible to import in this Document "
Exit Sub
End If
#End If
'Get the path to the folder with modules
If FolderWithVBAProjectFiles = "Error" Then
MsgBox "Import Folder not exist"
Exit Sub
End If
''' NOTE: This Document must be open in Word.
szMyTarget = appWord.ActiveDocument.Name
Set MyTarget = appWord.Application.Documents(szMyTarget)
If MyTarget.VBProject.Protection = 1 Then
MsgBox "The VBA in this Document is protected," & _
"not possible to Import the code"
Exit Sub
End If
''' NOTE: Path where the code modules are located.
szImportPath = FolderWithVBAProjectFiles & "\"
Set objFSO = New Scripting.FileSystemObject
If objFSO.GetFolder(szImportPath).Files.Count = 0 Then
MsgBox "There are no files to import"
Exit Sub
End If
'Delete all modules/Userforms from the ActiveDocument
DeleteVBAModulesAndUserFormsFromWordDocument
Set cmpComponents = MyTarget.VBProject.VBComponents
''' Import all the code modules in the specified path
''' to the ActiveDocument.
For Each objFile In objFSO.GetFolder(szImportPath).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 is ready"
End Sub
Private Function FolderWithVBAProjectFiles() As String
Dim WshShell As Object
Dim FSO As Object
Dim SpecialPath As String
Set WshShell = CreateObject("WScript.Shell")
Set FSO = CreateObject("scripting.filesystemobject")
SpecialPath = WshShell.SpecialFolders("MyDocuments")
If Right$(SpecialPath, 1) <> "\" Then
SpecialPath = SpecialPath & "\"
End If
If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
On Error Resume Next
MkDir SpecialPath & "VBAProjectFiles"
On Error GoTo 0
End If
If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
Else
FolderWithVBAProjectFiles = "Error"
End If
End Function
Private Function DeleteVBAModulesAndUserFormsFromExcelWorkbook() As Variant
#If bLateBinding Then
Dim appExcel As Object
Dim VBProj As Object
Dim VBComp As Object
#Else
Dim appExcel As Excel.Application
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
#End If
On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If Err Then
' Excel was not running
Set appExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set VBProj = appExcel.ActiveWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
'Thisworkbook or worksheet module
'We do nothing
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Function
Private Function DeleteVBAModulesAndUserFormsFromWordDocument() As Variant
#If bLateBinding Then
Dim appWord As Object
Dim VBProj As Object
Dim VBComp As Object
#Else
Dim appWord As Word.Application
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
#End If
On Error Resume Next
Set appWord = GetObject(, "Word.Application")
If Err Then
MsgBox "Word is not running." & vbCr & _
"There is nothing to Delete from Word.", vbOKOnly
Exit Function
End If
On Error GoTo 0
Set VBProj = appWord.ActiveDocument.VBProject
For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
'Thisworkbook or worksheet module
'We do nothing
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Function
