15,668,517 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick
Posted 2 Sep 2020

5.7K views
5 bookmarked

# Converting Column Numbers to Range Headers and Vice Versa

Rate me:
5.00/5 (1 vote)
2 Sep 2020CPOL
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

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

 First Prev Next
 possible glitch DarkStarHarry3-Sep-20 12:14 DarkStarHarry 3-Sep-20 12:14
 Re: possible glitch Darren G44116-Sep-20 2:02 Darren G441 16-Sep-20 2:02
 If a lower case letter is passed then an error is raised. you get a runtime exception #-2147220381 with text description:- Only letters A to Z are valid. You tried "c" I chose to implement this as a pure function style with a single responsibility of working with good input or error if not. I left the UCASE work to whatever function might be used that calls these little helpers.
 Use RC Ref Style directly in VBA ssa-ed3-Sep-20 10:43 ssa-ed 3-Sep-20 10:43
 Re: Use RC Ref Style directly in VBA Darren G44116-Sep-20 2:19 Darren G441 16-Sep-20 2:19
 Converting Column Numbers to Range headers and vice versa Frank Malcolm2-Sep-20 20:28 Frank Malcolm 2-Sep-20 20:28
 Re: Converting Column Numbers to Range headers and vice versa Frank Malcolm2-Sep-20 20:31 Frank Malcolm 2-Sep-20 20:31
 Re: Converting Column Numbers to Range headers and vice versa Darren G44116-Sep-20 2:27 Darren G441 16-Sep-20 2:27
 Last Visit: 31-Dec-99 18:00     Last Update: 5-Jun-23 7:02 Refresh 1