Excel Makes Waves

Engineers often use an Excel spreadsheet tocreate and manipulate information they use to test algorithms and run equipment. But, when a test requires several hundred values, the trial-and-error cut-and-paste process becomes tedious.

To save time, you can create an Excel Visual Basic (VB) program — also called a macro — to calculate just about any series of values you need. Recently, I wrote a VB program that produces sine-wave "signals" based on the number of points needed, an amplitude, and the number of sine-wave cycles required. I also can add a dc offset to the signal and modulate its amplitude. The program accommodates as many as 10,000 data points and automatically plots a "quick view" of the first 1,000 spreadsheet points.

Another VB program, CreateChart, dynamically plots all the data. You'll find a link to my VB programs and instructions in the Useful Links box at the bottom of this page. The programs exist under a GNU General Public License (GNU GPL), so you can use the software and modify it as you wish.

Now, when I need a sine wave with specific characteristics, Excel does the trick. Because the results appear in a column, I can apply any additional Excel processing by hand or with other software.

When you need test signals, but not necessarily sine waves, use this VB program as a template for other operations. (I make no claim to using good VB programming techniques, but comments and labels will help you understand what the program does.)

	The top chart shows an AM signal created with the JTSineWave.xls spreadsheet. The bottom chart illustrates a swept-AM signal produced by JTAMSweptSine.xls.

The software comprises two sections: First, a Module provides Sinewave() and SinewaveGen() routines. The former sets up a menu and the latter clears out old data, calculates signal values and puts them in the spreadsheet.

Second, a Form, frmSignalInput, obtains and checks numeric values typed into a data-entry box. The "quick-view" 1,000-point chart operates within the Excel spreadsheet, not as part of the VBA code. So, you can change and save chart parameters without altering the VB code.

To check the utility of the basic sine-wave program as a template for similar tasks, I modified the software to create another signal generator. This new program increases or decreases the amplitude of a sine-wave over a chosen range, thus sweeping its amplitude up or down. It took time to change — and keep track of — menu settings and variable names, but it took more time to create the signal equation and its algorithm. But, to create the signal in Excel cell-by-cell would have required that step, too.

Before you change these programs, some VB programming experience will help. I took the data-entry-box idea from Example 13 in Aubrey Kagan's book, "Excel by Example." By using VB's Help information and its Object Browser, I worked my way through problems and bugs. Microsoft's website also provides assistance with the dynamic chart.