2005.02.10 01:33 PM
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.
You don't really need recursion here - it just adds overhead and slows things down. Just use a simple Do loop.
Public Function GetAlphaBulletNoRecursion(ByVal Index As Long) As String
Const LETTERS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim strTemp As String
Do While Index > 0
strTemp = Mid$(LETTERS, (Index - 1) Mod 26 + 1, 1) & strTemp
Index = Index \ 26
GetAlphaBulletNoRecursion = strTemp
Helen | 2005.02.11 04:33 AM
You're right, if it can be recursed, it can be looped. We just thought the recursion was cleaner as it requires no local state variables.
And you're also right about performance. The loop is about 30% faster on my machine (uncompiled). But even at its slower pace, the recursive routine can execute over 250,000 times a second, which is probably fast enough for database report bullets.
Thanks for taking the time to share your thoughts. I really appreciate it.
ewbi.develops | 2005.02.11 08:58 AM
TrackBack URL: http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83436981553ef
Listed below are links to weblogs that reference Alpha Bullets, Part II: