VBA set up - calling Modules

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154
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.:

Rich (BB code):
Public Sub cmdOpenExcelTemplate_Click()
    
    Call MasterReportBuilderAc

End Sub

Whic calls this:

Rich (BB code):
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:
Rich (BB code):
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top