Re: Runs fast then slow

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: rpw (rpw_at_discussions.microsoft.com)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 15:13:02 -0700

Hi Mr. McRitchie,

Thank you for the quick response. I've asked more questions below. Please
keep in mind that I'm relatively new (and untrained) to coding (it took me a
couple of days to figure out the little bit that I posted earlier).

"David McRitchie" wrote:
> You should avoid changing the selection within a macro
Can you take the time to explain why?

> if you want to make bad coding run faster then turn off
> screen updating and turn off calculation.
I tried turning off screen updating and calculation and it seems to take as
long or maybe even longer. Can you explain "bad coding"?

> but that doesn't really solve your problem.
I posted a question, not a problem, and my question has still not been
answered. Is there a reason why the code runs fast (2-3 seconds) the first
time it's run and slower (8-10 seconds) on subsequent runs? How/why does
'bad code' get worse when it's run a second or third time?

btw, I was happy with the speed as it was originally - I just didn't
understand why there were differences in running speed. The potential users
that I displayed my program to were astonished to see that excel could work
like that. It was rather fun to see their reactions to the screen update
(rows being hidden) as the program ran.

However, that being said, I am open to suggestions. e.g. another
respondent, sebastienm, provided an alternative to my original and I can see
that one of the main differences is that there is not any changing of the
selection within the macro (probably why it runs in the blink of an eye!). I
will study how that's accomplished and try to utilize that in the future.

thanks again,

rpw....

> > I have this code that runs very fast the first time I open the workbook and
> > run it. All subsequent runs are about 1/2 to 1/3 the speed. I don't
> > understand why the slow down. Can someone explain it to me?
> >
> > Would I be better off taking the hide/unhide rows portion of it and making
> > it a function and include it as part of the cell formula - would that slow
> > the workbook way down?
> >
> > Thanks in advance for your responses. Here's the code:
> >
> > Dim RowCounter As Integer
> > Dim BreakCounter As Integer
> > Dim CellValue As String
> >
> > Worksheets("Summary Sheet").Activate
> > Worksheets("Summary Sheet").Range("A1").Select
> >
> > For RowCounter = 1 To 976
> > CellValue = Selection
> > If CellValue <> "Skip" Then
> > Selection.EntireRow.Hidden = True
> > Selection.Offset(1, 0).Range("A1").Select
> > Else:
> > Selection.EntireRow.Hidden = False
> > Selection.Offset(1, 0).Range("A1").Select
> > End If
> > Next RowCounter
> >
> > Worksheets("Summary Sheet").Range("p249").Select
> >
> > For BreakCounter = 1 To 4
> > CellValue = Selection
> > If CellValue = "Break" Then
> > Selection.PageBreak = xlPageBreakManual
> > Else: Selection.PageBreak = xlPageBreakNone
> > End If
> > Selection.Offset(241, 0).Range("a1").Select
> > Next BreakCounter
> >
> > ActiveSheet.PrintPreview
> >
> > Worksheets("Summary Sheet").Range("a1:a1000").Select
> > Selection.EntireRow.Hidden = False
> > Worksheets("Basic Pricing").Activate
> > Worksheets("Basic Pricing").Range("d3").Select
> > --
> > rpw
>
>
>

Relevant Pages

  • Re: Code Stops Due to No Values In Query
    ... Dim PoseidonHotelProgram As Database ... Title = "DEPARTURES PENDING" ' Define title. ... Response = MsgBox ... Set Myset2 = db1.OpenRecordset("RESPEL ALL CHARGES") ...
    (microsoft.public.access.modulesdaovba)
  • Re: "/decompile /recompile"
    ... Response As Integer) ... 'Undo the combo-box before updating control value to "NewData" ... Dim ctl As Control ... Set rstShipVia = MyDB.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: Requerying ComboBox in NotInList event
    ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ...
    (comp.databases.ms-access)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)