Processing Time on Spreadsheet with Hundreds of Form Controls

[bitiumribbon]

So I pretty much probably already know the answer to this question as soon as I ask it, but I feel like asking it anyway.

I've got a spreadsheet that's been weeks in the making that is less of a spreadsheet and more of a program, really. The data entry is virtually all text, but there are hundreds of various shapes on there (per page, about 16 scrollbars, 10 buttons, ~6 checkboxes, 5 text boxes, 2 radio buttons) along with a massive buttload of VBA code to make everything run the way I want it to. Very automated, exactly the way I want it.

Now (surprise surprise) I'm running into a processing/updating delay of about one second whenever I update a cell (in any way) or pretty much perform any other action that makes a change happen or calls up a macro. I set my calculation mode to manual and the delay still happened, so it doesn't seem to be a calculation issue (and oddly, when I did run calculations manually, they were almost instantaneous). So, I'm left with the guess that when I make a change directly to a cell, all my various form control objects refresh themselves, and since there are so many, it takes time.

The actual workbook really isn't that big (~500kb).

So my question is this: am I going to be able to find a way to speed up the processing time for this workbook, or am I just gonna have to suck it up? I'll suck it up and deal with the lag if I have to because I'm still quite happy with how the thing works, but if I can chip off some of that processing lag, I wouldn't complain.

Workbook attached for reference. Thanks in advance!

NEW HowYaDid Template WIP.xlsm

[mikeTRON]

https://www.google.com/webhp?sourcei...20run%20faster

[bitiumribbon]

Thank you for replying.

Your reply comes off as very sarcastic, but I'm going to assume positive intentions here and ignore that, so if I'm wrong, I apologize.

But to be clear: I wouldn't be here if I hadn't already asked Google this question. So I'll take, for example, the first result that came up, and I'll go through the steps mentioned there. I'll also assume that you didn't actually look at the content of my workbook, so I'll go into more detail on that as well.

If we follow the steps listed http://smallbusiness.chron.com/make-excel-calculate-faster-32149.html]on this page...

Step 1: Switch to manual calculation.

Already addressed this in my original post - with calculation mode set to manual, I update a cell. I finish updating the cell. Before I have the opportunity to press F9 to calculate, I'm already experiencing the delay. Then I *do* hit F9 and the calculation process is very quick. Calculation speed isn't the issue.

Step 2: Iterative calculation

I don't have any circular references. This point is moot.

Step 3: Move everything to a single worksheet

This isn't the problem either, as all my data already IS, for the most part, on a single worksheet. More to the point, this is the template version, so there isn't any data yet. I experience no noticeable change in processing time when I do add data. And again, 80% of the contents of this sheet are text, not numerical data.

Step 4: Check for repeated calculations

This is a moot point, because as I said above, calculation speed isn't the problem. However, I don't have any calculations that are identical from cell to cell - most involve cells relative to their own positions - I use a lot of Offset functions both in and out of VBA.

If offsets are the problem, that's another story, but again: calculation speed doesn't seem to be the problem.

Step 5: Sort your columns of data

Again, this isn't typical data, and again, calculation isn't a problem.

Step 6: Remove volatile functions

I have none except for a single 'TODAY()' function on a separate worksheet. This isn't the problem either.

Step 7: Set screen update and manual calculation to VBA macros

Screen updating is the one thing here I think might be useful, but not in this context. The delay happens whether or not there are macros involved.



If I had to guess, I'd say the screen (and all the scrollbars, combo boxes and checkboxes on it) is refreshing completely (all 60 pages) every time a change is made, and I'm not sure there's anything I can do about that.


So I return to my original point: having asked Google and found nothing specifically related to lots and lots of form objects, I haven't yet come up with an answer to the actual question I'm struggling with. So, bearing all that in mind, please be less condescending if you choose to answer again. If you think there isn't anything I can do about it, just say so.

Thank you again!

[mikeTRON]

Thank you for replying.

Your reply comes off as very sarcastic, but I'm going to assume positive intentions here and ignore that, so if I'm wrong, I apologize.

Well it really is better asked to google than us because it will take some hunting and pecking on your end to try and isolate the issue.
If I was to be sarcastic I would have posted this: http://lmgtfy.com/?q=excel+spreadsheets+faster
LoL

