0

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.

Pherdindy
  • 1,168
  • 7
  • 23
  • 52
  • 4
    *'I do not know where else to go'* - [code review excel](https://codereview.stackexchange.com/questions/tagged/excel) –  Jan 13 '18 at 04:24
  • @Jeeped Thanks should I put all my lines of code there? I have several pages long worth of VBA scripts that activate on events. – Pherdindy Jan 13 '18 at 04:26
  • I need to hire a programmer :(. Where can I find a good one for an affordable price. I am a manager and it took me 2 months to finish this payroll software. I do not think it's economical for me to code. Please do advice. We hired programmers before but their programs are absolutely garbage full of bugs and not practical to use. – Pherdindy Jan 13 '18 at 04:34
  • my guess is volatile functions and full column references in array formulas. –  Jan 13 '18 at 04:35
  • @Jeeped do you suggest I use VBA scripts instead for those that ues full column references? And remove full column references functions in the spreadsheet? – Pherdindy Jan 13 '18 at 04:35
  • 1
    [stackoverflow.com/jobs](stackoverflow.com/jobs) –  Jan 13 '18 at 04:36
  • No, some functions are not affected. Others take a huge calculation lag. –  Jan 13 '18 at 04:40
  • @Jeeped I only have one array formula I believe. In column R of the DTR worksheet. The others are index and match functions I believe even full column reference shouldn't affect it since i'm not using vlookup? – Pherdindy Jan 13 '18 at 04:42
  • A lot of programs like yours are written by non programmers - then one day they become a *Line Of Business* app. This is normal and good. See this post for VBA tips and the link at the end for Excel specific tips. http://stackoverflow.com/questions/29596432/pointers-needed-for-speeding-up-nested-loop-macro-in-vba/29597193#29597193 PS Your program should have been an Access program. Access is a database, but Excel - not a database, is the world's most popular program for databases. People use what they know. – ACatInLove Jan 13 '18 at 04:55
  • PS People like you are the most common type of programmer in the world. Self taught VBA programmers. – ACatInLove Jan 13 '18 at 05:09
  • Yes that's true. I just don't have the know how to use access. I believe it's too hard to re-learn. Thanks for the advice – Pherdindy Jan 13 '18 at 05:09
  • I'll see how I can hasten up my program using the link you sent. I believe VBA will give a good enough program to do complex things. I believe a gui is just too much work for a person doing managerial tasks as well.. although it would br ideal – Pherdindy Jan 13 '18 at 05:14
  • VBA is always slow - use VB.NET instead – braX Jan 13 '18 at 06:11
  • 1
    What a load of crap. VB.NET is far slower than VBA. – ACatInLove Jan 13 '18 at 06:26
  • 1
    It is slower because of array formulas, and you should get the results by value rather than taking the result as a formula. Do not assign the result to the cell one-to-one, but put the result into the array and write the result on the sheet at once to speed up the program. – Dy.Lee Jan 13 '18 at 07:13
  • @Dy.Lee I only have 1 array formula though which is on column R. Also, I do not understand what you mean by results by value rather than as formula. Do you mean I use VBA to get the values? Or do you mean after the formula computes the value, it somehow pastes as special to get the value only and remove the formula? I get what you mean by the array it was also the same as in the link posted earlier. I believe it'll make things faster. – Pherdindy Jan 13 '18 at 08:45

1 Answers1

2
=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
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
  • (My) order of merit for your persistent pleading in favour of arrays +. – T.M. Jan 13 '18 at 15:47
  • Thanks a lot for this will look into this set of codes soon. I have been a bit busy lately. I really wish I can use the program I made in the future. I was disappointed to see for the first time that excel had limits lol. – Pherdindy Jan 15 '18 at 12:06
  • I just though about it. In relation to #1, i'll just dynamically update it to be in the total number of rows in my worksheet. This way resources will be just in time. Although when my number of employees grow to over a hundred I feel the rows will reach a million in a decade's time. Lol I guess gotta keep making new spreadsheets/a legit program by then – Pherdindy Jan 18 '18 at 08:51