Macro takes a while to run - should I be concerned?

M

MJKelly

Hi,

I have a macro which takes over a minute to run. When it runs with
more data I anticipate it running for a few minutes. Is this okay, or
should I try to run the code in blocks or save the file part way
through etc?

regards,
Matt
 
B

Bernie Deitrick

Matt,

It depends - what are you doing with the macro?

Posting code is often a good idea, too.

HTH,
Bernie
MS Excel MVP
 
O

Orion Cochrane

Try turning screen updating off while the macro is running and turn it back
on at the end.

Sub Example()
Application.ScreenUpdating = False
'Your code here
Applicaiton.ScreenUpdating = True
End Sub
 
M

Mike H

Hi,

I would share your expectation that a macro doing more work (crunching more
data) would take longer to run. Whether that's OK or not is too much of an
open question. How can we know if it's OK, post the code.

Mike
 
R

Revolvr

Also write to remove selections.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- Show quoted text -
Click to expand...

Also, try setting the calculation to manual. I have often seen macros
that result in the entire spreadsheet being re-calculated multiple
times, which makes things really slow and normally not necessary.

At the start of the macro:

Application.Calculation = xlManual

and at the end of the macro:

Application.Calculation = xlAutomatic

If in testing you find the macro does need to recalculate everything
at a specific point, add:

Application.Calculate

Or to calculate one worksheet, something like:

ActiveSheet.Calculate

-- Rev
 
M

MJKelly

Hi All,

Thanks for the feedback so far.

Please see code below. I'm new to VBA so I'm sure there are
improvments to the code below. The code is taking a time input (time
window) and converting it to an amount of people available (put into a
table for reference). The code does now run faster, without the
changes you have mentioned so far as I removed it from a workbook
which was 22mb in size.

Sub OT_to_Availability_Grid(ByVal OTStartTime As String,
BookedStartTime As String)
'called from the Resource_OT routine (for each day of the week)

Dim DayStart, OTStart, MR1S, MR1E, OTEnd As Date
Dim DayStart_to_OTStart, OTStart_to_MR1S, MR1S_to_MR1E, MR1E_to_OTEnd,
OTStart_to_OTEnd As Date
Dim DayStart_to_OTStart_10m, OTStart_to_MR1S_10m, MR1S_to_MR1E_10m,
MR1E_to_OTEnd_10m, OTStart_to_OTEnd_10m As Integer

DayStart = 0.25

Dim Count, myOffset, TaskOffset, MROffset, StaffAmount As Integer
Dim Task As String

Dim c As Range
For Each c In Worksheets("Sched OT").Range(OTStartTime).Cells
If Not c.Value = "" Then ' Is the cell empty?
'c.Interior.ColorIndex = 3
'c.Offset(0, 1).Interior.ColorIndex = 3
'Else
OTStart = c.Value ' Get information required
OTEnd = c.Offset(0, 1).Value
MR1S = c.Offset(0, 2).Value
MR1E = c.Offset(0, 3).Value
Task = c.Offset(0, 4).Value

StaffAmount = 1
MROffset = 5

Select Case Task

Case Is = "Proc M"
TaskOffset = 0
Case Is = "Proc A"
TaskOffset = 1
Case Is = "MHE"
TaskOffset = 2
Case Is = "XD"
TaskOffset = 3
Case Is = "IND"
TaskOffset = 4

End Select

DayStart_to_OTStart = OTStart - DayStart 'Work out time between
duty elements
OTStart_to_MR1S = MR1S - OTStart 'Work out time between duty
elements
MR1S_to_MR1E = MR1E - MR1S 'Work out time between duty elements
MR1E_to_OTEnd = OTEnd - MR1E 'Work out time between duty elements
OTStart_to_OTEnd = OTEnd - OTStart 'used when there is no MR

DayStart_to_OTStart_10m = DayStart_to_OTStart / (1 / 144) 'Work
out 10 in blocks
OTStart_to_MR1S_10m = OTStart_to_MR1S / (1 / 144) 'Work out 10 in
blocks
MR1S_to_MR1E_10m = MR1S_to_MR1E / (1 / 144) 'Work out 10 in blocks
MR1E_to_OTEnd_10m = MR1E_to_OTEnd / (1 / 144) 'Work out 10 in
blocks
OTStart_to_OTEnd_10m = OTStart_to_OTEnd / (1 / 144) 'used when
there is no MR

'Round the 10min blocks to whole numbers

With Application.WorksheetFunction
DayStart_to_OTStart_10m = .Round(DayStart_to_OTStart_10m, 0)
OTStart_to_MR1S_10m = .Round(OTStart_to_MR1S_10m, 0)
MR1S_to_MR1E_10m = .Round(MR1S_to_MR1E_10m, 0)
MR1E_to_OTEnd_10m = .Round(MR1E_to_OTEnd_10m, 0)
OTStart_to_OTEnd_10m = .Round(OTStart_to_OTEnd_10m, 0)

End With

myOffset = 0
Count = 0

If Not MR1S = "" Then

Do 'Fill Duty start to MR1 start timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
myOffset, TaskOffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
myOffset, TaskOffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < OTStart_to_MR1S_10m

Count = 0
myOffset = 0

Do 'Fill MR1 start to MR1 end timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
OTStart_to_MR1S_10m + myOffset, MROffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
OTStart_to_MR1S_10m + myOffset, MROffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < MR1S_to_MR1E_10m

Count = 0
myOffset = 0

Do 'Fill MR1 end to OTEnd timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
OTStart_to_MR1S_10m + MR1S_to_MR1E_10m + myOffset, TaskOffset).Value =
_
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
OTStart_to_MR1S_10m + MR1S_to_MR1E_10m + myOffset, TaskOffset).Value +
StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < MR1E_to_OTEnd_10m

Count = 0
myOffset = 0


Else


Do 'Fill Duty start to MR1 start timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
myOffset, TaskOffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_to_OTStart_10m +
myOffset, TaskOffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < OTStart_to_OTEnd_10m
End If
End If
Next c

End Sub
 
Top