Add "Settings/Flags/Arguments" to Function or Sub?

[t0ny84]

Hi Again!

Just wondering if anyone knows if it is possible to add settings or flags to a function or sub so that it would action based on those flags.

To cut down on adding it to each macro and also to make something which can be imported across numerous workbooks I am creating a "Common Utilities Module" which I can import to the workbook and then call the relevant utility\command.

E.g. With this code user would type

Call SpeedUpOn (Xcalc)

and it would turn Calculations from Automatic to Manual.

Public Function SpeedUpOn(Optional xCalc As Boolean, Optional xEvent = "Events", Optional xScreen = "Screen", Optional xStatus = "StatusBar", Optional xAlerts = "Alerts", Optional xPageBreaks = "PageBreaks")
    
    ' Arguments For SpeedUpOn
    ' Calc - Sets Calculation From Automatic to Manual
    ' Events - Turns Enable Events Off
    ' Screen - Turns Screen Updating Off
    ' StatusBar - Turns Display Status Bar Off
    ' Alerts - Turns Display Alerts Off
    ' PageBreaks - Turns Display Page Breaks Off
    
    ' To Use In Your Code
    ' Put Below At The Top Of Your Code
    ' Call SpeedUpOn("calc", "Event", "Screen", "statusbar")
    ' Put Below At The Bottom Of Your Code
    ' Call SpeedUpOff
    
    
   With Application
        If Not xCalc Then
    .Calculation = xlCalculationAutomatic
        Else
    .Calculation = xlCalculationManual
        End If
        
    
        If Events = True Then
    .EnableEvents = False
        Else
    .EnableEvents = True
        End If
        
        If Screen = True Then
    .ScreenUpdating = False '
        Else
    .ScreenUpdating = True
        End If
        
        If Status = True Then
    .DisplayStatusBar = False
        Else
    .DisplayStatusBar = True
        End If
        
        If Alerts = True Then
    .DisplayAlerts = False
        Else
    .DisplayAlerts = True
        End If
        
    End With
    
    If PageBreaks = True Then
        ActiveSheet.DisplayPageBreaks = False
    Else
        ActiveSheet.DisplayPageBreaks = True
    End If
    
End Function

 

[dmt32]

Hi,
see if this update to your code does what you want

Public Sub SpeedUpOn(Optional ByVal xCalc As XlCalculation = xlCalculationAutomatic, _
                    Optional ByVal xEvent As Boolean = True, _
                    Optional ByVal xScreen As Boolean = True, _
                    Optional ByVal xStatus As Boolean = True, _
                    Optional ByVal xAlerts As Boolean = True, _
                    Optional ByVal xPageBreaks As Boolean)
   
    ' Arguments For SpeedUpOn
    ' xCalc - Sets Calculation From Automatic to Manual
    ' xEvents - Turns Enable Events Off
    ' xScreen - Turns Screen Updating Off
    ' xStatusBar - Turns Display Status Bar Off
    ' xAlerts - Turns Display Alerts Off
    ' xPageBreaks - Turns Display Page Breaks Off
   
    ' To Use In Your Code
    ' Put Below At The Top Of Your Code
    'Call SpeedUpOn(xlCalculationManual, False, False, False, False)
   
    ' Put Below At The Bottom Of Your Code
    ' Call SpeedUpOff
   
    With Application
        .Calculation = xlCalc
        .EnableEvents = xEvent
        .ScreenUpdating = xScreen '
        .DisplayStatusBar = xStatus
        .DisplayAlerts = xAlerts
    End With
   
    ActiveSheet.DisplayPageBreaks = xPageBreaks

End Sub

The default value of all but that last boolean parameters is TRUE.
xlCalc uses the built-in xlCalculation enumeration & will produce the IntelliSense giving list of calculation choices. - Default setting is xlCalculationAutomatic

Hope Helpful

Dave

 

[t0ny84]

Hi,

Hi dmt32,

Thanks for having a look at it. The code is doing the same for me just looping through but not changing any of the settings.

 

[dmt32]

Hi dmt32,

Thanks for having a look at it. The code is doing the same for me just looping through but not changing any of the settings.

Not sure what you mean by just looping as code does not loop - As published, code should do what you want - you just set which of the boolean parameters you need to disable to False & set calculation to manual or automatic as required

'turn all off
Call SpeedUpOn(xlCalculationManual, False, False, False, False)

With Application
MsgBox .Calculation & Chr(10) & _
        .EnableEvents & Chr(10) & _
        .ScreenUpdating & Chr(10) & _
        .DisplayStatusBar & Chr(10) & _
        .DisplayAlerts & Chr(10) & _
        ActiveSheet.DisplayPageBreaks
End With

'turn all on
Call SpeedUpOn(xlCalculationAutomatic)

Dave

 

[t0ny84]

Hey Dave,
Sorry for the delay in getting back to you, thanks for your help. The code does work I was just not using it correctly :\

 

[dmt32]

Hey Dave,
Sorry for the delay in getting back to you, thanks for your help. The code does work I was just not using it correctly :\

No worry glad all sorted

Dave