M
Matt S
This function is supposed to take second-by-second data and do the following
procedure:
1) Store data in arrays (arrTime, arrInletT, etc.)
2) Label data into four Modes of operation. (arrLabel)
3) Use the labels to store the data into four arrays (arrMode1, arrMode2,
etc.)
4) Perform seven calcs on the four Modes and store them in a 2-dimensional
array that is pasted back into Excel. (arrValues)
The data goes in order, Mode 1 for 40sec, Mode2 for 6sec, Mode 3 for 10sec,
Mode4 for 4sec, then repeats. This procedure is done for 50hours.
Because I'm new to arrays, the code is very rough and ill-defined. I think
it might work up to the calculations portion, but where I try to find out the
number of elements of the mode arrays will not work. The number of seconds
dont really stay constant... for example, Mode 1 could be 38-42 seconds long.
I do not know how to find out the #sec for that cycle. I need something
else besides NumElements.
Can someone straighten out my mess and get this code to work??
THANKS SO MUCH!
Matt
Function Label_Av(Cycles As Long)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Declare Arrays
ReDim arrValues(1 To Cycles, 1 To 8) As Variant
ReDim arrTime(8 To LastRow) As Double
ReDim arrInletT(8 To LastRow) As Double
ReDim arrBedT(8 To LastRow) As Double
ReDim arrRUEGO(8 To LastRow) As Double
ReDim arrLabel(8 To LastRow) As String
ReDim arrFUEGO(8 To LastRow) As Double
ReDim arrOxygen(8 To LastRow) As Double
ReDim arrTemp(8 To LastRow) As Double
CatTemp = WorksheetFunction.Average(Range("K100:K200"))
'Populate Arrays
For j = 8 To LastRow
arrTime(j) = Range("A" & j).Value
arrFUEGO(j) = Range("P" & j).Value
arrInletT(j) = Range("M" & j).Value
arrBedT(j) = Range("N" & j).Value
arrRUEGO(j) = Range("R" & j).Value
arrOxygen(j) = Range("E" & j).Value
arrTemp(j) = Range("K" & j).Value
Next j
'''''''''''''''''
'Label Modes '
'''''''''''''''''
For j = 8 To LastRow
If arrTemp(j) < CatTemp - 5 Then
arrLabel(j) = "Not Aging"
Else
If Round(arrFUEGO(j), 1) <= 0.95 Then
If arrOxygen(j) > 0 Then
arrLabel(j) = "Mode 3"
Else
arrLabel(j) = "Mode 2"
End If
Else
If arrOxygen(j) > 0 Then
arrLabel(j) = "Mode 4"
Else
arrLabel(j) = "Mode 1"
End If
End If
End If
Next j
ActiveSheet.Range("X8:X" & LastRow).Value =
Application.Transpose(arrLabel)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Perform calcs on four modes and place in separate table '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Populate Mode arrays with data to perform calculations
ReDim arrMode1(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode2(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode3(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode4(1 To LastRow, 1 To 100, 1 To 5)
CountCycle = 0
For j = 8 To LastRow
If arrLabel(j) = "Mode 1" Then
If Not arrLabel(j - 1) = "Mode 1" Then
CountCycle = CountCycle + 1
Count1 = 1
Count2 = 0
Count3 = 0
Count4 = 0
Else
End If
arrMode1(CountCycle, Count1, 1) = arrBedT(j)
arrMode1(CountCycle, Count1, 2) = arrInletT(j)
arrMode1(CountCycle, Count1, 3) = arrRUEGO(j)
ElseIf arrLabel(j) = "Mode 2" Then
Count2 = Count2 + 1
arrMode2(CountCycle, Count2, 1) = arrBedT(j)
arrMode2(CountCycle, Count2, 2) = arrFUEGO(j)
arrMode2(CountCycle, Count2, 3) = arrRUEGO(j)
ElseIf arrLabel(j) = "Mode 3" Then
Count3 = Count3 + 1
arrMode3(CountCycle, Count3, 1) = arrBedT(j)
arrMode3(CountCycle, Count3, 2) = arrRUEGO(j)
arrMode3(CountCycle, Count3, 3) = arrFUEGO(j)
arrMode3(CountCycle, Count3, 4) = arrOxygen(j)
ElseIf arrLabel(j) = "Mode 4" Then
Count4 = Count4 + 1
arrMode4(CountCycle, Count4, 1) = arrTime(j)
arrMode4(CountCycle, Count4, 2) = arrBedT(j)
arrMode4(CountCycle, Count4, 3) = arrRUEGO(j)
arrMode4(CountCycle, Count4, 4) = arrOxygen(j)
Else
End If
Next j
''''''''''''''''''''''''''''''''
'Do calculations on new arrays '
''''''''''''''''''''''''''''''''
For i = 1 To Cycles
'Time at end of Mode 4
N = NumElements(arrMode4, 2)
arrValues(i, 1) = arrMode4(i, N, 1)
'Average Control Oxygen for Modes 3 and 4 - 5 sec after mode 3 begins
SumOxygen = 0
N = NumElements(arrMode3, 2)
M = NumElements(arrMode4, 2)
For j = 5 To N
SumOxygen = SumOxygen + arrMode3(i, j, 4)
Next j
For k = 1 To M
SumOxygen = SumOxygen + arrMode4(i, k, 4)
Next k
arrValues(i, 2) = SumOxygen / (N + M - 5)
'Front UEGO averaged for Mode 2 and 3 - 3 sec after start of Mode 2
SumFUEGO = 0
N = NumElements(arrMode2, 2)
M = NumElements(arrMode3, 2)
For j = 3 To N
SumFUEGO = SumFUEGO + arrMode2(i, j, 2)
Next j
For k = 1 To M
SumFUEGO = SumFUEGO + arrMode3(i, j, 3)
Next k
arrValues(i, 3) = SumFUEGO / (N + M - 3)
'Inlet Temp is average temperature at mode 1 - 10 last seconds
averaged
SumInletTemp = 0
N = NumElements(arrMode1, 2)
For j = (N - 10) To N
SumInletTemp = SumInletTemp + arrMode1(i, j, 2)
Next j
arrValues(i, 4) = SumInletTemp / 10
'T Max Bed T for all modes
M = NumElements(arrMode1, 2)
N = NumElements(arrMode2, 2)
O = NumElements(arrMode3, 2)
P = NumElements(arrMode4, 2)
MaxValue = 0
For j = 1 To M
If arrMode1(i, j, 1) > MaxValue Then
MaxValue = arrMode1(i, j, 1)
End If
Next j
For j = 1 To N
If arrMode2(i, j, 1) > MaxValue Then
MaxValue = arrMode2(i, j, 1)
End If
Next j
For j = 1 To O
If arrMode3(i, j, 1) > MaxValue Then
MaxValue = arrMode3(i, j, 1)
End If
Next j
For j = 1 To P
If arrMode4(i, j, 1) > MaxValue Then
MaxValue = arrMode4(i, j, 1)
End If
Next j
arrValues(i, 5) = MaxValue
'RUEGO peak during Modes 2 and 3
M = NumElements(arrMode2, 2)
N = NumElements(arrMode3, 2)
MaxValue = 0
For j = 1 To M
If arrMode2(i, j, 3) > MaxValue Then
MaxValue = arrMode2(i, j, 3)
End If
Next j
For j = 1 To N
If arrMode3(i, j, 2) > MaxValue Then
MaxValue = arrMode3(i, j, 2)
End If
Next j
arrValues(i, 6) = MaxValue
'R UEGO peak during Modes 4 and the next cycle's 1
M = NumElements(arrMode4, 2)
N = NumElements(arrMode1, 2)
MaxValue = 0
For j = 1 To M
If arrMode4(i, j, 3) > MaxValue Then
MaxValue = arrMode4(i, j, 3)
End If
Next j
For j = 1 To N
If arrMode1(i + 1, j, 3) > MaxValue Then
MaxValue = arrMode1(i + 1, j, 3)
End If
Next j
arrValues(i, 7) = MaxValue
ActiveSheet.Range("Y9:AF" & Cycles + 8).Value = arrValues
Next
End Function
procedure:
1) Store data in arrays (arrTime, arrInletT, etc.)
2) Label data into four Modes of operation. (arrLabel)
3) Use the labels to store the data into four arrays (arrMode1, arrMode2,
etc.)
4) Perform seven calcs on the four Modes and store them in a 2-dimensional
array that is pasted back into Excel. (arrValues)
The data goes in order, Mode 1 for 40sec, Mode2 for 6sec, Mode 3 for 10sec,
Mode4 for 4sec, then repeats. This procedure is done for 50hours.
Because I'm new to arrays, the code is very rough and ill-defined. I think
it might work up to the calculations portion, but where I try to find out the
number of elements of the mode arrays will not work. The number of seconds
dont really stay constant... for example, Mode 1 could be 38-42 seconds long.
I do not know how to find out the #sec for that cycle. I need something
else besides NumElements.
Can someone straighten out my mess and get this code to work??
THANKS SO MUCH!
Matt
Function Label_Av(Cycles As Long)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Declare Arrays
ReDim arrValues(1 To Cycles, 1 To 8) As Variant
ReDim arrTime(8 To LastRow) As Double
ReDim arrInletT(8 To LastRow) As Double
ReDim arrBedT(8 To LastRow) As Double
ReDim arrRUEGO(8 To LastRow) As Double
ReDim arrLabel(8 To LastRow) As String
ReDim arrFUEGO(8 To LastRow) As Double
ReDim arrOxygen(8 To LastRow) As Double
ReDim arrTemp(8 To LastRow) As Double
CatTemp = WorksheetFunction.Average(Range("K100:K200"))
'Populate Arrays
For j = 8 To LastRow
arrTime(j) = Range("A" & j).Value
arrFUEGO(j) = Range("P" & j).Value
arrInletT(j) = Range("M" & j).Value
arrBedT(j) = Range("N" & j).Value
arrRUEGO(j) = Range("R" & j).Value
arrOxygen(j) = Range("E" & j).Value
arrTemp(j) = Range("K" & j).Value
Next j
'''''''''''''''''
'Label Modes '
'''''''''''''''''
For j = 8 To LastRow
If arrTemp(j) < CatTemp - 5 Then
arrLabel(j) = "Not Aging"
Else
If Round(arrFUEGO(j), 1) <= 0.95 Then
If arrOxygen(j) > 0 Then
arrLabel(j) = "Mode 3"
Else
arrLabel(j) = "Mode 2"
End If
Else
If arrOxygen(j) > 0 Then
arrLabel(j) = "Mode 4"
Else
arrLabel(j) = "Mode 1"
End If
End If
End If
Next j
ActiveSheet.Range("X8:X" & LastRow).Value =
Application.Transpose(arrLabel)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Perform calcs on four modes and place in separate table '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Populate Mode arrays with data to perform calculations
ReDim arrMode1(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode2(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode3(1 To LastRow, 1 To 100, 1 To 5)
ReDim arrMode4(1 To LastRow, 1 To 100, 1 To 5)
CountCycle = 0
For j = 8 To LastRow
If arrLabel(j) = "Mode 1" Then
If Not arrLabel(j - 1) = "Mode 1" Then
CountCycle = CountCycle + 1
Count1 = 1
Count2 = 0
Count3 = 0
Count4 = 0
Else
End If
arrMode1(CountCycle, Count1, 1) = arrBedT(j)
arrMode1(CountCycle, Count1, 2) = arrInletT(j)
arrMode1(CountCycle, Count1, 3) = arrRUEGO(j)
ElseIf arrLabel(j) = "Mode 2" Then
Count2 = Count2 + 1
arrMode2(CountCycle, Count2, 1) = arrBedT(j)
arrMode2(CountCycle, Count2, 2) = arrFUEGO(j)
arrMode2(CountCycle, Count2, 3) = arrRUEGO(j)
ElseIf arrLabel(j) = "Mode 3" Then
Count3 = Count3 + 1
arrMode3(CountCycle, Count3, 1) = arrBedT(j)
arrMode3(CountCycle, Count3, 2) = arrRUEGO(j)
arrMode3(CountCycle, Count3, 3) = arrFUEGO(j)
arrMode3(CountCycle, Count3, 4) = arrOxygen(j)
ElseIf arrLabel(j) = "Mode 4" Then
Count4 = Count4 + 1
arrMode4(CountCycle, Count4, 1) = arrTime(j)
arrMode4(CountCycle, Count4, 2) = arrBedT(j)
arrMode4(CountCycle, Count4, 3) = arrRUEGO(j)
arrMode4(CountCycle, Count4, 4) = arrOxygen(j)
Else
End If
Next j
''''''''''''''''''''''''''''''''
'Do calculations on new arrays '
''''''''''''''''''''''''''''''''
For i = 1 To Cycles
'Time at end of Mode 4
N = NumElements(arrMode4, 2)
arrValues(i, 1) = arrMode4(i, N, 1)
'Average Control Oxygen for Modes 3 and 4 - 5 sec after mode 3 begins
SumOxygen = 0
N = NumElements(arrMode3, 2)
M = NumElements(arrMode4, 2)
For j = 5 To N
SumOxygen = SumOxygen + arrMode3(i, j, 4)
Next j
For k = 1 To M
SumOxygen = SumOxygen + arrMode4(i, k, 4)
Next k
arrValues(i, 2) = SumOxygen / (N + M - 5)
'Front UEGO averaged for Mode 2 and 3 - 3 sec after start of Mode 2
SumFUEGO = 0
N = NumElements(arrMode2, 2)
M = NumElements(arrMode3, 2)
For j = 3 To N
SumFUEGO = SumFUEGO + arrMode2(i, j, 2)
Next j
For k = 1 To M
SumFUEGO = SumFUEGO + arrMode3(i, j, 3)
Next k
arrValues(i, 3) = SumFUEGO / (N + M - 3)
'Inlet Temp is average temperature at mode 1 - 10 last seconds
averaged
SumInletTemp = 0
N = NumElements(arrMode1, 2)
For j = (N - 10) To N
SumInletTemp = SumInletTemp + arrMode1(i, j, 2)
Next j
arrValues(i, 4) = SumInletTemp / 10
'T Max Bed T for all modes
M = NumElements(arrMode1, 2)
N = NumElements(arrMode2, 2)
O = NumElements(arrMode3, 2)
P = NumElements(arrMode4, 2)
MaxValue = 0
For j = 1 To M
If arrMode1(i, j, 1) > MaxValue Then
MaxValue = arrMode1(i, j, 1)
End If
Next j
For j = 1 To N
If arrMode2(i, j, 1) > MaxValue Then
MaxValue = arrMode2(i, j, 1)
End If
Next j
For j = 1 To O
If arrMode3(i, j, 1) > MaxValue Then
MaxValue = arrMode3(i, j, 1)
End If
Next j
For j = 1 To P
If arrMode4(i, j, 1) > MaxValue Then
MaxValue = arrMode4(i, j, 1)
End If
Next j
arrValues(i, 5) = MaxValue
'RUEGO peak during Modes 2 and 3
M = NumElements(arrMode2, 2)
N = NumElements(arrMode3, 2)
MaxValue = 0
For j = 1 To M
If arrMode2(i, j, 3) > MaxValue Then
MaxValue = arrMode2(i, j, 3)
End If
Next j
For j = 1 To N
If arrMode3(i, j, 2) > MaxValue Then
MaxValue = arrMode3(i, j, 2)
End If
Next j
arrValues(i, 6) = MaxValue
'R UEGO peak during Modes 4 and the next cycle's 1
M = NumElements(arrMode4, 2)
N = NumElements(arrMode1, 2)
MaxValue = 0
For j = 1 To M
If arrMode4(i, j, 3) > MaxValue Then
MaxValue = arrMode4(i, j, 3)
End If
Next j
For j = 1 To N
If arrMode1(i + 1, j, 3) > MaxValue Then
MaxValue = arrMode1(i + 1, j, 3)
End If
Next j
arrValues(i, 7) = MaxValue
ActiveSheet.Range("Y9:AF" & Cycles + 8).Value = arrValues
Next
End Function