VBA Convert Column Letter to Number

VBA Convert Column Letter to Number in Excel

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.

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.

VBA Column Number for Letter in Excel Example
VBA Column Number for Letter in Excel Example

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog

Leave a Comment