Alpha Bullets, Part II

While reducing inbox overload today I bumped into an old message from my pal Darnley Bynoe wherein he submitted a replacement for my earlier VB/VBA function to convert numbers into their corresponding alpha bullets (you might recall he helped clean up that version, too).

Darnley's replacement uses recursion to eliminate the arbitrary limit of 702 (i.e., 26 * 27 = "ZZ") I originally placed on the routine for simplicity (and practicality).

This is so much cleaner than the original. Thanks, Darnley!

Public Function GetAlphaBullet(ByVal Index As Long) As String

  Const LETTERS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

  If Index < 1 Then Exit Function
  
  GetAlphaBullet = GetAlphaBullet(Fix((Index - 1) / 26)) & Mid$(LETTERS, ((Index - 1) Mod 26) + 1, 1)

End Function

The Index parameter was changed to a Long to accommodate the increased size (which is now only limited by the environment's stack). Using the maximum VB/VBA Long value of 2,147,483,647 gets us an alpha bullet of "FXSHRXW". Cool.

Darnley's code actually included support for negatives, too, but I pulled it for simplicity (and, again, practicality). If you want to see it, though, just let me know.