Utility functions to get the column number (1 based) in Excel from the letter headers or the letters from the 1 based number
Introduction
In Excel VBA programming, it's sometimes necessary to take user input in terms of column header letters and convert it to a number or the reverse. These two functions will help you do that. They are very simple mathematical manipulation on base 26.
I ported this code from many similar JavaScript examples you can find for Apps Script on GSuite, the particular author that wrote the original answer to a Stack Exchange question was "AdamL".
I had to rewrite a little for the VBA way of thinking!
Background
I sometimes write my Excel VBA with a reliance on configuration details rather than coded values. As things change, I often want to build or slice dynamic range selections from configured column headers, etc. These functions help with that.
Using the Code
Place the function declarations in a standard module and then use anywhere in your project as global functions:
debug.print columnToLetter(27)
debug.print columnToLetter(702)
debug.print columnToLetter(703)
debug.print letterToColumn("AA")
debug.print letterToColumn("ZZ")
debug.print letterToColumn("AAA")
Public Function columnToLetter(column As Integer) As String
Dim temp As Integer
Dim letter As String
If column < 1 Or column > 16384 Then
Err.Raise vbObjectError + 1024 + 99, "columnToLetter", _
"Column numbers in the range 1 to 16384 (XFD) only. You tried: " & column
End If
Do While (column > 0)
temp = (column - 1) Mod 26
letter = Chr(temp + 65) + letter
column = (column - temp - 1) / 26
Loop
columnToLetter = letter
End Function
Public Function letterToColumn(ByVal letter As String) As Integer
Dim column As Integer
Dim length As Integer
Dim c As String
Dim n As Integer
Do
c = Left(letter, 1)
length = Len(letter)
n = Asc(c) - 64
If n < 1 Or n > 26 Then
Err.Raise vbObjectError + 1024 + 99, "letterToColumn", _
"Only letters A to Z are valid. You tried """ & c & """"
End If
column = column + n * 26 ^ (length - 1)
letter = Mid(letter, 2)
Loop Until Len(letter) = 0
letterToColumn = column
End Function
History
- 2nd September, 2020: Initial tip
I have been a software developer for about 20 years, mostly in small IT department that means you do all the roles all the time from 1st line support to 3rd line diagnostics and help authoring. To be fair, I don't do enough of the later which in turn causes way too much of the former with new staff always ready to show you how unintuitive your interfaces are!
I generally consider myself a "data plumber" with the majority of my work in back end SQL. If I could rule the world by writing a SPROC I would....
New definition of Stoicism: Someone who start a career in I.T. and still has a career in I.T. after 20 years!