Why is my Excel with VBA very slow?

Question

I know this post will get a lot of hate and thumbs down, but I do not know where else to go for someone to see why my excel is acting the way it is. If there is a forum for this, please do tell me so I will not put "unorthodox" posts in the future.

Details on my issue: I created a almost fully automated payroll excel file with formulas and VBA codes that activates on Worksheet deactivate events. However, I do not have cell change events, but even then, if I type on a cell even if it does not have a formula or VBA scripts, my excel will go into processing mode and hang my PC completely for 10-15 minutes. When the scripts activate, they may sometimes also freeze my PC for the next 10-15 minutes, and sometimes it will do so in a few seconds. I cannot alt+tab, I cannot break the scripts, I cannot exit the excel file, and I cannot open the task manager. I cannot paste my code here because it does not seem to be a code issue and it is too long.

70% of my code is in Worksheets("DTR") object 20% of my code is in Worksheets("Payroll Update") object 10% of my code is in Worksheets("DTR Summary"), Worksheets("Payroll Summary"), and ThisWorkbook.

Can anyone comment on the codes? I do not have extremely demanding scripts that should crash the file. I do not understand why it does though. Please refer below for the file I would really appreciate any advice to make things run faster.

Answer 1

=SUMIFS(DTR!$AB$2:$AB$1048576,DTR!$C$2:$C$**1048576**,">="&$A6,DTR!$C$2:$C$1048576,"<="&$B6,DTR!$B$2:$B$1048576,'Payroll - Extra'!Q$1)

1. 1048576 is too big. You have to resize it.
2. Because SUMIFS is an array formula, you only need to enter the value in the cell, not the formula.
   • With array formulas, every time a cell value changes, all the formulas in the sheet are recalculated, which slows the system up while consuming system resources.

3.Using variant array is faster. test() is that assign the result to the cell one-to-one, the code is slow. This code takes 5.33 seconds on my computer.

Sub test()
    Dim i As Long, j As Long
    Dim s, e, t
    Cells.Clear
    s = Timer
    For i = 1 To 10000
        For j = 1 To 20
            Cells(i, j) = i
        Next j
    Next i
    e = Timer
    t = e - s
    Range("v1") = t
End Sub

The following code( test2() ) uses an array variable and takes only 0.5 seconds.

Sub test2()
    Dim i As Long, j As Long
    Dim vArray(1 To 10000, 1 To 20)
    Dim s, e, t
    Cells.Clear
    s = Timer
    For i = 1 To 10000
        For j = 1 To 20
            vArray(i, j) = i
        Next j
    Next i
    Range("a1").Resize(10000, 20) = vArray
    e = Timer
    t = e - s
    Range("v1") = t
End Sub