CODEWITHSUNDEEP
excelvba
Nick
Nick

Reputation: 162

How can I make my VBA code run faster and not make the screen flicker?

I have written below code, which works as intended. But I could probably make it more efficient, I believe that I've avoided using "select", but is there something else I should try to avoid?

The macro takes a couple of seconds to run, is there any way to make it quicker? When I run it, the screen "flickers" quite a lot, is there something I can do about that? The file is quite big, so maybe introducing something that stop calculations?

Sub ActivateUser(User As String)
    Call LoadVariables
    
    Call UnProtectSheet("Master")
    LoadedUser = User
    
        Dim Cell As Range
        On Error Resume Next
        For Each Cell In ShowSheets
            Call UnProtectSheet(Cell.Value)
            Worksheets(Cell.Value).Visible = IIf(Cell.Offset(1, 0) = "YES", True, False) 'moving down one row to check if it should be showing or not,
                If Cell.Value = "Currencies" Then ' This is done to avoid hiding column A in the "currencies" sheet
                    Else
                    Worksheets(Cell.Value).Range("A:A").EntireColumn.Hidden = IIf(Cell.Offset(1, 0) = "YES", True, False) 'Showing or hiding column A
                End If
            Worksheets(Cell.Value).Range(FurthestColumn).EntireColumn.Hidden = IIf(Cell.Offset(1, 0) = "YES", False, True) 'Showing or hiding all other columns
                If (User = "AdminEdit") Or (User = "adminedit") Or (User = "Adminedit") Or (User = "adminEdit") Then
                    Else
                    Call HideSpecificColumns(Cell.Value)
                    Call ProtectSheet(Cell.Value)
                End If
        Next Cell
    

        Dim ws As Worksheet
        For Each ws In ActiveWorkbook.Worksheets
            ws.Activate
            ActiveWindow.ScrollColumn = 1
            ActiveWindow.ScrollRow = 1
        Next ws
    
    
        Call UnProtectSheet("Basic Data")
        FirstTimeOpenToday = "Yes"
        FirstTimeOpen = "Yes"
        Call ProtectSheet("Basic Data")
        
        Call HideSheet("LoginPage")
        
        
    Sheets("Quotation").Select
    Range("B1").Select
End Sub

Sub HideSpecificColumns(ws As String)
    Dim Fixed1Sub As String
    Dim Fixed2Sub As String
    Dim SubToCall As String
    
    'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Fixed1Sub = WorksheetFunction.Substitute(ws, " ", "")
    Fixed2Sub = WorksheetFunction.Substitute(Fixed1Sub, "-", "")
    SubToCall = Fixed2Sub & "Columns"
    
    Application.Run SubToCall
End Sub

This is how I set "Global variables". So I only have to change these things in one place.

Global ShowSheets As Range
Global LoadedUser As Range
Global UsertypeListBoxNamesFirstValue As Range
Global UsertypeListBoxNamesCount As Integer
Global FurthestColumn As String
Global LockPassword As String
Global CombinationRange As Range
Global CustomerPlace As Range
Global ProjectNamePlace As Range
Global DesignNumberPlace As Range
Global SalesPersonPlace As Range
Global YourNamePlace As Range
Global FirstTimeOpen As Range
Global FirstTimeOpenToday As Range
Global BuildNumber As Range


Sub LoadVariables()

'General
'======================================================================================================================================================
    LockPassword = "Admin123"
    Set CombinationRange = Worksheets("Master").Range("HS8:HS20") ' Range whereas all correct login are to activate users.
    Set FirstTimeOpen = Worksheets("Basic Data").Range("S4") ' Displays "Yes" or "No" depending on if this is the first time the files is opened
    Set FirstTimeOpenToday = Worksheets("Basic Data").Range("S7") ' Displays "Yes" or "No" depending on if this is the first time the files is opened today.
    Set BuildNumber = Worksheets("Basic Data").Range("N193")
'======================================================================================================================================================




'Macro to show/hide sheets based on User
'======================================================================================================================================================
    Set ShowSheets = Worksheets("Master").Range("GR19:HL19") 'Pick the range with the name of the sheets, the range above the yes or no. (For the macro to hide and show sheets based on User)
    Set LoadedUser = Worksheets("Master").Range("HN22") ' Cell with the name of the loaded user.
    FurthestColumn = "B:KE" ' When not open, this field is what is hidden on every sheet, must be larger than the largest one.
'======================================================================================================================================================





'Loginsheet
'======================================================================================================================================================
    Set UsertypeListBoxNamesFirstValue = Sheets("Master").Range("HO10") ' This should be the first cell that has a value of the list of 'User' to login with.
    UsertypeListBoxNamesCount = Sheets("Master").Range("HO19") ' This should be the cell with the count of how many users there are.
'======================================================================================================================================================





