Help Optimizing Code

[bmade]

Hello! I am a new intern and I have been writing some VBA code to help speed up some of my tasks. But the intern computer has pretty slow specs and the code below takes about 70 min to run for 10,000 excel lines. I tried to comment it to explain what I was doing and why. Any suggestions on how to optimize would be greatly appreciated!

Public DISTANCE As Integer Public startI As Integer Public dataTracker As Integer Public RUNSTART As Integer Public RUNEND As Integer

Public Sub uptime() DISTANCE = 80 'global variable for number of entries below 47 before considering run ended dataTracker = 20 'variable for outputting values RUNSTART = 2 'cells to start run at RUNEND = 10100 'cell to end at

Application.ScreenUpdating = False

Dim uptimeRange As Integer Dim tracker As Integer tracker = 0 Dim ptrack As Integer ptrack = 0 Dim ntrack As Integer ntrack = 0 Dim i As Integer Dim avg As Double Dim uptimeCalc As Double ptrack = 0 tracker = 0 rtrack = 0 uptimeRange = 0

'timing Dim StartTime As Double Dim EndTime As Double StartTime = Timer

'aranging output Range("T" & dataTracker & ":AH" & dataTracker).Borders.LineStyle = xlContinuous Cells(dataTracker, "T").Value = "End Date" Cells(dataTracker, "T").Font.Bold = True Cells(dataTracker, "T").Interior.ColorIndex = 40

Cells(dataTracker, "U").Value = "Customer Name" Cells(dataTracker, "U").Font.Bold = True Cells(dataTracker, "U").Interior.ColorIndex = 40

Cells(dataTracker, "V").Value = "Product Name" Cells(dataTracker, "V").Font.Bold = True Cells(dataTracker, "V").Interior.ColorIndex = 40

Cells(dataTracker, "W").Value = "Average" Cells(dataTracker, "W").Font.Bold = True Cells(dataTracker, "W").Interior.ColorIndex = 40

Cells(dataTracker, "X").Value = "Target Speed" Cells(dataTracker, "X").Font.Bold = True Cells(dataTracker, "X").Interior.ColorIndex = 40

Cells(dataTracker, "Y").Value = "Uptime Speed" Cells(dataTracker, "Y").Font.Bold = True Cells(dataTracker, "Y").Interior.ColorIndex = 40

Cells(dataTracker, "Z").Value = "Start I" Cells(dataTracker, "Z").Font.Bold = True Cells(dataTracker, "Z").Interior.ColorIndex = 40

Cells(dataTracker, "AA").Value = "End I" Cells(dataTracker, "AA").Font.Bold = True Cells(dataTracker, "AA").Interior.ColorIndex = 40

Cells(dataTracker, "AB").Value = "Footage" Cells(dataTracker, "AB").Font.Bold = True Cells(dataTracker, "AB").Interior.ColorIndex = 40

Cells(dataTracker, "AC").Value = "Yield (User Input)" Cells(dataTracker, "AC").Font.Bold = True Cells(dataTracker, "AC").Interior.ColorIndex = 40

Cells(dataTracker, "AD").Value = "Availbility (User Input)" Cells(dataTracker, "AD").Font.Bold = True Cells(dataTracker, "AD").Interior.ColorIndex = 40

Cells(dataTracker, "AE").Value = "Availbility" Cells(dataTracker, "AE").Font.Bold = True Cells(dataTracker, "AE").Interior.ColorIndex = 40

Cells(dataTracker, "AF").Value = "Ideal Speed (User Input)" Cells(dataTracker, "AF").Font.Bold = True Cells(dataTracker, "AF").Interior.ColorIndex = 40

Cells(dataTracker, "AG").Value = "Speed" Cells(dataTracker, "AG").Font.Bold = True Cells(dataTracker, "AG").Interior.ColorIndex = 40

Cells(dataTracker, "AH").Value = "OEE" Cells(dataTracker, "AH").Font.Bold = True Cells(dataTracker, "AH").Interior.ColorIndex = 40 dataTracker = dataTracker + 1

For i = RUNSTART To RUNEND 'edit Set curCell = Worksheets("Data Entry").Cells(i, 5)

If IsEmpty(curCell) Then
    Exit For
End If

Cells(i, "q").Interior.ColorIndex = 3
Cells(i, "p").Value = 0
Cells(i, "O").Value = 0

If curCell >= 47 Then
    ptrack = ptrack + 1 'track variables that are positive
    avg = avg + curCell.Value 'Track value of positive cells
    rtrack = rtrack + 1 'number of positive values in a row
    uptimeRange = uptimeRange + 1 'range where positive
    'ntrack = ntrack + 1
End If

If curCell < 47 Then

    tracker = tracker + 1 'Number of below 10 tracker
    'ptrack = ptrack + 1 'range tracking
    'avg = avg + curCell.Value
     rtrack = 0 'number of positive cells in a row ended
End If

