How can I apply loops and if statements to this code to speed it up?

Question

I am trying to avoid using application as it is making my code slow

Application.Calculation = xlAutomatic
Application.Calculation = xlManual

Application.ScreenUpdating = True  
 
Application.Calculation = xlAutomatic
Application.Calculation = xlManual

Application.ScreenUpdating = True


End Sub```

There's another community called [code review](https://codereview.stackexchange.com/) which was created for these kind of questions — Sgdva, Sep 06 '22 at 17:48
Pretty much mandatory reading in the VBA world: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Sep 06 '22 at 17:54
You apply the property directly to the object like `cells.entirecolumn.hidden=false`. — findwindow, Sep 06 '22 at 17:55

score 2 · Accepted Answer · answered Sep 06 '22 at 20:20

Here's one way to re-work it. I'm not sure it's going to be much faster though.

The sheet names go in an array, and you loop over that array.

Sub Test()

    Dim v, arr, shtName, wb As Workbook, wsDash As Worksheet, xmax As Long, x As Long, m
    
    xmax = 23 'Number of listed utilities
    
    arr = Array("Reliability standards", "Targets", "Data quality scores", _
                "Utility scale RoS results", "Electricity Access Indicators", _
                "Customer Service Standards", "CSI Visualisation data", _
                "RoS Visualisation data")
    
    Set wb = ThisWorkbook ' or ActiveWorkbook
    Set wsDash = wb.Worksheets("Dashboard")
    GoFast
    
    For x = 1 To xmax 'For each utility
        If wsDash.Cells(7 + x, 8) = True Then 'Checks if the utility checkboxes are selected
            v = wsDash.Cells(7 + x, 7).Value  'value to match on
            For Each shtName In arr
                With wb.Worksheets(shtName)
                    m = Application.Match(v, .Range("5:5"), 0) 'find a match (no `.WorksheetFunction`)
                    If Not IsError(m) Then    'got a match?
                        .Cells(5, m).EntireColumn.Hidden = False 'Unhide selected utility
                    
                        Select Case shtName 'extra actions for couple of sheets
                            Case "CSI Visualisation data", "RoS Visualisation data"
                                .Cells(5, 50 + m).EntireColumn.Hidden = False
                        End Select
                    Else
                        MsgBox "No match for '" & v & "' on sheet '" & shtName & "'"
                    End If
                    .Activate
                    Application.GoTo .Range("A1"), scroll:=True
                End With
            Next shtName
        End If
    Next x
    
    wsDash.Activate
    GoFast False ' turn off optimizations
End Sub

'maximize code speed by turning off unneeded stuff
'******** must reset !!!!
Sub GoFast(Optional bYesNo As Boolean = True)
  With Application
      .ScreenUpdating = Not bYesNo
      .Calculation = IIf(bYesNo, xlCalculationManual, xlCalculationAutomatic)
  End With
End Sub

How can I apply loops and if statements to this code to speed it up?

1 Answers1