VBA Excel : share variables across modules, sheets and workbooks

Question

i am trying to access variables across several modules, sheets and workbooks. i'm not even able to share them across modules in same workbook... i wonder what's missing. In this case, i want to open a file and then share its name across modules in order to manipulate it through other functions.

When running this procedure "Sub get_workbook_and_sheets_names_S", it asks to run a macro (I chose Sub myMain), but then I only got the macro output.

Sub myMain()
Dim i As Integer
Static v_sheet_name_S As Variant
Static v_workbook_name_S As Variant


'Call f_FSOGetFileName_S

With Application.Workbooks(f_FSOGetFileName_S)
     v_workbook_name_S = .Name
    
     Debug.Print "this is WORKBOOK  : " & v_workbook_name_S
    
     For i = 1 To .Sheets.Count
         
        
         v_sheet_name_S = .Sheets(i).Name
         
         Debug.Print "this is workbook SHEET : " & v_sheet_name_S
         
     Next

End With

'Call f_FSOGetFileName_T


End Sub



Function f_FSOGetFileName_S()   'OPEN SOURCE FILE
    
    Dim v_strFile_S As String
    Dim v_FileName_S As String
    Dim v_FSO_S As New FileSystemObject
        
    Dim v_FileNameWOExt_S As Variant
    
    
    Set v_FSO_S = CreateObject("Scripting.FileSystemObject")

    'get file full path
    v_strFile_S = Application.GetOpenFilename(filefilter:="Excel files,*.x*", Title:="select SOURCE file")
    Workbooks.Open Filename:=v_strFile_S


    
    
    'Get File Name
    
    v_FileName_S = v_FSO_S.GetFileName(v_strFile_S)
    
   
    
    'Get File Name no Extension
    v_FileNameWOExt_S = Left(v_FileName_S, InStr(v_FileName_S, ".") - 1)
    
    f_FSOGetFileName_S = v_FileName_S    'FUNCTION RESULT

End Function



Sub get_workbook_and_sheets_names_S(v_workbook_name_S, v_sheet_name_S)

Debug.Print "Source workbook name : " & v_workbook_name_S
Debug.Print "Source sheet name : " & v_sheet_name_S


End Sub

score 2 · Answer 1 · answered Sep 12 '22 at 11:58

In order to create a Global variable, please proceed in the next way:

Create a Public variable on top of a standard module (in the declarations area):

   Public v_workbook_name_S As String

This variable can be accessed/used from all modules of the workbook where it has been declared. You should simple use:

   Debug.print v_workbook_name_S

Of course, the variable must previously receive a value...

In order to be accessible from other workbooks, you should also create a function (**not Private) in the workbook where the global variable has been declared. In a standard module, too:

Sub setGobVarStr()
    v_workbook_name_S = "myString"
End Sub

The global variable value can be accessed colling the above function, using:

Sub testReadGlobalVar()
     Dim wbName As String, myName As String
     wbName = "Teste Forum StackOverflow Last.xlsm"

     myName = Application.Run("'" & wbName & "'!getWbName")
     Debug.Print myName
End Sub

Of course, the global variable should previously received a value. If not, the code will return a VBNullString, anyhow...

Note:

You must not declare the same variable inside the colling Sub/Function! In such a case, the code will not raise any error, but it will rewrite the global variable and return a VBnullString, too...

VBA Excel : share variables across modules, sheets and workbooks

1 Answers1