Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency in code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Efficiency in code
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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Efficiency of Code Help | Excel Programming | |||
efficiency | Excel Programming | |||
VBA Code Efficiency Question | Excel Programming | |||
Efficiency in my code (a critique from the guru's) | Excel Programming |