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

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
VBA Code:
Call SpeedUpOn (Xcalc)
and it would turn Calculations from Automatic to Manual.

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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
Hi,
see if this update to your code does what you want

VBA Code:
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
 
Last edited:
Solution

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
'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

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,001
Messages
5,622,125
Members
415,878
Latest member
jjj12345

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
Top