Showing Progress for Multi-Stage Processes

[Greg Truby]

Howdy folks,

I'm in the middle of a rather large (months-long) project, so I haven’t spent much time on the boards lately. One aspect of many projects is keeping the user informed of progress while processes are executing. Quite a while back I had written my own version of Walkenbach’s userform-with-label as progress indicator. Basically, I'd added more bars (labels if you want to be technical) to Walkenbach's form so that I could track progress on a half-dozen major processes as well as having a bar for sub-processes. However, I found adapting my multi-bar form to use in other projects was cumbersome. While trying to recycle it into my current project I got fed up enough with it to try using a UserForm as a class module (I remember reading somewhere that this is really what they are…) Anyhow, it seems to work pretty well as a class module. And now I've got a progress-indicator tool that's pretty easy to drop into new projects.

So, I've cleaned it up reasonably well [500+ lines of code (thus far)] and would be willing to share it if'n it'd be of interest to anyone. So anyone interested?

 

[powerpackinduo]

Thanks for taking the time to do all of that for me.
I've started to try it and I was going to go line by line...
But I came across a problem at the first line when I stepped into it.

Public frmProg As ufxl_ProgressIndicator

Generates:
Compile Error:
User-defined type not defined

I was going to comment that out and keep going... but that didn't work.
All I did was cut and paste your code as shown in your post and go from there... Also I'm using Excel 2000.

Can you tell me what's wrong?

 

[Greg Truby]

That would be because you need to have a copy of the userform in your project before you can declare a variable to be of that object type. (The same holds true for ClassModules too.) Assuming you have the downloaded workbook of mine open in Excel, then the simplest way is to just use the Project Explorer window in the VB Editor and drag the item from one project to another:

(Before)

(click thumbnail image for larger view)


(click thumbnail image for larger view)

 

[Greg Truby]

One suggestion that I would give would be that if you are going to step through the code, excluding the call to Show, and certainly when in Main, I would step through using Shift+F8 instead of just the F8 key otherwise you are going to end up diving in and out of the class module itself. Once you have walked through using Shift+F8, you might consider going back and using just the F8 key to see everything that's going on, but you might find it a tad perplexing at first, especially if you have not played with Class Modules very much.

 

[powerpackinduo]

This is quite the crash course. LOL
I feel completely out of my element but I'm going to get this.

This time when I try to run it:

Public frmProg As ufxl_ProgressIndicator

Generates:
Compile Error:
Private object modules cannot be used in public object modules as parameters or return types for public procedures, as public data members, or as fields of public user defined types

I dragged the ProgressIndicator into the Project and cut and pasted the code...

I've tried changing 'public' to 'private' and vice/versa in both code window and no luck.

Any more help?
Thanks for all the time you're taking, I really appreciate it.

 

[Greg Truby]

Did you see this bit in my original "how to" post?

...Note that you'd need to edit your sheet's code module so that the button's click method simply calls Main_UsingPI. All of the code below would be in a standard module...

My guess is that you are trying to put this code into the code module for the worksheet. Worksheet/workbook code modules are actually a species of class module and there are restrictions. Among them: no public variables can be declared in a class module. Is that our error?

^edit If that's not our error, then I'll need to find someone with XL2000 to test this 'cause we may be running into a version issue. _/edit

 

[powerpackinduo]

Hey Greg,
You're correct I did not 'read' LOL after I put it in the Standard Module it worked just fine.

I do have one problem with it that I haven't been able to figure out and I've been trying to walk through it with your xls example also where it works fine.
When the code is finished the progress indicator stays on the screen and the last sub process stay at 50% yet the Total Progress shows complete.
It stops at the last line of the following code and doesn't do anything:

Private Sub UserForm_Activate()
'....code truncated for space...
                                                m_varArguments(4), _
                                                m_varArguments(5)
        Case Else:
            MsgBox "You need to add code to the UserForm_Activate event handler" & _
                    vbCr & "so that it can pass " & Me.ArgumentCount _
                    & " arguments to " & Me.ProgramName & ".", _
                    vbCritical, "Programming Error"
            Unload Me
    End Select
End Sub

Yet on your example it keeps going to the UserForm_Terminate to clear out the progress indicator. (Also displays the hello world message)
Is there some more code I need to add to the routine to have it continue?
The Hello world message is fine if that needs to pop up I can change that to something appropriate.
If you'd like me to send the code to you again I can, but it's the same as above.
Thanks for your help, this tool is awesome.

 

[Greg Truby]

Glad to hear there are no version issues.

As for the other -- that would be because you are not telling VBA to fold up the form and put it back in the drawer...

In my sample code, the "Hello World" thing was just a way to demonstrate that parameters can be passed into the MAIN() procedure and that passed-in parameters can be accessed after the form is dismissed from memory. All you need to add is simply

Unload frmProg

after you no longer need the progress indicator to be displayed. You can put this at the end of MAIN() or you can place it after the closing End With in Main_UsingPI()

 

[powerpackinduo]

That worked great.
I'm going to have so much fun playing and learning from this example.
Thanks for all of your help. It's greatly appreciated!
I Love This Board!