Progress Bars, Optimization, Time Study

**msanford88** · 11-15-2012, 10:55 AM

Hello all,
I am new to the forum as well as to Visual Basic. I am working on a project involving crunching a medium-sized data set (1600 - 10800 values). I am experiencing greatly increased run time and have conducted a small time study to determine where the increase in time is coming from. To see the full code I am working with, please see the macro-enabled sheet attached to this post.

The code itself is broken into three stages, three loops that manipulate the data one stage at a time. The bare-bones code takes long enough to run that I have added a progress bar as well as a displayed "percentage complete" so the user is not left in the dark however, I have noticed that these items have added a significant amount of processing time. The time study that I completed is found in the "Diag" sheet of the attachment. The gist of my findings is that although both the progress bar and percentage display add time to processing, the progress bar adds significantly more time however, it primarily affects stages one and three.

I am asking for your help to determine why it is causing so much excessive processing time and to improve the code in order to retain the features but reduce processing time. I am not asking for you to complete my work, just your guidance. The current data set is just shy of 1800 values however future data sets may contain up to 10800 values.

Instructions / Notes for using the attachment:
Click "Clear Results" before clicking "Compile" (As a result of writing this sentence, I will force the Compiling program to Clear Results before running. I will not be adding this before I upload.)
Do NOT hit "Clear All" - this will wipe the imported data.
I have commented out the timing feature - if you wish to replicate my time study you will have to re-enable this.
Both the progress bar and the percentage are active - they will need to be commented out if desired.

Computer Specs:
Windows XP 32bit Service Pack 3
Intel Core 2 Duo E7500 @ 3.02 GHz
3.50 GB RAM

I apologize for the messy code, I am learning Visual Basic as I go. If you have suggestions for other parts of the code, I would more than welcome your feedback! I can post snapshots of the code if needed, but I think that seeing the overall document will provide a better perspective.

Thanks!
Mike

Edit: It appears that my file size is larger than allowed - I will see if I can host the file elsewhere.

Second Edit: I have uploaded it to my Google Docs, Link

For Example, Stage 1 looks like this:

Please Login or Register  to view this content.

Stage 2:

Please Login or Register  to view this content.

Stage 3

Please Login or Register  to view this content.

**Kyle123** · 11-15-2012, 12:08 PM

Ok, you're trying to solve a problem that doesn't exist.

I can make your code execute < 1 second by adding this to the start of the sub:

Please Login or Register  to view this content.

and this to the end:

Please Login or Register  to view this content.

and commenting out all your updating code. The reason your cod eis slow is that each time you change something on a worksheet, excel has to update the screen - screen updating turns this off. Setting calculation to manual turns off calculation until the end of the sub, otherwise the sheet calculates for every formula your sub enters. - I don;t know how long this takes - I couldn't be bothered waiting for it to finish

**msanford88** · 11-15-2012, 12:25 PM

Thanks for your help. I understand the screen updating and the calculations... unfortunately, I wasn't able to use the code you suggested at the very beginning of the sub as it breaks stage 2 of the code and causes the results not to be calculated correctly. I was able to use the code you suggested between stages two and three which did speed up the code immensely.

Thanks again!