'StartPage
'======================================================================================================================================================
Set CustomerPlace = Sheets("Quotation").Range("D5")
Set ProjectNamePlace = Sheets("Quotation").Range("I2")
Set DesignNumberPlace = Sheets("Quotation").Range("I4")
Set SalesPersonPlace = Sheets("Quotation").Range("I5")
Set YourNamePlace = Sheets("Quotation").Range("I6")
'======================================================================================================================================================

End Sub

This is some of my "general" formulas I use.

'Unprotecting and protecting sheets
'===================================================================
Sub UnProtectSheet(Sheet As String)
    Call LoadVariables
    Dim ws As Worksheet
    Set ws = Sheets(Sheet)
    ws.Unprotect LockPassword
End Sub

Sub ProtectSheet(Sheet As String)
    Call LoadVariables
    Dim ws As Worksheet
    Set ws = Sheets(Sheet)
    ws.Protect LockPassword, AllowFiltering:=True
End Sub
'===================================================================


'Hiding and unhiding sheets
'===================================================================

Sub UnHideSheet(Sheet As String)
    Worksheets(Sheet).Visible = True
End Sub

Sub HideSheet(Sheet As String)
    Worksheets(Sheet).Visible = False
End Sub
'===================================================================



'Opening StandardUserforms
'===================================================================
Sub OpenLoginForm()
    Loginform.show
End Sub
Sub OpenStartForm()
    Startpage.show
End Sub
'===================================================================


'Testing Attempts to login
'===================================================================
Function UnlockWall(Combination As String) As Boolean
    Dim Key As Integer
    Call LoadVariables
    UnlockWall = False
    
    If Combination = "/" Then
        Exit Function
    End If

    On Error Resume Next
    Key = WorksheetFunction.Match(Combination, CombinationRange, 0)
    
    If Key = 0 Then
        Else
        UnlockWall = True
    End If
End Function
'===================================================================


'First time opening and logging in to the workbook, do this:
'===================================================================
Sub PopulateWithStartPage(A As String, B As String, C As String, D As String, E As String)
Call LoadVariables

    Call UnHideSheet("Quotation")
    Call UnProtectSheet("Quotation")
    YourNamePlace = A
    ProjectNamePlace = B
    DesignNumberPlace = C
    SalesPersonPlace = D
    CustomerPlace = E

    
    Call UnHideSheet("Basic Data")
    Call UnProtectSheet("Basic Data")
    FirstTimeOpen = "Yes"
    FirstTimeOpenToday = "Yes"
    Call ProtectSheet("Basic Data")
    
End Sub
'===================================================================

I do also have these subs, which I use to hide specific columns on each sheet. I've not included all of these, but one sub exist per sheet I'm looping through in the "activateuser sub".

Sub MasterColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    'exit sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
    
    Dim ColumnArray(6) As String 'The number within the "()" is the maximum amount of column that can be hidden. May not be lower then used below.
    
    ColumnArray(0) = "D:G" ' Change as per columns you want hidden, note that it has to be in the "X:X" format.
    ColumnArray(1) = "J:M"
    ColumnArray(2) = "P:S"
    ColumnArray(3) = "W:AJ"
    ColumnArray(4) = "AM:AP"
    ColumnArray(5) = "AS:AW"
    'ColumnArray(6) = ""
    'ColumnArray(7) = ""
    'ColumnArray(8) = ""
    'ColumnArray(9) = ""
    'ColumnArray(10) = ""
    'ColumnArray(11) = ""
    'ColumnArray(12) = ""
    'ColumnArray(13) = ""
    'ColumnArray(14) = ""
    'ColumnArray(15) = ""
    'ColumnArray(16) = ""
    'ColumnArray(17) = ""
    'ColumnArray(18) = ""
    'ColumnArray(19) = ""
    'ColumnArray(20) = ""
    
    Dim i As Integer
    For i = 0 To 5 ' ================== This has to be one less than above number
        Sheets("Master").Range(ColumnArray(i)).EntireColumn.Hidden = True '=========== Change to correct sheet
    Next i
End Sub


Sub BasicDataColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Exit Sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
End Sub
Sub ErrorFinderColumns() 'Can't contain any special characters, i'm only taking "-" and " " into account now.
    Exit Sub 'Turn on/off depending on if the sheets includes any columns that should be hidden
End Sub

Upvotes: 1

Views: 1922

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The macro takes a couple of seconds to run, is there any way to make it quicker?

In that case, my advise is not to touch your macro. If it ain't broke, don't fix it. Most of us actually thrive to reach this "ecstatic state" :D

If it is just a learning excercise then yes, you can make these few changes and it will optimize your code. Not in terms of performance, but in terms of writing concise code. You can obvioulsy take care fo few minor tweaks like flicker etc.

