Celebrating my MVP award: Discount offer

Hi everyone!

Every quarter Microsoft announces who are the lucky ones to receive their Most Valuable Professional Award. An MVP award lasts a year, so for a quarter of the MVPs, October 1st is an important day.

I got re-awarded!

And to celebrate that I am offering a 3 day 50 percent discount on my Formula auditing tool: RefTreeAnalyser

From October 8, 2014 to October 10, 2104 you receive 50 % off of the list price when you enter this coupon code: MVP2014

Head over to my website and download the tool, you can try it for free!

Jan Karel Pieterse

Friday Thoughts

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.

For Whom the RSS Tolls

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 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 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.

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 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.

Blog Blog Blogaversary

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.

Noughts and Crosses

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.

Sub TicTacToe()
End Sub

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. :roll:


Getting a Font List to a Combo Box

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.

Font Sampler Image

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:

Option Explicit
Private Fface As String, FaceNdx As Long
Private InstalledFonts As String

Public Property Get FontFace() As String
FontFace = FFace
End Property

Private Sub btnMonoFonts_Click()
Call AddFontBox(1)
Me.lblFontcbo = "Monospace Fonts"
End Sub

Private Sub btnAllFonts_Click()
Dim i As Long
Dim TempFonts As Variant


TempFonts = Split(InstalledFonts, ",")
For i = LBound(TempFonts) To UBound(TempFonts)
Me.cboFontOther.AddItem TempFonts(i)
Next i
Me.cboFontOther.Text = "Comic Sans MS"
Me.lblFontcbo = "All Fonts"
End Sub

Private Sub cboFontOther_Change()
Me.lblFontcboOverLabel = Me.cboFontOther.Text
Me.lblFontcboOverLabel.Font = Me.cboFontOther.Text
Me.lblFontcboOverLabel.Font.Size = 12
Fface = Me.cboFontOther.Text
End Sub

Private Sub UserForm_Initialize()
Dim FontList As CommandBarControl
Dim Tempbar As CommandBar, i As Long

Set FontList = Application.CommandBars("Formatting").FindControl(Id:=1728)
If FontList Is Nothing Then
Set Tempbar = Application.CommandBars.Add
Set FontList = Tempbar.Controls.Add(Id:=1728)
End If

For i = 1 To FontList.ListCount
If Left$(FontList.List(i), 1) Like "[A-Za-z0-9]" Then
Me.cboFontOther.AddItem FontList.List(i)
InstalledFonts = InstalledFonts & FontList.List(i)
If i <> FontList.ListCount Then InstalledFonts = InstalledFonts & ","
End If
Next i
Me.lblFontcbo = "All Fonts"

Me.cboFontOther.Text = "Impact"

On Error Resume Next
End Sub

Private Sub AddFontBox(i As Long)
Dim MonoFont As Variant

Dim TempFont As Variant, TempStr As String, Str1 As String, Str2 As String

MonoFont = "Monaco,Courier New,Courier,Lucida Sans Typewriter," & _
"Lucida Console,Nimbus Mono L,DejaVu Sans Mono,Andale Mono," & _
"Liberation Mono,Consolas,Courier 10 Pitch,FreeMono," & _
"Menlo Bold,Menlo Bold Italic,Menlo Italic,Menlo Regular," & _
"OCR A Extended,Tlwg Typist,TlwgMono,TlwgTypewriter," & _
"Tlwg Typo,Bitstream Vera Sans Mono"

Select Case i
Case 1: TempFont = Split(MonoFont, ",")
End Select


For i = LBound(TempFont) To UBound(TempFont)
Str1 = TempFont(i) & ","
Str2 = "," & TempFont(i)
If InStr(1, InstalledFonts, Str1, vbTextCompare) Or _
InStr(1, InstalledFonts, Str2, vbTextCompare) Then
TempStr = TempStr & TempFont(i)
If i <> UBound(TempFont) Then TempStr = TempStr & ","
End If
Next i
TempFont = Split(TempStr, ",")

For i = LBound(TempFont) To UBound(TempFont)
Me.cboFontOther.AddItem TempFont(i)
Next i

Me.cboFontOther.Text = TempFont(0)
End Sub

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:

Private Sub cboFontOther_Change()
Me.lblFontcboOverLabel = "Jackdaws love my big sphinx of quartz."
Me.lblFontcboOverLabel.Font = Me.cboFontOther.Text
Me.lblFontcboOverLabel.Font.Size = 12
Fface = Me.cboFontOther.Text
End Sub


A Suite of XL Color Functions, Part 3

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 25510. In HEX, pure red–FF000–is 1671168010. 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, 25510, becomes FF, then 0000FF, and then FF0000. This is the XL2HEX() function.

Function XL2HEX(xlclr As Long, Optional Prefix As String) As String
Dim Temp As String
Temp = Hex(xlclr)
If Len(Temp) < 6 Then Temp = String(6 - Len(Temp), "0") & Temp XL2HEX = Prefix & Right$(Temp, 2) & Mid$(Temp, 3, 2) & Left$(Temp, 2) End 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():

?xl2hex(vbwhite,"#") ?xl2hex(vbred,"#") ?xl2hex(vbgreen,"#") ?xl2hex(vbblue,"#")
#FFFFFF #FF0000 #00FF00 #0000FF
?xl2hex(vbyellow,"#") ?xl2hex(vbcyan,"#") ?xl2hex(vbmagenta,"#") ?xl2hex(vbblack,"#")
#FFFF00 #00FFFF #FF00FF #000000

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.

Function HEX2XL(hexclr As String) As Long
Dim i As Long
Dim Red As Long, Green As Long, Blue As Long
Dim Temp As String

Temp = CheckCSS(hexclr)

Red = CLng("&h" & Left$(Temp, 2))
Green = CLng("&h" & Mid$(Temp, 3, 2))
Blue = CLng("&h" & Right$(Temp, 2))

HEX2XL = RGB(Red, Green, Blue)
End Function

Function CheckCSS(hexclr As String) As String
Dim Temp As String, i As Long
If Len(hexclr) = 4 And Left$(hexclr, 1) = "#" Then
For i = 2 To 4
Temp = Temp & Mid$(hexclr, i, 1) & Mid$(hexclr, i, 1)
Next i
For i = 1 To Len(hexclr)
If Mid$(hexclr, i, 1) Like "[A-Fa-f0-9]" Then Temp = Temp & Mid$(hexclr, i, 1)
Next i
If Len(Temp) < 6 Then Temp = String(6 - Len(Temp), "0") & Temp End If CheckCSS = Temp End Function

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

Function HEX2RED(hexclr As String) As Long
Dim i As Long
Dim Temp As String

Temp = CheckCSS(hexclr)

HEX2RED = CLng("&h" & Left$(Temp, 2))
End Function

Function HEX2GRN(hexclr As String) As Long
Dim i As Long
Dim Temp As String

Temp = CheckCSS(hexclr)

HEX2GRN = CLng("&h" & Mid$(Temp, 3, 2))
End Function

Function HEX2BLU(hexclr As String) As Long
Dim i As Long
Dim Temp As String

Temp = CheckCSS(hexclr)

HEX2BLU = CLng("&h" & Right$(Temp, 2))
End Function

Function HEX2RGB(hexclr As String) As Variant
Dim i As Long, Red As Long, Green As Long, Blue As Long
Dim Temp As String

Temp = CheckCSS(hexclr)

Red = CLng("&h" & Left$(Temp, 2))
Green = CLng("&h" & Mid$(Temp, 3, 2))
Blue = CLng("&h" & Right$(Temp, 2))

HEX2RGB = Array(Red, Green, Blue)
End Function

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

A Suite of XL Color Functions, Part 2

CMYK color modelWhen 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.

Function XL2CMYK(xlclr As Long) As Variant
Dim Red As Double, Green As Double, Blue As Double
Dim Cyan As Double, Magenta As Double, Yellow As Double, Black As Double

Red = XL2RED(xlclr) / 255 'normalizing
Green = XL2GRN(xlclr) / 255
Blue = XL2BLU(xlclr) / 255

Black = Application.WorksheetFunction.Min(1 - Red, 1 - Green, 1 - Blue)
Cyan = Round(100 * (1 - Red - Black) / (1 - Black), 0) 'scaling then rounding
Magenta = Round(100 * (1 - Green - Black) / (1 - Black), 0)
Yellow = Round(100 * (1 - Blue - Black) / (1 - Black), 0)
Black = Round(100 * Black, 0)

XL2CMYK = Array(Cyan, Magenta, Yellow, Black)
End 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:

Function CMYK2XL(Rng As Range) As Long
Dim Cyan As Double, Magenta As Double, Yellow As Double, Black As Double
Dim Red As Double, Green As Double, Blue As Double

Cyan = Rng.Cells(1, 1) / 100 'normalizing
Magenta = Rng.Cells(1, 2) / 100
Yellow = Rng.Cells(1, 3) / 100
Black = Rng.Cells(1, 4) / 100

Red = (1 - Cyan) * (1 - Black)
Green = (1 - Magenta) * (1 - Black)
Blue = (1 - Yellow) * (1 - Black)

Red = Round(255 * Red, 0) 'scaling then rounding
Green = Round(255 * Green, 0)
Blue = Round(255 * Blue, 0)

CMYK2XL = RGB(Red, Green, Blue)
End Function

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:

11196331⟹     ⟸11130539

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()