VBA set up - calling Modules

[MNpoker]

Questions:
- Once I name a variable in One module how do I get it to keep it's value when calling another module?
- Do I need to keep using Dim in each called module?

- Is there a much better way to do this?


THANKS!!

I have the following In a Form Object.:

Public Sub cmdOpenExcelTemplate_Click()
    
    Call MasterReportBuilderAc

End Sub

Whic calls this:

Public Sub MasterReportBuilderAc()
    
    Dim strFullPath As String
    Dim sXL_Path As String
    Dim XLapp As Excel.Application
    Dim XLwb As Excel.Workbook
    Dim ReportName As String
            
    Dim Objcat As New ADOX.Catalog
    Dim oConn As New ADODB.Connection
            
    ' Get the Path Name
    strFullPath = CurrentDb().Name
    sXL_Path = Left(strFullPath, InStrRev(strFullPath, "\"))
    
    ReportName = "NewReport"
    
    MsgBox (sXL_Path)
    
    Set XLapp = CreateObject("excel.application")
    Set XLwb = Workbooks.Open(sXL_Path & TargetXLFile)
    XLapp.Visible = True
    
    XLwb.SaveAs (sXL_Path & ReportName)
        
Call CountyTIVac
Call DistToCoastAc
Call LimitProfileAc
' See Module below
       
    XLapp.Quit
    
    Set Objcat = Nothing
    Set oConn = Nothing
    Set XLapp = Nothing
    Set XLwb = Nothing
End Sub

In a Module.

Next I have all the modules being called (there will be more than 3) each in their own module:

Sub DistToCoastAc()

    Dim sDB_Path As String
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim Rw As Long
    Dim fld As ADODB.Field
    Dim ShDest As Worksheet
    Dim Cmd As ADODB.Command
    Dim VtSQL As String
    Dim XLwb As Excel.Workbook
    Dim sXL_Path As String
    Dim ReportName As String
        
    sDB_Path = CurrentDb().Name
      
    'SET UP
    Set cnn = CurrentProject.Connection
    Set Cmd = New ADODB.Command
    Set Cmd.ActiveConnection = cnn
       
    'CREATE AND RUN QUERY
    VtSQL = ""
    VtSQL = VtSQL & "SELECT DistCoast_Complete.CountOfLOCID, DistCoast_Complete.SumOfTIVVALUE FROM DistCoast_Complete;"
        
    Cmd.CommandText = VtSQL
    ' Cmd.CommandType = adCmdQuery
    Cmd.Execute
        
    ' PUT QUERY TO RECORD SET
    Set rst = New ADODB.Recordset
    Set rst.ActiveConnection = cnn
    rst.Open Cmd
    
    ' CREATE LINK TO EXCEL FILE & DO STUFF IN EXCEL
    Set XLwb = Workbooks(sXL_Path & ReportName)
        
    With XLwb.Worksheets("CountyTIV")
        .Range("A7").CopyFromRecordset rst
    End With
    
    Rw = XLwb.Worksheets("CountyTIV").Range("A7").End(xlDown).Row
    
    With XLwb.Worksheets("CountyTIV")
        .Range("A" & Rw + 1, "A999").EntireRow.Delete
    End With
       
    ' Close the connection
    Set cnn = Nothing
    Set Cmd = Nothing

End Sub

 

[Joe4]

Declare a Public Variable. Then you can set its value in one Module, and use that value in another. If you look at the built-in VBA help on "Declaring Variables", it should provide help on how to does this, if you need it.