Daily Dose of Excel

I need this procedure automatically called from every entry point procedure I run:

When I refer to properties, I say “The x property of the y object”, as in “The Value property of the Range object”. I’m updating the DDoE style guide to reflect a change a recent editorial decision. From now on, I will refer to properties as “The y.x property”, as in “The Workbook.FullName property” or “The Collection.Add method”. Please make a note of it.

Chandoo is polling his readers to find their favorite Excel feature. There’s almost 50 comments as of this writing and a lot of good features mentioned. A couple of people, including Chandoo, named formulas as a top feature. You can’t say formulas are the best feature of Excel. That’s like saying wetness is the best feature of the Ocean. Or cheesiness is the best feature of the Moon. Formulas are Excel. They are Excel’s essence. Aren’t they?

For what it’s worth, I’ve been using the 2010 UI for a couple of months now (formerly mostly using VBA). The thing I’m most grateful for is autocomplete of Table references. I prefix all my Table name with tbl, so I can type =SUMPRODUCT((tbl {tab} [#A {tab} and half my formula is written for me. I should make a video of that, because typing it doesn’t do it justice. Or I’ll just go find one on Contextures since Debra has created every possible Excel video.

I don’t know if that qualifies as my favorite Excel feature, but I smile a little every time I use it.

Here’s a picture of my new grill. Summer is here.

As you are no doubt aware, Google Reader is going away. The public outcry hasn’t brought it back, nor open sourced it, by now so that’s not going to happen. There are some options, but I decided that I wasn’t going to switch until May 15th. That will give the smart people an opportunity to vet the options and tell me what to do. I haven’t tried the options, obviously, so I can’t comment on them. Instead, I want to reflect on what I want from an RSS reader.

But first, a word about RSS. I’ve read and listened to a lot about Reader’s demise lately. A lot of people are characterizing RSS a a two-faced beast (most notably John Gruber, but I can’t remember if it was The Talk Show or some podcast on which he was a guest). One face is the user-interface like Google Reader. It’s how I read web pages. I almost never read a website that doesn’t have a feed. In fact, I read less than a handful of sites that don’t publish the full content in their feed.

The other face is this back-end plumbing, this architecture, this infrastructure that is RSS. How you read websites is a personal choice. But whether you pop over to DDoE to see what’s new or you check a feed reader, this site still publishes an xml file. You could write some software that parses that file. You could buy some software that does it. You could do something interesting like the Spreadsheet Page’s Excel Blog Headlines Page. The XML file is out there. Use it, don’t use it, but don’t get rid of it. It has value. Someone needs to build an interface that gets the masses interested in reading pages from a single source. Just don’t call it RSS and it will be fine.

What do I want from a reader? Here’s what I love about Google Reader:

• Great syncing – Google updates quickly. Really quickly.
• No duplicates – Google does a good job figuring out which posts I’ve read and which I haven’t. There have been a few glitches along the way but all-in-all it’s been solid.
• Good rendering – For the most part, the feeds render the web pages really well. I hate the way it shows DDoE code, but other than that, it’s good.
• Shortcut keys – Google respects the mouse-a-phobes and provides keyboard shortcuts for easy navigation. I can learn new shortcuts, but if they’re not there it’s a deal breaker.
• Ubiquity – I like Reader on the web, in Chrome for iOS, and everywhere else I’ve used it.

Here’s what I don’t like:

• Screen Real Estate – Nearly 1/3 of the screen is used up by non-value added bullshit. Some of that is Firefox, but everyone has to do their part.
• No sorting – My folders are sorted alphabetically, which is the just about the worst way you could organize them. Let me assign a number to each folder that determines the order. Some feeds I read no matter what and some I read when I get the chance. I want the former at the top.

Every feed that I read falls into one of four categories.

News
News is the least important, the highest volume, the most skimmed (as opposed to read), and the least missed. I have some news feeds, but if they ever amassed too many unread entries, I have no problem blowing away large swaths. Some of my news feeds are:

• KETV.com
• AP Top Headlines
• A couple Reuters feeds
• Stack Overflow feeds
• Netflix new releases

You see that they don’t have to be news in the traditional sense of the word. In fact, I should stop calling them that. Their most defining characteristic is that I don’t care if I miss something.

Content
Content are feeds I read because they’re damn good. If I thought I missed something through one of these feeds, I would go visit that site to make sure. I’m going to read every entry posted to these feeds. That may be because I know the feed has short entries that take no time to read (web comics) or the content is just so good that it’s worth my effort to avoid missing any entries (kottke). Content feeds include:

• Family memeber’s blogs (let’s call this content ‘highly relevant’ rather than ‘good’
• Web comics like xkcd, Oatmeal, Savage Chickens, Dilbert, NIH, Scenes from a Multiverse
• Signal vs. Noise
• Street Smarts

Not only do I not want to miss a single post, I want to read these right away. When xkcd publishes, I don’t save that gem for later, I read it right now.

Obligation
Some feeds I read out of obligation. I feel it’s my job to know what’s happening in the world of Excel, so I read a whole crap load of Excel blogs. Some are great and some aren’t. If it’s not a blog that solely exists to sell a product, I’m probably reading it whether I find it currently useful or not. I also read feeds about manufacturing and petroleum. I’m not going to miss a post on these either, but I don’t need to read them today. Mike’s Data Explorer post was awesome, but if I read it five days after he posted, it would still be awesome. I’m not going to list all the Excel blogs I read, you can read my OPML file if you’re so inclined.

Saves
Saves are either long reads or things that I wish I were interested in. The Harvard Business Review has some nice articles, but I save them for a rainy day. Same with Kahn’s Corner, Lowering the Bar, and a host of other sites. I may get to them someday or I may not. But I want them there.

My reader should have two panes. The left pane holds Content feeds. The ones I want to read right now. The right pane holds News feeds. It will be pretty full, but I’ll blow through them in no time. When I have no content feeds, the left pane turns to Obligaton feeds. The Saves feeds never show up unless I specifically ask. I can put any single post into Saves while still keeping the feed in another category.

I can set an expiration on any pane. I may say, for instance, that entries from News feeds go away if I don’t read them for five days. For me, personally, every other pane will be set at ‘never expire’, but others may want their Obligation entries to fall off after some time. The panes show all of the unread entries from the feeds assigned to them. If you want to see entries you’ve already read, you can search for them. Entries can be shown in chronological order or the reverse. Feeds can be rated 1 to 5 stars. A combination of age and rating determines what’s on top in a pane. The feeds inherit all these features from their pane, but can be overridden on a feed-by-feed basis. For example, I read almost everything chronologically so that’s how my news pane will be ordered. A notable exception is Stack Overflow, where I prefer to see the newest (least likely to have been answered) first and I will set that feed to be reverse chronological. All other feeds in the News pane will inherit the chronological property from the pane.

So there you have it. Give me two big panes, keyboard shortcuts, effortless syncing, and a semi-consistent experience across web and iOS and I’ll be fine. Oh, one more thing: I’ll pay $100 per year for the service.

DDoE had a birthday on March 29th and I totally forgot about it. I would forget my own birthday if my wife didn’t buy me a present, so I don’t feel too bad about it. I often tell people that DDoE is the first Excel blog. Is that really true? I did a Google search and limited the dates to before March 29, 2004 and not much showed up. There were blogs that mentioned Excel, but not Excel blogs. Now I hear that blogs are dead and Tumblr is the new thing. I think I’ll skip that “revolution” and catch the next one.

I thought I’d check the stats eight years in and see what’s what.

Hmmm, seems I may have forgotten to put the Google Analytics javascript back in when the blog took a crap last fall. But that begs the question why isn’t it showing all zeros?

Thanks for reading. And thanks to the other authors for picking up the slack when I have nothing to say. As for the title, I can’t get the Flip-a-delphia song out of my head today.

Last month I read an article about a job interviewer who played TicTacToe with his applicants. It was like an instant shared round of golf, and the interviewer put great store in the correlation of good hires with good players. So in case any DDoE readers might run into that guy, here is TicTacToe (or Noughts and Crosses as it’s known across the pond) in a user form suitable for practice.
 

 
There are several XL versions out there in the wide world of web (though not too many in a user form) and this is just the latest. It’s a refresh of my first XL code that wasn’t a tutorial, written in XLM too many moons ago.

You can set if you go first or second, set if you are X or O, and set if XL plays hard (don’t know if it can be beat), medium (can be beat), or dumb (as in very dumb—makes random legal moves). It’ll keep score. If you play well, you can’t lose. If XL plays well, you can’t win.
 

 
Start refreshes the game, and Reset takes you back to where you can adjust the mode of play. It looks different, but it works fine on a Mac with XL 2011. Its default is the Comic Sans MS font. If you’re playing second, you can control the start square for XL by clicking Start until XL randomly picks the square you want it to use.

The form is available here. After importing, it needs just a one-line macro to run.

Presumably, that guy giving the interview plays TicTacToe well, so you won’t win. I don’t think you want to lose, either. Or maybe just lose one out of three. I think I read about it on the BBC’s iPad service. If you remember where it was, please comment. And if you beat it with XL playing hard, please say how. Your game is captured in the VBE’s Immediate Window.

Provided as a public service to Dick’s readers.

…mrt
©¿©¬

The other day a reader asked about how to get a font list to a combo box so his users could have a choice to specify. Borrowing shamelessly from John Walkenbach’s Tip 79, this is one way to do it. I use this approach when I take a spreadsheet to a web table and want to specify the font.

From top to bottom, the form has:

• A label, named lblFontcboOverLabel
• A label, named lblFontcbo
• A combo box, named cboFontOther
• A frame, named Frame1
• An option button, named btnAllFonts
• An option button, named btnMonoFonts

This is the code behind the form:

It has the “Get Property” at the top. I screen the installed fonts to filter the Asian fonts from my list. Remove the “Like” operator if you need them. I capture the the installed fonts as a global string variable in a comma-delimited list. The list of monospaced fonts came from here, and represents the fonts installed as a minimum on ⅓ of all Windows, Mac, and Unix computers. They’re listed in percentage order irrespective of machine and not by installation count.

As I use the full form, the Over Label lies atop the combo box. But if you want to see more than the font’s name in its own font, modify the combo box change to something like this:

…mrt
©¿©¬

HEXADECIMAL and XL color values are equivalent systems for representing RGB colors, but they start at different ends. In XL, pure red–RGB(255,0,0)–is 255₁₀. In HEX, pure red–FF000–is 16711680₁₀. XL has red as the least significant digits, HEX has it as the most significant digits. But we’re using computers, so this version of endianness is a manageable problem.

To go from XL representation to web-friendly HEX, turn the XL color to HEX, pad it with leading zeroes, and then swap the end pairs. Green stays in the middle. Red, 255₁₀, becomes FF, then 0000FF, and then FF0000. This is the XL2HEX() function.

It includes an optional prefix. Usual ones are # for web colors, and &H or &h to indicate a hexadecimal number. There are many more. You can pass the VB color constants to XL2HEX():

The flip function needs to strip off the prefix, make sure it’s six characters long, read the red, green, and blue, and then create the XL color. Since CSS allows #ABC to represent #AABBCC we’ll special case it.

The CLng() conversion turns hex into longs as part of its business. The HEX-to-color functions are more of the same.

The HEX2RGB() function is array-entered. The function I use the most is XL2HEX(). I pass it cell or font color properties, such as FontColor = XL2HEX(.Color)

Thus ends the XL Color Functions. The picture at the top? It’s hexclr’d.

… mrt
©¿©¬

When you go to replenish your ink cartridges, you don’t come home with a Red/Green/Blue gizmo. You come home with a Cyan/Magenta/Yellow/Black multi-pack whose cost is about the same as your printer. (It’s the razor and blades business model.) Your computer (input device) works in RGB; your printer (output device) works in CMYK. The RGB color vectors are zero to 255, the CMYK vectors are zero to 100. But it’s not that simple (more on this at the end). It’s like converting Fahrenheit to Celsius and only being able to use integer degrees. The -40° equivalent commonality point for colors is zero. The image is a representation of the CMYK color space.

A Code Project page referenced from Wikipedia gives the generic algorithm:

• Black = minimum(1-Red,1-Green,1-Blue)
• Cyan = (1-Red-Black)/(1-Black)
• Magenta = (1-Green-Black)/(1-Black)
• Yellow = (1-Blue-Black)/(1-Black)

Red, Blue, and Green are normalized to 0.0 to 1.0 (divide by 255) and the output then is scaled (multiply by 100). This is the XL2CMYK() function.

With the RGB shown on the linked page {171,215,170}–an apple green, the xlclr is 11196331. The CMYK out is {20,0,21,16}, irritatingly different than what is shown in the Code Project page’s image, but what in fact does happen when you run the downloadable file.

The flip function CMYK2XL() is this:

When input {20,0,21,16} the output is 11130539. Tilt. It’s because of the rounding and aligning to integers. This is what it looks like:

Close enough for now. I can’t see a difference, but these eyes have some miles on them, and are behind plastic to boot. But there are differences. Wikimedia Commons gets almost the last word.

A comparison of RGB and CMYK color spaces. … If you were to print the image on a CMYK device (an offset press or maybe even a ink jet printer) the two sides would likely look much more similar, since the combination of cyan, yellow, magenta and black cannot reproduce the range (gamut) of color that a computer monitor displays. This is a constant issue for those who work in print production. Clients produce bright and colorful images on their computers and are disappointed to see them look muted in print. (An exception is photo processing. In photo processing, like snapshots or 8×10 glossies, most of the RGB gamut is reproduced.)

The code above is a device-independent solution to a device-dependent problem. Your printer will vary. But passing your work through it and back will give you an idea of what the printed product will look like, before it’s printed.

Next up, Part 3: XL2HEX() and HEX2XL()

…mrt
©¿©¬

Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 256³ or 16,777,216. One color for every combination possible of reds, greens, and blues in the range of 0 to 255 each. (Of course, unless you have 256³ pixels, you can’t see all those colors at once.) The image is of the RGB color cube, 255x255x255, with black at the origin, diagonally opposite the white corner. Excel keeps track of its colors as a long integer, the same as returned by the VBA.RGB(Red, Green, Blue) function.

RGB(1,0,0) is 1, RGB(0,1,0) is 256, and RBG(0,0,1) is 65536.
RGB(2,0,0) is 2, RGB(0,2,0) is 512, and RGB(0,0,2) is 131072.
RGB(3,3,3) is 3*1 + 3*256 + 3*65536, or 197379.

To go backwards, we can get the blue component of Excel’s colors by:

or INT(xlclr/65536) in a spreadsheet. The green component is doing integer division on the remainder by 256.

Red is what’s left after subtracting Blue and Green (a whole new meaning to subtractive colors):

To go from the XL color to RGB in one line:

XL2RGB() must be array-entered across 3 cells. That version does compute XL2BLU 3 times and XL2GRN twice, but the calculations are quick In even quicker form, it’s like this:

The flip function, RGB2XL is this:

And as it must, xlclr = RGB2XL(XL2RGB(xlclr))

Up next, Part 2: XL2CMYK() and CMYK2XL() where the identity will only be “close enough.”

… mrt
©¿©¬

Writing for Wikipedia is a bit like writing in your third language—you know there are words for it, but your guides are from your second language—Wiki pour Les Nuls or maybe Wiki para Maniquíes. It’s a different dialect of HTML. Having done it for several years, Wikipedia’s goal seems to be a reduction in keystrokes to be saved on its servers. For example, to italicize, rather than using the i- or em- tags, you use paired apostrophes (not double-quotes) to lead and close, saving at least three key strokes. To embolden, you use three apostrophes to lead and close, saving at least one stroke over the b- or strong- tags. And bold italics is thus five apostrophes leading, five apostrophes closing, saving at least four strokes. An unordered list’s members are just lines started with an asterisk, and an ordered list’s members are lines started with a pound sign. This economy follows into Wiki tables, where pipes (“|”) and curly braces (“{}”)are used for all the tags. There is a good summary of the table markup differences here. This little table:

 
In Wiki is this:

as opposed to this:

If you have a table like this is in your spreadsheet:

 
The MakeWikiTable() macro will make a table like this for Wikipedia:

And this table G33:H38

 
where H34: = Text(J34, "0.00") & K34 is aligned right and filled down, looks like this:

Tapping into the class = “wikitable”, MakeWikiTable() can make any combination of sortable, collapsible, and collapsed tables. It invokes the class if the upper left table cell is not empty, and thus your (literally) first column is data vice row headers. There is a longstanding bug in the wikitable class that disables sorting if there is too much adornment given the table, no matter if the table is set as “sortable” or not. The combinations look like this in Wikipedia:

All these examples are available here. You’ll see the footnotes you created in Excel are at the bottom of the Wiki page. It looks different at Wiki Commons, but the code still works. Examples of a tables I made there with MakeWikiTable() are these. You’ll see it uses a sideways arrow instead of text for hide and show.

This is the MakeWikiTable code:

Note that the Caption is at Offset(1,0) from the your selected table. Wikipedia’s examples all use muted grays for the headers. The first two of the following are the provided colors, the third, aka “silver” is what I used above.

The macro should be invoked from a form instead of daisy-chained message boxes. I got lazy. The MakeFracs() function is here. If you want to put something in the upper-left cell, and still get row headers, put it in after you paste your table into Wikipedia.

… mrt
©¿©¬

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8″ pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

 
At least that’s all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added “j-loop” in the middle translates the &fracnd;’s into Dec code. This works fine, though it’s a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

 
Frankly, I’m not sure that this is an improvement. You’ll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn’t, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it’s being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