Variables across functions

[rlarkin]

Hello,

Would someone be able to point me in the right direction with this one. I have written code to format Excel sheets, which works great when the code is placed with the same sub. As I want to use it in numerous places, I've tried to call it from a separate function, which doesn't work. I know it's something to do with maintaining the variables, but I'm struggling to find the answer:

Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim PathDatedFolder As String
Dim strSheet As String
strPathDated = CurrentProject.Path & "\Reports\Individual Worksheets\" & Format(Date, "dd-mm-yyyy")
 
 
 
With Me
If IfNoneChecked = True Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "Custom Query", strPathDated & "\Custom Query.xlsx", True
    strSheet = "Custom_Query"
    With objApp
        .Visible = True
        .DisplayAlerts = False
        Set objBook = .Workbooks.Open(strPathDated & "\Custom Query.xlsx", , False)
    End With
    Call DoFormat
    'objBook.Close False
Else
 'other stuff
End If
End With

Public Function DoFormat()
With objBook.Sheets(strSheet)
     .Cells.EntireColumn.AutoFit
            With .Columns("A:Q")
                .WrapText = True
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlTop
                With .Font
                    .Color = -9609385
                    .Name = "Century Schoolbook"
                    .Size = 9
                End With
            End With
            With .Range("A1:Q1")
                .WrapText = False
                .Font.Size = 18
            End With
      .Range("A2").AutoFilter
End With
End Function

Thanks,

Russ

 

[Galaxiom]

Variables declared in the Declarations section of the module (before the first sub/function) have scope throughout the module.

 

[rlarkin]

Ah, simple. Thanks a lot.

 

[Galaxiom]

BTW Variables in a Standard Module have scope across the entire Project.

 

[vbaInet]

BTW Variables in a Standard Module have scope across the entire Project.

... and can be accessed from anywhere within the project if declared as Public.