Attribute VB_Name = "Module7" ' Macro NormarizeWaveforms() ' Analyze the time measurement waveform files automatically. The files are recorded by MSO9404A ' scope. To run this macro, first need to prepare the data files, they should be located in the ' subdirectory as indicated in the FileName below. All files should started with "T", the followed ' by numbers, such as 1,..., 1000. You can use a program "Bulk Rename Utility" to make the global ' name change. ' C. Lu 2/18/2012 ' Noramrize the waveforms by dividing each data point with the absolute value of the minimum from each ' waveform. ' C. Lu 5/9/2012 Sub normarizeWaveForms() Dim Order As String Dim FileName, FileNameSave As String Dim i As Integer Dim w As Workbook ' Speed up the calculation do the following Application.ScreenUpdating = False For i = 101 To 9422 Order = CStr(i) FileName = "C:\Users\Lu\Fast Timing\0507312\4mm^2\1775V\T" & Order & ".csv" FileNameSave = "C:\Users\Lu\Fast Timing\0507312\4mm^2\1775V\TT" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook ' Normarize two waveforms Range("B337").Select ActiveCell.FormulaR1C1 = "=MIN(R[-336]C:R[-2]C)" Range("B337:C337").Select Selection.FillRight Range("D335").Select ActiveCell.FormulaR1C1 = "=RC[-2]/ABS(R337C2)" Range("E335").Select ActiveCell.FormulaR1C1 = "=RC[-2]/ABS(R337C3)" Range("D335").Select Application.Goto Reference:="R1C4:R335C5" Selection.FillUp Selection.Copy Range("F1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:E").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft ChDir "C:\Users\Lu\Fast Timing\0507312\4mm^2\1775V" ActiveWorkbook.SaveAs FileNameSave ' Go back to previous opened workbook and activate it ' w.Activate ' Close this Workbook w/o saving the changes ActiveWorkbook.Close SaveChanges:=False 0 Next i Application.ScreenUpdating = True End Sub Sub OpenOneFile() ' Similar macro as openfile() but only opend one file ' CGL 2/19/2012 Dim Order As String Dim FileName As String Dim i As Integer Dim w As Workbook i = 50 Order = CStr(i) FileName = "C:\Users\Lu\Fast Timing\0227312\64mm^2\T" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName Else MsgBox ("No file found!") End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook ' Analize the data (eg. dT macro) Range("A1").Select Application.Goto Reference:="R1C1:R7000C3" Selection.Cut Application.CutCopyMode = False Selection.Delete Shift:=xlUp Application.Goto Reference:="R3001C1:R9000C3" Selection.Delete Shift:=xlToLeft Range("A1").Select Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ' Make constant fraction calculation, you can change the nagative constant -0.6 ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("C1").Select Application.Goto Reference:="R1C3:R801C3" Selection.FillDown ' Shift the inversed pulse up 100 cells Application.Goto Reference:="R101C3:R801C3" Selection.Copy Range("D1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("E1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]" Range("E1").Select Application.Goto Reference:="R1C5:R801C5" Selection.FillDown Range("F1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,0)" Range("F1").Select Application.Goto Reference:="R1C6:R801C6" Selection.FillDown Range("G1").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]" Range("G1").Select Application.Goto Reference:="R1C7:R801C7" Selection.FillDown Columns("H:H").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("H1").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-6]<-0.05),1,0)" Range("H1").Select Application.Goto Reference:="R1C8:R801C8" Selection.FillDown Range("I1").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-7]<-0.05),RC[-8],0)" Range("I1").Select Application.Goto Reference:="R1C9:R801C9" Selection.FillDown ' calculate channel 2 Columns("K:K").Select Range("K1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("K1").Select Application.Goto Reference:="R1C11:R801C11" Selection.FillDown Application.Goto Reference:="R101C11:R801C11" Selection.Copy Range("L1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("M1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]" Range("M1").Select Application.Goto Reference:="R1C13:R801C13" Selection.FillDown Range("N1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,1,0)" Range("N1").Select Application.Goto Reference:="R1C14:R801C14" Selection.FillDown Range("O1").Select ActiveCell.FormulaR1C1 = "=RC[-1]-R[1]C[-1]" Range("O1").Select Application.Goto Reference:="R1C15:R801C15" Selection.FillDown Range("P1").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-6]<-0.05),1,0)" Range("P1").Select Application.Goto Reference:="R1C16:R801C16" Selection.FillDown Range("Q1").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-1]>0,RC[-7]<-0.05),RC[-16],0)" Range("Q1").Select Application.Goto Reference:="R1C17:R801C17" Selection.FillDown ' Get the delta T Range("I2801").Select ActiveCell.FormulaR1C1 = "=SUM(R[-801]C:R[-1]C)" Range("H2801").Select ActiveCell.FormulaR1C1 = "=SUM(R[-801]C:R[-1]C)" Range("P2801").Select ActiveCell.FormulaR1C1 = "=SUM(R[-801]C:R[-1]C)" Range("Q2801").Select ActiveCell.FormulaR1C1 = "=SUM(R[-801]C:R[-1]C)" Range("R2801").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-10]=1,RC[-2]=1),RC[-9]-RC[-1],1000)" ' Copy dT to other file "dTsummary.xlsx" Selection.Copy Range("R2802").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Copy ' Windows("dTsummary.xlsx").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(1, 0).Activate ' Go back to previous opened workbook and activate it w.Activate ' Close this Workbook w/o saving the changes ' ActiveWorkbook.Close SaveChanges:=False End Sub Sub ConstantFractionNew() ' ' ConstantFraction Macro ' Find the zero crossing point with constant fraction principle. ' Multiply -0.5 to the original pulse, shift forward 100 cells, then add it to the ' original pulse. Search for the zero crossing point on x axis. ' 2/17/2012 CGL ' Try to apply this macro to ZVA&AM amplifier's waveform files ' 6/10/2012 CGL ' Dim Order As String Dim FileBase, HistFileName As String Dim FileName, FileNameSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\Fast Timing\1126312\" Application.ScreenUpdating = False HistName = "dTSummary.xlsx" HistFileName = FileBase & HistName ' cross is the normarized pulse crossing point for the time reference cross = 0.5 ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If For i = 313 To 500 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Prepare 5 new empty columns Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("C1").Select Application.Goto Reference:="R1C3:R801C3" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C3:R801C3" Selection.Copy Range("D1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("E1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]" Range("E1").Select Application.Goto Reference:="R1C5:R801C5" Selection.FillDown Range("F1").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(RC[-1]:R[300]C[-1]),RC[-1]:R[300]C[-1],0)" i1 = Cells(1, "F") v1max = Cells(i1, "E") ActiveSheet.Range(Cells(i1, 6), Cells(i1, 6)).Select ActiveCell.FormulaR1C1 = "=ABS(RC[-1])" ActiveSheet.Range(Cells(i1, 6), Cells(i1 + 100, 6)).Select Selection.FillDown ' Application.Goto Reference:="R[i]C[1]:R[i+50]C[1]" With ActiveSheet Set rngCopy = .Range(.Cells(i1, 6), .Cells(i1 + 99, 6)) Set rngPaste = .Range(.Cells(1, 7), .Cells(1 + 990, 7)) rngCopy.Copy rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With Range("F3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[-2]C[1]:R[98]C[1]),R[-2]C[1]:R[98]C[1],0)" j = Cells(1, "F") + Cells(3, "F") - 1 t1 = Cells(j, "A") ' Treat channel#2 Range("I1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("I1").Select Application.Goto Reference:="R1C9:R801C9" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C9:R801C9" Selection.Copy Range("J1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column K Range("K1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]" Range("K1").Select Application.Goto Reference:="R1C11:R801C11" Selection.FillDown Range("L1").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(RC[-1]:R[300]C[-1]),RC[-1]:R[300]C[-1],0)" ii = Cells(1, "L") v2max = Cells(ii, "K") ActiveSheet.Range(Cells(ii, 12), Cells(ii, 12)).Select ActiveCell.FormulaR1C1 = "=ABS(RC[-1])" ActiveSheet.Range(Cells(ii, 12), Cells(ii + 100, 12)).Select Selection.FillDown ' Application.Goto Reference:="R[i]C[1]:R[i+50]C[1]" With ActiveSheet Set rngCopy = .Range(.Cells(ii, 12), .Cells(ii + 99, 12)) Set rngPaste = .Range(.Cells(1, 13), .Cells(1 + 99, 13)) rngCopy.Copy rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With Range("L3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[-2]C[1]:R[98]C[1]),R[-2]C[1]:R[98]C[1],0)" jj = Cells(1, "L") + Cells(3, "L") - 1 t2 = Cells(jj, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Shift cell right dT = t1 - t2 ' Copy dT, t1, t2, V1min, V2min, V1max, V2max to dTHIST2 ActiveCell.FormulaR1C1 = dT ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v1max ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v2max ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = FileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -5).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub Sub ConstantFractionZX60Transformer() ' ' ConstantFraction Macro ' Find the zero crossing point with constant fraction principle. ' Multiply -0.5 to the original pulse, shift forward 100 cells, then add it to the ' original pulse. Search for the zero crossing point on x axis. ' 2/17/2012 CGL ' Try to apply this macro to ZVA&AM amplifier's waveform files ' 6/10/2012 CGL ' Modified to analyze the data set of ZX60-LN amplifiers ' 12/02/2012 CGL ' Tested on 12/3/2012, works. CGL Dim Order As String Dim FileBase, HistFileName, DataFileName As String Dim FileName, FileNameSave, DataFileSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\12072012\64mm^2\ZX60\-1725V\" Application.ScreenUpdating = False HistName = "dTSummary-12072012-1725V.xlsx" HistFileName = FileBase & HistName ' cross is the normarized pulse crossing point for the time reference cross = 0.5 ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "dT" Range("B1").Select ActiveCell.FormulaR1C1 = "t1" Range("C1").Select ActiveCell.FormulaR1C1 = "t2" Range("D1").Select ActiveCell.FormulaR1C1 = "V1max" Range("E1").Select ActiveCell.FormulaR1C1 = "V2max" Range("A2").Select For i = 1 To 4308 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" DataFileName = FileBase & FileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" DataFileSave = FileBase & FileNameSave ' Check if this file is existing If Dir(DataFileName) <> "" Then Workbooks.Open DataFileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Prepare 5 new empty columns Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("C2").Select Application.Goto Reference:="R2C3:R402C3" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C3:R401C3" Selection.Copy Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("E2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("E2").Select Application.Goto Reference:="R2C5:R402C5" Selection.FillDown Range("F2").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(RC[-1]:R[300]C[-1]),RC[-1]:R[300]C[-1],0)" i1 = Cells(2, "F") + 1 v1max = Cells(i1, "E") Range("F3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[135]C[-1]:R[200]C[-1]),R[135]C[-1]:R[200]C[-1],0)" i2 = Cells(3, "F") + 135 + 2 t1 = Cells(i2, "A") ' Treat channel#2 Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("I2").Select Application.Goto Reference:="R2C9:R402C9" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C9:R402C9" Selection.Copy Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column K Range("K2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("K2").Select Application.Goto Reference:="R2C11:R402C11" Selection.FillDown Range("L2").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(RC[-1]:R[300]C[-1]),RC[-1]:R[300]C[-1],0)" i1 = Cells(2, "L") + 1 v2max = Cells(i1, "K") Range("L3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[100]C[-1]:R[200]C[-1]),R[100]C[-1]:R[200]C[-1],0)" i2 = Cells(3, "L") + 100 + 2 t2 = Cells(i2, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-12072012-1725V.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Shift cell right dT = t1 - t2 ' Copy dT, t1, t2, V1min, V2min, V1max, V2max to dTHIST2 ActiveCell.FormulaR1C1 = dT ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v1max ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v2max ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = FileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -5).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub Sub ConstantFractionZX60TransformerBNLTest() ' ' ConstantFraction Macro for BNL beam test data file ' Find the zero crossing point with constant fraction principle. ' Multiply -0.5 to the original pulse, shift forward 100 cells, then add it to the ' original pulse. Search for the zero crossing point on x axis. ' 2/17/2012 CGL ' Tested on 12/3/2012, works. CGL ' Modified for BNL beam test data file ' 3/16/2013 CGL ' Modified for two channels: one is APD, the other is strip line. ' 4/8/2013 CGL ' Modified on 6/10/2013. Analyze three channels: strip, 2x2APD and 8x8APD. You ' can either use t1-t2 to get 2x2 APD time resolution, or use t1-t3 to get 8x8 ' APD time resolution. ' 6/10/2013 CGL Dim Order As String Dim FileBase, HistFileName, HistName, DataFileName As String Dim FileName, FileNameSave, DataFileSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook FileBase = "C:\Users\Lu\FastTiming\BNLBeamTest\ATF April 26\" Application.ScreenUpdating = False HistName = "dTSummary-ATFApril26-test.xlsx" HistFileName = FileBase & HistName ' cross is the normarized pulse crossing point for the time reference cross = 0.5 ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "ConstantFractionZX60TransformerBNLTest.macro" Range("A2").Select ActiveCell.FormulaR1C1 = "dT" Range("B2").Select ActiveCell.FormulaR1C1 = "t1" Range("C2").Select ActiveCell.FormulaR1C1 = "t2" Range("D2").Select ActiveCell.FormulaR1C1 = "t3" Range("E2").Select ActiveCell.FormulaR1C1 = "V1max" Range("F2").Select ActiveCell.FormulaR1C1 = "V2max" Range("G2").Select ActiveCell.FormulaR1C1 = "V3max" Range("A3").Select For i = 12 To 100 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" DataFileName = FileBase & FileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" DataFileSave = FileBase & FileNameSave ' Check if this file is existing If Dir(DataFileName) <> "" Then Workbooks.Open DataFileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "Temp" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Read original ATF beam test data file (ch#1 - strip line V, ch#2-8x8APD, ch#3-2x2APD, ch#4-strip H), ' select a useful signal section, plot two pulses. ' CGL 4/8/2013 ' Range("A1").Select Application.Goto Reference:="R1C1:R1000C5" Selection.Delete Shift:=xlUp 'Range("A1001").Select Application.Goto Reference:="R1001C1:R2500C5" Selection.Delete Shift:=xlUp Range("A1").Select ' plot data Application.Goto Reference:="R1C1:R1000C4" ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SetSourceData Source:=Range("$A$1:$D$1000") ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).AxisGroup = 2 'ActiveSheet.ChartObjects("Chart 2").Activate ' ActiveChart.SeriesCollection(2).Select ' ActiveChart.Axes(xlCategory).Select 'ActiveChart.Axes(xlCategory).MinimumScale = 0 ActiveChart.Axes(xlCategory).MinimumScale = -0.000000004 'ActiveChart.Axes(xlCategory).MaximumScale = 0.00000005 ActiveChart.Axes(xlCategory).MaximumScale = 0.000000003 ' Prepare a new empty columns Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0)" i1 = Cells(1, "C") v1min = Cells(i1, "B") t1min = Cells(i1, "A") Range("C2").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0) " i2 = Cells(2, "C") v1max = Cells(i2, "B") t1max = Cells(i2, "A") Range("C3").Value = v1max ' Use maximum and minimum of th epulse to define their middle point as time reference. t1 = (t1min + t1max) / 2 Range("C4").Value = t1 ' Treat channel#3, which is 2x2 APD Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("F100").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("F100").Select Application.Goto Reference:="R100C6:R1000C6" Selection.FillDown ' Shift this inverse waveform backwords for 10 data points and copy to column D Application.Goto Reference:="R100C6:R1000C6" Selection.Copy Range("G1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("H1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("H1").Select Application.Goto Reference:="R1C8:R1000C8" Selection.FillDown Range("I1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-4]:R1000C[-4]),R1C[-4]:R1000C[-4],0)" i1 = Cells(1, "I") v2max = Cells(i1, "E") Range("I2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R200C[-1]:R400C[-1]),R200C[-1]:R400C[-1],0)" i2 = Cells(2, "I") + 200 - 1 t2 = Cells(i2, "A") Range("I3").Value = v2max Range("I4").Value = t2 ' Range("F2").Select ' ActiveCell.FormulaR1C1 = i2 ' Range("F3").Select ' ActiveCell.FormulaR1C1 = t1 ' Treat channel #2, which is 8x8 APD Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("E100").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("E100").Select Application.Goto Reference:="R100C5:R1000C5" Selection.FillDown ' Shift this inverse waveform backwords for 10 data points and copy to column D Application.Goto Reference:="R100C5:R1000C5" Selection.Copy Range("F1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("G1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("G1").Select Application.Goto Reference:="R1C7:R1000C7" Selection.FillDown Range("H1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-4]:R1000C[-4]),R1C[-4]:R1000C[-4],0)" i1 = Cells(1, "H") v3max = Cells(i1, "D") Range("H2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R300C[-1]:R500C[-1]),R300C[-1]:R500C[-1],0)" i2 = Cells(2, "H") + 300 - 1 t3 = Cells(i2, "A") Range("H3").Value = v3max Range("H4").Value = t3 ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-ATFApril26-test.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Shift cell right dT = t1 - t2 ' Copy dT, t1, t2, V1min, V2min, V1max, V2max to dTHIST2 ActiveCell.FormulaR1C1 = dT ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v1max ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v2max ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v3max ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = FileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -7).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("Temp.csv").Close SaveChanges:=False Kill "Temp.csv" 0 Next i End Sub Sub ConvertToString() Dim vStr Dim Cstring As String vStr = 10000 ' MsgBox IsNumeric(strNum) MsgBox IsNumeric(vStr) Cstring = CStr(vStr) MsgBox IsNumeric(CStr(vStr)) End Sub Function MultiLetter(InputNumber) As String Dim CumSum As Variant, InputValue As Variant Dim StringPosition As Integer Dim i As Integer, Modulus As Integer Dim TempString As String, PartialValue As Variant On Error GoTo Err_MultiLetter InputValue = CDec(InputNumber) If InputValue < 1 Then MultiLetter = "" Else StringPosition = 0 CumSum = CDec(0) TempString = "" Do PartialValue = Int(CDec((InputValue - CumSum - 1) / (26 ^ StringPosition))) ' The code above should be all on 1 line ... Modulus = PartialValue - Int(CDec(PartialValue / 26)) * 26 TempString = Chr(Modulus + 65) & TempString StringPosition = StringPosition + 1 CumSum = CDec(0) For i = 1 To StringPosition CumSum = CDec((CumSum + 1) * 26) Next i Loop While InputValue > CumSum MultiLetter = TempString End If Exit Function Err_MultiLetter: MsgBox "Error " & Err.Number & ": " & Err.Description End Function Sub Commit() Dim lRow As Long, lRows As Long Dim rngCopy As Range, rngPaste As Range With ActiveSheet lRow = .Range("F1").Value lRows = .Range("F2").Value - 1 Set rngCopy = .Range(.Cells(11, 5), .Cells(11 + lRows, 5)) Set rngPaste = .Range(.Cells(lRow, 10), .Cells(lRow, 10 + lRows)) MsgBox "Copy Area " & rngCopy.Address MsgBox "Paste area " & rngPaste.Address rngCopy.Copy rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True End With 'clean up Application.CutCopyMode = False Set rngCopy = Nothing Set rngPaste = Nothing End Sub Sub StdCalc() ' ' StdCalc Macro ' Calculate histogram standard deviation. Column M is time axis, ' column N is data. The calculated Std is place in cell P33. ' ' CGL 8/1/312 ' Range("O1").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("O1").Select Application.Goto Reference:="R1C15:R30C15" Selection.FillDown Range("O33").Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)" Range("N33").Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)" Range("O34").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R[-1]C[-1]" Range("O35").Select Range("P1").Select ActiveCell.FormulaR1C1 = "=(RC[-3]-R34C15)^2*RC[-2]" Range("P1").Select Application.Goto Reference:="R1C16:R30C16" Selection.FillDown ActiveWindow.LargeScroll Down:=5 Range("P33").Select ActiveCell.FormulaR1C1 = "=SQRT(SUM(R[-32]C:R[-3]C)/RC[-2])" Range("P34").Select End Sub Sub inverseData() ' ' inverseData Macro ' We use ConstantFractionZX60Transformer() to analyze the ZX60 data set, but it requires positive pulses, sometime the ' pulses are negative ones. In order to be able to use this macro, we first need to inverse the pulse's polarity. This ' macro is used to inverse all data file polarity. ' 12/4/2012 CGL ' Dim Order As String Dim FileBase, FileName, DataFileName As String Dim i As Integer ' Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\12032012\4mm^2\-1725V\ZX60\500psScale\" Application.ScreenUpdating = False ' Check if the files are existing, then inverse the polarity for them. For i = 11 To 12 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" DataFileName = FileBase & FileName 'FileNameSave = FileBase & "TT" & Order & ".csv" 'FileNameSave = "TT" & Order & ".csv" 'DataFileSave = FileBase & FileNameSave ' Check if this file is existing If Dir(DataFileName) <> "" Then Workbooks.Open DataFileName ' Rename the data file as T.csv, will be killed latter ' ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later ' Set w = ActiveWorkbook ' Inverse the polarity Range("D1").Select ActiveCell.FormulaR1C1 = "=RC[-2]*-1" Range("E1").Select ActiveCell.FormulaR1C1 = "=RC[-2]*-1" Range("D1").Select Application.Goto Reference:="R1C4:R402C5" Selection.FillDown Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("B:B").Select Application.CutCopyMode = False Selection.NumberFormat = "0.0000E+00" Columns("C:C").Select Selection.NumberFormat = "0.0000E+00" Columns("D:E").Select Selection.Delete Shift:=xlToLeft Range("A1").Select ActiveWorkbook.Close SaveChanges:=True 0 Next i End Sub Sub LaserDiodeZX60Transformer() ' ' ConstantFraction Macro ' Find the zero crossing point with constant fraction principle. ' The LaserDiode data file is 800 rows, the useful data are from 401-th to 800-th cells. ' 12/08/2012 CGL Dim Order As String Dim FileBase, HistFileName As String Dim DataFileName, FileName, FileNameSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\01182013\4mm^2\-1750V\20C\4.75mm\" Application.ScreenUpdating = False HistName = "dTSummary-01182013-1750V-4.75mm.xlsx" HistFileName = FileBase & HistName ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "t" Range("B1").Select ActiveCell.FormulaR1C1 = "Vmin" Range("A2").Select For i = 1 To 2000 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" DataFileName = "T" & Order & ".csv" FileName = FileBase & DataFileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Prepare 5 new empty columns Range("C1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.3" Range("C1").Select Application.Goto Reference:="R1C3:R1601C3" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R51C3:R1601C3" Selection.Copy Range("D1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("E1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("E1").Select Application.Goto Reference:="R1C5:R1601C5" Selection.FillDown Range("F1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(RC[-4]:R[1601]C[-4]),RC[-4]:R[1601]C[-4],0)" i1 = Cells(1, "F") vmin = Cells(i1, "B") Range("F2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[1100]C[-1]:R[1200]C[-1]),R[1100]C[-1]:R[1200]C[-1],0)" i2 = Cells(2, "F") + 1101 t1 = Cells(i2, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-01182013-1750V-4.75mm.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Copy t, Vmin to dTHIST2 ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = vmin ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = DataFileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -2).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub Sub LaserDiodeZX60554MHz() ' ' ConstantFraction Macro ' Find the zero crossing point with constant fraction principle. ' The LaserDiode data file is 800 rows, the useful data are from 401-th to 800-th cells. ' 12/08/2012 CGL ' Reduce scope bandwidth to 554MHz ' 1/15/2013 CGL Dim Order As String Dim FileBase, HistFileName As String Dim DataFileName, FileName, FileNameSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\01182013\4mm^2\-1750V\20C\4.75mm\" Application.ScreenUpdating = False HistName = "dTSummary-01182013-1750V-475mm.xlsx" HistFileName = FileBase & HistName ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "t" Range("B1").Select ActiveCell.FormulaR1C1 = "Vmin" Range("A2").Select For i = 1 To 2000 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" DataFileName = "T" & Order & ".csv" FileName = FileBase & DataFileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Prepare 5 new empty columns Range("C1").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.3" Range("C1").Select Application.Goto Reference:="R1C3:R400C3" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R51C3:R400C3" Selection.Copy Range("D1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("E1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("E1").Select Application.Goto Reference:="R1C5:R400C5" Selection.FillDown Range("F1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(RC[-4]:R[400]C[-4]),RC[-4]:R[400]C[-4],0)" i1 = Cells(1, "F") vmin = Cells(i1, "B") Range("F2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[150]C[-1]:R[400]C[-1]),R[150]C[-1]:R[400]C[-1],0)" i2 = Cells(2, "F") + 151 t1 = Cells(i2, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-01182013-1750V-475mm.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Copy t, Vmin to dTHIST2 ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = vmin ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = DataFileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -2).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub Sub MeanCalc() ' ' MeanCalc Macro ' ' Calculate the mean of a histogram A2:B42, mean value stored in D45. ' 12/18/2012 CGL ' Range("C2").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("C2").Select Application.Goto Reference:="R2C3:R42C3" Selection.FillDown Range("B45").Select ActiveCell.FormulaR1C1 = "=SUM(R[-43]C:R[-3]C)" Range("C45").Select ActiveCell.FormulaR1C1 = "=SUM(R[-43]C:R[-3]C)" Range("D45").Select ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]" End Sub Sub baselineZX60Transformer() ' ' get baseline RMS for the waveform. ' 12/22/2012 CGL Dim Order As String Dim FileBase, HistFileName As String Dim DataFileName, FileName, FileNameSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\12172012\4mm^2\-1750V\30C\" Application.ScreenUpdating = False HistName = "BaselineSummary-12172012-1750V-30C.xlsx" HistFileName = FileBase & HistName ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "Stdev" For i = 1 To 2000 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" DataFileName = "T" & Order & ".csv" FileName = FileBase & DataFileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" ' Check if this file is existing If Dir(FileName) <> "" Then Workbooks.Open FileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook ' Prepare 5 new empty columns Range("C1").Select ActiveCell.FormulaR1C1 = "=STDEV.P(RC[-1]:R[199]C[-1])" stdev1 = Cells(1, "C") ' Copy the result to dTHIST.xlsx Workbooks("BaselineSummary-12172012-1750V-30C.xlsx").Activate ActiveCell.FormulaR1C1 = stdev1 ' Shift cell to the next row ActiveCell.Offset(1, 0).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub Sub PeakTimingZX60TransformerBNLTest() ' ' Modified on 6/11/2013. Analyze three channels: strip, 2x2APD and 8x8APD. ' Find the timing of the 8x8 APD peak as the time reference, then calculate dt. ' 6/11/2013 CGL Dim Order As String Dim FileBase, HistFileName, HistName, DataFileName As String Dim FileName, FileNameSave, DataFileSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook FileBase = "C:\Users\Lu\FastTiming\BNLBeamTest\ATF April 26\" Application.ScreenUpdating = False HistName = "dTSummary-ATFApril26-peak.xlsx" HistFileName = FileBase & HistName ' cross is the normarized pulse crossing point for the time reference cross = 0.5 ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "PeakTimingZX60TransformerBNLTest.macro" Range("A2").Select ActiveCell.FormulaR1C1 = "dT12(8x8)" Range("B2").Select ActiveCell.FormulaR1C1 = "dT13(2x2)" Range("C2").Select ActiveCell.FormulaR1C1 = "t1(strip)" Range("D2").Select ActiveCell.FormulaR1C1 = "t2(8x8)" Range("E2").Select ActiveCell.FormulaR1C1 = "t3(2x2)" Range("F2").Select ActiveCell.FormulaR1C1 = "V1max" Range("G2").Select ActiveCell.FormulaR1C1 = "V2max(8x8)" Range("H2").Select ActiveCell.FormulaR1C1 = "V3max(2x2)" Range("A3").Select For i = 12 To 1343 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" DataFileName = FileBase & FileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" DataFileSave = FileBase & FileNameSave ' Check if this file is existing If Dir(DataFileName) <> "" Then Workbooks.Open DataFileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "Temp" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook Range("A1").Select ' Read original ATF beam test data file (ch#1 - strip line V, ch#2-8x8APD, ch#3-2x2APD, ch#4-strip H), ' select a useful signal section, plot two pulses. ' CGL 4/8/2013 ' Range("A1").Select Application.Goto Reference:="R1C1:R1000C5" Selection.Delete Shift:=xlUp 'Range("A1001").Select Application.Goto Reference:="R1001C1:R2500C5" Selection.Delete Shift:=xlUp Range("A1").Select ' plot data Application.Goto Reference:="R1C1:R1000C4" ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SetSourceData Source:=Range("$A$1:$D$1000") ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).AxisGroup = 2 'ActiveSheet.ChartObjects("Chart 2").Activate ' ActiveChart.SeriesCollection(2).Select ' ActiveChart.Axes(xlCategory).Select 'ActiveChart.Axes(xlCategory).MinimumScale = 0 ActiveChart.Axes(xlCategory).MinimumScale = -0.000000004 'ActiveChart.Axes(xlCategory).MaximumScale = 0.00000005 ActiveChart.Axes(xlCategory).MaximumScale = 0.000000003 ' Prepare a new empty columns Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0)" i1 = Cells(1, "C") v1min = Cells(i1, "B") t1min = Cells(i1, "A") Range("C2").Select ActiveCell.FormulaR1C1 = "=MATCH(MAX(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0) + 1" i2 = Cells(2, "C") v1max = Cells(i2, "B") t1max = Cells(i2, "A") Range("C3").Value = v1max ' Use maximum and minimum of th epulse to define their middle point as time reference. t1 = (t1min + t1max) / 2 Range("C4").Value = t1 ' Treat channel#3, which is 2x2 APD ' Get timing at the peak as the reference Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("F1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0)" i1 = Cells(1, "F") v3min = Cells(i1, "E") t3min = Cells(i1, "A") ' Treat channel #2, which is 8x8 APD Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("E1").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R1C[-1]:R1000C[-1]),R1C[-1]:R1000C[-1],0)" i1 = Cells(1, "E") v2min = Cells(i1, "D") t2min = Cells(i1, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-ATFApril26-peak.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Shift cell right dT2 = t1 - t2min dT3 = t1 - t3min ' Copy dT, t1, t2, V1min, V2min, V1max, V2max to dTHIST2 ActiveCell.FormulaR1C1 = dT2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = dT3 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t2min ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t3min ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v1max ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v2min ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v3min ' Copy file name into dTSummary-****.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = FileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -8).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("Temp.csv").Close SaveChanges:=False Kill "Temp.csv" 0 Next i End Sub Sub ConstantFractionZX60TransformerSource() ' ' ConstantFraction Macro ' Find the zero crossing point with constant fraction principle. ' Multiply -0.5 to the original pulse, shift forward 100 cells, then add it to the ' original pulse. Search for the zero crossing point on x axis. ' 2/17/2012 CGL ' Try to apply this macro to ZVA&AM amplifier's waveform files ' 6/10/2012 CGL ' Modified to analyze the data set of ZX60-LN amplifiers ' 12/02/2012 CGL ' Tested on 12/3/2012, works. CGL ' For Sr-90 source test analysis. ' 5/26/2013 CGL ' Used to analyze 5/28/13 data set ' 5/29/2013 CGL Dim Order As String Dim FileBase, HistFileName, DataFileName As String Dim FileName, FileNameSave, DataFileSave As String Dim rngCopy As Range, rngPaste As Range Dim i As Integer Dim v1max, v2max As Double Dim dT, t1, t2 As Double Dim w As Workbook ' Speed up the calculation do the following FileBase = "C:\Users\Lu\FastTiming\06132013\meshAPDs\-1750V\" Application.ScreenUpdating = False HistName = "dTSummary-06132013.xlsx" HistFileName = FileBase & HistName ' cross is the normarized pulse crossing point for the time reference cross = 0.5 ' Check if this Histogram file is existing If Dir(HistFileName) <> "" Then Workbooks.Open HistFileName Else Workbooks.Add ActiveWorkbook.SaveAs HistFileName, FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False End If Range("A1").Select ActiveCell.FormulaR1C1 = "ConstantFractionZX60TransformerSource" Range("A2").Select ActiveCell.FormulaR1C1 = "dT" Range("B2").Select ActiveCell.FormulaR1C1 = "t1" Range("C2").Select ActiveCell.FormulaR1C1 = "t2" Range("D2").Select ActiveCell.FormulaR1C1 = "V1min" Range("E2").Select ActiveCell.FormulaR1C1 = "V2min" Range("A3").Select For i = 1 To 1000 Order = CStr(i) 'FileName = FileBase & "T" & Order & ".csv" FileName = "T" & Order & ".csv" DataFileName = FileBase & FileName 'FileNameSave = FileBase & "TT" & Order & ".csv" FileNameSave = "TT" & Order & ".csv" DataFileSave = FileBase & FileNameSave ' Check if this file is existing If Dir(DataFileName) <> "" Then Workbooks.Open DataFileName ' Rename the data file as T.csv, will be killed latter ActiveWorkbook.SaveAs "T.csv" Else GoTo 0 End If ' Use w to get this Workbook activated later Set w = ActiveWorkbook ' Delete first 800 useless rows Application.Goto Reference:="R1C1:R800C3" Selection.Cut Selection.Delete Shift:=xlUp Range("A1").Select ' Prepare 5 new empty columns Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("C2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("C2").Select Application.Goto Reference:="R2C3:R402C3" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C3:R401C3" Selection.Copy Range("D2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column E Range("E2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("E2").Select Application.Goto Reference:="R2C5:R402C5" Selection.FillDown Range("F2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(RC[-4]:R[300]C[-4]),RC[-4]:R[300]C[-4],0) + 1" i1 = Cells(2, "F") v1min = Cells(i1, "B") Range("F3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[150]C[-1]:R[240]C[-1]),R[150]C[-1]:R[240]C[-1],0) + 150 + 2" i2 = Cells(3, "F") t1 = Cells(i2, "A") ' Treat channel#2 Range("I2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*-0.5" Range("I2").Select Application.Goto Reference:="R2C9:R402C9" Selection.FillDown ' Shift this inverse waveform backwords for 30 data points and copy to column D Application.Goto Reference:="R31C9:R402C9" Selection.Copy Range("J2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ' Add original and shifted inversed waveforms together and put into column K Range("K2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=abs(RC[-3]+RC[-1])" Range("K2").Select Application.Goto Reference:="R2C11:R402C11" Selection.FillDown Range("L2").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(RC[-4]:R[300]C[-4]),RC[-4]:R[300]C[-4],0) + 1" i1 = Cells(2, "L") v2min = Cells(i1, "H") Range("L3").Select ActiveCell.FormulaR1C1 = "=MATCH(MIN(R[150]C[-1]:R[220]C[-1]),R[150]C[-1]:R[220]C[-1],0) + 150 + 2" i2 = Cells(3, "L") t2 = Cells(i2, "A") ' Copy the result to dTHIST.xlsx Workbooks("dTSummary-06132013.xlsx").Activate ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ' :=False, Transpose:=False ' Shift cell right dT = t1 - t2 ' Copy dT, t1, t2, V1min, V2min, V1max, V2max to dTHIST2 ActiveCell.FormulaR1C1 = dT ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t1 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = t2 ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v1min ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = v2min ' Copy file name into dTHIST1.xlsx ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = FileName ' Shift cell to the next row and first column ActiveCell.Offset(1, -5).Select ' To close the file T.csv, then delete it ' ActiveWorkbook.Close SaveChanges:=False Windows("T.csv").Close SaveChanges:=False Kill "T.csv" 0 Next i End Sub