Converting Excel column number to letter and letter to column number is actually something that every VBA developer does at least once a day. The easiest way is probably to use the property .Column or .Address of the range object that you are using and to play a bit with it.
Not a lot to do with #VBA, just a nice picture from the weekend.
Anyway, if you want to do it with Maths and without using the Excel range properties, this is the way to do it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Function ColumnToLetter(columnNumber As Long) As String If columnNumber < 1 Then Exit Function ColumnToLetter = UCase(ColumnToLetter(Int((columnNumber - 1) / 26)) & Chr(((columnNumber - 1) Mod 26) + Asc("A"))) End Function Function LetterToColumn(letters As String) As Long Dim i As Long letters = UCase(letters) For i = Len(letters) To 1 Step -1 LetterToColumn = LetterToColumn + (Asc(Mid(letters, i, 1)) - 64) * 26 ^ (Len(letters) - i) Next End Function |
And if you want to see some tests, check here:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Tests() Debug.Print LetterToColumn("a") = 1 Debug.Print LetterToColumn("A") = 1 Debug.Print LetterToColumn("Z") = 26 Debug.Print LetterToColumn("AA") = 27 Debug.Print LetterToColumn("AZ") = 52 Debug.Print LetterToColumn("BA") = 53 Debug.Print ColumnToLetter(1) = "A" Debug.Print ColumnToLetter(26) = "Z" Debug.Print ColumnToLetter(27) = "AA" Debug.Print ColumnToLetter(52) = "AZ" Debug.Print ColumnToLetter(53) = "BA" End Sub |
Enjoy! 🙂