Convert Excel column number to letters and vice versa

Since I haven't written on here for a while, I thought I'd put up a quick post with some code to convert an Excel column letter reference to a number or the reverse: convert column letters to a number. This is a good candidate for your Excel utility module.

This function uses recursion, but there isn't any risk of stack overflow because there will at most log26(n) recursions, where n is the column number. It also uses the relatively unknown VBA \ operator, which does integer division (rounding down). Input validation is left as an exercise. I don't do any because I only ever call this function with constant values, which are pretty hard to get wrong.

' converts column letter to number or vice versa
Public Function Col(c As Variant) As Variant
    Dim x As Variant
    
    If VarType(c) = vbLong Or VarType(c) = vbInteger Then
        x = Chr(Asc("A") + (c - 1) Mod 26)
        
        If c > 26 Then
            Col = Col((c - 1) \ 26) & x
        Else
            Col = x
        End If
    ElseIf VarType(c) = vbString Then
        x = Asc(Right(c, 1)) - Asc("A") + 1
        
        If Len(c) > 1 Then
            Col = x + Col(Left(c, Len(c) - 1)) * 26
        Else
            Col = x
        End If
    Else
        Col = Null
    End If
End Function

Excel versions 2007 and later support 16,384 columns, so a good test is with column 16,384, which you can verify is XFD by opening a new Workbook and pressing Ctrl+Right.

' Executed in the immediate window
?Col(16384)
XFD

?Col("XFD")
 16384