Why on earth would anyone want to build a simulated LED news ticker display in Excel? While certainly not the most efficient way to get one's news, this modest project is interesting on a number of levels, starting with how deftly it demonstrates the power of boolean bitmasks.
Ask yourself how you would set about using the Excel charting and calculation engines to graph any alphanumeric message. Let's go ahead and stipulate an X Y (Scatter) chart type. But without using VBA how would you calculate the values for the chart series without knowing the message in advance? I suspect that many would conclude that a number of nested IF functions for each pixel would be in order. But be careful. There are at least 68 characters that need to be displayed (26 capitalized letters, 10 numeric digits, and a bevy of punctuation and special characters); in reality there could be hundreds of unique characters, but 68 would be the minimum to make a useful display. Any strategy chosen should be able to handle all of the characters one might wish to include in your character set.
Excel versions prior to 2007 were limited to 7 nested IF functions. While 2007 allows for 64 levels of nesting, that would be insanely unmanageable and would only allow for 64 characters; not to mention the horror it would be to suffer such a slow, bloated formula is 560 cells! That's how many points this chart has.
The next strategy considered would probably be a lookup table using Index/Match, Offset/Match, or Vlookup. Each character in this LED display has 35 pixels, so you would be looking at 35 columns of Yes/No type data for each character in your character set. This is a viable solution, while the nested IF formula is definitely not.
However, I'd like to share with you an elegant approach that you may not have considered. In most programming languages (Excel formulas included) the use of boolean bitmasks can often dramatically reduce program looping and branching. Here is a Wikipedia article
that explains the concept. In my last post I explained in detail the technique for achieving bitwise logical operations between two values, such as Logical AND, OR and XOR.
These operators are available in VBA, but tragically they are not natively available in the roster of Excel worksheet functions (I'm not talking about the normal And and Or worksheet functions. Bitwise AND and OR do something quite different). But we can synthetically reproduce these by using Excel's indomitable function, the flexile Sumproduct. This strategy is blazingly quick and since the bitmask allows us to bit decode a column of 7 pixels from one number using the synthetic AND operator, we can reduce our lookup table from 35 columns per character to just 5 columns, one for each column of 7 pixels in a character. And instead of meticulously placing 35 distinct formulas in 16 character positions for the chart, we can use one standard formula copied to all 560 cells.
In our eternal quest of "faster, shorter, easier to maintain, or simpler" this bitmask technique wins hands down as the optimum non-VBA method to graph a message on our simulated LED RSS News Ticker. The technique is versatile and once you open your mind to it, you will find countless uses.
Three years ago I used the very same technique in the crafting of an Excel formula based Soduku solver. All other solvers that I've seen use brute force VBA algorithms to crack a puzzle. Employing the bitmask technique, I was able to create a solver that uses accepted Sudoku techniques (human logic, such as Naked Triples, Remote Pairs, and Sword-Fish) to solve any puzzle and it interactively shows the user which technique is the one that worked to solve each cell. I'll post about that later, as it has some other very interesting Excel tactics.
Another interesting Excel project where I've used this strategy was to produce an unbreakable (even in theory) encryption cypher by using the synthetic XOR operator on a message and a one-time-pad composed of a feed of genuine random numbers produced by the radioactive decays detected by a Geiger-Müller tube interfaced to a computer at Fourmilab in Switzerland! These are esoteric uses to be sure, but the bitmask strategy can be used in many everyday situations.
Please download the accompanying workbook. It's definitely worth examining. By adjusting the Interval setting, you may notice that those 560 Logical AND calcs via Sumproduct are faster than Excel's charting engine. Try setting the Interval to "0." Remarkable, really.
Another interesting aspect of this project are the Array Formulas used to parse the XML RSS feed from CNN and Fox News. I'm using Microsoft's MSXML2.XMLHTTP COM object to grab the RSS feeds, so it would have been simple enough to also use it to wend through the XML trees and parse out the news items, but alas Fox News embeds illegal characters in their feed. So rather than ferreting them out, I chose to parse both feeds with an Array Formula and then concatenate the resulting array with VBA. Its an interesting approach which works quite well. Array formulas are not the scary monsters that many people fear. When used with understanding they are the most concise programming language I have ever encountered in my 25-year programming career. It is unfortuante that most users rarely go there. We will try to change that here with their judicious employment.
Take notice of the Character Set table in column A of the Font sheet. It was tedious ordering this table so that the MATCH formulas on the LED sheet could lookup each character without using the "Exact match" setting. The "Less than" setting works at binary sort speed and so I wanted to use that setting. Having Excel sort the table of characters DID NOT get the characters in the proper order for MATCH to work successfully; some characters worked, others did not. Through trial and error I arrived at the current table order, and this seems to work. If anyone knows of a reference that shows the sort order the "Less than" setting for MATCH works under, I would be very grateful if you left me a note.
I've taken care to align the 560 calculated cells directly under their corresponding chart pixels so it's easy to decipher how the process works. The final point of note here is that I used the WinINet library from within VBA to test for an Internet connection prior to retrieving a feed. This allows the program to gracefully restrict the user to displaying the Custom Text field when not connected. WinINet is the best method I've ever found for testing if a connection to the internet exists; it returns instantly, unlike some other methods that take seconds to time out.
Please download the RSS News Ticker workbook