Making of Making Waves, Part 1


The idea for having a bunch of sinewaves hitting an immovable point on the x-axis on a graph came to me a few months ago while I was playing with my prime number pictures. What I needed was to find the largest product that Excel could handle for the lowest set of primes so that each of the prime sinewaves would all converge on the one point, for the largest number of primes supportable. That is, if I just used 2 and 3, they’d converge at 6, and if I added 5 to the set, 2, 3 and 5 would converge at 30. It’s pretty clear that the convergence point gets big fast with each new prime. (7 => 210; 11 => 2310; 13 => 30,030; 17 => 510,510; 19 => 9,699,690). After 19, Excel treats the product as an exponent and the least significant digits get lost. This is a good stopping point anyway, because with more than 8 sinewaves, the screen gets too cluttered.

Just to get a feel for how this was going to look, I built up a spreadsheet, which needed 1,000 data points to make nice, clean lines. Having gotten this far, I figured I might as well try getting Excel to make the animation for me. And this is where things got stupid. (Surprised?)

The first issue is that Excel wants to automatically resize the chart based on the number of data points and the largest value in the data set. If the animation is going to start with 1 data point of a value of 0, the first chart is going to look wrong. I needed the same-sized chart for all frames. Trying to figure out how to do this via Excel help was a waste of time. Eventually, I found the information I needed online, and of course, now that I want to write about it, I can’t find that page, or the specific property in Excel anymore. For the chart height, it’s an obscure graph property. For chart width, I had to use a fake column and leave the cells empty. As long as the range for one column is 1,000 cells long, the chart will stay at 1,000 data points.


(What I wanted.)

The next part was to tell Excel to use a variable number of cells for the actual sinewave data. 1,000 times. This called for the use of Visual Basic. I don’t have VBA, but Windows comes with VBScript. In many cases, VBScript is adequate for automating processes in Excel, and in fact, many tutorials on how to use VBScript revolve around exactly that. Naturally, there’s no list of all controllable properties for Excel, and the Microsoft online documentation for the Excel object is a joke. Apparently, if you have VBA you can use the programming environment to display them, but the only alternative for people working in VBScript is to record whatever task as a macro and then examine the macro code created by Excel. Naturally, in certain later versions, Excel selectively chooses to not include various chart operations within the macro. With a lot of digging I found example code for changing the chart data ranges. I’d thought I’d had it made then.

The problem is that there’s two properties: Value and XValue. I wasn’t paying attention, and I only took the code for XValue.

What I really needed was:
wb.ActiveChart.SeriesCollection(1).Values = wb.Worksheets(“Sheet1”).Range(“c3:k1002”)

What I used was:
wb.ActiveChart.SeriesCollection(1).XValues = wb.Worksheets(“Sheet1”).Range(“c3:k1002”)


(What I got.)

Not the same thing. The resulting charts looked like garbage and I spent a day trying to figure out what was wrong. At about that point, I got swamped at work and had to put off playing with this idea for a couple months.

Recently, I had a few spare hours, and I decided to try to come back at this from a different angle, creating the graph from scratch. And that’s when I discovered just how under-documented the Excel object is online. No matter how I searched, I couldn’t find properties for setting a fixed height for the chart y-axis, etc. Additionally, I discovered that you can’t use Excel enumerators (xlNone, xlRight, etc.) from VBScript. You have to use the raw numbers, and that meant a lot more digging in the documentation to find those raw numbers.

Realizing that creating the kind of chart I wanted from scratch was too hard, I went back to my original idea of using an existing chart and trying to get the x-axis range to change correctly again. After another couple hours, I found one example on the net that had .Value and .XValue side-by-side, and that’s when I figured out the mistake I’d been making.

Once I could set the range for .Value via VBScript, everything else became easy. I generated 1,000 charts, with the range increasing 1 row at a time, and saved each one to a .gif file. I loaded the gifs into Microsoft movie maker and played it back. The play time was too long, so I adapted the VBScript to print out every fourth chart. With frame rates of 0.05s, this gave me a rough play time for one animation at 12 seconds. But, now MovieMaker started crashing.

””’
””’ VBScript code to make sinewave animation using Excel
””’ I love the way WordPress keeps code formatting…
””’

Set objExcel = CreateObject(“Excel.Application”)

objExcel.Application.DisplayAlerts = False
objExcel.Visible = True

Set wb = objExcel.Workbooks.Open(“c:\my projects\gakken\primeswave.xlsx”)
wb.Sheets(“Sheet1”).activate
wb.ActiveSheet.ChartObjects(1).Activate

wb.ActiveChart.SetSourceData wb.ActiveSheet.Range(“c3:k1002”)

‘ Start plotting only the 2’s wave; then 2 & 3; etc.
for chLines = 1 to 8

‘ Set the range for each sinewave to “c3:c3”, etc.
for chLCntr = 1 to 8

‘ chr(98) = “b”
chColRng = chr(98 + chLCntr) & “3:” & chr(98 + chLCntr) & “3”

wb.ActiveChart.SeriesCollection(chLCntr).Values = wb.Worksheets(“Sheet1”).Range(chColRng)
next

‘ Animate display of all 1000 data points per sinewave
for plotPnt = 1 to 250

‘ Start selecting cells with row 3, then plot every nth graph.
stepSize = 4
chVal = 2 + plotPnt * stepSize

‘ Plot each line from 1 up to chLines
for chLCntr = 1 to 8

if (chLCntr <= chLines) then
‘ Create range in format of c3:c68
chColRng = chr(98 + chLCntr) & “3:” & chr(98 + chLCntr) & chVal

‘ Set range for all sinewaves being plotted
wb.ActiveChart.SeriesCollection(chLCntr).Values = wb.Worksheets(“Sheet1”).Range(chColRng)
end if

next

‘ Build filename then save chart to file.
fn = “yngvi_” & chLines & “_” & right(“0000” & plotPnt, 4) & “.gif”
wb.ActiveChart.Export fn

next
next

objExcel.Quit

To be cont.

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: