Slow Code !! :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Slow Code !!
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Zorax
Board Master


Joined: 02 Jan 2003
Posts: 111

Flag: Blank

Status: Offline

 Reply with quote  

Slow Code !!

I am using the following code (from Yogi) that colour codes a cell depending on its bracketed number immediately that data is entered(exactly what I was trying to achieve). Before I enter data, I create a new sheet by clearing the previous sheet and running a small bit of code that adds the dates and days of a particular month. By themselves, the macro's are very fast acting - a matter of seconds but together, it can take over 2.5 minutes just to add the columns of dates/days (5 minutes on a slower computer). I think this is because as each new date/day is added, the worksheet_change routine runs, therefore slowing everything down considerably. Is there a way of running my formatting routine before the Worksheet_change function is activated?

If not, any suggestions/comments on why the whole process is so slow would be appreciated. It's not a huge problem but I didn't expect such a small routine to be so memory intensive.

Thanks in advance



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
For Each cell In [E4:M34]
On Error Resume Next
LocateLeftParen = WorksheetFunction.Find("(", cell)
Debug.Print LocateLeftParen
ParenNum = Mid(cell, LocateLeftParen + 1, 255)
ParenNum = Val(ParenNum)
Debug.Print ParenNum
Select Case ParenNum
Case 1: cell.Interior.ColorIndex = 17
Case 2: cell.Interior.ColorIndex = 6
Case 3: cell.Interior.ColorIndex = 43
Case 4: cell.Interior.ColorIndex = 40
Case 5: cell.Interior.ColorIndex = 15
Case 6: cell.Interior.ColorIndex = 41
Case 7: cell.Interior.ColorIndex = 54
Case 8: cell.Interior.ColorIndex = 3
Case 9: cell.Interior.ColorIndex = 20
Case 10: cell.Interior.ColorIndex = 1
Case Else
cell.Interior.ColorIndex = 0
End Select
Next cell
End Sub

Post Sat Sep 20, 2003 2:03 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Slow Code !!

can you give us the other macro - it might be a simple case of running one then the other, but not both at the same time as you have said.
_________________
George J

Post Sat Sep 20, 2003 2:38 pm 
 View user's profile Send private message Send e-mail

Zorax
Board Master


Joined: 02 Jan 2003
Posts: 111

Flag: Blank

Status: Offline

 Reply with quote  

Re: Slow Code !!

As requested - thanks


Sheets(2).Select


DateAgain:

Ldate = InputBox("The Date Should Be Entered In This Format dd/mm/yy , ie. 01/04/03", "These are the leave sheets for what month?")

If Ldate = "" Then
Exit Sub
End If

Ldate = Format(Ldate, "dd,mmm,yyyy")

If IsDate(Ldate) = False Then
If MsgBox("Check date entry", 49, "Have You Entered The Date Correctly ?") = vbOK Then
'GoTo DateAgain
End If
End If

nDate = CDate(Ldate)
delta = Day(DateSerial(Year(nDate), Month(nDate) + 1, 0))

With Columns(1)

.NumberFormat = "ddd"
.HorizontalAlignment = xlCenter
.Font.Size = 8
End With
With Columns(2)

.NumberFormat = "dd"
.Font.Size = 8
.Font.Bold = False
.HorizontalAlignment = xlCenter
End With

For icounter = 0 To delta - 1
Range("b4").Offset(icounter, 0).Formula = nDate + icounter
Range("b4").Offset(icounter, -1).Formula = nDate + icounter
Next icounter


Range("C4").Select
Dim ID As Integer
Do While ActiveCell.Offset(0, -1).Value <> ""
If ActiveWorkbook.Date1904 = True Then
ID = ActiveCell.Offset(0, -1) - 36441
Else
ID = ActiveCell.Offset(0, -1) - 37883
End If
Select Case Right(ID, 1)
Case 0
ActiveCell.Value = "O"
Case 1 To 2
ActiveCell.Value = "M"
Case 3 To 4
ActiveCell.Value = "A"
Case 5 To 6
ActiveCell.Value = "N"
Case 7
ActiveCell.Value = "O"
Case 8 To 9
ActiveCell.Value = "O"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop


Sheets(3).Select


With Columns(1)

.NumberFormat = "ddd"
.HorizontalAlignment = xlCenter
.Font.Size = 8
End With
With Columns(2)

.NumberFormat = "dd"
.Font.Size = 8
.Font.Bold = False
.HorizontalAlignment = xlCenter
End With

For icounter = 0 To delta - 1
Range("b4").Offset(icounter, 0).Formula = nDate + icounter
Range("b4").Offset(icounter, -1).Formula = nDate + icounter
Next icounter



Range("C4").Select
Do While ActiveCell.Offset(0, -1).Value <> ""
If ActiveWorkbook.Date1904 = True Then
ID = ActiveCell.Offset(0, -1) - 36441
Else
ID = ActiveCell.Offset(0, -1) - 37883
End If
Select Case Right(ID, 1)
Case 0
ActiveCell.Value = "O"
Case 1 To 2
ActiveCell.Value = "M"
Case 3 To 4
ActiveCell.Value = "A"
Case 5 To 6
ActiveCell.Value = "N"
Case 7
ActiveCell.Value = "O"
Case 8 To 9
ActiveCell.Value = "O"
Case Else
MsgBox "Error...You Should never see this"
End Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

Post Sat Sep 20, 2003 2:42 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Slow Code !!

This might look totally stupid, but it might help you get round the problem
If you have something like this at the beginning

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If start = "Empty" Then
exit sub
Else

For Each cell In [E4:M34]

etc
end if
end sub

and at the end of your other macro

start = "go"

the worksheet change macro should then kick in. Please feel free to laugh if this of ineffective.
_________________
George J


Last edited by George J on Sat Sep 20, 2003 3:27 pm; edited 2 times in total

Post Sat Sep 20, 2003 3:02 pm 
 View user's profile Send private message Send e-mail

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Slow Code !!

Hi Z,

As you have summised, if you have Worksheet_Change event code then your second macro will cause this event to fire each time it causes a change to the worksheet in question.

Try adding "Application.EnableEvents = False" at the start of your second macro and then reset to "Application.EnableEvents = True" at the end. This will prevent events from firing until the second routine is complete. You may also notice a speed improvement by the use of "Application.ScreenUpdating = False" at the start of the routine and, again, resetting at the end with "Application.ScreenUpdating = True". This prevents the worksheet from repainting itself until the code is finished.

HTH
_________________
Richie

Post Sat Sep 20, 2003 3:12 pm 
 View user's profile Send private message

Zorax
Board Master


Joined: 02 Jan 2003
Posts: 111

Flag: Blank

Status: Offline

 Reply with quote  

Re: Slow Code !!

Richie/George

Thank you very much. Tried Richie's solution first and it has made a huge difference. I knew there had to be a simple solution but I couldn't think of one.

Thanks again

Zorax

Post Sat Sep 20, 2003 4:51 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.