Excel: converting column number to name

[Cakkie]

I have a problem I just can get figured out (maybe because it's so late).

I need to find a way to convert a column number to the character form.
Ok, I will give an example, 1 -> A, 2 -> B, 52 -> AZ, ... you get the point. So I came up with this brilliant idea (not really) to treat it in about the same way you would treat conversion from binary to hex, but using a 26-based-like system in stead of a 16-based. At first, all look good, but after some testing, I got some errors with multiples of 26 (not including 26 itself). I think the problem is that my system does not has a zero equvalent. After AZ must come BA, not B0 <- B-ZERO.
Any suggestions

public Function XLCol(byval iCol as Long) as string

Dim lvl as Integer
Dim r as Long
Dim cpos as string
Dim strABC as string

strABC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

lvl = 0

Do Until 26 ^ (lvl + 1) > iCol
r = iCol Mod (26 ^ (lvl + 1))
cpos = mid(strABC, r, 1) & cpos
iCol = iCol - r
lvl = lvl + 1
Loop

cpos = mid(strABC, iCol / (26 ^ lvl), 1) & cpos

XLCol = cpos

End Function




Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Kdev]

public Function XLCol(byval iCol as Long) as string
Dim lvl as Integer
Dim r as Long
Dim cpos as string
Dim strABC as string

strABC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

lvl = 0
Do Until 26 ^ (lvl + 1) >= iCol
r = iCol Mod (26 ^ (lvl + 1))
If r = 0 then r = 26 'Special Case
cpos = mid(strABC, r, 1) & cpos
iCol = iCol - r
lvl = lvl + 1
Loop
cpos = mid(strABC, iCol / (26 ^ lvl), 1) & cpos
XLCol = cpos
End Function



Note that since you are using 'A' as 1 and having no 0 then you need to break your loop EQUAL to or above 26 so that multiples of 26 give you 'Z' and not an unknown 0.

To handle the missing case of 0 inside your loop treat perfect multiples of 26 as being on the current level, that is, 26 = 'Z' 52 = 'AZ' and so forth.

-K

[coolbiz]

alternative code (probably slower than yours anyway :))


private Function ConvertColInd(nInd as Integer) as string
ConvertColInd = ""

' check boundary
If ((nInd < 1) Or (nInd > 256)) then Exit Function

Dim nUpperInd as Integer
Dim nLowerInd as Integer
Dim szTemp as string

' get the pure number part of the division
szTemp = CStr(nInd / 26)
If (InStr(1, szTemp, ".") > 0) then
' got decimal place - strip it off
szTemp = Left$(szTemp, InStr(1, szTemp, ".") - 1)
End If
nUpperInd = CInt(szTemp)

' get the lower index
nLowerInd = nInd Mod 26

Dim szUpper as string
Dim szLower as string

' a little trick to fix the multiple of 26s
If (nLowerInd = 0) then
nLowerInd = 26
nUpperInd = nUpperInd - 1
End If

If (nUpperInd > 0) then szUpper = mid$(szABC, nUpperInd, 1)
If (nLowerInd > 0) then szLower = mid$(szABC, nLowerInd, 1)
ConvertColInd = szUpper & szLower
End Function




-Cool Bizs

[Cakkie]

So, simple, yet so easy to look over. Thanks

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook