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?)

+3


source to share


2 answers


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.

+3


source


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

      

+1


source







All Articles