Column letter to column number in Excel.
In a previous blog I posted a VBA algorithm for Excel that took the column letter (A, B, Z, AA) and returned the corresponding column number. There was a tiny little problem in the code, so I'm reposting the entry with the fix. The tiny little problem was you had to pass the column letter in upper case.
I couldn't find a method to covert the column letter to column number in Excel, i.e. Column A = Column 1, Column AA = 27 so, as you do, I wrote my own. It's good for letting the user enter a column in a GUI as a letter while you use it in code as a number.
Function ColRef(Col As String) As Integer
'
' Returns Excel column number
'
' Input:
' Col Excel column reference (eg. AA)
'
' Output:
' ColRef Column number (eg. 27)
'col needs to be upper case
Col = UCase(Col)
If Len(Col) = 1 Then
ColRef = Asc(Col) - 64
ElseIf Len(Col) = 2 Then
C1 = Left$(Col, 1)
ColRef1 = (Asc(C1) - 64) * 26
C2 = Right$(Col, 1)
ColRef = ColRef1 + (Asc(C2) - 64)
End If
If (ColRef <> 256) Then
MsgBox "Wrong Column number", vbExclamation
ColRef = -1
Exit Function
End If
End Function
It takes the ASCII code of a single column letter and coverts it to it's numerical equivalent by taking 64 from it. ASCII code of A = 65 (- 64) = Column 1.
If there are two letters then it works in base 26.
AB
Asc(B) = 66 - 64 = 2
Asc(A) = 65 - 64 = 1 * 26 = 26
AB = 26 + 2 = 28