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.

Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

Thanks for the great info about leds, we are making led buchstaben as well. Leuchtreklame

Hello! My friend's and I enjoy your website. It is full of tons of helpful information. I will definatly check out this site again in the future.I hope you have a great day and thanks for reading.

Hello, simply considered i would reveal to you something.. This is two times today i’ve ended up on your blog in the previous 3 days exploring for completely unrelated issues. Crazy or what!!

"Any sufficiently advanced bug is indistinguishable from a feature."

Daniel,

I am going to have to dub you the new Stephen Bullen. Your ability to pull in technology to create great Excel solutions is at the highest level. I am learning tricks and techniques from you that will allow me to make "whatever". In the near future my Excel comeback might lead me onto the Internet, where I hope to "share" some of my own new creations. Anyway, keep up the good work!

@David-

Wow. That's quite a compliment!

Thank you very much and welcome to my blog.

I'm trying to continually add useful information and I'm glad you find it so.

Regards,

Daniel Ferry
excelhero.com/blog

Hello Daniel

When I tried to run the RSS spreadsheet I got a missing reference error, for something called 'Msie ActiveX Control module'. I have not been able to find anything on this on the net. Any suggestions? I am running Excel 2007 on Windows Server 2008.

regards
John

@John -

That project has a REFERENCE to that control, but I just checked and the version that is posted on the blog does not need the control, so you can remove the reference and it should work just fine.

To do so, open the VE Editor (ALT-F11). Then go to the Tools menu (at the top) and select References.

In the dialog UNcheck the box to the left of that control entry. It should be very close to the top of the list. Finally click OK on the dialog and then save the workbook.

It should work properly now.

Regards,

Daniel Ferry
excelhero.com

Daniel,

I'm a little late checking out this post! Anyways, I downloaded the file and attempted to run it in Excel 2010 and it immediately crashed. The problem appears to be a "Compile Error" in which it "Can't find object or library" for the named formulas, referenced in the VBA using the shortcut method ([]). Any suggestions on getting this to function properly? I'd really like to check it out!

Tom

Okay, I just tried the workbook at my work computer which is running Excel 2007 on Windows Vista and I experienced the exact same issue ("Compile Error"). I know your busy with Excel Hero Academy, so no worries. One day, I'd like to see this work ;)

@Tomquist
See Daniel's reply to John Oct. 6 above regarding the missing reference.
Once it was fixed the project worked fine in Excel 2007.
Ed

• Atom feed
• Daniel Ferry