You can have it right, Or you can have it now, But you cannot have it right now. (Bryan Kelly, 2002 )

1. You can have it right, Or you can have it now,But you cannot have it right now.(Bryan Kelly, 2002 )

2. Using Excel Macros For Charting Bryan Kelly Principle Systems Engineer Range Safety Systems Honeywell

3. Presentation Agenda • Get the Macros • www.bkelly.ws/macros • Macros • User’s Guide • Example Data • Email to macros@bkelly.ws • Purpose and origin • Demonstrate the Macros • Import ViaSat Tracking Data • Build Charts • Introducing the main concepts, things you will change • Control the Charts • Import the Data • Building the Charts

4. Origins • Lead systems engineer for tracking antenna sub system • Need to verify proper operation to customer and evaluators • Numerous charts required • Manual charting is error prone and time consuming • Decided to automate the charting process

5. Demonstrate The Macros

6. Control The Charts • Import function builds work sheet “notes” • Worksheet notes: • Provides Chart Titles • Specifies X and Y Axis Scales • Sets Line Colors • User edits notes to scale and title charts

7. Notes Sheet Heading & X

8. Notes Sheet Y Specs

9. Color Reminders

10. Color and Width Specs

11. Macros in Four Modules • Macros reside in four VBA Modules • Common_Macros • Common procedures for all data sets • ViaSat_Macros • Central code, Imports and builds charts • ViaSat_Constants • Nothing but constants, makes editing code easier. • ViaSat_Chart_Build_Selector • The form used to select and build charts

12. Import Procedure Sub Import_ViaSat_10Hz_Log() FName = Application.GetOpenFilename("Text Files (*.txt),*.txt)") Workbooks.OpenText Filename:=FName, _ Comma:=True, Space:=True, _ FieldInfo:=Array(Array(1, xlMDYFormat), _ Array(2, xlTextFormat)) ‘ x x x Call Rename_ViaSat_Columns() Call Add_Seconds_Of_Day_Column("time", "Seconds of the Day") ‘ time format is HH:MM:SS.ttt, seconds of day is a number. Call Add_Time_Increment_Column("Seconds of the Day", "Time Increment") Call Add_Elapsed_Time_Column("Time Increment", "Elapsed Time")

13. Import Procedure ' Convert pedestal reference to north references Call Add_Negated_AZ_Column Call Add_Negated_AZ_Cmd_Column ‘ Calculate some new columns Call Add_Column_X_minus_Y("AZ", "AZ Slave", "AZ - Slave") Call Add_Column_X_minus_Y("EL", "EL Slave", "EL - Slave") Call Add_Column_X_minus_Y("AZ", "AZ cmd", "AZ - Cmd") Call Add_Column_X_minus_Y("EL", "EL cmd", "EL - Cmd") Call Create_ACU_Sheet_Notes End Sub

14. Chart AGC This typical function charts the AGC values of four tuners and the number of the tuner selected for tracking. Sub Chart_AGC_And_RCVR() Turn off screen updating to make the macros run much faster Application.ScreenUpdating = False Delete the currently existing copy of this chart. Delete_Chart ("AGC & RCVR") Declare that the chart will be constructed from this work sheet. Sheets("data").Select

15. Select The Columns To Chart The variable Column_List holds the names of the columns to be charted. Column_List = "Elapsed Time,RCVR,LHCP1,RHCP1,LHCP2,RHCP2“ ‘ 0 1 2 3 4 5 VERY IMPORTANT: Order this list in the left to right order as the data resides in the work sheet. Excel numbers the selected columns 0 to n-1, in that order. If you keep the correct order in this list, it is easer to deal with Excel when building charts. Note that series 0, left most column, always X axis. Call Create_Chart(Column_List, Notes_RCVR_Values_Label) ‘ scales left Y axis When this call completes, the chart will have been constructed.

16. Create_Chart( P1, P2) • Member of common macros • Create the chart w/ selected columns, P1 • Scale the left Y axis, P2 • Scale the X axis • Turn on legend and put it at bottom • Set the chart title (not axis titles) • Set the background color

17. Move To Right Axis The parenthetical argument for SeriesCollection specifies which item to move to the right. The number comes from the order that the item is found in the worksheet as discussed earlier. ActiveChart.SeriesCollection(2).AxisGroup = Right_Y_Axis << LHCP 1 ActiveChart.SeriesCollection(2).MarkerStyle = xlNone ActiveChart.SeriesCollection(3).AxisGroup = Right_Y_Axis << RHCP 1 ActiveChart.SeriesCollection(3).MarkerStyle = xlNone ActiveChart.SeriesCollection(4).AxisGroup = Right_Y_Axis << LHCP 2 ActiveChart.SeriesCollection(4).MarkerStyle = xlNone ActiveChart.SeriesCollection(5).AxisGroup = Right_Y_Axis << RHCP 2 ActiveChart.SeriesCollection(5).MarkerStyle = xlNone

18. Scale, Line Width & Color The parameter is a constant from the notes sheet and tells the function where to find the scaling values in the notes sheet. Call Scale_Right_Y_Axis(Notes_AGC_Values_Label) Remember, this is the left to right ordering in the worksheet. The numbering is 0 to N – 1. The X axis data item is always number zero. Call Set_Line_Color_Width( RCVR_Color_Row, 1 ) Call Set_Line_Color_Width( LHCP1_Color_Row, 2 ) Call Set_Line_Color_Width( RHCP1_Color_Row, 3 ) Call Set_Line_Color_Width( LHCP2_Color_Row, 4 ) Call Set_Line_Color_Width( RHCP2_Color_Row, 5 )

19. Axis Titles And Chart Name Call Set_Y_Primary_Axis_Title("Selected Receiver") Call Set_Y_Secondary_Axis_Title("AGC") Call Set_X_Axis_Title("MET (Seconds)") This sets the name of the worksheet containing this chart. ActiveChart.Name = "AGC & RCVR“ Turn on screen updating so the chart will be visible Application.ScreenUpdating = True End Sub At the end of this procedure, the chart is formatted and ready to use.

20. Review • Adjust the import procedures to accommodate the format of your data • Try to control the logging function • Change the constants in the declaration file and in the code • Change the macros that build the charts • Non Trivial, but these macros can save you many hours of tedious work

21. New Concept For NotesEmail with Comments

22. Questions, Comments • www.bkelly.ws/macros • macros@bkelly.ws • Comments? • Questions?