Efficiency in code

[DawnTreader]

Hello All

i have this code:

Public Sub refreshPivotTables()
Dim pt As PivotTable 'help
Dim Counter As Integer
Dim ptsDone As Integer
Dim PctDone As Single
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim waitTime As Long

'find the total amount of pivot tables
Counter = 0
For Each pt In ActiveWorkbook.Worksheets("Data Compilation").PivotTables
Counter = Counter + 1
ActiveWorkbook.Worksheets("PivotChartLog").Range("A" & Counter) =
Counter
Next pt

'refresh each pivot table and show progress
ptsDone = 0
For Each pt In ActiveWorkbook.Worksheets("Data Compilation").PivotTables
' pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

ActiveWorkbook.Worksheets("PivotChartLog").Range("B" & ptsDone + 1)
= pt.Name & " Started Refresh"
ActiveWorkbook.Worksheets("PivotChartLog").Range("C" & ptsDone + 1)
= Now()
' ActiveWorkbook.Save
pt.RefreshTable
ActiveWorkbook.Worksheets("PivotChartLog").Range("D" & ptsDone + 1)
= pt.Name & " Refresh Done Sucessfully"
ActiveWorkbook.Worksheets("PivotChartLog").Range("E" & ptsDone + 1)
= Now()
' ActiveWorkbook.Save
ptsDone = ptsDone + 1
PctDone = ptsDone / Counter
With frmUpdating
.lblWorkingOn.Caption = pt.Name
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
DoEvents

Next pt
'wait for msquery to finish
Application.OnTime Now() + TimeValue("00:00:03"), "UnloadfrmUpdating"
' Unload frmUpdating
ActiveWorkbook.Worksheets("Dashboard").Columns("E:E").EntireColumn.AutoFit

End Sub

and i am wondering do i need to have the DO EVENTS in there? what is it
doing? i understand that Do Events is like having a gap in the program
allowing for the OS to do other things. the question is, are there things
that the OS needs to do in my code? it is refreshing pivot tables and MS
query is involved, but i am still unsure if this is causing the loading of my
sheet to go slower.

 

[Jim Cone]

Unless you have a DoEvents inside a loop then chances are
that pressing the Escape key or Ctrl + Break would be ignored.
Meaning that you or the user can't exit the program.
I doubt that DoEvents is causing any delay.

Turning off ScreenUpdating would probably speed things up.
However that could cause problems with the progress bar display.

--
Jim Cone
Portland, Oregon USA



"DawnTreader" <[email protected]>
wrote in message
Hello All
i have this code:

Public Sub refreshPivotTables()
Dim pt As PivotTable 'help
Dim Counter As Integer
Dim ptsDone As Integer
Dim PctDone As Single
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim waitTime As Long

'find the total amount of pivot tables
Counter = 0
For Each pt In ActiveWorkbook.Worksheets("Data Compilation").PivotTables
Counter = Counter + 1
ActiveWorkbook.Worksheets("PivotChartLog").Range("A" & Counter) =
Counter
Next pt

'refresh each pivot table and show progress
ptsDone = 0
For Each pt In ActiveWorkbook.Worksheets("Data Compilation").PivotTables
' pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

ActiveWorkbook.Worksheets("PivotChartLog").Range("B" & ptsDone + 1)
= pt.Name & " Started Refresh"
ActiveWorkbook.Worksheets("PivotChartLog").Range("C" & ptsDone + 1)
= Now()
' ActiveWorkbook.Save
pt.RefreshTable
ActiveWorkbook.Worksheets("PivotChartLog").Range("D" & ptsDone + 1)
= pt.Name & " Refresh Done Sucessfully"
ActiveWorkbook.Worksheets("PivotChartLog").Range("E" & ptsDone + 1)
= Now()
' ActiveWorkbook.Save
ptsDone = ptsDone + 1
PctDone = ptsDone / Counter
With frmUpdating
.lblWorkingOn.Caption = pt.Name
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
DoEvents

Next pt
'wait for msquery to finish
Application.OnTime Now() + TimeValue("00:00:03"), "UnloadfrmUpdating"
' Unload frmUpdating
ActiveWorkbook.Worksheets("Dashboard").Columns("E:E").EntireColumn.AutoFit

End Sub

and i am wondering do i need to have the DO EVENTS in there? what is it
doing? i understand that Do Events is like having a gap in the program
allowing for the OS to do other things. the question is, are there things
that the OS needs to do in my code? it is refreshing pivot tables and MS
query is involved, but i am still unsure if this is causing the loading of my
sheet to go slower.