Solved: trouble getting a progress bar working

[s.schwantes]

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

[VBA]
Private Sub UserForm_Activate()
Call Build_Pivots
End Sub
[/VBA]

at the beginning of the build pivots sub:

[VBA]
' 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
[/VBA]

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

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

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

Finally, here's the update progress routine:

[VBA]
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
[/VBA]

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

Thanks!!

Steve S.

[Bob Phillips]

I must be missing something, but what is the question?

[s.schwantes]

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]

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]

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:

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

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]

Ok partner, that did the trick!

thanks a million

SS

[Bob Phillips]

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.