Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Code slows down after running once

  Asked By: Alma    Date: Oct 29    Category: MS Office    Views: 990
  

I'm needing some help. A little
background; I have limited experience with VBA, but have done
extensive programming in Liberty Basic. The below code runs
lightening fast the first time you run it, but then takes up to two
minutes to run subsequent times. If I close Excel and reopen it, it
runs fast again on the first run, but then is slow after that once
again. Your help is greatly appreciated.


Sub SelectPackage()
Dim stag As String
Dim etag As String
Dim i As Integer

ActiveSheet.Unprotect
Application.ScreenUpdating = False

If Sheet1.Cells(20, 1) > 0 Then
stag = "<1lane>"
etag = "</1lane>"
End If
If Sheet1.Cells(21, 1) > 0 Then
stag = "<2lane>"
etag = "</2lane>"
End If
If Sheet1.Cells(22, 1) > 0 Then
stag = "<3lane>"
etag = "</3lane>"
End If
If Sheet1.Cells(23, 1) > 0 Then
stag = "<4lane>"
etag = "</4lane>"
End If
If Sheet1.Cells(24, 1) > 0 Then
stag = "<5lane>"
etag = "</5lane>"
End If

i = 3

While Not (Sheet13.Cells(i, 1) = stag)
Cells(i, 1).EntireRow.Hidden = True
i = i + 1
Wend

While Not (Sheet13.Cells(i, 1) = etag)
Cells(i, 1).EntireRow.Hidden = False
i = i + 1
Wend

Cells(i + 1, 1).EntireRow.Hidden = False

While Not (Sheet13.Cells(i, 1) = "</5lane>")
Cells(i, 1).EntireRow.Hidden = True
i = i + 1
Wend

Cells(i + 1, 1).EntireRow.Hidden = True

Application.ScreenUpdating = True
ActiveSheet.Protect

End Sub

Share: 

 

11 Answers Found

 
Answer #1    Answered By: Raymond Fischer     Answered On: Oct 29

I can't understand what the code  is supposed to be doing.

When I run  it on a blank workbook it produces an overflow error, I even get
overflow values if I put in some values.
However, I do not know what values are required.

I am fairly convinced there is an easier way to do what you want - if we knew
what it was.

The reason it takes a long time  could be that, having hidden  rows with key
data in them, when it runs again it is unable to read the data it needs. It
then spends "forever" hiding all the blank rows on spreadsheets Sheet1 and
Sheet13. In my setup the integer value for i runs out when it reaches 32768
and an overflow results. Ohterwise it would hide all the rows to the end  of
the spreadsheet.

 
Answer #2    Answered By: Sebastian Anderson     Answered On: Oct 29

This code  examines a sales order on sheet1  to determine which package to
un-hide on sheet13. Sheet13 is a purchase order and the start and end  of
each package is marked with a tag in the first column, i.e. <1lane> and
</1lane> for the start and end respectively of that package. The code hides
all the lines for packages that are not ordered, and un-hides the lines for
the ordered package, i.e. everything between stag and etag.

At first I thought it was the whole trying to examine data in hidden  rows
thing, so I tried manually un-hiding all of the rows and running  it, still
slow. If I hide all of them, save the workbook, close  Excel and then reopen
the workbook, even with the rows hidden it runs in just a few seconds the
first time, but slows down after that. This exercise was to rule out the
possibility that the hidden rows were causing the issue.

 
Answer #3    Answered By: Inez Wood     Answered On: Oct 29

In answer to your question, I still think it is the time  it takes
unnecessarily hiding blank rows.

To get it to work reliably I had to prefix all the lines of code  with

Cells(i + 1, 1).EntireRow.Hidden
With
Sheet13.

Making them, for example

Sheet13.Cells(i, 1).EntireRow.Hidden = False

I still find that it does not work quite as you say it should. I think the
code I have may be missing something as it unhides the final row of 5lane
regardless, rather than unhiding the last row of the model chosen. I am sure
you will sort this out.

If you are still having problems with the time taken, you could put a
breakpoint on the first "While Not "line of code, run  it, press f5 a few times
and then see what it is doing and where. At this point step through with f8
and keep looking at the variables. The variable "i" will tell you what row
you are on.

 
Answer #4    Answered By: Natasha Rivera     Answered On: Oct 29

