I've solved similar problems to this before by not only copying worksheets into the new workbook (with each sheet's accompanying support code), but also by importing VBA modules into the new workbook. Clearly, the VBA modules can define any publically global variables and have them exist for that workbook. This is important when the add-in has been installed for a workbook and you want those public variables "local" to the add-in enabled workbook.
At some point, you'll have to export your VBA module to a text file:
Sub ExportAllModulesAndClasses()
On Error GoTo Err_ExportAllModulesAndClasses
'Purpose: Connects to the current project and exports each of the VBA
' components to an external, text-based file. File extensions
' are automatically selected based on the type of the component.
'Return: n/a
'Author: PeterT
Dim i As Integer
Dim sourceCode As Object
Dim filename As String
i = 0
For Each sourceCode In Application.VBE.ActiveVBProject.VBComponents
filename = CHOOSE_YOUR_DIRECTORY_PATH_HERE & sourceCode.name & GetFileExtension(sourceCode)
Debug.Print "Exported: " & filename
sourceCode.Export filename
i = i + 1
Next
Debug.Print "Export complete: " & i & " source code files created from this application"
Exit_ExportAllModulesAndClasses:
Exit Sub
Err_ExportAllModulesAndClasses:
MsgBox "In ExportAllModulesAndClasses: " & Err.Number & " - " & Err.Description, vbOKOnly
Resume Exit_ExportAllModulesAndClasses
End Sub
Public Function GetFileExtension(vbComp As Object) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns the appropriate file extension based on the Type of
' the VBComponent.
' based on: http://www.cpearson.com/excel/vbe.aspx
'
' Type property constants:
' vbext_ct_StdModule = 1 Standard Module
' vbext_ct_ClassModule = 2 Class Module
' vbext_ct_MSForm = 3 Microsoft Form
' vbext_ct_ActiveXDesigner = 11 ActiveX Designer
' vbext_ct_Document = 100 Document Module
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select Case vbComp.Type
Case 2 'class
GetFileExtension = ".cls"
Case 100 'document
GetFileExtension = ".cls"
Case 3 'form
GetFileExtension = ".frm"
Case 1 'standard module
GetFileExtension = ".bas"
Case Else
GetFileExtension = ".bas"
End Select
End Function
And then when your add-in installs, you can import your module into the new workbook:
Sub ImportVBAProjectFiles()
On Error GoTo Err_ImportVBAProjectFiles
'Purpose: Uses the constants defined above to access a specific
' directory. All files within that directory will be added as
' a module, class, form, etc to this application project.
'Return: n/a
'Author: PeterT
Dim i As Integer
Dim name As Variant
Dim filenames As New Collection
'--- build up an array of all the files (modules, forms, classes, etc)
' that will be imported
Call FillDir(filenames, CHOOSE_YOUR_DIRECTORY_PATH_HERE , "*.*", False)
'--- add each item to this project
i = 0
For Each name In filenames
Application.VBE.ActiveVBProject.VBComponents.Import CStr(name)
Debug.Print "Imported: " & name
i = i + 1
Next
Exit_ImportVBAProjectFiles:
Exit Sub
Err_ImportVBAProjectFiles:
MsgBox "In ImportVBAProjectFiles: " & Err.Number & " - " & Err.Description, vbOKOnly
Resume Exit_ImportVBAProjectFiles
End Sub
Private Function FillDir(colDirList As Collection, ByVal strFolder As String, _
strFileSpec As String, bIncludeSubfolders As Boolean)
'Build up a list of files, and then add add to this list, any additional folders
'from: http://allenbrowne.com/ser-59.html
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colDirList.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop
'Call function recursively for each subfolder.
For Each vFolderName In colFolders
Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(varIn As Variant) As String
'from: http://allenbrowne.com/ser-59.html
If Len(varIn) > 0& Then
If Right(varIn, 1&) = "\" Then
TrailingSlash = varIn
Else
TrailingSlash = varIn & "\"
End If
End If
End Function
Application.Run("my_addin.xla!MySub")
– John Alexiou