Sub a_Prepare_Data() ' ' Macro1 Macro ' Dim intNumberOfLinesToCopy As Integer Dim strNumberOfLinesToCopy As String Dim intColumnNumber As Integer Dim strColumnLetter As String Dim i As Integer ' intNumberOfLinesToCopy = Sheets("Raw Data").Range("A1").End(xlDown).Row strNumberOfLinesToCopy = intNumberOfLinesToCopy Sheets("Graph Data").Range("D2").Select Range("E2").Value = "of 108" For i = 2 To 108 strColumnLetter = strNumber2Letter(i) Cells(4, i).AutoFill Destination:=Range(strColumnLetter & "4:" & strColumnLetter & strNumberOfLinesToCopy) Sheets("Graph Data").Range("D2").Value = i Next i Range("A4").AutoFill Destination:=Range("A4:A" & strNumberOfLinesToCopy), Type:=xlFillSeries Sheets("Graph Data").Range("F2").Value = "Vmax = " Sheets("Graph Data").Range("G2").Formula = "=MAX(G4:G" & strNumberOfLinesToCopy & ")" Sheets("Graph Data").Range("BB1").Value = "Min = " Sheets("Graph Data").Range("BB2").Value = "Max = " Sheets("Graph Data").Range("BC1").Formula = "=MIN(BC4:BC" & strNumberOfLinesToCopy & ")" Sheets("Graph Data").Range("BC2").Formula = "=MAX(BC4:BC" & strNumberOfLinesToCopy & ")" Sheets("Graph Data").Range("BC1:BC2").AutoFill Destination:=Range("BC1:DD2") End Sub Function strNumber2Letter(intNum As Integer) As String Dim ChrNum As Long If intNum <= 130 Then ChrNum = intNum + 64 - 104 If ChrNum >= 65 Then strNumber2Letter = "D" & Chr(ChrNum) End If End If If intNum <= 104 Then ChrNum = intNum + 64 - 78 If ChrNum >= 65 Then strNumber2Letter = "C" & Chr(ChrNum) End If End If If intNum <= 78 Then ChrNum = intNum + 64 - 52 If ChrNum >= 65 Then strNumber2Letter = "B" & Chr(ChrNum) End If End If If intNum <= 52 Then ChrNum = intNum + 64 - 26 If ChrNum >= 65 Then strNumber2Letter = "A" & Chr(ChrNum) End If End If If intNum <= 26 Then ChrNum = intNum + 64 strNumber2Letter = Chr(ChrNum) End If End Function Sub Graph_Ignition_Misfire() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "Ignition Misfire" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' Ignition Misfire ' ' RPM(B) ' Cyl1 Firing(CT) ' Cyl2 Firing(CV) ' Cyl3 Firing(CX) ' Cyl4 Firing(CZ) ' Cyl5 Firing(DB) ' Cyl6 Firing(DD) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$B$" & GraphStartLine & ":$B$" & GraphEndLine & _ ",'Graph Data'!$CT$" & GraphStartLine & ":$CT$" & GraphEndLine & _ ",'Graph Data'!$CV$" & GraphStartLine & ":$CV$" & GraphEndLine & _ ",'Graph Data'!$CX$" & GraphStartLine & ":$CX$" & GraphEndLine & _ ",'Graph Data'!$CZ$" & GraphStartLine & ":$CZ$" & GraphEndLine & _ ",'Graph Data'!$DB$" & GraphStartLine & ":$DB$" & GraphEndLine & _ ",'Graph Data'!$DD$" & GraphStartLine & ":$DD$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$B$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$CT$3" ActiveChart.SeriesCollection(3).Name = "='Graph Data'!$CV$3" ActiveChart.SeriesCollection(4).Name = "='Graph Data'!$CX$3" ActiveChart.SeriesCollection(5).Name = "='Graph Data'!$CZ$3" ActiveChart.SeriesCollection(6).Name = "='Graph Data'!$DB$3" ActiveChart.SeriesCollection(7).Name = "='Graph Data'!$DD$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_Temperatures() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "Temperatures" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' Temperatures ' ' RPM(B) ' Coolant Temperature(D) ' Intake Temperature(F) ' Speed(H) ' Oil Temperature(AT) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$B$" & GraphStartLine & ":$B$" & GraphEndLine & _ ",'Graph Data'!$D$" & GraphStartLine & ":$D$" & GraphEndLine & _ ",'Graph Data'!$F$" & GraphStartLine & ":$F$" & GraphEndLine & _ ",'Graph Data'!$H$" & GraphStartLine & ":$H$" & GraphEndLine & _ ",'Graph Data'!$AT$" & GraphStartLine & ":$AT$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$B$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$D$3" ActiveChart.SeriesCollection(3).Name = "='Graph Data'!$F$3" ActiveChart.SeriesCollection(4).Name = "='Graph Data'!$H$3" ActiveChart.SeriesCollection(5).Name = "='Graph Data'!$AT$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_VANOS() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "VANOS" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' VANOS ' ' RPM(B) ' VANOS Intake (Target)(N) ' VANOS Intake (Actual)(P) ' VANOS Exhaust (Target)(R) ' VANOS Exhaust (Actual)(T) ' Air Mass (LMM)(AP) ' Air Mass (Luftmasse)(AR) ' Motor Under Load(BP) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$B$" & GraphStartLine & ":$B$" & GraphEndLine & _ ",'Graph Data'!$N$" & GraphStartLine & ":$N$" & GraphEndLine & _ ",'Graph Data'!$P$" & GraphStartLine & ":$P$" & GraphEndLine & _ ",'Graph Data'!$R$" & GraphStartLine & ":$R$" & GraphEndLine & _ ",'Graph Data'!$T$" & GraphStartLine & ":$T$" & GraphEndLine & _ ",'Graph Data'!$AP$" & GraphStartLine & ":$AP$" & GraphEndLine & _ ",'Graph Data'!$AR$" & GraphStartLine & ":$AR$" & GraphEndLine & _ ",'Graph Data'!$BP$" & GraphStartLine & ":$BP$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$B$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$N$3" ActiveChart.SeriesCollection(3).Name = "='Graph Data'!$P$3" ActiveChart.SeriesCollection(4).Name = "='Graph Data'!$R$3" ActiveChart.SeriesCollection(5).Name = "='Graph Data'!$T$3" ActiveChart.SeriesCollection(6).Name = "='Graph Data'!$AP$3" ActiveChart.SeriesCollection(7).Name = "='Graph Data'!$AR$3" ActiveChart.SeriesCollection(8).Name = "='Graph Data'!$BP$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_VANOS_Inlet() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "VANOS_Inlet" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' VANOS_Inlet ' ' VANOS Intake (Target)(M) ' VANOS Intake (Actual)(O) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$M$" & GraphStartLine & ":$M$" & GraphEndLine & _ ",'Graph Data'!$O$" & GraphStartLine & ":$O$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$M$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$O$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_VANOS_Exhaust() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "VANOS_Exhaust" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' VANOS_Exhaust ' ' VANOS Exhaust (Target)(Q) ' VANOS Exhaust (Actual)(S) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$Q$" & GraphStartLine & ":$Q$" & GraphEndLine & _ ",'Graph Data'!$S$" & GraphStartLine & ":$S$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$Q$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$S$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_RPM_v_Air() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "RPM_v_Air" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' RPM v Air ' ' RPM(B) ' Throttle %(L) ' Air Mass (LMM)(AP) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$B$" & GraphStartLine & ":$B$" & GraphEndLine & _ ",'Graph Data'!$L$" & GraphStartLine & ":$L$" & GraphEndLine & _ ",'Graph Data'!$AP$" & GraphStartLine & ":$AP$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$B$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$L$3" ActiveChart.SeriesCollection(3).Name = "='Graph Data'!$AP$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub Graph_RPM_v_Speed() ' ' Graphs_Creator Macro ' Dim GraphStartLine As Integer Dim GraphEndLine As Integer Dim GraphName As String GraphName = "RPM_v_Speed" On Error GoTo ErrorHandler GraphStartLine = Sheets("Graph Data").Range("C1").Value GraphEndLine = Sheets("Graph Data").Range("C2").Value If GraphEndLine <= GraphStartLine Then Response = MsgBox("Graph Stop Line must be bigger than Graph Start Line", vbOKOnly, "Silly Billy") Exit Sub End If If GraphStartLine < 4 Then Response = MsgBox("Graph Start Line must be at least 4", vbOKOnly, "Silly Billy") Exit Sub End If ' ' RPM v Speed ' ' RPM(B) ' Speed(H) ' ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source:=Range( _ "'Graph Data'!$B$" & GraphStartLine & ":$B$" & GraphEndLine & _ ",'Graph Data'!$H$" & GraphStartLine & ":$H$" & GraphEndLine) ActiveChart.ChartType = xlLine ActiveChart.Axes(xlValue).MajorGridlines.Delete ActiveChart.SeriesCollection(1).XValues = "='Graph Data'!$A$" & GraphStartLine & ":$A$" & GraphEndLine ActiveChart.SeriesCollection(1).Name = "='Graph Data'!$B$3" ActiveChart.SeriesCollection(2).Name = "='Graph Data'!$H$3" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=GraphName Sheets(GraphName).Move After:=Sheets(3) Sheets("Graph Data").Select Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. Case 1004 ' "File already exists" error. Application.DisplayAlerts = False Sheets(GraphName).Delete Application.DisplayAlerts = True Resume Case Else ' Handle other situations here... End Select End Sub Sub z_DeleteBeforeClosing() ' '----------------------------------------------------------- ' ' Do you really want to do this? Dim Response As VbMsgBoxResult Response = MsgBox("Do you want to delete all the graphical data?" & Chr(10) & Chr(10) & _ "(The raw data will not be deleted by this action)", vbYesNo, "Delete Graphical Data") If Response = vbNo Then Exit Sub ' '----------------------------------------------------------- ' 'On Error GoTo ErrorHandler Sheets("Graph Data").Move Before:=Sheets(1) Sheets("Raw Data").Move Before:=Sheets(1) If Sheets.Count > 2 Then Application.DisplayAlerts = False Do Until Sheets.Count = 2 Sheets(3).Delete Loop Application.DisplayAlerts = True End If Sheets("Graph Data").Select Rows("5:5").Select Range(Selection, Selection.End(xlDown)).Delete Shift:=xlUp Range("A1:DD2").ClearContents Range("A1").Select Exit Sub ErrorHandler: ' Error-handling routine. Select Case Err.Number ' Evaluate error number. ' Handle situation here Case Else ' Handle other situations here... End Select End Sub