Link add global variables from worksheet code
I am creating an Excel Addin that declares some public variables in sub. When the Addin is first used, it copies some sheets from the Addin (ThisWorkBook) to the user's workbook (ActiveWorkBook). These worksheets have multiple subtitles in them.
Question. ActiveWorkBook event principals must refer to public variables defined in ThisWorkBoook, but cannot find them. I suppose because they are in another book. But in this add-on situation, of course there must be some way to do it?
Concrete example: GlobalAddin.xlam in Module1 declares
Option Explicit Public TestMe As Integer
Public Sub RunSub() TestMe = 10 MsgBox "The Addin says that TestMe is " & TestMe End Sub
and RunSub is called from the ThisWorkBook._Open () event.
GlobalAddin.xlam becomes active add-on. Now in another book Book2.xlm in Sheet1 we have
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox "The Worksheet says TestMe is " & TestMe End Sub
Now open Book2.xlm. The dialog box reports a value of 10 for TestMe (this from the xml workbook_open even calls RunSub.) But then clicking on Sheet1 in Book2 throws a variable undefined error saying TestMe is undefined.
So it makes sense, but how can I access the TestMe value inside the worksheet event in Book2?
(Edit: I'm guessing I can pass variables through a (hidden) sheet in ActiveWorkBook, but that seems clunky. Is there a better way?)
source to share
Can't you do the following?
Create an admin with the fields declared in the module.
public TestMe as a whole
Also declare a public function in the same addin module to get the field value.
public GetTestMe () function as integer GetTestMe = TestMe end Function
Now, in any other workbook in the same Excel application, I have late bound a call to get the value.
public Sub TestAddinCall () Dim x as Integer x = Application.run ("GetTestMe") End Sub
This will obviously be late, but probably the easiest way.
source to share
I decided to solve similar problems before by not only copying the sheets into a new workbook (with each sheet accompanying the support code), but also importing the VBA modules into the new workbook. Obviously, VBA modules can define and have any public globals for this book. This is important when the add-in has been installed for a workbook and you want these public variables to be "local" in the workbook with the add-in enabled.
At some point, you will need 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-ins are installed, you can import your module into a new workbook:
Sub ImportVBAProjectFiles() On Error GoTo Err_ImportVBAProjectFiles : 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. : n/a : 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 CStr(name) "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) up a list of files, and then add add to this list, any additional folders : http://allenbrowne.com/ser-59.html Dim strTemp As String Dim colFolders As New Collection Dim vFolderName As Variant the files to the folder. strFolder = TrailingSlash(strFolder) strTemp = Dir(strFolder & strFileSpec) Do While strTemp <> vbNullString strFolder & strTemp strTemp = Dir Loop If bIncludeSubfolders Then 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 strTemp End If End If strTemp = Dir Loop 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 : 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
source to share