VBA Convert Column Letter to Number

Convert Column Letter to Number in Excel VBA. Usually VBA programmers, refer column names or numbers. In general column header names represents like A,B, C,D,… . Lets assume we may want to know column ‘E’ represents which number. It represents number ‘5’. We can say it easily because we can count it in fingers. But when comes to multiple letters, it will be difficult to find number. In the following article lets learn a function and example. It helps to know column number based on specified column letter.

Table of Contents:

Objective
Function and Example to Convert to Column Number
Instructions to Run VBA Macro Code
Other Useful Resources

Function and Example to Convert to Column Number using Letter

Let us see the function to convert column Letter. You can use same function in excel to find column Letter of corresponding column Number.
Function:

Public Function VBA_Column_Letter_To_Number(ByVal ColNum As String) As Integer
    
    'Function to get Column Letter from Column number.
    VBA_Column_Letter_To_Number = Range(ColNum & 1).Column
    
End Function

Example:
Here is an example macro to find letter from number using VBA code. Lets see how to use above specified function for macro example. In the following example we are checking letter ‘ZZ’ of column number.

Sub VBA_Column_Letter_To_Number_Example()
    
    'Variable declaration
    Dim sColName As String
    
    'Assign value to a variable
    sColName = "ZZ"
    
    'Call Function
    MsgBox "Column Number for Column Letter " & sColName & " is :" & VBA_Column_Letter_To_Number(sColName), vbInformation, "VBAF1"

End Sub

Output: Here is the output screenshot of above example.