Programming lag

E

Ernst Guckel

Hello,

I have a list of employees on a schedule. I have a piece of code look
them up, sort them by start time, and then put them on a daily line up for
printing.. Problem is that it takes a long time on the work PC's (200mhz).
The problem seems to be after the array returns sorted. I cannot seem to
figure it out. Any ideas?

Thanks...

Sub Sunday(week)

Dim TheArray(60, 3) As Variant
Dim a, c, e, R
Dim Lunch, After, Dinner, Late As Boolean

a = 0

Range("A9:C49").ClearContents

' Create a new progress bar
Set sb = New clsProgressBar

' Display the progress bar
sb.Show constWait, vbNullString, 0

' Create the array.

For Each c In Range(week)

a = a + 1

TheArray(a, 1) = c.Value
TheArray(a, 2) = c.Cells(1, 2).Value
TheArray(a, 3) = c.Cells(1, -1).Value

Next

' Sort the Array and display the values in order.

BubbleSort TheArray

'Lag seems to start here...
R = 10

For a = 1 To UBound(TheArray)

e = TheArray(a, 1)

If e = "" Then GoTo Bottom
If Application.WorksheetFunction.IsText(e) = True Then GoTo Bottom

If Lunch = False Then
If e >= 0.458 Then
R = R + 1
Lunch = True
End If
End If

If After = False Then
If e >= 0.58 Then
R = R + 7
After = True
End If
End If

If Dinner = False Then
If e >= 0.708 Then
R = R + 1
Dinner = True
End If
End If

If Late = False Then
If e >= 0.833 Then
R = R + 1
Late = True
End If
End If

Range("A" & R).Value = TheArray(a, 1)
Range("B" & R).Value = TheArray(a, 2)
Range("C" & R).Value = TheArray(a, 3)

R = R + 1

Bottom:
sb.PercentComplete = (a / 60) * 100
Next

End Sub
 
J

Jim Cone

Ernst,

I've made some small changes in the code, it may or may not speed it up,
but it will be more efficient...
All the variables have been declared as I think appropriate. All of yours were variants,
except for "Late"
The array now is 60 by 3 instead of 61 by 4.
The progress bar has been replaced with a simple status bar display (much less overhead).
The check for blanks or text is replaced with a number verification.
The concatenation in the range addresses is replaced by the use of "Cells".

Give it a try and let us know. I have no way of testing the code without the data.

Regards,
Jim Cone
San Francisco, CA

REVISED CODE FOLLOWS...

Sub Sunday(week)
Dim TheArray() As Double
Dim a As Long
Dim R As Long
Dim c As Range
Dim e As Double
Dim Late As Boolean
Dim Lunch As Boolean
Dim After As Boolean
Dim Dinner As Boolean

a = 0
ReDim TheArray(1 to 60, 1 to 3)
Range("A9:C49").ClearContents

' Create the array.

For Each c In Range(week)
a = a + 1
TheArray(a, 1) = c.Value
TheArray(a, 2) = c.Cells(1, 2).Value
TheArray(a, 3) = c.Cells(1, -1).Value
Next 'c

' Sort the Array and display the values in order.

'BubbleSort TheArray

'Lag seems to start here...
R = 10

For a = 1 To UBound(TheArray)
e = TheArray(a, 1)
If Not IsNumeric(e) Then GoTo Bottom

If Lunch = False Then
If e >= 0.458 Then
R = R + 1
Lunch = True
End If
End If

If After = False Then
If e >= 0.58 Then
R = R + 7
After = True
End If
End If

If Dinner = False Then
If e >= 0.708 Then
R = R + 1
Dinner = True
End If
End If

If Late = False Then
If e >= 0.833 Then
R = R + 1
Late = True
End If
End If

Cells(R, 1).Value = TheArray(a, 1)
Cells(R, 2).Value = TheArray(a, 2)
Cells(R, 3).Value = TheArray(a, 3)

R = R + 1

Bottom:
Application.StatusBar = "PERCENT COMPLETE " & Format$((a / 60), "#00%")
Next 'a

End Sub


Ernst Guckel said:
Hello,

I have a list of employees on a schedule. I have a piece of code look
them up, sort them by start time, and then put them on a daily line up for
printing.. Problem is that it takes a long time on the work PC's (200mhz).
The problem seems to be after the array returns sorted. I cannot seem to
figure it out. Any ideas?

Thanks...

-SNIP-
 
E

Ernst Guckel

I've made some small changes in the code, it may or may not speed it up,
but it will be more efficient...
All the variables have been declared as I think appropriate. All of yours were variants,
except for "Late"
The array now is 60 by 3 instead of 61 by 4.
The progress bar has been replaced with a simple status bar display (much less overhead).
The check for blanks or text is replaced with a number verification.
The concatenation in the range addresses is replaced by the use of "Cells".

Give it a try and let us know. I have no way of testing the code without
the data.

fixed a few of the issues that you see. After changing the code it ran
slower for some reason. I created a hybrid of the two. If providing the
data will help track down the lag I will be happy to send you the
spreadsheet...

BubbleSort sorts the employees by start time. So the listing has
organization.

Thanks for the help.
Ernst.
 
J

Jim Cone

Ernst,

Well you never know until you test it. <g>
There could be something else going on besides code problems.
With the amount of data you have, 60 x 3, the program should be complete
by the time you finish clicking the button.
Good luck with it.

Regards,
Jim Cone
San Francisco, CA
 
E

Ernst Guckel

Well you never know until you test it. said:
There could be something else going on besides code problems.
With the amount of data you have, 60 x 3, the program should be complete
by the time you finish clicking the button.

it takes about 3 seconds at home but over 20 at work. Somethings not
right... :(

Ernst.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top