I decided to go ahead and prefix all of the Cells(... rows with the SheetX
and it now runs fast  everytime. I'm not sure why it would run  fast the
first time  and not on subsequent runs though. Strange. Anyway, here is my
final code  that runs correctly;

Sub SelectPackage()
Dim StartingTag As String
Dim EndingTag As String
Dim i As Integer

i = 3

Sheet13.Unprotect
Application.ScreenUpdating = False

If Sheet1.Cells(20, 1) > 0 Then '\
StartingTag = "<1lane>"
EndingTag = "</1lane>"
ElseIf Sheet1.Cells(21, 1) > 0 Then ' \
StartingTag = "<2lane>"
EndingTag = "</2lane>"
ElseIf Sheet1.Cells(22, 1) > 0 Then ' > Defines what package to
un-hide
StartingTag = "<3lane>"
EndingTag = "</3lane>"
ElseIf Sheet1.Cells(23, 1) > 0 Then ' /
StartingTag = "<4lane>"
EndingTag = "</4lane>"
ElseIf Sheet1.Cells(24, 1) > 0 Then '/
StartingTag = "<5lane>"
EndingTag = "</5lane>"
End If

While Not (Sheet13.Cells(i, 1) = StartingTag) 'Hides all lines
before hitting the beginning of the defined
Sheet13.Cells(i, 1).EntireRow.Hidden = True
'package(StartingTag)
i = i + 1
Wend

While Not (Sheet13.Cells(i, 1) = EndingTag) 'Un-hides all lines
in the defined package (between StartingTag
Sheet13.Cells(i, 1).EntireRow.Hidden = False 'and EndingTag)
i = i + 1
Wend

Sheet13.Cells(i + 1, 1).EntireRow.Hidden = False 'Un-hides the line
containing EndingTag

While Not (Sheet13.Cells(i, 1) = "</5lane>") 'Hides all lines
after the defined package to the end  of the
Sheet13.Cells(i, 1).EntireRow.Hidden = True 'packages (</5lane>
is the tag at the end of the last package)
i = i + 1
Wend

Sheet13.Cells(i + 1, 1).EntireRow.Hidden = True 'Hides the line
containing the last tag

Application.ScreenUpdating = True
Sheet13.Protect

End Sub

 
Answer #5    Answered By: Saila Iqbal     Answered On: Oct 29

Two coding errors leap out, and either could be causing problems:

1) You use Sheet1. and Sheet13. prefixes on most statements, but you do not
use them on the statements that actually hide/unhide the rows. That means
that it is going to hide/unhide the rows in whatever happens to be the
active sheet at the time  you run  the code. I assume you should actually be
changing the rows in Sheet13.

2) Your second While loop runs to an end  tag, then your third While loop
runs out to </5lane>. It should not run the third loop when etag is set to
</5lane>.

Whether either of these is contributing to your current problem, I don't
know.

Also, you use the ActiveSheet. prefix on the protect/unprotect statements.
Presumably this should be Sheet13.

What puts the > 0 values into the Sheet1 cells?

As David G says, there is an easier way to do what you want. (Or at least
make it more readable anyway.) Use a subroutine to do the scan and hide,
and give it the number of the section that you want to make visible. Also,
restructure the first part of the code  to use IF / ELSE IF to select just
the one action you want to perform. For instance, you could write the first
part of the code as

If Sheet1.Cells(24, 1) > 0 Then
Call MakeVisible (5)
ElseIf Sheet1.Cells(23, 1) > 0 Then
Call MakeVisible (4)
ElseIf Sheet1.Cells(22, 1) > 0 Then
Call MakeVisible (3)
ElseIf Sheet1.Cells(21, 1) > 0 Then
Call MakeVisible (2)
ElseIf Sheet1.Cells(20, 1) > 0 Then
Call MakeVisible (1)
End If

assuming you really do want section 5 to take precedence over section 4,
etc, as in your code.

 
Answer #6    Answered By: Wallace Kelly     Answered On: Oct 29

RE: 1) I did catch the prefix issue, but that has not caused problems to
this point because Sheet13 is always the active sheet when I am running  this
Macro. It will be corrected before releasing the sheet though.

RE: 2) the </5lane> is the last tag in the sheet, so the purpose of the 3rd
While loop is to hide the lines in all of the packages after the package
that was un-hidden. If the etag is set to </5lane> then that third While
loop doesn't even run, because the very first cell it looks at meets the
condition.

The values on Sheet1 are entered by a salesman when they sell a 1, 2, 3, 4
or 5-lane package.

I know this is all difficult without the actual sheet, and I would make that
available, but the sheet contains a LOT of confidential information that I
cannot release. The bottom line is that this code  works every time  it is
run, it's just that if you run  it more than once without closing and
re-opening Excel in between, it runs extremely slow, 2+ minutes versus just
a few seconds for the first time through.

I do however appreciate the suggestion of the ElseIf, that is not a command
the Liberty Basic has, so I was not familiar with it.

I will work on optimizing this code and making it easier to follow and if
I'm still having issues I will re-post it.

 
Answer #7    Answered By: Zack Thompson     Answered On: Oct 29

I wonder if it's something to do with the size of the Excel "undo" list
(although I wouldn't have expected a macro to be adding to that).

Rather than hide/unhide the rows one by one, why not remember the start row
of an area, then use Range (top cell, bottom cell) to mark the multiple rows
to receive a single hide or unhide?

 
Answer #8    Answered By: Krista Warren     Answered On: Oct 29

The reason I am scanning the sheet for tags to start and stop the
hide/un-hide process is for scalability. This way lines can be added to or
removed from a package and as long as the tags remain, the macro will work.

 
Answer #9    Answered By: Aakifah Khan     Answered On: Oct 29

Yes but ...

Please read my reply again. It is no less scalable than yours. It just
works out what group of rows need to be hidden/unhidden and does them all
with one command (each - three in total), rather than lots.

I wasn't suggesting you not scan, just that you record the information and
submit fewer hide/show commands.

 
Answer #10    Answered By: Chelsea Rose     Answered On: Oct 29

Sorry, I understand what you were saying now. Guess it's just been one of
those weekends.

 
Answer #11    Answered By: Leigh Carpenter     Answered On: Oct 29

I have no idea why prefixing the sheet object to the hide/unhide would speed
things up. Glad a fix you wanted to make anyway has removed the problem
too.

 
Didn't find what you were looking for? Find more on Code slows down after running once Or get search suggestion and latest updates.




Tagged: