PDA

View Full Version : Solved: trouble getting a progress bar working



s.schwantes
10-21-2008, 08:43 AM
Hi all,

I've got a working macro which takes a file with one pivot table in it, and builds it out to a workbook with 43 pivot tables (each pt report slices the data differently). As you might imagine, it takes roughly 25 minutes for the full routine to run.

There are several published snippets of code which illustrate an example of a progress bar using a for next loop which generates a bunch of random numbers.

In my case, I don't think i can use the for / next loop, and don't need the random nubmers.

My logic works as such:


start / show user form
user form activates & calls my "main" sub which starts building the 43 PTs
update progress routine
My logic for the counter - embedded within the main sub, is essentially counter = 1, counter = 2, up to 43.

For the percentage complete - also in the mian sub - I'm using "counter / 43".

I've encountered some circular logic errors, which I think I've resolved, but now have some simple logic or syntax errors, e.g., my pct or pct done is not defined, e.g., = "null".

Here's what some of the code looks like:

attached to the UserForm1


Private Sub UserForm_Activate()
Call Build_Pivots
End Sub


at the beginning of the build pivots sub:


' Progress Bar Code:

' Call Start ' in module 5 (currently, this is saved as a note, as i think it was circular causing an error) ...

Dim Counter As Integer
Dim PctDone As Single

' 1 - TOTAL MTL COST
Counter = 1


at the end of the routine which builds out each PT report:


'Update the percentage completed
PctDone = Counter / 43
'Call subroutine to update progress bar
UpdateProgress PctDone


If I change this to "Call UpdateProgress PctDone" I get a syntax error

Finally, here's the update progress routine:


Sub UpdateProgress(pct)

With UserForm1
'update the Caption property of the Frame Control
.FrameProgress.Caption = Format(PctDone, "0%")
'widen the label control
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With

'DoEvents allows the user form to update
DoEvents

End Sub


Please help with any ideas ASAP. I'm trying to put a wrapper on this project so I can move on to greener grass. :help

Thanks!!

Steve S.

Bob Phillips
10-21-2008, 08:51 AM
I must be missing something, but what is the question?

s.schwantes
10-21-2008, 08:56 AM
xld -

when I run my macro, it breaks giving me the following error:

run time error 94, invalid use of Null

on the following line:

.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)

Bob Phillips
10-21-2008, 09:21 AM
That is because the code uses a variable called PctDone, whereas the argument passed to the routine is pct. They should be the same.

s.schwantes
10-21-2008, 09:33 AM
hey, just noticed you're in Tx ... which part?

any way ... I'm still confused. The update progress routine is called:

UpdateProgress(pct), but the with statement uses the following:


'update the Caption property of the Frame Control
.FrameProgress.Caption = Format(PctDone, "0%")
'widen the label control
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)


what am i missing ? Do i need to change the sub name to:

UpdateProgress(PctDone) ?

BTW ... I didn't think the parentheses did anything ... e.g., some subs are just blank parens, e.g., sub name()

thanks!

SS, p.s. I'm in Austin

s.schwantes
10-21-2008, 09:39 AM
:beerchug:

Ok partner, that did the trick!

thanks a million

SS

Bob Phillips
10-21-2008, 09:50 AM
hey, just noticed you're in Tx ... which part?

Look again at the flag.

I used to live in Austin, but that was many years ago, up near Spicewood Springs Road.