VBA calculation takes too long

[Akbarov]

Hey Dear community,

I am using VBA+ formulas to analyz sales data ( about 1000,000rows ) it takes 7-8 minutes for calculation, i wanna know if there any other way to make it faster? because i need to calculate this report daily.

I use following VBA for 80-90 different ranges on that report.

Sub Bonaqua()    Dim Markets As Worksheet
    Set Markets = Sheets("sheet4")
    Sheets("DATA").Range("A:A").Name = "urun"
    Sheets("DATA").Range("L:L").Name = "sifaris"
    Sheets("DATA").Range("M:M").Name = "Printed"
    Sheets("DATA").Range("E:E").Name = "musteri"
    Sheets("sheet4").Range("AP:AP").Name = "bayi"
    Markets.Range("c1:c20").Name = "MARKET"
    With Sheets("DATA").Cells(5, "V")
    .FormulaArray = "=sum(if((isnumber(match(urun,market,0)))*(sifaris>0)*(urun<>"""")*(not(isnumber(match(musteri,bayi,0)))),printed))"
    .Value = .Value
End With
End Sub

 

[Michael M]

Reduce the size of your named ranges

Sub Bonaqua()
Dim Markets As Worksheet, lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
Set Markets = Sheets("sheet4")
    Sheets("DATA").Range("A1:A" & lr).Name = "urun"
    Sheets("DATA").Range("L1:L" & lr).Name = "sifaris"
    Sheets("DATA").Range("M1:M" & lr).Name = "Printed"
    Sheets("DATA").Range("E1:E" & lr).Name = "musteri"
    Sheets("sheet4").Range("AP1:AP" & lr).Name = "bayi"
    Markets.Range("c1:c20").Name = "MARKET"
    With Sheets("DATA").Cells(5, "V")
    .FormulaArray = "=sum(if((isnumber(match(urun,market,0)))*(sifaris>0)*(urun<>"""")*(not(isnumber(match(musteri,bayi,0)))),printed))"
    .Value = .Value
End With
End Sub

 

[frabulator]

You can also try screenupdating. When screenupdating is disabled VBA code (typically) moves faster because it has less to update on the screen.

Application.ScreenUpdating = False

'do something here

Application.ScreenUpdating = True

Something else you can try, which I have had varied success with, is called Do Events. Do Events let you operate Excel while other processes are going on in the background. While it may, or may not, 'speed up' the process it does give the user some freedom as Excel doesnt stay in a non-responsive state while the Sub is running.

Here are some documentation on DoEvent

Microsoft URL - Wellsr - AutomateExcel

 

[Akbarov]

You can also try screenupdating. When screenupdating is disabled VBA code (typically) moves faster because it has less to update on the screen.

Application.ScreenUpdating = False

'do something here

Application.ScreenUpdating = True

Something else you can try, which I have had varied success with, is called Do Events. Do Events let you operate Excel while other processes are going on in the background. While it may, or may not, 'speed up' the process it does give the user some freedom as Excel doesnt stay in a non-responsive state while the Sub is running.

Here are some documentation on DoEvent

Microsoft URL - Wellsr - AutomateExcel

Thank you will try