converts the Integer to a String representation of the same column. : Column « Excel « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Excel
3. Column

converts the Integer to a String representation of the same column.

 
Sub Main()
   Debug.Print GetColumnRef(3)
End Sub

Function GetColumnRef(columnIndex As Integer) As String
    Dim numAlpha As Integer
    Dim firstLetter As String
    Dim secondLetter As String
    Dim remainder As Integer

    numAlpha = columnIndex \ 26
    Select Case columnIndex / 26
        Case Is <= 1      'Column ref is between A and Z
            firstLetter = Chr(columnIndex + 64)
            GetColumnRef = firstLetter
        Case Else      'Column ref has two letters
            remainder = columnIndex - 26 * (columnIndex \ 26)
            If remainder = 0 Then
                firstLetter = Chr(64 + (columnIndex \ 26) - 1)
                secondLetter = "Z"
                GetColumnRef = firstLetter & secondLetter
            Else
                firstLetter = Chr(64 + (columnIndex \ 26))
                secondLetter = Chr(64 + remainder)
                GetColumnRef = firstLetter & secondLetter
            End If
    End Select
End Function

 

Related examples in the same category