How to Get the Column Letter in VBA for Excel 2007

We have shown you how to get the Column number from Excel 2007 using the Column Function. But what if you need the actually letter for a macro in your VBA code? Here is how you do it.

Here is the Code, don’t worry I’ll explain in a second:

Sub Convert_Column_Letter()
Dim MyColumn As String, CellLocation As String

‘ Get the address of the active cell in the current selection
CellLocation = ActiveCell.Address

‘ Because .Address is $<columnletter>$<rownumber>, drop the first
‘ character and the characters after the column letter(s).
MyColumn = Mid(CellLocation, InStr(CellLocation, “$”) + 1, InStr(2, CellLocation, “$”) – 2)

‘ Show the answer.
MsgBox MyColumn

End Sub

So lets go there each line, one at a time, obviously the first on is the Sub name so we will skip to the next one.

Dim MyColumn As String, CellLocation As String

I call this part the declaration code. Here we are dimensioning, dim, MyColumn and CellLocation as a string.

CellLocation = ActiveCell.Address

Next part we are assigning the active cell address to CellLocation.

    ‘ Because .Address is $<columnletter>$<rownumber>, drop the first
‘ character and the characters after the column letter(s).
MyColumn = Mid(CellLocation, InStr(CellLocation, “$”) + 1, InStr(2, CellLocation, “$”) – 2)

This next part is where the money is at, or were it finds the letter in the column name. So lets explain a little first. ActiveCell.Address, the .Address part pulls “$columnletter$rownumber”, without quotes. In other words it pulls “$A$1”, without quotes. Since that isn’t the column letter we need to get rid of the rest of the stuff mainly the $’s and the 1. That’s where the Mid and InStr functions come in handy. Mid Function extracts a substring from a string from a location, where InStr functions comes in and lets us return the first occurrence of a string, so in the above we are selecting $ then adding + 1 at the end to only select the Letter, in this case A. Next we  do the same thing just select 2 as the starting position in InStr then subtracting the last letters off with the – 2, just to get A.

MsgBox MyColumn

This line shows a message box with the answer.

Here is what it looks like all nice and neat in Microsoft Visual Basic Editor:

If you are worried about the AA or 10006 rows or something like that, take a look at the picture below, this little trick will work ever time on any column:

Be Sociable, Share!

•