Click here to Skip to main content
15,867,704 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick

Converting Column Numbers to Range Headers and Vice Versa

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
2 Sep 2020CPOL 6.8K   5   7
Two functions to help manipulate Excel sheets with VBA by converting Column Header letters to 1 based numbers and back
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:

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

  • 2nd September, 2020: Initial tip

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer self interested
United Kingdom United Kingdom
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!

Comments and Discussions

 
Questionpossible glitch Pin
DarkStarHarry3-Sep-20 12:14
DarkStarHarry3-Sep-20 12:14 
AnswerRe: possible glitch Pin
Darren G44116-Sep-20 2:02
Darren G44116-Sep-20 2:02 
QuestionUse RC Ref Style directly in VBA Pin
ssa-ed3-Sep-20 10:43
ssa-ed3-Sep-20 10:43 
AnswerRe: Use RC Ref Style directly in VBA Pin
Darren G44116-Sep-20 2:19
Darren G44116-Sep-20 2:19 
QuestionConverting Column Numbers to Range headers and vice versa Pin
Frank Malcolm2-Sep-20 20:28
Frank Malcolm2-Sep-20 20:28 
AnswerRe: Converting Column Numbers to Range headers and vice versa Pin
Frank Malcolm2-Sep-20 20:31
Frank Malcolm2-Sep-20 20:31 
GeneralRe: Converting Column Numbers to Range headers and vice versa Pin
Darren G44116-Sep-20 2:27
Darren G44116-Sep-20 2:27 
Hi Frank,

The point of these functions is to be used inside of a VBA macro rather than a formula.

The ADDRESS function etc. is not available to the VBA engine.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.