Programming lag

[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

 

[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

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-

 

[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.

 

[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

 

[Ernst Guckel]

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.