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.
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.