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

Before anything, see this [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/1422451) (especially first and second top answer). — Parfait, May 31 '21 at 01:54
ok will do but the slow part is from updating the formula and vlookup in the data sheet, the select part for pivot table is the last part and high speed. However, I will change it to prevent problems — DLP, May 31 '21 at 02:04
Why are you calling multiple `DoEvents`? Use this with caution and mainly for background/external needs. See Remarks on [MSDN docs page](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/doevents-function). — Parfait, May 31 '21 at 02:31
Usually using DoEvents to make sure every part of code has time to finish until doing the remaining code. Anyway, I have removed all DoEvents and it is still very slow and still on the vlookup / formula on over 30K row of data retraived from SQL/ODBC query. If I remove all the vlookup it is blasing fast. so the problem is about the formula updating with the data retreived... — DLP, May 31 '21 at 02:50
Vlookups are going to be slow with that many rows. If you can find a way to preload that lookup into a dictionary (will take initial time to load instead, but will only have to be done once) then it will probably be much faster. — braX, May 31 '21 at 02:59
If I remove Application.Calculation = xlCalculationManual it is much faster but take all CPU. Take like 2 minutes instead of 15 minutes but went from 8% cpu to 90%. It will crash all user from the server if I'm taking 90% cpu 16 cores. Any way to reduce CPU usage or just from the option to select multitread and use less cores for Excel? I really need to do this 1 step: open the file and it generate the pivot tables from data. 2 minutes is better than 15 but cpu is now the issue! — DLP, May 31 '21 at 03:24
is index / equiv faster than the double vlookup for processing 30k row? — DLP, May 31 '21 at 03:27
Found code to make a dictionary and doing vlookup faster however it does not work when reference is on another tab. quick fix? https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup — DLP, May 31 '21 at 03:56
I believe the ActiveWorkbook.RefreshAll will also re-fetch the data from your source and after that will apply the pivot refresh. if you are linking the tables with some key in powerquery, it is also very slow, try to do the links in the database — usmanhaq, May 31 '21 at 06:22
Please show `vlookup` and *few formula* which can run in SQL query. Even show SQL query. — Parfait, May 31 '21 at 13:35
=IFERROR(VLOOKUP([@CodeProduit],'DERNIERE-RECEPTION'!B:C,2,TRUE),VLOOKUP([@CodeProduit],'DERNIERE-RECEPTION'!B:C,2,FALSE)) =IFERROR(VLOOKUP(A2,'DERNIERE-RECEPTION'!B:G,5,TRUE),VLOOKUP(A2,'DERNIERE-RECEPTION'!B:G,5,FALSE)) =IFERROR(VLOOKUP(A2,'DERNIERE-RECEPTION'!B:G,6,TRUE),VLOOKUP(A2,'DERNIERE-RECEPTION'!B:G,6,FALSE)) — DLP, Jun 01 '21 at 21:01
Without these 3 vlookup the query populate the Excel file in few seconds. When adding these columns it make it very very slow, like 3 minutes with 97% CPU usage that freeze everything or when I add the Application.calculationmode = xlManualClaculation it is smooth up to 20% max cpu (mainly 7-8%) but takes 15 minutes to fully process. Please note that I have 38 columns and over 30k rows, using 16 cores 85GO. I know that I can use less cores but it is not the way to go. — DLP, Jun 01 '21 at 21:02

score 0 · Answer 1 · answered May 31 '21 at 06:39

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

The problem is the refresh.all and all the vlookup into the data sheet that is populated from sql. I think it get slowly updated because of more than 30k vlookup x 3 columns, mean 90k vlookup to update then update pivot table. — DLP, Jun 01 '21 at 01:15
Found the vbalookupcode from https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup CTRL+SHIFT+ENTER to validate code but I cannot make it work. {=vbalookup(A2:A2;sheet2!B3:C99999;2)} A2 = 001-00108 SHEET2 B3 = code list (ie 001-00108) SHEET2 C3 = date so the vbalookup has to find the LAST date Please note that code list has been sorted and last duplicated code = last date — DLP, Jun 01 '21 at 01:26

score 0 · Answer 2 · answered Dec 20 '22 at 07:33

0

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.

answered Dec 20 '22 at 07:33

realbenny

1

This answer does not address the OP's question and has misleading advice. – Vijay Varadan Dec 23 '22 at 21:34

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

2 Answers2

Methods to improve performance