Results 1 to 2 of 2

VBA set up - calling Modules

This is a discussion on VBA set up - calling Modules within the Microsoft Access forums, part of the Question Forums category; Questions: - Once I name a variable in One module how do I get it to keep it's value when ...

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Posts
    154

    Default VBA set up - calling Modules

    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.:

    Code:
    Public Sub cmdOpenExcelTemplate_Click()
        
        Call MasterReportBuilderAc
    
    End Sub
    Whic calls this:

    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:
    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

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,996

    Default Re: VBA set up - calling Modules

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com