Step 4: Check for repeated calculations

This is a moot point, because as I said above, calculation speed isn't the problem. However, I don't have any calculations that are identical from cell to cell - most involve cells relative to their own positions - I use a lot of Offset functions both in and out of VBA.

If offsets are the problem, that's another story, but again: calculation speed doesn't seem to be the problem.

It seems to be my understanding that you don't understand what a volatile formula is, because OFFSET is a volatile formula. This means that whenever you change anything, ALL of those formulas have to recalculate EVERYTIME. On top of that, every formula DOWNSTREAM of those offsets has to recalculate as well.

I like offset in small doses, but if I have a ton of them, I tend to restructure the worksheet or workbook to avoid them because that has caused this EXACT issue for me before. With that being said, I cannot confidently tell you if THAT is the only problem here.

Tip: create a copy of this file so you can break it, and delete all of the offset formulas and see if the issue goes away.

Step 6: Remove volatile functions

I have none except for a single 'TODAY()' function on a separate worksheet. This isn't the problem either.

See Above and the following link from Chandoo.
http://chandoo.org/wp/2014/03/03/han...-are-dynamite/

The fact that each and every cell ‘downstream’ of any volatile formulas get recalculated is an important point to get your head around. Many people think that slow calculation times due to volatility is due to the time it takes to recalculate large amounts of volatile functions in a model. But often most of that delay is in fact due to the recalculation of all the cells ‘downstream’ from those volatile functions. In other words, even just one volatile formula with a very long calculation chain hanging off it could cause you grief. And if that calculation chain gets more and more complex, so does the effect of that one volatile formula.

If I had to guess, I'd say the screen (and all the scrollbars, combo boxes and checkboxes on it) is refreshing completely (all 60 pages) every time a change is made, and I'm not sure there's anything I can do about that.

My hunch is still the volatile formulas.

So I return to my original point: having asked Google and found nothing specifically related to lots and lots of form objects, I haven't yet come up with an answer to the actual question I'm struggling with. So, bearing all that in mind, please be less condescending if you choose to answer again. If you think there isn't anything I can do about it, just say so.

Thank you again!

My original point (while being on my phone in between meetings at work LoL) was to direct you to all of the other possible issues because those links have helped me in the past, and it is safer for the both of us for us to assume nothing and just share information than for me to simply confirm your suspicion that is is the form controls (which I speculate is not the issue).


All in, this is how I would deal with it.

1. Read the Chandoo link.
2. Create a copy of the workbook so you can break it without damaging the original.
3. Randomly go about deleting an entire tab (maybe start with the form control one).
4. Check if the lag gets better.
5. If so, close without saving and open back up but this time ONLY delete portion of the tab and recheck.
6. If it didn't get better with deleting that tab, delete something else and keep going until things get better.

Hopefully this analogy makes sense (and this post isnt too long winded haha) but this is somewhat similar to finding a parasitic draw in an automobile. You park your car overnight and you wake up and the battery is dead. It COULD be the battery, (and usually is...but is irrelevant to this story hahaha), but if you find that there is a slow trickle being drawn off the battery you measure the draw while pulling fuses out of the fuse block one at a time. When the draw stops, you have located the problem circuit. Then you keep scoping in trying to isolate the exact issue.

Actually this is exactly the same as the lawn care sprinkler companies do when they are trying to find a leak in an underground sprinkler system. Check a spot, still full pressure. Move further down the line. Still full pressure. Move further, no pressure. Okay the problem exists between the prior spot and this spot.

[mikeTRON]

I would also remove the workbook_sheetactivate as it has an if statement then does nothing.

[bitiumribbon]

Sorry for being testy, and thank you for all this detail.

I'm going to check out that Chandoo link and try what you've suggested, but while I go about that I wanted to leave some food for thought.

You could be very right about the number of offsets (there are many, many of those...) but the one thing I keep coming back to is this: if the problem was the number of offset functions I'm using (most of which are in VBA, but some of which are in the main sheet itself, but unfortunately all of which I can't really get rid of, because so much of this program depends on 'if this cell is HERE, then update that cell THERE) then... theoretically, I would be encountering this problem *during* the calculation stage, wouldn't I? This is all happening before the cells even have a chance to calculate.

