Skip to content

Instantly share code, notes, and snippets.

@alpsayin
Last active May 3, 2022 20:56
Show Gist options
  • Save alpsayin/930d708146d36626e41c37f6f57f1843 to your computer and use it in GitHub Desktop.
Save alpsayin/930d708146d36626e41c37f6f57f1843 to your computer and use it in GitHub Desktop.
Dekunu Jump Log Excel Macros
Attribute VB_Name = "DekunuJumpLogMacros"
Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column
Dim lRow As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "Last Row: " & lRow & vbNewLine & _
"Last Column: " & lCol
End Sub
Sub f1_bold_and_freeze_top_row()
Attribute f1_bold_and_freeze_top_row.VB_ProcData.VB_Invoke_Func = " \n14"
'
' bold_and_freeze_top_row Macro
'
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Cells.EntireColumn.AutoFit
ActiveWindow.Zoom = 85
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Selection.AutoFilter
Columns("B:B").ColumnWidth = 6
Columns("C:C").ColumnWidth = 7.71
Columns("E:E").ColumnWidth = 7.86
Columns("F:F").ColumnWidth = 7.14
Columns("G:G").ColumnWidth = 7.57
Columns("H:H").ColumnWidth = 5.14
Columns("K:K").ColumnWidth = 5
Range("L:L,S:S,AI:AI").ColumnWidth = 15.57
Columns("M:M").ColumnWidth = 7.71
Columns("N:N").ColumnWidth = 7.29
Columns("O:O").ColumnWidth = 8
Columns("P:P").ColumnWidth = 8.43
Columns("Q:Q").ColumnWidth = 7.29
Columns("R:R").ColumnWidth = 8.43
Columns("T:T").ColumnWidth = 6.43
Columns("AA:AA").ColumnWidth = 8.86
Columns("AB:AB").ColumnWidth = 6
Columns("AC:AC").ColumnWidth = 6.43
Columns("AD:AD").ColumnWidth = 7.57
Columns("AE:AE").ColumnWidth = 8
Columns("AF:AF").ColumnWidth = 8.86
Columns("AG:AG").ColumnWidth = 8.86
Columns("AH:AH").ColumnWidth = 8.14
Columns("AI:AI").ColumnWidth = 8.43
Columns("AJ:AJ").ColumnWidth = 8.86
Columns("AL:AL").ColumnWidth = 4
Columns("AM:AM").ColumnWidth = 7.57
Columns("AN:AN").ColumnWidth = 6
Columns("AO:AO").ColumnWidth = 7
Rows("1:1").Select
Selection.RowHeight = 78.75
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub f2_Contextualise_columns()
'
' Contextualise_columns Macro
'
'
Dim lRow As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Columns("B:B").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
Columns("AE:AE").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AE1").Select
ActiveCell.FormulaR1C1 = "10-sample speed"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "30-sample speed"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "100-sample speed"
Columns("AE:AE").EntireColumn.AutoFit
Columns("AF:AF").EntireColumn.AutoFit
Columns("AG:AG").EntireColumn.AutoFit
Range("AE12").Select
ActiveCell.FormulaR1C1 = "=(RC[-2]-R[-10]C[-2])*1000/(RC[-30]-R[-10]C[-30])"
Range("AF32").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-R[-30]C[-3])*1000/(RC[-31]-R[-30]C[-31])"
Range("AG102").Select
ActiveCell.FormulaR1C1 = "=(RC[-4]-R[-100]C[-4])*1000/(RC[-32]-R[-100]C[-32])"
Range("AE12").Select
Selection.AutoFill Destination:=Range("AE12:AE" & lastRow)
Range("AF32").Select
Selection.AutoFill Destination:=Range("AF32:AF" & lastRow)
Range("AG102").Select
Selection.AutoFill Destination:=Range("AG102:AG" & lastRow)
Columns("AE:AG").Select
Selection.NumberFormat = "0.00"
Columns("F:F").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Columns("H:H").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Columns("K:K").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Range("M:M,O:O").Select
Range("O1").Activate
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Columns("C:C").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueNumber
.Value = 0.33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueNumber
.Value = 0.67
.Operator = 7
End With
Columns("AA:AC").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 15698432
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 15698432
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Columns("AB:AB").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 8700771
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 8700771
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Range("L:L,AL:AL,S:S").Select
Range("S1").Activate
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Columns("AD:AG").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=-50
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=10
End With
With Selection.FormatConditions(1).BarColor
.Color = 8061142
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 8061142
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Columns("P:P").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=130
End With
With Selection.FormatConditions(1).BarColor
.Color = 5920255
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 5920255
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Range("R:R,AK:AK").Select
Range("AK1").Activate
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 15
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 13011546
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 35
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 16776444
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 55
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Range("A1").Select
End Sub
Sub f3_hide_usually_unused_columns()
'
' hide_usually_unused_columns Macro
'
'
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Columns("T:Z").Select
Selection.EntireColumn.Hidden = True
End Sub
Sub f4_Compute_TimeDeltas()
'
' Compute_TimeDeltas Macro
'
'
Dim lRow As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "Delta"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & lastRow)
Range("B:B").Select
Columns("B:B").Select
Columns("B:B").ColumnWidth = 7.86
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 100
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 1501
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 3001
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub f5_ConvertGPStime()
'
' f5_ConvertGPStime Macro
'
'
Dim lRow As Long
Dim lCol As Long
'Find the last non-blank cell in column A(1)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").Select
ActiveCell.FormulaR1C1 = "humanTimestamp"
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=((R[0]C[-2]+R[0]C[-1]/100)/86400)+DATE(1970,1,1)"
Range("G2").Select
Selection.NumberFormat = "[$-en-GB]dd-mmm-yyyy hh:mm:ss.00"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & lastRow)
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = "gpsTimeDiff"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-R[-1]C[-3])*1000+(RC[-2]-R[-1]C[-2])*10"
Range("H3").Select
Selection.NumberFormat = "0"
Selection.AutoFill Destination:=Range("H3:H" & lastRow)
Columns("H:H").ColumnWidth = 7.86
Range("H:H").Select
Columns("H:H").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 100
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 1501
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 3001
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub f6_timestampBars()
'
' BarsToTimestamp Macro
'
'
Columns("A:A").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueLowestValue
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With Selection.FormatConditions(1).BarColor
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlRTL
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
End Sub
Sub f7_plotAltitudes()
'
' plotAltitudes Macro
'
'
Dim newShape As Object
Dim newChart As Object
Sheets(1).Select
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = "Altitude Plots"
Sheets(1).Select
Range("G:G,O:O,V:V,AD:AD,AF:AF,AO:AO").Select
Range("AO1").Activate
Set newShape = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers)
newShape.Select
Set newChart = newShape.Chart
newChart.SetSourceData Source:=Range( _
"$G:$G,$O:$O,$V:$V,$AD:$AD,$AF:$AF,$AO:$AO" _
)
With newChart
.ChartTitle.Text = "Altitude Plots"
With .Parent
.Left = Sheets("Altitude Plots").Range("A1").Left
.Top = Sheets("Altitude Plots").Range("A1").Top
.Width = Sheets("Altitude Plots").Range("A1:AM1").Width
.Height = Sheets("Altitude Plots").Range("A1:A56").Height
End With
.Location xlLocationAsObject, "Altitude Plots"
End With
ActiveSheet.ChartObjects("Chart 1").Activate
Sheets("Altitude Plots").Select
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).AxisGroup = 2
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
ActiveChart.FullSeriesCollection(4).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
ActiveChart.FullSeriesCollection(5).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineDash
End With
Range("A1").Select
End Sub
Sub f8_plotVerticalSpeeds()
'
' Macro3 Macro
'
'
Dim lRow As Long
Dim lCol As Long
Dim newShape As Object
Dim newChart As Object
'Find the last non-blank cell in column A(1)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Sheets(1).Select
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = "Vert. Speed Plots"
Sheets(1).Select
Range("G:G,AG:AG,AH:AH,AI:AI,AJ:AJ").Select
Range("AJ1").Activate
Set newShape = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers)
newShape.Select
Set newChart = newShape.Chart
newChart.SetSourceData Source:=Range( _
"$G:$G,$AG:$AG,$AH:$AH,$AI:$AI,$AJ:$AJ" _
)
With newChart
.ChartTitle.Text = "Vert. Speed Plots"
With .Parent
.Left = Sheets("Vert. Speed Plots").Range("A1").Left
.Top = Sheets("Vert. Speed Plots").Range("A1").Top
.Width = Sheets("Vert. Speed Plots").Range("A1:AM1").Width
.Height = Sheets("Vert. Speed Plots").Range("A1:A56").Height
End With
.Location xlLocationAsObject, "Vert. Speed Plots"
End With
ActiveSheet.ChartObjects("Chart 1").Activate
Sheets("Vert. Speed Plots").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = -0.5
.Transparency = 0
End With
ActiveChart.FullSeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 92, 0)
.Transparency = 0
End With
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
ActiveChart.FullSeriesCollection(4).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(112, 48, 160)
.Transparency = 0
End With
Range("A1").Select
End Sub
Sub f9_plotAltAboveGndPow5()
'
' Macro7 Macro
'
'
Dim lRow As Long
Dim lCol As Long
Dim newShape As Object
Dim newChart As Object
Sheets(1).Select
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = "Alt^5 Plot"
Sheets(1).Select
'Find the last non-blank cell in column A(1)
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Find the last non-blank cell in row 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range("AP1").Select
ActiveCell.FormulaR1C1 = "altAboveGndPow5"
Range("AP2").Select
ActiveCell.FormulaR1C1 = "=POWER(RC[-10],5)"
Range("AP2").Select
Selection.AutoFill Destination:=Range("AP2:AP" & lastRow)
Range("G:G,AP:AP").Select
Range("AP1").Activate
Set newShape = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers)
newShape.Select
Set newChart = newShape.Chart
newChart.SetSourceData Source:=Range("$G:$G,$AP:$AP" _
)
With newChart
.ChartTitle.Text = "AltitudeAboveGround^5 Plot (for highlighting fluctuations)"
With .Parent
.Left = Sheets("Alt^5 Plot").Range("A1").Left
.Top = Sheets("Alt^5 Plot").Range("A1").Top
.Width = Sheets("Alt^5 Plot").Range("A1:AM1").Width
.Height = Sheets("Alt^5 Plot").Range("A1:A56").Height
End With
.Location xlLocationAsObject, "Alt^5 Plot"
End With
ActiveSheet.ChartObjects("Chart 1").Activate
Range("A1").Select
End Sub
Sub saveAsXlsxWithPrefix()
'
' saveAsNoProblems Macro
'
'
Dim FilePath, FileOnly, PathOnly As String
Dim intResult As Integer
FilePath = ActiveWorkbook.FullName
FileOnly = ActiveWorkbook.Name
PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))
Extension = Right(FileOnly, 4)
FileNameOnly = Left(FileOnly, Len(FileOnly) - Len(Extension))
' MsgBox PathOnly & " <> " & FileOnly & " <> " & Extension
intResult = StrComp(".csv", Extension, vbTextCompare)
If (intResult = 0) Then
' MsgBox "Saving as XLSX"
Dim answer As Integer
Dim prefix As String
answer = MsgBox("Would you like to add a custom prefix before saving as XLSX?", vbQuestion + vbYesNo + vbDefaultButton2, "Anomaly check?")
If answer = vbYes Then
prefix = InputBox("Summarise the problem without spaces and in camelCase")
Else
prefix = "x"
End If
ActiveWorkbook.SaveAs Filename:= _
PathOnly & prefix & "_" & FileNameOnly & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Else
MsgBox "Already saved"
End If
End Sub
Sub fAll_processCSVlog()
Attribute fAll_processCSVlog.VB_ProcData.VB_Invoke_Func = "m\n14"
'
' fAll_processCSVlog Macro
'
'
Application.Run "PERSONAL.XLSB!saveAsXlsxWithPrefix"
Application.Run "PERSONAL.XLSB!f1_bold_and_freeze_top_row"
Application.Run "PERSONAL.XLSB!f2_Contextualise_columns"
Application.Run "PERSONAL.XLSB!f3_hide_usually_unused_columns"
Application.Run "PERSONAL.XLSB!f4_Compute_TimeDeltas"
Application.Run "PERSONAL.XLSB!f5_ConvertGPStime"
Application.Run "PERSONAL.XLSB!f6_timestampBars"
Application.Run "PERSONAL.XLSB!f7_plotAltitudes"
Application.Run "PERSONAL.XLSB!f8_plotVerticalSpeeds"
Application.Run "PERSONAL.XLSB!f9_plotAltAboveGndPow5"
ActiveWorkbook.Save
Sheets(1).Select
Range("A1").Select
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment