Excel Macro Userís Guide

 

Bryan Kelly

 


Table of Contents

Introduction.. 3

Getting The Macros. 3

Using The Macros. 3

Enable Macros. 3

Macro Tool Bar.. 5

Import Data File.. 5

Add Notes. 6

Range Specs. 7

X Axis. 7

Y Axis. 7

Spec Titles. 7

Colors. 7

Color Specs. 8

Notes Dynamics. 8

Creating Charts. 8

Charts in Documents. 10

Figures

 

Figure 1 Options Menu. 4

Figure 2 Security Level 4

Figure 3 Visual Basic Tool Bar 5

Figure 4 Macro Menu. 5

Figure 5 Import Build Menu. 5

Figure 6 ViaSat Charting Menu. 9

Figure 7 First Chart 10

Figure 8 Copy Picture. 11

 


 

Excel Macro Userís Guide

 

Introduction

The purpose of this document is to introduce the users to this set of macros.† It will show how to use them and how to modify them for your data.

These macros provide the ability to load data into an Excel workbook, perform some translations and calculations, then chart the data.† The macros create a worksheet that is used to control formatting of the charts.† That worksheet may be edited by the user to present the data in a common format.

This guide is written for those having a fundamental understanding of how to use Excel. †

Getting The Macros

A starter kit of the macros is available at www.bkelly.ws/macros.† There are two methods of downloading the macros.† You may download an Excel workbook containing the macros and set up for importing and analyzing antenna log data.† You may download the source code for the macros.† Downloading the source code requires a bit more knowledge about using macros than does downloading the workbook.† This guide will continue under the concept that you have downloaded the workbook.

 

There are two versions of the macros to download, one for the ViaSat 3880 ACU (Antenna Control Unit), and one for the TCS ACU.† There are data files to download, one for each type of ACU.† Obviously, they are not interchangeable.† We will use the ViaSat macros by name.† The TCS macros are very similar and use the same instructions.† Where needed, differences are explained.

Using The Macros.

After the workbook and data file have been downloaded, open the workbook.† You should find a worksheet named VIASAT_MACROS.†

Enable Macros

The first step is to ensure macros are enabled.† From the toolbar, click on Options | Security.† Under Excel 2003, you should see the below window.

 

Figure 1 Options Menu

Select Macro SecurityÖ

Figure 2 Security Level

Select Low and OK your way out.† If your company or personal preferences prohibit this, you may receive notices and prompts when running these macros.† To keep this guide simple, I will not deal with the security issue.

Macro Tool Bar

You must have the macro tool bar enabled.† Select View |Toolbars, then ensure Visual Basic is selected.† When selected, you will see the below item in your tool bar.

Figure 3 Visual Basic Tool Bar

In the event you have not used Excel macros, here is a quick rundown of the buttons we will use:

The green triangle:†††††††† Select a macro to run

The red ball:†††††††††††††††† Record a macro

Green Square:††††††††††††† Stop recording.† Replaces red ball while recording.

The page symbol:††††††††† Open the macro editor

Add ViaSat Menu

To add the ViaSat menu, click the green triangle to start a macro.† It opens a macro select window.† Near the top of that window you will find the macro Add_ViaSat_Menu (or Add_TCS_Menu).† Double click it or select it and click run.† A ViaSat Macros drop down will appears at the top of your Excel window just to the left of the Help menu.

Figure 4 Macro Menu

 

Note that there are many macros in this macro menu.† The two macros Add_ViaSat_Menu and Delete_ViaSat_Menu are the only two items expected to be run from this window.† Most of the macros you see are utility functions and should not or cannot be run interactively.

 

Click on the menu item to open a two item menu:

 

Figure 5 Import Build Menu

Import Data File

Click on the Import ViaSat Data File to begin the import procedure.† This will open a standard Windows Open window to navigate to the file and select it for opening.†

Note that the ViaSat 10 Hz log files are always text files.† The macro is set to show only dot txt files.† If you have changed the name of the log file, you will not be able to find it.

Once the file is selected, the macro begins series of procedures to import the data.† One of the first things the macro does is to rename the work sheet to data.† This is that data that will be charted.† All the macros expect the data to be found in this named worksheet.† Changing this name will cause the macros to fail.

The import macro will change all of the column headers to be a bit more generic and user friendly.† Again, these names are important and cannot be changed without changing the code.

After that, the macro will create a time increment column and an elapsed time column.† The time increment is the time between each row of date.† The elapsed time begins with zero and increments each row by the amount of elapsed time.† I refer to this column as the MET or Mission Elapsed Time.

The next few steps are to create some new columns of data.† For instance, the ViaSat logs show azimuth angle in angles relative to the pedestal.† It also shows movement to the right of the cable wrap center position (clockwise) as positive values and movement to the left as negative numbers. (Our pedestal is limited to 720 degrees travel in azimuth)† When evaluating data I donít need pedestal angles, I need geographic north referenced angles. †The macro makes the translation and puts the result in a new column named AZ.

There are a few more calculated columns that are added to the worksheet.† These columns are the angles of the two axes minus the slave angle.† In our missions, this is an important comparison and it added up front.

Add Notes

The last phase of importing is to add the notes sheet.† The worksheet contains information on how the charts are to be formatted.† Again, the worksheet name notes must not be changed.

Before continuing, please download the macros and the data file.† Follow the instructions up to this point and import the data file.† The import procedure will take a few seconds to complete and you will have a workbook with two worksheets, notes and data.† The data sheet should be rather obvious.

Select the notes sheet and examine it while reading this description.

The first few rows provide a few reminders to the user.† Row 3 is a bit cryptic.† These macros were developed to track rocket launches.† The critical point in a rocket launch is T-0, or lift off time.† When you know the lift off time, go to the data sheet and scroll down until you find that lift off time in columns A and B.† Look in the elapsed time column and note the time there.† Then go to the top of the data worksheet, and replace the elapsed time of† 0 (zero) with the negative of the time at liftoff.† The column will be recalculated and will count down to T-0, then count up for the mission.

The next two rows become the header of all the charts.† If you want a multi-line header, as seen in the original, you must embed a CR in the text.† `Edit these two fields before you begin constructing your charts.

Range Specs

Following the title is a set of specifications that control the horizontal and vertical axes.† Each spec contains five lines.† The lines always have the same meanings.† Note that column A may not be edited.† Column 2 is intended to be edited as you may desire.

1)      Title:†††† The name of that spec.

2)      Minimum:†††††††† The minimum value for that axis

3)      Maximum:††††††† The maximum value for that axis

4)      Major Units:†††† The intervals between the markers along the axis

5)      Crosses††††††††††† Where this axis and the other cross (not always applicable)

To find this data directly in the chart, right click on a chart axis and select Format Axis.† The values in these locations of the notes sheet are installed in the appropriate places in this window.

X Axis

The first axis spec is for the X axis. †The X axis will always have an initial value of 0.† Maximum is the right side of the X axis and the top of the Y axis.† While building the notes sheet, the macros search the data for the last time in the elapsed time column.† That last value is put here.† In the ViaSat example, the value is 743.† Until this value is changed, the charts will show data from all the rows of the data worksheet.† Major Units is the time hacks at the bottom of the chart.† Crosses determines where the Y axis crosses the X axis.†

Y Axis

The entries in the Y axis have the same meanings as the X axis.† For each fundamental type of data in the ViaSat logs that I chart, I have created a data type with its own range.† When that type of data is charted, the macros come to the notes sheet and find the appropriate ranges to apply to the chart.

Spec Titles

Note the title of each of the axis specs. The name in the spec sheet is identical to the constant in the code that references that spec.† When changing the macros, merely look at the notes sheet for the constant to use.† This eliminates searching through the constants for the right one.

Colors

Further down the chart, beginning at row 80, there are five columns of 10 colors, each with a number to the left.† This section is a reference to the color designators.† It is not used by the macros, only by you.†

Color Specs

The color specs begin at row 92 with some column names. Column A is contains the name of the each color spec.† There is one row entry for each type of data charted.† Again, the name here matches the constants found in the code.† Column B contains the number of the color that will be assigned to this data item.† The purpose of the colored cells mentioned above should be more apparent.

Column C specifies the width of the line used to chart this data item.† Looking over in column D, there is a short table showing the allowed values.†

Just as a note of curiosity, line widths 1, 2, and 4 correspond to Hairline, Thin, and Thick.† Medium has a value of -4138.† The value 3 wonít work.† I donít have a clue as to where or why Microsoft derived this scheme, but the macros make the conversion so you can use 1 through 4.

Notes Dynamics

Please be aware that the values in the notes sheet are not dynamic.† Changing the value in the sheet will not change any chart.† However, as you can rebuild all charts in a few seconds anyway, that is of little consequence.† But you do need to be aware.

Creating Charts

Now that the demo data is loaded, lets construct the charts.† Click on ViaSat Macros, then on Build ViaSat Charts.

Figure 6 ViaSat Charting Menu

The check boxes select the charts to be constructed.† The command buttons Select All, Clear All, and Cancel are self evident.† When you select Build, all the charts that have been selected will be constructed.† Select Favorites will check the charts that I most often used.† It will not clear any charts already selected and does not toggle.† When you get to the code, you will be able to modify this to suit yourself.† After clicking Select Favorites, you must then select Build to continue.

I suggest you begin by selecting AZ EL AUTO AGC as shown above.†

Figure 7 First Chart

 

This chart is from tracking a satellite pass overhead.† The left side of the chart is the beginning of the log file and the right side is the end of the log file.† Time is displayed in intervals of 50 seconds.† When the log file was started, the azimuth axis was pointed towards about 20 degrees relative to true north while the elevation axis was at about 10 degrees.† The AGC (Automatic Gain Control) stated at about 10 then jumped up to about 15 when the signal was acquired.† These three items are tracked on the left or main Y axis.† The label on the left tells the user.† While not completely clear, the axis data is scaled in degrees and the AGC data is scaled in dB above noise.

 

The azimuth and elevation autotrack errors are charted on the secondary Y axis and are scaled on the right side.† The range of these values is radically different from that of the two axes so they must be charted on a different scale.† They are scaled in degrees.† The majority of this pass track at an error of less than 0.1 degrees.† A pretty good pass.

 

Looking over time 450, we can see that something caused the antenna to lose track of the target.

Charts in Documents

Putting charts in a document can produce unexpected results.† One chart may add 20 megabytes to a Word file.† This is because by putting a chart into your Word document, you may be unknowingly add the entire Excel file to your Word document.†

To avoid this:† Hold down the shift key and left click on Edit in the tool bar. Select Copy Picture for this menu.

 

Figure 8 Copy Picture

Click As shown when printed then use ctl-v to paste the chart into Word or any other document.† You will get only the image you desire.† Of the methods I have tried, this produces the smallest increase in file size.