VBA Convert Column Number to Name in Excel

VBA Convert Column Number to Letter

VBA Convert Column Number to Letter in Excel or Column Name. Most of the times when we are writing programs in Excel, we refer column names or numbers. In general column header names represents like A,B, C,D,… . Lets assume we may want to know 5th column represents which letter. It represents letter ‘E’. We can say it easily because we can count it in fingers. But when comes to higher number, it will difficult to find letter. In the following article lets learn a function. It helps to know column name based on specified column number.

1st Method: Function and Example to Convert to Column Letter using Number

Let us see the function to convert column number. You can use same function in excel to find column number of corresponding column name.

Public Function VBA_Column_Number_To_Letter(ByVal ColNum As Integer) As String
    
    VBA_Column_Number_To_Letter = Split(Cells(1, ColNum).Address, "$")(1)
    
End Function

Here is an example macro to find number to letter VBA code. Lets see how to use above specified function for macro example. In the following example we are checking number 30 of column letter.

Sub Test_VBA_Column_Number_To_Letter()
    
    'Variable declaration
    Dim iColNum As Integer
    
    'Assign value to a variable
    iColNum = 30
    
    'Call Function
    MsgBox "Column Letter using Number " & iColNum & " is :" & VBA_Column_Number_To_Letter(iColNum), vbInformation, "VBAF1"

End Sub

Please find output screenshot of the 1st example.

VBA Convert Column Number to Column Letter
VBA Convert Column Number to Name

2nd Method: Function and Example to Convert Column Number

Here is another function and example to convert to letter in Excel VBA.

Function:

Function VBA_Convert_Number_To_Letter1(ByVal ColNum As Integer) As String
   
   'Variable Declaration
   Dim iCnt As Integer
   Dim jCnt As Integer
   
   'Calculations
   iCnt = Int(ColNum / 27)
   jCnt = ColNum - (iCnt * 26)
   
   'Check first Condition
   If iCnt > 0 Then
      VBA_Convert_Number_To_Letter1 = Chr(iCnt + 64)
   End If
   
   'Check second Condition
   If jCnt > 0 Then
      VBA_Convert_Number_To_Letter1 = VBA_Convert_Number_To_Letter1 & Chr(jCnt + 64)
   End If
   
End Function

Example: Lets see the example and respective output.

Sub Test_VBA_Column_Number_To_Letter1()
    
    'Variable declaration
    Dim iColNum As Integer
    
    'Assign value to a variable
    iColNum = 702
    
    'Call Function
    MsgBox "Column Letter for Number " & iColNum & " is :" & VBA_Convert_Number_To_Letter1(iColNum), vbInformation, "VBAF1"

End Sub

Please find output screenshot of the 2nd example.

VBA Convert Column Letter from Number
VBA Convert Column Letter from Number

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 Letter to Number in Excel

Column Number to Letter and Vice Versa Quick Reference in Excel

Leave a Comment

Your email address will not be published. Required fields are marked *