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
and then
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
'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
source to share