Excel macro to visualize recorded respiration data from XeThru Explorer 2.0

Excel macro to visualize recorded respiration data from XeThru Explorer

  1. IngarH
    Instructions attached as PDF (Download Button)

    Code:
    '
    ' Process_Recordings Macro
    
    '
    
    '
    
    Dim AllStates As Double
    
    Range("A4").Select
    Selection.Cut Destination:=Range("O15")
    Range("A12").Select
    Selection.Cut Destination:=Range("O16")
    Range("A6").Select
    Selection.Cut Destination:=Range("O17")
    Range("A7").Select
    Selection.Cut Destination:=Range("O18")
    Rows("1:14").Select
    Selection.Delete Shift:=xlUp
    
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    
    RowCount = Application.WorksheetFunction.CountA(Range("A:A"))
    
    Rows(RowCount).Select
    Selection.Delete Shift:=xlUp
    
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("B2").Select
    
    ActiveCell.FormulaR1C1 = "=DATEVALUE(MID(RC[-1],1,10)) + TIMEVALUE(MID(RC[-1],12,8))"
    Selection.NumberFormat = "hh:mm;@"
    
    RowCount = Application.WorksheetFunction.CountA(Range("A:A"))
    
    Range("B2:B" & RowCount).FillDown
    
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "State"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "3"
    
    CountState0 = Application.WorksheetFunction.CountIf(Range("C:C"), Range("I1"))
    CountState1 = Application.WorksheetFunction.CountIf(Range("C:C"), Range("J1"))
    CountState2 = Application.WorksheetFunction.CountIf(Range("C:C"), Range("K1"))
    CountState3 = Application.WorksheetFunction.CountIf(Range("C:C"), Range("L1"))
    
    Range("I2").Select
    ActiveCell.Value = CountState0
    Range("J2").Select
    ActiveCell.Value = CountState1
    Range("K2").Select
    ActiveCell.Value = CountState2
    Range("L2").Select
    ActiveCell.Value = CountState3
    AllStates = CLng(CountState0) + CLng(CountState1) + CLng(CountState2) + CLng(CountState3)
    
    Range("I3").Select
    ActiveCell.Value = CountState0 / AllStates
    Selection.NumberFormat = "0.00 %"
    Range("J3").Select
    ActiveCell.Value = CountState1 / AllStates
    Selection.NumberFormat = "0.00 %"
    Range("K3").Select
    ActiveCell.Value = CountState2 / AllStates
    Selection.NumberFormat = "0.00 %"
    Range("L3").Select
    ActiveCell.Value = CountState3 / AllStates
    Selection.NumberFormat = "0.00 %"
    
    Range("H4").Select
    ActiveCell.Value = AllStates / (CLng(86400) * 20)
    Selection.NumberFormat = "hh:mm:ss;@"
    
    Range("I4").Select
    ActiveCell.Value = CountState0 / (CLng(86400) * 20)
    Selection.NumberFormat = "hh:mm:ss;@"
    
    Range("J4").Select
    ActiveCell.Value = CountState1 / (CLng(86400) * 20)
    Selection.NumberFormat = "hh:mm:ss;@"
    
    Range("K4").Select
    ActiveCell.Value = CountState2 / (CLng(86400) * 20)
    Selection.NumberFormat = "hh:mm:ss;@"
    
    Range("L4").Select
    ActiveCell.Value = CountState3 / (CLng(86400) * 20)
    Selection.NumberFormat = "hh:mm:ss;@"
    
    Rem remove all zeros in RPM, and Distance, replace with ""
    
    Columns("D:E").Select
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Zoom:"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "1"
    
    Range("T1").Select
    ActiveCell.Formula = "=B1"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=OFFSET(RC[-18],R2C14,0)"
    Selection.NumberFormat = "hh:mm;@"
    Range("T2:T2002").FillDown
    
    Range("U1").Select
    ActiveCell.Formula = "=F1"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "=OFFSET(RC[-15],R2C14,0)"
    Range("U2:U2002").FillDown
    
    Rem State plot:
    Range("B:B,C:C").Select
    Range("C1").Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range("$B:$B,$C:$C")
    ActiveSheet.Shapes("Chart 1").IncrementLeft -100.2
    ActiveSheet.Shapes("Chart 1").IncrementTop -80.4
    ActiveSheet.Shapes("Chart 1").ScaleHeight 0.4527777778, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleWidth 2.135, msoFalse, msoScaleFromTopLeft
    
    With ActiveChart.Parent
    .Height = 100 ' resize
    .Width = 600 ' resize
    .Top = 60 ' reposition
    .Left = 50 ' reposition
    End With
    
    Rem RPM plot:
    Range("B:B,D:D").Select
    Range("D1").Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range("$B:$B, $D:$D")
    ActiveSheet.Shapes("Chart 2").IncrementLeft -99.6
    ActiveSheet.Shapes("Chart 2").IncrementTop 22.8
    ActiveSheet.Shapes("Chart 2").ScaleWidth 2.135, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 2").ScaleHeight 0.4638888889, msoFalse, msoScaleFromTopLeft
    
    With ActiveChart.Parent
    .Height = 150 ' resize
    .Width = 600 ' resize
    .Top = 160 ' reposition
    .Left = 50 ' reposition
    End With
    
    Rem Object Distance plot:
    Range("B:B,E:E").Select
    Range("E1").Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range("$B:$B, $E:$E")
    ActiveSheet.Shapes("Chart 3").IncrementLeft -99
    ActiveSheet.Shapes("Chart 3").IncrementTop 129
    ActiveSheet.Shapes("Chart 3").ScaleWidth 2.1316666667, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 3").ScaleHeight 0.5805555556, msoFalse, msoScaleFromBottomRight
    
    With ActiveChart.Parent
    .Height = 150 ' resize
    .Width = 600 ' resize
    .Top = 310 ' reposition
    .Left = 50 ' reposition
    End With
    
    Rem Signal Quality plot:
    Range("B:B,G:G").Select
    Range("G1").Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range("$B:$B, $G:$G")
    ActiveSheet.Shapes("Chart 4").IncrementLeft -99
    ActiveSheet.Shapes("Chart 4").IncrementTop 129
    ActiveSheet.Shapes("Chart 4").ScaleWidth 2.1316666667, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 4").ScaleHeight 0.5805555556, msoFalse, msoScaleFromBottomRight
    
    With ActiveChart.Parent
    .Height = 150 ' resize
    .Width = 600 ' resize
    .Top = 460 ' reposition
    .Left = 50 ' reposition
    End With
    
    
    Rem Object Movement plot:
    Range("T1:T2002,U1:U2002").Select
    Range("T1").Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
    ActiveChart.SetSourceData Source:=Range("$T$2:$T$1002, $U$2:$U$1002")
    ActiveSheet.Shapes("Chart 5").IncrementLeft -99
    ActiveSheet.Shapes("Chart 5").IncrementTop 129
    ActiveSheet.Shapes("Chart 5").ScaleWidth 2.1316666667, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 5").ScaleHeight 0.5805555556, msoFalse, msoScaleFromBottomRight
    
    With ActiveChart.Parent
    .Height = 300 ' resize
    .Width = 600 ' resize
    .Top = 60 ' reposition
    .Left = 650 ' reposition
    End With
    
    excel_dump2.jpg excel-dump1.jpg