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
• Users Guide
• Example Data
• Email to macros_at_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
FilenameFName, _ CommaTrue,
SpaceTrue, _ FieldInfoArray(Array(1,
xlMDYFormat), _
Array(2, xlTextFormat)) x x x Call
Rename_ViaSat_Columns() Call Add_Seconds_Of_Day_C
olumn("time", "Seconds of the Day") time
format is HHMMSS.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("E
L", "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_L
ist, 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 ltlt LHCP 1 ActiveChart.SeriesCollect
ion(2).MarkerStyle xlNone ActiveChart.SeriesCol
lection(3).AxisGroup Right_Y_Axis ltlt RHCP
1 ActiveChart.SeriesCollection(3).MarkerStyle
xlNone ActiveChart.SeriesCollection(4).AxisGroup
Right_Y_Axis ltlt LHCP 2 ActiveChart.SeriesColle
ction(4).MarkerStyle xlNone ActiveChart.SeriesC
ollection(5).AxisGroup Right_Y_Axis ltlt 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_at_bkelly.ws
• Comments?
• Questions?