Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft Excel - Macros - Master Run Routine ======

This is a suggested master run routine.

<code excel>
' Runs whichever subroutine name is passed to it.
' This is the 'master' controlling routine, which also times how long other routines take and displays messages.
Sub Z99999_Run_Subroutine(strQueryName As String, Optional strDescription As String = "")

    Dim sngStart As Single
    Dim sngEnd As Single
    Dim sngElapsed As Single

    ' Set a shortcut to the workbook.
    ' wb_name is declared global as Public wb_name As String                       ' Workbook Name
    wb_name = ThisWorkbook.name

    ' Control to prevent user from overwriting the template.
    If ThisWorkbook.name Like "* Template.xlsm" Then
        MsgBox "This workbook is the template, you must not make changes to it." & vbCrLf & vbCrLf & "You need to save as a new workbook name before running any macros."
        Exit Sub
    End If


    ' Get start time.
    sngStart = Timer
    
    
    ' Initializes global variables.
    Call Z00000_Init

    
    ' To speed up processing.
    If ctrl_disable_auto_calcs_before_subroutine Then
        With Application
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    End If
    
    
    ' Turn filters off on all sheets.
    Call TurnFilterOffAllSheets
    
    
    ' Update StatusBar message.
    If strDescription = "" Then
        Application.StatusBar = "Running " & strQueryName
    Else
        Application.StatusBar = "Running " & strDescription
    End If
    
    
    ' Run query.
    'DoCmd.OpenQuery strQueryName, acNormal
    'Call strQueryName
    Application.Run strQueryName
        
        
    ' Re-enable automatic calculations.
    If ctrl_reenable_auto_calcs_after_subroutine Then
        With Application
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
    

    ' Activate the dashboard.
    If ctrl_show_dashboard_after_subroutine Then
        Workbooks(wb_name).Sheets("Dashboard").Activate
    End If
   
        
    ' Display how long the subroutine ran for.
    sngEnd = Timer                                 ' Get end time.
    sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.


    ' Make excel top most to bring it to the top of other applications.
    SetXLOnTop
        
        
    ' Display message to user.
    If strDescription = "" Then
        Message "", sngElapsed, strQueryName
        Application.StatusBar = "Finished " & strQueryName
    Else
        Message "", sngElapsed, strDescription
        Application.StatusBar = "Finished " & strDescription
    End If
        
        
    ' Make excel not top most to allow other applications to go on top of it.
    SetXLNormal
        
End Sub
</code>