Reference add in global variables from worksheet code

Question

I'm building an Excel Addin, which declares some Public variables in a sub. When the Addin is first used, it copies some worksheets from the Addin (ThisWorkBook) to the user's workbook (ActiveWorkBook). Those worksheets have some worksheet event subs in them.

Question: The ActiveWorkBook worksheet event subs need to reference the public variables defined in ThisWorkBoook, but can't seem to find them. I suppose because they're in a different workbook. But in this add-in situation, surely there must be some way of doing that?

Specific example: GlobalAddin.xlam in module 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 is made an active addin. Now in another workbook Book2.xlm in Sheet1 we hasve

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "The Worksheet says TestMe is " & TestMe
End Sub

Now open Book2.xlm. A dialog box reports a value of 10 for TestMe (this from the xlam workbook_open even calling RunSub.) But then clicking on Sheet1 in Book2 causes an variable not defined error, saying TestMe is not defined.

So this makes sense, but how is it that I can access the value of TestMe inside a worksheet event in Book2?

(Edit: I suppose I could pass variables via a (hidden) sheet in ActiveWorkBook, but that seems kind of clumsy. Is there a better way?)

Answer 1

Can you not do the following?

1. Create an addin that has fields declared in a module.

   public TestMe as integer

2. Also declare a public function in the same addin module to get the field value.

   public Function GetTestMe() as integer GetTestMe = TestMe end Function

3. Now in any other workbook in the same Excel application, late bound the call to get the value.

   public Sub TestAddinCall() Dim x as Integer x = Application.run("GetTestMe") End Sub

This will obviously be late bound, but is probably the simplest way.

Answer 2

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