2004.12.06 08:37 PM
Alpha Bullets
Somehow in twenty years of doing this I've never had to derive an alpha value from a number in the same way that a word processor (or even HTML) labels alpha bullets or a spreadsheet labels columns, which is to say A-Z for the first 26, then A-Z as a prefix to A-Z for each subsequent set of 26, as in:
1=A
2=B
...
25=Y
26=Z
27=AA
28=AB
...
701=ZY
702=ZZ
Having done it today (for some obscure thing), I figured I'd share.
Public Function GetAlphaBullet(ByVal Index As Integer) As String Const LETTERS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" Dim lPrefix As Integer Dim sPrefix As String If Index < 1 Or Index > 702 Then Exit Function lPrefix = Fix((Index - 1) / 26) If lPrefix > 0 Then sPrefix = Mid$(LETTERS, lPrefix, 1) GetAlphaBullet = sPrefix & Mid$(LETTERS, ((Index - 1) Mod 26) + 1, 1) End Function
After making and testing a first pass, I ran it by my admittedly smarter pal Darnley Bynoe who simplified my math with the Fix (don't ask what I was doing) and added a constant string of letters (my original use of an offset from Chr(Asc("A")) was clear enough, but really unnecessary).
I suppose the next thing to do is eliminate the 26*27 upper limit through recursion. ;)
Comments
What does "fix" do? I want it!
Jeff Key | 2004.12.07 07:07 AM
Jeff - I can't tell when you're pulling my leg! ;)
Just in case, it's the same as VBA.Conversion.Int (or .NET's System.Math.Floor), except that Fix is asymmetric. I could have used either. The main point was that use of the Fix (or Int) eliminated some less than clear Mod'ing I was doing to find the letter group for the prefix.
ewbi.develops | 2004.12.07 07:24 AM
Hell, I could have just used integer division (\ vs. /) and done without Fix or Int.
ewbi.develops | 2004.12.08 03:30 PM
Darnley answered the call for recursion:
http://ewbi.blogs.com/develops/2005/02/alpha_bullets_p.html
Thanks!
ewbi.develops | 2005.02.10 01:36 PM
Post a Comment
TrackBack
TrackBack URL: http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d834213f5d53ef
Listed below are links to weblogs that reference Alpha Bullets:
» Google Groups. From Useful to Useless. from { public virtual blog
Google just had to go and mess up a great thing. Google Groups was the greatest thing around, until Google decided to mess it up and make it totally useless. [Read More]
Tracked on Dec 6, 2004 11:09:43 PM
» Google Groups. From Useful to Useless. from { public virtual blog
Google just had to go and mess up a great thing. Google Groups was the greatest thing around, until Google decided to mess it up and make it totally useless. [Read More]
Tracked on Dec 6, 2004 11:10:18 PM