Archive for the ‘Miscellany’ Category.

Friday Thoughts

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

Public Function PreventHavingToKillExcelAndLoseWork() As Boolean
    Dim lCountEOF As Long
    Dim lCountMoveNext As Long
    lCountEOF = CountInstanceOfStringInAllModules(".EOF")
    lCountMoveNext = CountInstanceOfStringInAllModule(".MoveNext")
    PreventHavingToKillExcelAndLoseWork = lCountEOF <= lCountMoveNext
End Function

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:

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


A Suite of XL Color Functions, Part 1

RGB Color Cube
Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 2563 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 2563 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:

Function XL2BLU(xlclr As Long) As Long
   XL2BLU = xlclr \ 65536
End Function

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

Function XL2GRN(xlclr As Long) As Long
   XL2GRN = (xlclr - XL2BLU(xlclr) * 65536) \ 256
End Function

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

Function XL2RED(xlclr As Long) As Long
   XL2RED = xlclr - XL2BLU(xlclr) * 65536 - XL2GRN(xlclr) * 256
End Function

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

Function XL2RGB(xlclr As Long) As Variant
   XL2RGB = Array(XL2RED(xlclr), XL2GRN(xlclr), XL2BLU(xlclr))
End Function

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:

Function XL2RGB(xlclr as long) as Variant
   Dim Red as Long, Green as Long, Blue as Long
   Blue = xlclr\65536
   Green = (xlclr - Blue*65536)\256
   Red = xlclr - Blue* 65536 - Green*256
   XL2RGB = Array(Red,Green,Blue)
End Function

The flip function, RGB2XL is this:

Function RGB2XL(Rng As Range) As Long
   Dim Red As Long, Green As Long, Blue As Long
   Red = Rng.Cells(1, 1)
   Green = Rng.Cells(1, 2)
   Blue = Rng.Cells(1, 3)
   RGB2XL = RGB(Red, Green, Blue)
End Function

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

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

… mrt

Making a Wiki Table

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:

1 2
3 4

In Wiki is this:

| 1 || 2
| 3 || 4

as opposed to this:


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

Sports Teams    
  Boston New York
Baseball Boston Red Sox New York Yankees<br />New York Mets
Football New England Patriots New York Giants<br />New York Jets
Basketball Boston Celtics New York Knicks<br />New York Nets
Hockey Boston Bruins New York Rangers<br />New York Islanders

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

Sports Teams
  Boston New York
Baseball Boston Red Sox New York Yankees
New York Mets
Football New England Patriots New York Giants
New York Jets
Basketball Boston Celtics New York Knicks
New York Nets
Hockey Boston Bruins New York Rangers
New York Islanders

And this table G33:H38

  G H I J K
32 Testing Sorting        
33 Coins Value      
34 Pennies 0.01<font color=white>[-]</font>   0.01 <font color=white>[-]</font>
35 Nickels 0.05<font color=white>[-]</font>   0.05 <font color=white>[-]</font>
36 Dimes 0.10<font color=white>[-]</font>   0.1 <font color=white>[-]</font>
37 Quarters 0.25<ref name=two>Two bits</ref>   0.25 <ref name=two>Two bits</ref>
38 Halves 0.50<ref name=four>Four bits</ref>   0.5 <ref name=four>Four bits</ref>

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

Testing Sorting
Coins Value
Pennies 0.01[-]
Nickels 0.05[-]
Dimes 0.10[-]
Quarters 0.25[1]
Halves 0.50[2]

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:

With Sorting
With Sorting
No Sorting
No Sorting
Coins Coins [hide] Coins [show] Coins [hide] Coins [show]

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:

Public Sub MakeWikiTable()
   Const DQ    As String * 1 = """"   'double double double-quotes
    Dim DataObj As New MSForms.DataObject
   'Check VBE Tools/References Microsoft Forms 2.0 Object Library
    Dim Rng     As Range
   Dim Cell    As Range
   Dim sReturn As String
   Dim TextAlign As String
   Dim CellContents As String
   Dim UseRowHeaders As Boolean
   Dim R As Long, C As Long
   Dim IsSortable As Long, IsCollapsible As Long, IsCollapsed As Long
   Dim Caption   As String
   Dim BgColor As String, FontColor As String

   Set Rng = Selection
   R = Rng.Rows.Count
   C = Rng.Columns.Count
   Caption = Rng.Cells(1, 1).Offset(-1, 0).Text   'Don't start the table in Row(1)
    If Len(Rng.Cells(1.1)) = 0 Then
      UseRowHeaders = True
      IsSortable = vbNo
      IsSortable = MsgBox("Use Sortable Headers for your " & R & "-row by " & C & "-column table?", _
                          vbYesNoCancel + vbQuestion, "MRT's Wiki Table Maker")
      If IsSortable = vbCancel Then Exit Sub
      IsCollapsible = MsgBox("Do you want your table to collapse?", _
                          vbYesNoCancel + vbQuestion + vbDefaultButton1, "MRT's Wiki Table Maker")
      If IsCollapsible = vbCancel Then Exit Sub
      If IsCollapsible = vbYes Then
         IsCollapsed = MsgBox("Do you want your table to load as collapsed?", _
                          vbYesNoCancel + vbQuestion + vbDefaultButton1, "MRT's Wiki Table Maker")
         If IsCollapsed = vbCancel Then Exit Sub
      End If
   End If
   If IsSortable = vbYes Or IsCollapsible = vbYes Then
      sReturn = "{|class=" & DQ & "wikitable" & IIf(IsSortable = vbYes, " sortable", "") & _
            IIf(IsCollapsible = vbYes, " collapsible", "") & _
            IIf(IsCollapsed = vbYes, " collapsed", "") & DQ & _
            " style=" & DQ & "margin: 1em auto 1em auto;" & DQ & vbNewLine
      sReturn = "{|border=" & DQ & "1" & DQ & " cellpadding=" & DQ & "5" & DQ & " cellspacing=" & DQ _
             & "0" & DQ & " style=" & DQ & "margin: 1em auto 1em auto;" & DQ & vbNewLine
   End If
   sReturn = sReturn & "|+'''" & Caption & "'''" & vbNewLine
   sReturn = sReturn & "|-<!--Header-->" & vbNewLine
   For Each Cell In Rng.Rows(1).Cells
      CellContents = Cell.Text
      If Len(CellContents) = 0 Then
         CellContents = "&nbsp;"
         CellContents = Application.WorksheetFunction.Trim(CellContents)
      End If
      BgColor = HexColor(Cell.Interior.Color)
      FontColor = HexColor(Cell.Font.Color)
      sReturn = sReturn & "!scope=" & DQ & "col" & DQ & " style=" & DQ & "background-color:" & _
         BgColor & "; color:" & FontColor & ";" & DQ & "| " & _
         CellContents & vbNewLine
   Next Cell

   For R = 2 To Rng.Rows.Count
      sReturn = sReturn & "|-<!--Row " & R - 1 & "-->" & vbNewLine
      For C = 1 To Rng.Columns.Count

         Set Cell = Rng.Cells(R, C)
         CellContents = Cell.Text
         If Len(CellContents) = 0 Then CellContents = "&nbsp;"

         CellContents = MakeFracs(CellContents)
         CellContents = Application.WorksheetFunction.Trim(CellContents)
         CellContents = VBA.Replace(CellContents, "0 / 0", "zero / zero", 1, 1, vbTextCompare)

         If C = 1 And UseRowHeaders Then
            BgColor = HexColor(Cell.Interior.Color)
            FontColor = HexColor(Cell.Font.Color)
            sReturn = sReturn & "!scope=" & DQ & "row" & DQ & " style=" & DQ & "background-color:" & _
                  BgColor & "; color:" & FontColor & ";" & DQ & "| " & _
                  CellContents & vbNewLine
            Select Case Cell.HorizontalAlignment
               Case xlGeneral
                  TextAlign = "left"
                  If IsNumeric(Cell.Value) Then TextAlign = "right"
                  If IsError(Cell.Value) Then TextAlign = "center"
               Case xlLeft
                  TextAlign = "left"
               Case xlCenter
                  TextAlign = "center"
               Case xlRight
                  TextAlign = "right"
               Case xlJustify
                  TextAlign = "center"
            End Select
            sReturn = sReturn & "|align=" & TextAlign & "| "
            With Cell.Font
               If .Italic Then sReturn = sReturn & "''"
               If .Bold Then sReturn = sReturn & "'''"
            End With

            sReturn = sReturn & CellContents

            With Cell.Font
               If .Bold Then sReturn = sReturn & "'''"
               If .Italic Then sReturn = sReturn & "''"
            End With
            sReturn = sReturn & vbNewLine
         End If
      Next C
   Next R
   sReturn = sReturn & IIf(IsSortable = vbYes, "|-class=sortbottom" & vbNewLine, "")
   sReturn = sReturn & "|}" & vbNewLine
   DataObj.SetText sReturn

End Sub

Function HexColor(Color As Long) As String
   Dim Red As String, Green As String, Blue As String
   Red = VBA.Hex(Color And 255)
   Green = VBA.Hex(Color \ 256 And 255)
   Blue = VBA.Hex(Color \ 256 ^ 2 And 255)
   If Len(Red) = 1 Then Red = "0" & Red
   If Len(Green) = 1 Then Green = "0" & Green
   If Len(Blue) = 1 Then Blue = "0" & Blue
   HexColor = "#" & Red & Green & Blue
End Function

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

Using HTML5 Fractions in Exported Excel Tables

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:

Name Hex Dec Result
&frac12; U+000BD 189 ½
&frac13; U+02153 8531
&frac14; U+000BC 188 ¼
&frac15; U+02155 8533
&frac16; U+02159 8537
&frac18; U+0215B 8539
&frac23; U+02154 8532
&frac25; U+02156 8534
&frac34; U+000BE 190 ¾
&frac35; U+02157 8535
&frac38; U+0215C 8540
&frac45; U+02158 8536
&frac56; U+0215A 8538
&frac58; U+0215D 8541
&frac78; U+0215E 8542

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

Result Named
½ &frac12; &#X00BD; &#189;
&frac13; &#X2153; &#8531;
¼ &frac14; &#X00BC; &#188;
&frac15; &#X2155; &#8533;
&frac16; &#X2159; &#8537;
&frac18; &#X215B; &#8539;
&frac23; &#X2154; &#8532;
&frac25; &#X2156; &#8534;
¾ &frac34; &#X00BE; &#190;
&frac35; &#X2157; &#8535;
&frac38; &#X215C; &#8540;
&frac45; &#X2158; &#8536;
&frac56; &#X215A; &#8538;
&frac58; &#X215D; &#8541;
&frac78; &#X215E; &#8542;

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:

1 1/1 1/2 1/3 1/4 1/5 1/6 1/7 1/8 1/80
2 2/1 2/2 2/3 2/4 2/5 2/6 2/7 2/8 2/8.
3 3/1 3/2 3/3 3/4 3/5 3/6 3/7 3/8 3/8A
4 4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 6 4/8
5 5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 7 5/8
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 6/8 8 6/8
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 7/8 9 7/8
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

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.

Function MakeFracs(Arg As String) As String
   Dim sIN     As String
   Dim sOUT    As String
   Dim i As Long, j As Long
   Dim n As Long, d As Long
   Dim Fracs(1 To 15, 1 To 3) As String
   Fracs(1, 1) = "&frac12;": Fracs(1, 2) = "&#189;": Fracs(1, 3) = "&#X00BD;"
   Fracs(2, 1) = "&frac13;": Fracs(2, 2) = "&#8531;": Fracs(2, 3) = "&#X2153;"
   Fracs(3, 1) = "&frac14;": Fracs(3, 2) = "&#188;": Fracs(3, 3) = "&#X00BC;"
   Fracs(4, 1) = "&frac15;": Fracs(4, 2) = "&#8533;": Fracs(4, 3) = "&#X2155;"
   Fracs(5, 1) = "&frac16;": Fracs(5, 2) = "&#8537;": Fracs(5, 3) = "&#X2159;"
   Fracs(6, 1) = "&frac18;": Fracs(6, 2) = "&#8539;": Fracs(6, 3) = "&#X215B;"
   Fracs(7, 1) = "&frac23;": Fracs(7, 2) = "&#8532;": Fracs(7, 3) = "&#X2154;"
   Fracs(8, 1) = "&frac25;": Fracs(8, 2) = "&#8534;": Fracs(8, 3) = "&#X2156;"
   Fracs(9, 1) = "&frac34;": Fracs(9, 2) = "&#190;": Fracs(9, 3) = "&#X00BE;"
   Fracs(10, 1) = "&frac35;": Fracs(10, 2) = "&#8535;": Fracs(10, 3) = "&#X2157;"
   Fracs(11, 1) = "&frac38;": Fracs(11, 2) = "&#8540;": Fracs(11, 3) = "&#X215C;"
   Fracs(12, 1) = "&frac45;": Fracs(12, 2) = "&#8536;": Fracs(12, 3) = "&#X2158;"
   Fracs(13, 1) = "&frac56;": Fracs(13, 2) = "&#8538;": Fracs(13, 3) = "&#X215A;"
   Fracs(14, 1) = "&frac58;": Fracs(14, 2) = "&#8541;": Fracs(14, 3) = "&#X215D;"
   Fracs(15, 1) = "&frac78;": Fracs(15, 2) = "&#8542;": Fracs(15, 3) = "&#X215E;"

   i = VBA.InStr(1, Arg, "/", vbTextCompare)
   If i = 0 Then   'there's no fraction
      MakeFracs = Arg
   ElseIf Mid$(Arg, i, 3) Like "/##" Then   'not HTML5
      MakeFracs = Arg
   ElseIf Mid$(Arg, i - 2, 4) Like " [1-7]/[234568]" Then
     sOUT = Mid$(Arg, i - 1, 3)
     n = VBA.Val(Left$(sOUT, 1))   'numerator
      d = VBA.Val(Right$(sOUT, 1))   'denominator
      If n < d Then
         If d Mod n = 0 Then
            d = d / n
            n = 1
         ElseIf d Mod 2 = 0 And n Mod 2 = 0 Then
            d = d / 2
            n = n / 2
         End If
         sIN = "&frac" & n & d & ";"
         For j = 1 To 15
            If Fracs(j, 1) = sIN Then
              sIN = Fracs(j, 2) '<-or Fracs(j, 3) for HEX
               Exit For
            End If
         Next j
         MakeFracs = VBA.Replace(Arg, sOUT, sIN)
         MakeFracs = Arg
      End If
      MakeFracs = Arg
   End If

End Function

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:

1 1/1 &#189; &#8531; &#188; &#8533; &#8537; 1/7 &#8539; 1/80
2 2/1 2/2 &#8532; &#189; &#8534; &#8531; 2/7 &#188; &#188;.
3 3/1 3/2 3/3 &#190; &#8535; &#189; 3/7 &#8540; &#8540;A
4 4/1 4/2 4/3 4/4 &#8536; &#8532; 4/7 &#189; 6 &#189;
5 5/1 5/2 5/3 5/4 5/5 &#8538; 5/7 &#8541; 7 &#8541;
6 6/1 6/2 6/3 6/4 6/5 6/6 6/7 &#190; 8 &#190;
7 7/1 7/2 7/3 7/4 7/5 7/6 7/7 &#8542; 9 &#8542;
8 8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

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:

1/1 ½ ¼ 1/7 1/80
2/1 2/2 ½ 2/7 ¼ ¼.
3/1 3/2 3/3 ¾ ½ 3/7 ⅜A
4/1 4/2 4/3 4/4 4/7 ½ 6 ½
5/1 5/2 5/3 5/4 5/5 5/7 7 ⅝
6/1 6/2 6/3 6/4 6/5 6/6 6/7 ¾ 8 ¾
7/1 7/2 7/3 7/4 7/5 7/6 7/7 9 ⅞
8/1 8/2 8/3 8/4 8/5 8/6 8/7 8/8 10 8/8

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.