Passing Public variable between .xlam and .xlsm files

Question

I'm running a look up function from the Ribbon *.xlam (file1) that opens a remote *.xlsm file containing all the project macros (file2), that in turn runs the lookup code on the remote database (file3).

File2 creates a variable "Confirm" string that works great when displayed as a Msgbox - but the msgbox is modal and stops the code. Meaning other users won't have write access to file3 while file2 is keeping it open.

To solve this, I created a Userform on file1 to mimic the msgbox in file2. Everything works and file2 and 3 are opened and closed as expected, but file2's variable "Confirm" is not passed through to File1's Userform label.

Condensed code below. Any help is greatly appreciated!

File1 *.xlam code:

Public Confirm As String

Sub ViewSUB(control As IRibbonControl)
    Dim Sub_Macros As String, strFileExistsA As String, strFileExistsB As String
    Dim currentWorkbook As Workbook
    
            strFileExistsA = Dir(Sub_Macros_L)
            strFileExistsB = Dir(Sub_Macros_R)
                If strFileExistsA <> "" Then
                   Sub_Macros = Sub_Macros_L
                ElseIf strFileExistsB <> "" Then
                   Sub_Macros = Sub_Macros_R
                Else
                    MsgBox "Macro File is missing", vbCritical, "Macro File Missing"
                    Exit Sub
                End If
        Application.ScreenUpdating = False
        Application.Calculation = xlManual
        Set currentWorkbook = Application.ActiveWorkbook
        Workbooks.Open Sub_Macros, ReadOnly:=True
        currentWorkbook.Activate
        Application.Run ("'Macros.xlsm'!ViewSUB")
        
    ViewSub_Details.Show vbModeless
    
        Workbooks("Macros.xlsm").Close SaveChanges:=False
        Application.Calculation = xlAutomatic
        Application.ScreenUpdating = True
End Sub

File2 simply creates the string and I can prove it works with a msgbox. This is just a section of that code:

Public Confirm As String

        For i = 3 To LastrowDBase
            If DBase.Cells(i, 2) = SUBPN And DBase.Cells(i, 5) = "SomeText" Then
                Confirm = Confirm & "  - " & DBase.Cells(i, 3) & " - Flag: " & DBase.Cells(i, 7) & " - " & DBase.Cells(i, 6) & vbCrLf
            End If
        Next i
        
            MsgBox Confirm

Then back at File1 the Userform code:

Option Explicit

Private Sub UserForm_Initialize()

    Label1.Caption = Confirm
    
End Sub

Private Sub CommandButton01_Click()

    Unload ViewSub_Details

End Sub

Answer 1

These quotes are from Excel help files

Public Variables declared using the Public statement are visible to all procedures in all modules in all applications unless Option Private Module is in effect. In that case, the variables are public only within the project in which they reside.

referenced project The project you directly create a link to from the current project you are working on. A project referenced by one of the current project's directly referenced projects is called an indirectly referenced project. Its Public variables are not accessible to the current project except through qualification with its project name. Any combination of direct and indirect references between projects is valid as long as they do not result in a complete cycle.

referencing project The current project. How you create a link to a project depends on the host application. For example, to directly reference a project in Microsoft Excel, select the project from the References dialog box of the Tools menu. Public variables in a directly referenced project are visible to the directly referencing project, but Public variables in a directly referencing project are not visible to a directly referenced project.

Defining Public Module-Level Scope If you declare a module-level variable as public, it's available to all procedures in the project. In the following example, the string variable

strMsg

can be used by any procedure in any module in the project.

Include in Declarations section of module.

Public strMsg As String

All procedures are public by default, except for event procedures. When Visual Basic creates an event procedure, the Private keyword is automatically inserted before the procedure declaration. For all other procedures, you must explicitly declare the procedure with the Private keyword if you do not want it to be public.

You can use public procedures, variables, and constants defined in standard modules or class modules from referencing projects. However, you must first set a reference to the project in which they are defined.

Public procedures, variables, and constants defined in other than standard or class modules, such as form modules or report modules, are not available to referencing projects, because these modules are private to the project in which they reside.

Answer 2

You cannot directly read a variable from a different workbook without referencing the respective workbook and not having the same variable in the working one. But you can do it without referencing the workbook, even if the variable in discussion is declared as Private, calling a Public function which return the respective variable:

1. In a standard module of the workbook where from you need to read the variable, copy the next code:

Option Explicit

Private myVar As String 'the variable to be returned in another workbook...

Sub GiveMyVarValue()
   myVar = "test variable"
End Sub

Public Function GetMyvar() As String 'PUBLIC function!
   GetMyvar = myVar
End Function

And run GiveMyVarValue to give a value to the respective variable (of course, you can use your existing procedure/variable, but take care to lace it as return of GetMyvar function .

2. Use the next code in the active workbook, to read the variable from the other workbook:

Sub ReadVariableFromDifferentWB()
  Dim wb As Workbook: Set wb = Workbooks("Personal.xlsb") 'use here the real workbook name where from you need to return the variable
  Dim x As String
  
  x = Application.Run("'" & wb.name & "'!GetMyVar")
  MsgBox x
End Sub

Please, send some feedback after testing the above suggested solution, which I used many times when needed...

Answer 3

You cannot access variables outside of the project (the project is basically your Excel file). Your confirm variables are different variables and file1 can't access confirm of file2. The keyword public means that the variable is global within the whole project of file2 (so all modules of file2 can access it), but not public to any other running VBA code.

However what you can do is provide the confirm string as a result of a function:

Public Function GetConfirmString
    For i = 3 To LastrowDBase
        If DBase.Cells(i, 2) = SUBPN And DBase.Cells(i, 5) = "SomeText" Then
            Confirm = Confirm & "  - " & DBase.Cells(i, 3) & " - Flag: " & DBase.Cells(i, 7) & " - " & DBase.Cells(i, 6) & vbCrLf
        End If
    Next i
    GetConfirmString = Confirm
End Function

You can call this function using Application.Run:

Private Sub UserForm_Initialize()
    Label1.Caption = Application.Run ("'Macros.xlsm'!GetConfirmString")
End Sub