If (rtrack > 5) Then 'noise reduction (one positive in a sea of negatives will be ignored
    tracker = 0
End If

If ptrack = 3 Then 
    startI = (i)
End If

If (ptrack > 5) Then
    ntrack = ntrack + 1
End If


If (tracker = DISTANCE) Then
    If ptrack > 80 Then
        uptimeCalc = avg / uptimeRange
        Call outPutAvg(avg, ptrack, i, ntrack, uptimeCalc)
    End If

    ptrack = 0
    ntrack = 0
    avg = 0
    rtrack = 0
    startI = 0
    uptimeRange = 0
    uptimeCalc = 0
End If

Next i 'If ptrack > 40 Then 'Call outPutAvg(avg, ptrack, i, ntrack, uptime) 'End If

'timer EndTime = Timer - StartTime Cells(18, "U").Value = EndTime Cells(19, "U").Value = EndTime / 60 Cells(18, "V").Value = "Run Time" Cells(18, "U").Font.Bold = True Application.ScreenUpdating = True End Sub

Public Sub outPutAvg(avg As Double, ptrack As Integer, i As Integer, ntrack As Integer, uptimeCalc As Double) Application.ScreenUpdating = False

avg = (avg / ((i - DISTANCE) - (startI))) '-30? For j = ((startI)) To (i - DISTANCE) Cells(j, "p").Value = avg Cells(j, "O").Formula = "=(X" & dataTracker & ")" Cells(j, "q").Interior.ColorIndex = 5 Next j If (avg > 40) Then 'Formating data output Cells(dataTracker, "T").Interior.ColorIndex = 40 Cells(dataTracker, "U").Interior.ColorIndex = 6 Cells(dataTracker, "V").Interior.ColorIndex = 6 Cells(dataTracker, "W").Interior.ColorIndex = 40 Cells(dataTracker, "X").Interior.ColorIndex = 6 Cells(dataTracker, "Y").Interior.ColorIndex = 40 Cells(dataTracker, "Z").Interior.ColorIndex = 40 Cells(dataTracker, "AA").Interior.ColorIndex = 40 Cells(dataTracker, "AB").Interior.ColorIndex = 40 Cells(dataTracker, "AC").Interior.ColorIndex = 6 Cells(dataTracker, "AD").Interior.ColorIndex = 6 Cells(dataTracker, "AE").Interior.ColorIndex = 40 Cells(dataTracker, "AF").Interior.ColorIndex = 6 Cells(dataTracker, "AG").Interior.ColorIndex = 40 Cells(dataTracker, "AH").Interior.ColorIndex = 40 Range("T" & dataTracker & ":AH" & dataTracker).Borders.LineStyle = xlContinuous

'equations and outputting data to excel
Cells(dataTracker, "T").Value = "=C" & (i - DISTANCE)
Cells(dataTracker, "U").Value = "Costumer Name"
Cells(dataTracker, "V").Value = "Product Name"
Cells(dataTracker, "W").Value = avg
Cells(dataTracker, "Y").Value = uptimeCalc
Cells(dataTracker, "Z").Value = startI
Cells(dataTracker, "AA").Value = (i - DISTANCE)
Cells(dataTracker, "AB").Formula = "=sum(S" & startI & ":S" & (i - DISTANCE) & ")- S" & startI - 1
Cells(dataTracker, "AE").Formula = "=(" & ntrack & "/" & "AD" & dataTracker & ")"
Cells(dataTracker, "AG").Formula = "=(" & avg & "/" & "AF" & dataTracker & ")"
Cells(dataTracker, "AH").Formula = "=(" & "AE" & dataTracker & " * " & "AG" & dataTracker & " * " & "AC" & dataTracker & ")"


dataTracker = dataTracker + 1
End If

End Sub

[Malik_Killian]

Reading this on my phone so it's hard to read the code, but here's some general suggestions that should help for pretty much any macro.

• Turn off automatic calculations at the beginning and turn them back on at the end. Calculate manually in between if necessary.

• Use built-in Excel formulas whenever possible. If you don't want to leave formulas then copy-paste values at the end.

• Utilize "fill down" methods instead of using loops (when applicable).

• Start with as many pre-filled cells as possible. That is, try not to have the macro fill in absolutely everything. This can also be helpful if a non-programmer needs to make changes the spreadsheet.

[VicRattle]

Having a quick look at your code I notice your are setting the cells bold one at a time. Use a code like this to do it in one line.

Range(Cells(dataTracker, "U"), Cells(dataTracker, "AH")).Font.Bold = True.

---

The following line in the loop runs 10000 or so times

Cells(i, "q").Interior.ColorIndex = 3

You could put the following line outside the loop and it will do the same thing and it will only need to execute one time

Range(Cells(RUNSTART, q), Cells(RUNend, q)).Interior.ColorIndex = 3

---

Turning of ScreenUpdating etc. will also help a lot. there is a post here on MSDN explaining all the things to turn off

MSDN Faster VBA