Excel: Column Number to Letter(s)

I found myself working inside Excel this week, doing a little bit of VBA…

It has been a while since I had to work inside this environment, my memory was stretched as I tried to remember what code I had already written (short libraries), etc. My troubles were compounded by the fact that the machine I was using did not have the VBA Help for Excel installed, so I was relying on my memory…which isn’t a good thing!

So I’m grateful for the existence of Dick’s Blog and in particular this post.

I had the need to convert a column number into it’s alphabetic counter-part…e.g. ColumnLetter(1) returns A…I was pleased to find this code snippet over at Dick’s blog:

Function ColumnLetter(ByVal colNum As Long) As String
Dim i As Long, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then
ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
End If
Next i
End Function

It was just the ticket – thanks Dick!