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.
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.
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.
You can click on the following links for other useful related articles for your reference.