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

1 / 22
About This Presentation

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


You can have it right, Or you can have it now, But you cannot have it right now. (Bryan Kelly, 2002 ) Using Excel Macros For Charting Bryan Kelly Principle Systems ... – PowerPoint PPT presentation

Number of Views:12
Avg rating:3.0/5.0
Slides: 23
Provided by: Brya195
Tags: bryan | kelly | now | right


Transcript and Presenter's Notes

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

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

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

Presentation Agenda
  • Get the Macros
  • Macros
  • Users Guide
  • Example Data
  • Email to
  • Purpose and origin
  • Demonstrate the Macros
  • Import ViaSat Tracking Data
  • Build Charts
  • Introducing the main concepts, things you will
  • Control the Charts
  • Import the Data
  • Building the Charts

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

Demonstrate The Macros

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

Notes Sheet Heading X
Notes Sheet Y Specs
Color Reminders
Color and Width Specs
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

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")
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
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
Select The Columns To Chart
The variable Column_List holds the names of the
columns to be charted. Column_List "Elapsed
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.
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

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.
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
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
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 )
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.
  • 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

New Concept For NotesEmail with Comments
Questions, Comments
  • Comments?
  • Questions?
Write a Comment
User Comments (0)