VBA Convert Column Letter to Number in Excel

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.

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.

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

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 use macro code

Here are the instructions to use above macro and function in Visual basic editor.

  • Open Visual Basic Editor(VBE) by clicking Alt +F11
  • Go to code window by clicking F7
  • Copy above specified macro or procedure
  • Paste above copied code in code window
  • Run macro by clicking F5 or Run command
  • Find above specified output in on the screen.

Related Articles

You can click on the following links for other useful related articles for your reference.

VBA Convert Column Number to Letter in Excel

Column Number to Letter and Vice Versa Quick Reference in Excel

Leave a Comment