Solved: Excel Progress Indicator

[jwise]

I found some code on JWalk's site to do a progress indicator. I copied and adapted this code but it's still not working. I'm pretty sure it's because I can't figure a way to display the userform with the progress indicator. The macro in question is one of several, and is usually selected from the list of available macros when you use "ALT F8". If my sub does the "UserForm1.Show", then execution stops... A "catch 22" problem.

Since I don't have an event that will do this "Show", how can I handle it? Also, it would seem to me that some form of event is necessary to update the "bar", but the code seems to be doing this solely with the "With" loop.

Link to JWalk's site: http://j-walk.com/ss/excel/tips/tip34.htm

[Bob Phillips]

The design paradigm is slightly different here, you

- fire the userform

- start the logic code from the form activate event

- from within the logic code, have interrupt point and within these interrupts and update the progress bar. You have to determine where the interrupt points are depending upon your code logic.

[Ago]

i had the same problem with the progressbar i made.
you have to use UserForm1.Show (0)
and the code wont stop.

[jwise]

Thanks XLD and AGO for your suggestions.

After I understood my problem, I could see that a different approach was necessary. Thanks for providing that XLD.

I tried AGO's suggestion, and it solved the issue at hand. I should've done more research on the "SHOW". I never thought of that... until someone else did. Hopefully next time I'll use this lesson to broaden my horizon a little.

From Chip Pearson's site, I was intrigued by the notion of an "event" with an "ID number", which is much akin to a "private event" that can be used for asynchronous processes (or tasks in the mainframe world). I sort of expected an answer that used this paradigm. Unfortunately, when I read Chip's explanantion, it did not sink in. I am also confused by the "DoEvent" statement. Does it cause all "events" to be fired, or only those already waiting but just needing a time slice? Seems to me Windows would understand this requirement, i.e. whenever you create an "event", the event would get priority over the thread/process that "forked" ("spawned"?) it. Otherwise how would this ever work?

I expected to find a "DoEvent IDNumber = xxxx" to crank off this event. I did not find this. Perhaps my brain is cluttered with the IBM mainframe paradigm.

Thanks again for the suggestions.

[mdmackillop]

Check out DoEvents in VBA Help.

[jwise]

I read the Help file, but unfortunately didn't learn much from it. I have struggled with this issue, undoubtedly my background is insufficient, and I usually wind up searching the internet with keywords I pull from the help files. Maybe I get what I need 20% of the time from the help files.

I will go back and read it again. Thanks for the suggestion.

[jwise]

After getting the code to work with AGO's suggestion, I tested it many times. I was just not happy with the way it worked. I decided to look again at XLD's suggestion. After doing so, I decided that the restructuring necessary was not that difficult and that I could compare the original implementation with XLD's suggestion.

This turned out to be easier than I thought after I carefully analyzed the code. I tested the new version, and it just works better. This is very difficult to quantify, but it does work better. The earlier version seems to have delays before it puts out the userform with the initial progress indicator while the latter puts it out as expected. The latter also seems to update the progress indicator more smoothly. In retrospect, I have no idea why this is so, but there is a very noticeable difference with the latter being much more predictable. I have no idea why this is true...

The moral to this story is to begin with the correct tactic in the first place. Otherwise, you wind up with less than you expected or the rewrite task. Luckily for me, this was not very difficult.

[mdmackillop]

The moral to this story is to begin with the correct tactic in the first place
..but one learns by one's mistakes! :banghead:

[Bob Phillips]

After getting the code to work with AGO's suggestion, I tested it many times. I was just not happy with the way it worked. I decided to look again at XLD's suggestion. After doing so, I decided that the restructuring necessary was not that difficult and that I could compare the original implementation with XLD's suggestion.

This turned out to be easier than I thought after I carefully analyzed the code. I tested the new version, and it just works better. This is very difficult to quantify, but it does work better. The earlier version seems to have delays before it puts out the userform with the initial progress indicator while the latter puts it out as expected. The latter also seems to update the progress indicator more smoothly. In retrospect, I have no idea why this is so, but there is a very noticeable difference with the latter being much more predictable. I have no idea why this is true...

The moral to this story is to begin with the correct tactic in the first place. Otherwise, you wind up with less than you expected or the rewrite task. Luckily for me, this was not very difficult.

Exactly ... the hardest but most important stage in the development cycle is design, get that right and it all flows nicely thereafter.

Actually, requirements must be the mosst important, a great design on poor rqeuirements is just wasted effort.