That formula only works until you get to column AA.

w/XL07, columns go to XFD.

For WinXL00/02/03/07, the UDF could be shortened a bit:

Public Function ColName(strRange As String) As String
ColName = Split(Range(strRange).Address, "$")(1)
End Function

And, throwing in a little bit of error handling:

Public Function ColName(strRange As String) As Variant
Dim rTest As Range
On Error Resume Next
Set rTest = Range(strRange)
On Error GoTo 0
If Not rTest Is Nothing Then
ColName = Split(rTest.Address, "$")(1)
Else
ColName = CVErr(xlErrRef)
End If
End Function




In article ,
JMB wrote:

I'm not sure why you need this function. Excel worksheet functions can get
the column letter of the upper left cell with

=CHAR(64+COLUMN(Test))

and VBA doesn't really need the column letter as it can reference ranges
using the column number. If you have to have a VBA function to get the
column letter (of the upper left cell of a named range) you could try:

Function ColName(strRange As String) As String
ColName = Chr(64 + _
Range(strRange).Cells(1).Column)
End Function