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.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.