help with some slow code

M

Matt S

Hi all! The following code works, but takes forever to finish. The
background is that I have LabView recording data from a reactor that has four
modes. Sometimes the modes don't exactly go as planned, so some seconds are
skipped in a mode. I therefore need to correct the data and correctly label
the modes.

Once the four modes are labeled correctly, I then need to do several
calculations and place the results in a separate table. The problem is that
the data is 50 hours long, so doing the calculations takes at least 5 minutes.

If there is a better way to do this, please point me in the right direction.

Thanks!
Matt


Function Label_Av(Cycles As Variant)

CellsShifted = 0

For i = 1 To Cycles

If Not ActiveCell.Offset(60 * (i - 1), -1).Value = """" Then

'''''''''''''''''
'Label Modes '
'''''''''''''''''

ActiveCell.Range("A" & 60 * (i - 1) + 1 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Value = "Mode 1"
ActiveCell.Range("A" & 60 * (i - 1) + 41 - CellsShifted &
":A" & 60 * (i - 1) + 46 - CellsShifted).Value = "Mode 2"
ActiveCell.Range("A" & 60 * (i - 1) + 47 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Value = "Mode 3"
ActiveCell.Range("A" & 60 * (i - 1) + 57 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Value = "Mode 4"

'Check to see if time values match up to labels and fix if
not correct
Time_Diff = ActiveCell.Offset(60 * (i - 1) + 60 -
CellsShifted, -23).Value - ActiveCell.Offset(60 * (i - 1) + 1 - CellsShifted,
-23).Value

If Not Time_Diff < 60.9 Then '<60.9 because some values
recorded are not exactly 1 second
CellsShifted = CellsShifted + Fix_Time(i)
Else
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Perform calcs on four modes and place in separate table '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Time at end of Mode 4
Range("Y" & i + 9).Value = ActiveCell.Offset(0,
-23).Range("A" & 60 * (i - 1) + 60 - CellsShifted)

'Control Oxygen for Modes 3 and 4 - 5 sec after start of
Mode 3
Range("Z" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -20).Range("A" & 60 * (i - 1) + 47 + 5 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'Front UEGO averaged for Mode 2 and 3 - 3 sec after start of
Mode 2
Range("AA" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -8).Range("A" & 60 * (i - 1) + 41 + 3 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'Inlet Temp is average temperature at mode 1 - 10 last
seconds averaged
Range("AB" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -11).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'Average Bed T for Mode 1 - 10 last seconds
Range("AC" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'T Max Bed T for all modes
Range("AD" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 - CellsShifted & ":A"
& 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'R UEGO peak during Modes 2 and 3
Range("AE" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 41 - CellsShifted & ":A"
& 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

'R UEGO peak during Modes 4 and the next cycle's 1
Range("AF" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 57 - CellsShifted & ":A"
& 60 * (i - 1) + 60 + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"

End If
Next

Label_Av = CellsShifted

End Function
 
T

Tim Williams

Have you tried turning off screen updating and setting calculation to manual
before running ?
(Don't forget to set them back when done.)

Tim
 
M

Matt S

yes, I have. It is just the 50 hours second-by-second data is the reason
it's so slow. Maybe some type of array method would be better? My array
knowledge is a little weak.
 
A

Ariel Dugan

Hi Matt,

The code below is untested. If it doesn't compile, please send me your
entire workbook to test it on, as some of the procedures that your function
calls weren't present. Basically I just put the data in an array as you
suggested.

Function Label_Av(Cycles As Variant)

'''Declare array
Dim arrValues(1 To Cycles, 1 To 8) As Variant

CellsShifted = 0

For i = 1 To Cycles

If Not ActiveCell.Offset(60 * (i - 1), -1).Value = """" Then

'''''''''''''''''
'Label Modes '
'''''''''''''''''