Here are my suggestions

  1. Avoid the reckless use of On Error Resume Next. It is like telling Excel to shut up :). Instead do proactive programming. Pre-empt and handle the errors.

  2. Switch off Events. However, whenever you are switching off events, use error handling to turn it back on, else if you get an error, the code will not run the next time. Also ensure that you do not tinker with user settings. Change as you need and then reset them when done as @PaulOgilvie suggested. To avoid the flicker you can use Application.ScreenUpdating = False

    Here is an example

    Sub Sample()
    On Error GoTo Whoa

    Dim PrevCalcMode As Integer
    Dim PrevScrUp As Boolean

    '~~> Get previous settings
    PrevCalcMode = Application.Calculation
    PrevScrUp = Application.ScreenUpdating

    '~~> Set your new settings
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    '
    '~~> Your code
    '

Letscontinue:
    '~~> Reset to original settings
    Application.EnableEvents = True
    Application.ScreenUpdating = PrevScrUp
    Application.Calculation = PrevCalcMode
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

  3. Instead of using Global variable and then loading the range into those variable, use Named Ranges

    For example, you do not need Set CombinationRange = Worksheets("Master").Range("HS8:HS20"). You can name the range HS8:HS20 in Master as CombinationRange as shown in the link above and then use Range("CombinationRange") in your code.

  4. Instead of creating a Global variable Global LockPassword As String and then setting it's value LockPassword = "Admin123", directly declare it as Public LockPassword As String = "Admin123" at the very top.

  5. Avoid calling procedures unnecessarily. It increases execution time. For example you are calling Call LoadVariables in almost every sub.

  6. Use With End With. I will not go into details here. @Moosli has already covered that.

Upvotes: 2

Moosli
Moosli

Reputation: 3275

There are a few rules for VBA that you can use to make your code faster. And which you should always have in mind when writing new code.

Rule #1. Don't Copy and Paste

The Copy and Paste (or PasteSpecial) functions are slow. It is about 25 times faster to use the following to copy and paste values.

Range("A1:Z100").value = Range("A101:Z200").value

If you are doing it this way your Code will Probably work. There is maybe a problem with the Mamory if your are doing this on to many Rows.

Rule #2. Calculation

Normally, Excel will recalculate all cells, formulas and conditional formatting if a value in a cells or ranges have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method like Worksheets(1).Calculate.

Rule #3. ScreenUpdating

As the colleagues have already mentioned in the comments, you can already get a lot out of this command.

The Problem with VBA is, every time VBA writes data to the worksheet it refreshes the screen image that you see. Refreshing the image is a considerable drag on performance. The following command turns off screen updates.

Application.ScreenUpdating = FALSE

At the end of the macro use the following command to turn screen updates back on.

Application.ScreenUpdating = TRUE

Rule #4 Ignore Events

If you have a Worksheet_Change event implemented for the Sheet1 of your workbook. Any time a cell or range is altered on the Sheet1, the Worksheet_Change event will run. So if you have a standard macro that manipulates several cells on Sheet1, each time a cell on that sheet is changed, your macro has to pause while the Worksheet_Change event runs. You can imagine how this behavior would slow down your macro.

Application.EnableEvents = False

At the end of your code, you can set the EnableEvents mode back to True with the statement:

Application.EnableEvents = True

Rule #5 With statement

When recording macros, you will often manipulate the same object more than once. You can save time and improve performance by using the With statement to perform several actions on a given object in one shot.

The With statement utilized in the following example tells Excel to apply all the formatting changes at one time:

With Range("A1").Font
.Bold = True
.Italic = True
.Underline = xlUnderlineStyleSingle
End With

Exmaple for the With Statement:

Sub Normal()
    Application.ScreenUpdating = False
    
    Dim StartTime As Double
    Dim MinuteElapsed As String
    StartTime = Timer
    For i = 1 To 100
        For y = 1 To 500
            ActiveSheet.Cells(i, y).Value = i * y
            ActiveSheet.Cells(i, y).Font.Bold = True
            ActiveSheet.Cells(i, y).Font.Size = 16
            ActiveSheet.Cells(i, y).Interior.Color = RGB(255, 255, 0)
        Next y
    Next i
    MinuteElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    Application.ScreenUpdating = True
    MsgBox (MinuteElapsed)
End Sub

This Code takes about 18 Seconds with my Computer.

Sub WithStatment()
  Application.ScreenUpdating = False
    Dim StartTime As Double
    Dim MinuteElapsed As String
    StartTime = Timer
    
    For i = 1 To 100
        For y = 1 To 500
        With ActiveSheet.Cells(i, y)
            .Value = i * y
            .Font.Bold = True
            .Font.Size = 16
            .Interior.Color = RGB(255, 255, 0)
        End With
        Next y
    Next i
    MinuteElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    Application.ScreenUpdating = True
    MsgBox (MinuteElapsed)
End Sub

The Same Code with the With Statment Takes about 15 to 16 Seconds on my Computer.

Getting into the habit of chunking actions into With statements will not only keep your macros running faster but also make it easier to read your macro code.

Upvotes: 2

Related Questions