Search

VBA Program Booster

Hello friends, all relevant materials for this topic/tutorial can be downloaded from here. Please support us by subscribing to our channel and sharing them with your friends.

If you have any questions/feedback/tutorial request, please you can email me directly vbaa2z.team@gmail.com or comment on YouTube Video (blog comments are not actively monitored).

Support our channel: youtube.com/vbaa2z

Insert new module and paste below code.

Refer to Usage Example on how to use the code.

Public PriorCalcMode As Variant

Public Function TurnOnSpeed(x As Boolean)

'-----------------------------
'Thanks for downloading the code. 
'Please visit our channel for a quick explainer on how to use this code.
'Feel free to update the code as per your need and also share with your friends.
'Download free codes from http://vbaa2z.blogspot.com
'Support our channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

    If x = True Then
    With Application
        PriorCalcMode = Application.Calculation
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Cursor = xlWait
            .Calculation = xlCalculationManual
    End With
    
    ElseIf x = False Then
    
    With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .EnableEvents = True
            .StatusBar = False
            .Cursor = xlDefault
        .Calculation = PriorCalcMode
        End With
    End If

End Function

In short, this function will turn off ScreenUpdating, DisplayAlerts, EnableEvents, Calculation
before executing the code and when TurnOnSpeed is set to False it will turn back on.

Please remember that if you are using this code too along with some your code that does the cell / Excel calculation you can instruct your code to calculate cell/range/ application using below codes.

Execute calculation for from VBA

'application level (all workbook)
Application.Calculate

'sheet level (only specified sheet
Worksheets("SheetName").Calculate
Activesheet.Calculate

'range
Range("S1:Z900").Calculate
Range.CalculateRowMajorOrder

'ForceFullCalculation All
'If you can ensure that any dependencies within a block of formulas always refer backward to cells to the left or above, the Range.CalculateRowMajorOrder can be the fastest calculation method in Excel on a single processor system.

Workbook.ForceFullCalculation


'Calculate All
'ctrl + alt + f9
Application.CalculateFull

'CalculateFullRebuild
'ctrl + shift + alt + f9
Application.CalculateFullRebuild

Usage Example

Sub YourMacro()
'your declarations

'place this line "TurnOnSpeed True" before your macro execution starts
TurnOnSpeed True


'this is where all your codes / scripts will reside


'place this line "TurnOnSpeed False" where your macro execution ends
TurnOnSpeed False

Exit Sub

ErrHndler:

'place this line "TurnOnSpeed False" where your macro execution ends / exit the code due to error / for error handlers
TurnOnSpeed False

End Sub
Labels: