Excel-VBA-Query Is there a way to improve Excel VBA speed with a Refresh.All with a lot of query?

Question

I have a tab and few pivot table. Everything was working fine but now because I added vlookup in the tab along with few formula it make the file VERY slow. Please note that I have a connection to ODBC and SQL to pull like 30,000 row and 32 column of data.

Is there a way that I could improve performance?

I tried: uncheck the background update added a wait for query

It is still very slow... take like 15 minutes to finish and open! Any help to reduce this would be appreciate!

Thanks,

Private Sub Workbook_Open()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

DoEvents

ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
If Not Application.CalculationState = xlDone Then
DoEvents
End If
DoEvents

    Range("A3:P3").Select
    Sheets("FRQ-4").Select
     Range("A6").Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"

    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-3").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select


    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-2").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select


    DoEvents
    Range("A3:P3").Select
    Sheets("FRQ-1").Select
        With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("STATUT")
        .PivotItems("INACTIF").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.Range("$A$3:$P$385").AutoFilter Field:=14, Criteria1:="=1", _
    Operator:=xlOr, Criteria2:="=BESOIN-ACHAT " & Chr(10) & "1=OUI 0=NON"
        Range("A6").Select
    
    Sheets("FRQ-4").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With

    Sheets("FRQ-3").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With
    
    Sheets("FRQ-2").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With
    
    Sheets("FRQ-1").Select
    With ActiveSheet.Columns("E:S")
      .HorizontalAlignment = xlCenter
    End With
    With ActiveSheet.Columns("A:D")
        .HorizontalAlignment = xlLeft
    End With

MsgBox "Ready"

End Sub

Answer 1

Methods to improve performance

(without workbook to aide, it's difficulty to bottom-out true culprit)...

Seems like you've covered most of the generic VB parts:

Application.screenupdating=falss
Application.calculationmode = xlManualClaculation

etc., so moving onto what might be the culprit*:

*(although you realise 'Doevents' will slow down the macro

---

1) VLOOKUP

1.1) Use Index-Match

I personally detest VLOOKUP and haven't used it since, like, Excel 2000 or something because I did not know any better back then. Whilst XLOOKUP looks promising, generally the case that VLOOKUP too restrictive in relation to index/match as it can only search to the right of the lookup column (setting aside exceptional cases).

Guess what? It's ALSO a lot slower than Index/Match!

See here (Kyd, n.a, with over 40k views with downloadable excel workbook/example)

"With unsorted data, VLOOKUP and INDEX-MATCH have about the same calculation times. ... With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP."

A discussion re the merits of offset/match vs. index match can (performance-wise) can be found here (Ed, 2003)

1.2) Restrict range

Whether you use VLOOKUP/INDEX-MATCH/OTHER, try to:

• Restrict the range over which is applies - e.g. don't highlight entire column if that's what you're already doing, or use a dynamic range (Cheusheva, 2021)

---

2.1) OTHER

• Re your VB - have you tried executing different parts of the program (first, 2nd, 3rd key areas)? i.e. to discover which is taking the longest?
• When does it start getting 'bogged down'? At import stage, or when doing calculations? or creating/updating the pivots
• How large is the excel file? If over 40MB this could be an issue. Save as filetype .xlsb if you're not already, you'll have same VB access and unless you're using an SQL output (you may be), you could reduce the filesize by ~40-50%!
• Given you do have a SQL connection, is there something odd / funny with your network connection more recently (if applic.?) Could you import the data into a completely separate workbook then use data import to manipulate the data in the local file (I know you could, what I mean is, try this and see if it reduces the time)
• Calculations that feed into pivots which then get 're-pivoted' could lead to substantive delays

Answer 2

If you're pulling data from SQL server, please make sure the query is selecting from a VIEW. Never ever select from a static TABLE.