Am I being stupid on that? Tell me if I'm being stupid. But this is exactly why I turned off Automatic calculation - to test whether calculation was the problem. The result I found was that the calculations were instantaneous when I ran them, but the delay after updating the cell still happened. Am I being stupid here, or are there calculations happening before I calculate?

EDIT: The SheetActivate subroutine you're referring to is actually supposed to do something, I just needed to put it on hold for a bit while I fixed up another part of the worksheet. Its function *will* be to send the text from the textboxes on the 'meters' sheet to cells in the 'record sheet' sheet. That'll take a while, but I'm fine with that - I'm more concerned with making multiple changes to the cells in the 'meters' part of the book and having to wait forever between each change.

[mikeTRON]

I suppose my question would be, how do you know when it is recalculating? When I checked you have over 600 formulas with offset functions in them, with many of them having multiple offset functions in the same formula.

I like offset, but I have realized I need to avoid them like the plague if I am developing a tool with a lot of calculations.
So now I try and design a layout that allows me to NOT have so many calculations, and honestly I probably spend about as much time optimizing as I do just coming up with my original solutions. For example I try to avoid SUMIFS and SUMPRODUCT and instead use pivot tables to aggregate data, then a simple lookup or index to pull the data into the reporting. I will instead of offset come up with an alternate method of indicating what work I need to do and where.

[mikeTRON]

So I put the workbook in design mode on the developer tab (as in it will not fire off any code) and the lag went away.

Module 9 and 13 do not have any code, so I would delete them.

I would probably consolidate most of those modules into a small few. 1,2,3,4,7,12,15,16,17 are all one line of code or VERY short, so those would probably all get grouped. well I would group things in a logical and intuitive manner, but that is an easy start.


I also ran this on the two worksheets to just try removing the offset formulas to see if it ran any smoother:

Please Login or Register  to view this content.

[bitiumribbon]

See, this is where my general inexperience with VBA fails me.

To your points/questions:

how do you know when it is recalculating?

That's more or less what I'm trying to work out. I thought that when excel is in manual calculation mode, no calculations happen until after I press F9. Was I wrong about that?

So now I try and design a layout that allows me to NOT have so many calculations, and honestly I probably spend about as much time optimizing as I do just coming up with my original solutions.

The thing is, I'm not sure how, given what I'm trying to do, to accomplish my needs without using the offset function.

Let me give you an example. For context, the purpose of the workbook is to create assessments for students.

Each page on the sheet can potentially be an individual assessment, or a group assessment.

I cannot have a set number of groups, or members of groups, because this is a template version that needs to be able to accommodate any task I give, and I am very flexible when I do it - some students might be in groups of 2 or 3, some might work alone, on the same task.

So, I need to be able to, for example, update the criteria for the group (UserForm4). It has to a) fill the criteria on the active page (offset function used to accomplish that), b) detect how many group members there are, and then c) for each group member, update the criteria (so if there are 3 other members, update the next page, and the one after that, and the one after that.)

But I also have to be able to detect whether I'm updating the info for, say, the third member of the group (in which case it needs to fill in the one below, and the two pages before).

If I can accomplish all that without using offsets, I am more than happy to, but I am not experienced enough to know how to do that and still keep the flexibility/automation I've got going. So much of the program depends on getting the relative position of the cells I'm working with.

I would probably consolidate most of those modules into a small few.

Good point, will do.

logical and intuitive manner

Funny thing is, that hasn't been a huge issue for me because up until now, I've figured I'm the only one who will be seeing or modifying it. And I'm not enough of a programmer to know what constitutes 'logical and intuitive' because I just don't have the experience. This workbook represents the very first steps I've ever taken into messing around with VBA.

Finally: that code you ran - I'll certainly check it out on my own but I'm curious about what the result was on your end, if you're willing to share.


Thanks again!

[bitiumribbon]

Update: I ran the code you provided (cleared all the offset formulas) and I didn't notice any change in the lag. Although, funny thing, on the original (ie unchanged) workbook, the lag actually isn't as bad now, and I don't know why. I haven't really done anything to it.

EDIT: Bonus update, I got the same lag in design mode that I got out of design mode.