Converting Column Numbers to Range Headers and Vice Versa

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) ' Gets AA
debug.print columnToLetter(702) ' Gets ZZ
debug.print columnToLetter(703) ' Gets AAA

debug.print letterToColumn("AA") ' Gets 27
debug.print letterToColumn("ZZ") ' Gets 702
debug.print letterToColumn("AAA") ' Gets 703

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) ' Trim off first letter
  Loop Until Len(letter) = 0
  letterToColumn = column
End Function

History

• 2^nd September, 2020: Initial tip