ActiveCell.Range("A" & 60 * (i - 1) + 1 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Value = "Mode 1"
ActiveCell.Range("A" & 60 * (i - 1) + 41 - CellsShifted &
":A" & 60 * (i - 1) + 46 - CellsShifted).Value = "Mode 2"
ActiveCell.Range("A" & 60 * (i - 1) + 47 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Value = "Mode 3"
ActiveCell.Range("A" & 60 * (i - 1) + 57 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Value = "Mode 4"

'Check to see if time values match up to labels and fix
ifnot correct

Time_Diff = ActiveCell.Offset(60 * (i - 1) + 60 -
CellsShifted, -23).Value - ActiveCell.Offset(60 * (i - 1) + 1 -
CellsShifted, -23).Value

If Not Time_Diff < 60.9 Then '<60.9 because some
valuesrecorded are not exactly 1 second
CellsShifted = CellsShifted + Fix_Time(i)
Else
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Perform calcs on four modes and place in separate table '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Time at end of Mode 4
arrValues(i + 9, 1) = ActiveCell.Offset(0, -23).Range("A" &
60 * (i - 1) + 60 - CellsShifted)
'Range("Y" & i + 9).Value =
ActiveCell.Offset(0, -23).Range("A" & 60 * (i - 1) + 60 - CellsShifted)

'Control Oxygen for Modes 3 and 4 - 5 sec after start ofMode
3
arrValues(i + 9, 2) =
Application.WorksheetFunction.Average(ActiveCell.Offset(0, -20).Range("A" &
60 * (i - 1) + 47 + 5 - CellsShifted & ":A" & 60 * (i - 1) + 60 -
CellsShifted).Address)
'Range("Z" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -20).Range("A" & 60 * (i - 1) + 47 + 5 - CellsShifted &
":A" & 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Front UEGO averaged for Mode 2 and 3 - 3 sec after start
ofMode 2
arrValue(i + 9, 3) =
Application.WorksheetFunction.Average(ActiveCell.Offset(0, -8).Range("A" &
60 * (i - 1) + 41 + 3 - CellsShifted & ":A" & 60 * (i - 1) + 56 -
CellsShifted).Address)
'Range("AA" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -8).Range("A" & 60 * (i - 1) + 41 + 3 - CellsShifted &
":A" & 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Inlet Temp is average temperature at mode 1 - 10
lastseconds averaged
arrValue(i + 9, 4) =
Application.WorksheetFunction.Average(ActiveCell.Offset(0, -11).Range("A" &
60 * (i - 1) + 1 + 30 - CellsShifted & ":A" & 60 * (i - 1) + 40 -
CellsShifted).Address)
'Range("AB" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -11).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

'Average Bed T for Mode 1 - 10 last seconds
arrValue(i + 9, 5) =
Application.WorksheetFunction.Average(ActiveCell.Offset(0, -10).Range("A" &
60 * (i - 1) + 1 + 30 - CellsShifted & ":A" & 60 * (i - 1) + 40 -
CellsShifted).Address)
'Range("AC" & i + 9).FormulaR1C1 = "=Average(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 + 30 - CellsShifted &
":A" & 60 * (i - 1) + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"
'T Max Bed T for all modes
arrValue(i + 9, 6) =
Application.WorksheetFunction.Max(ActiveCell.Offset(0, -10).Range("A" & 60 *
(i - 1) + 1 - CellsShifted & ":A" & 60 * (i - 1) + 60 -
CellsShifted).Address)
'Range("AD" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -10).Range("A" & 60 * (i - 1) + 1 - CellsShifted & ":A"
& 60 * (i - 1) + 60 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"
'R UEGO peak during Modes 2 and 3

arrValue(i + 9, 7) =
Application.WorksheetFunction.Max(ActiveCell.Offset(0, -6).Range("A" & 60 *
(i - 1) + 41 - CellsShifted & ":A" & 60 * (i - 1) + 56 -
CellsShifted).Address)
'Range("AE" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 41 - CellsShifted & ":A"
& 60 * (i - 1) + 56 - CellsShifted).Address(ReferenceStyle:=xlR1C1) & ")"
'R UEGO peak during Modes 4 and the next cycle's 1
arrValue(i + 9, 8) =
Application.WorksheetFunction.Max(ActiveCell.Offset(0, -6).Range("A" & 60 *
(i - 1) + 57 - CellsShifted & ":A" & 60 * (i - 1) + 60 + 40 -
CellsShifted).Address)
'Range("AF" & i + 9).FormulaR1C1 = "=Max(" &
ActiveCell.Offset(0, -6).Range("A" & 60 * (i - 1) + 57 - CellsShifted & ":A"
& 60 * (i - 1) + 60 + 40 - CellsShifted).Address(ReferenceStyle:=xlR1C1) &
")"

End If
Next
ActiveSheet.Range("Y" & i + 9, "AF" & i + 9).Value = arrValue
Label_Av = CellsShifted

End Function

Thanks
Ariel
 
M

Matt S

oh my. I really didn't expect so much help! THANK YOU! If it doesn't
compile, I'll troubleshoot and learn more of your technique.

THANKS AGAIN!
Matt
 
M

Matt S

Ariel,

Thanks again for your help! I incorporated your new code and had to edit
just a few things.

1) I needed to make Dim = ReDim
2) I needed to add Range() within the Average and Max formulas. For
example, Application.WorksheetFunction.Average(Range(activecell.offset....))

Other than that, the code is MUCH MUCH faster now! AND I learned some new
stuff! THANK YOU SO MUCH!

Matt
 

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

Similar Threads

Problem with using addreslocal 0
how to use Solver in Excel VBA 1
Run-Time Error 1004 Application-Definition or Object-Definition Error 5
Transfer Data from UserForm to Worksheet 8
Code does not error but no copy either 4
Help me4 2
error in code 2
Error 2042 problem with vlookup 0

Top