VBA Convert Column Number to Letter | Function | Example

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.

Table of Contents:

Objective
1st Method: Function and Example to Convert to Column Letter
2nd Method: Function and Example to Convert Column Letter
Instructions to Run VBA Macro Code
Other Useful Resources

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.