Alphabetic Column Designation

Assuming you have the "View," "Headings" checked,k the following procedure will toggle the headings from Letters (ABC...) to Numbers(123...) or vice versa.
Sub ToggleR1C1()
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else: Application.ReferenceStyle = xlA1
End If
End Sub

Billy

@ Willy: Thanks very much for the info and for the function. One small comment: You said below (20190525) "BUT DON'T COPY IT (it contains non-breaking spaces): .."
That warning would be valid if the non-breaking spaces were within quotes as a necessary part of the code, but if they only occur in the indentations then copying and pasting the code into a module work fine.

Peter,
I must admit that in your previous version that line was indeed executed because you had a line "On Error Resume Next" (I didn't pay attention to it until now).
The code line: "ColRef = Range(ref & 1).Column", generates a vba error when >XFD, causing CoLRef to turn to zero. Then the macro resumes with the following line where you trapped the 0.

Your last macro though has still some weaknesses.
Row 8 in your picture is not correct.
The macro works only with upper case letters. With lower case it generates always an error.
The line "If ColRef = 0 Then ColRef = "<=XFD" 'CVErr(2023)" will never be executed because the error is now trapped by "ElseIf Application.IsText(ref) And Left(ref, 3) > MAXAddr Then".

In fact in my initial version (23th May) there is only one situation where the user could be confused and that's if the argument is > 16384. But I thought it is so obvious that it was not worth it to do something about that. And if that bothers you it can easily be fixed by replacing Cells(ref) by Cells(1,ref). The other wrong arguments (zero and > XFD) cause vba errors that excel displays as #VALUE!. So I stick to that version because it's simple.

BTW in your versions you mention: "Author: Willy Vanhaelen" but that is not entirely correct because the changes made are not mine.

Willy

However you placed "If ColRef = 0 Then ColRef = CVErr(2023)" in the "Else section" but there it will never be executed because the macro will only get there if ref contains column letter(s).

Yes, that is why I put it in, if someone entered say 'XFE1' then the COLREF = 0. I still think that your original is better with that modification.

Function ColRef(Optional ref As Variant) As Variant
'Author: Willy Vanhealen
'Date: 23 May 2019
Const MAXCOL As Integer = 16384
Const MAXAddr As String = "XDF"
Application.Volatile

If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) And ref > MAXCOL Then
ColRef = "<= 16384" 'CVErr(xlErrRef)
ElseIf Application.IsText(ref) And Left(ref, 3) > MAXAddr Then
ColRef = "<= XFD"
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
If ColRef = 0 Then ColRef = "<=XFD" 'CVErr(2023)
End If
End Function

(see Figure 1 below)

Here is the macro with indentation because it is easier to understand. BUT DON'T COPY IT (it contains non-breaking spaces):

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
   ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
   If ref = 0 Then
      ColRef = "> zero"
   ElseIf ref > 16384 Then
      ColRef = "< 16385"
   Else
      ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
   End If
Else
   ColRef = Range(ref & 1).Column
End If
End Function

Peter,
I though about that but didn't include it for clarity. But you are right, users with less experience could be misled.

However you placed "If ColRef = 0 Then ColRef = CVErr(2023)" in the "Else section" but there it will never be executed because the macro will only get there if ref contains column letter(s).

Here is my version. Instead of using xlErrRef, I give the user a meaningfull message so he knows exactly what he did wrong:

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
If ref = 0 Then
ColRef = "> zero"
ElseIf ref > 16384 Then
ColRef = "< 16385"
Else
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
End If
Else
ColRef = Range(ref & 1).Column
End If
End Function

Willy

I've added a couple of lines for error (colummns > 16384)

Function ColRef(Optional ref As Variant) As Variant
'Author: Willy Vanhealen
'Date: 23 May 2019
Const MAXCOL As Integer = 16384
On Error Resume Next

If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) And ref > MAXCOL Then
ColRef = CVErr(xlErrRef)
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
If ColRef = 0 Then ColRef = CVErr(2023)
End If
End Function

Willy
I thought that you might improve the macros. Excellent a usual!

Here is a UDF that does it all:

Function ColRef(Optional ref As Variant) As Variant
If IsMissing(ref) Then
ColRef = Mid(Selection.Address, 2, InStr(2, Selection.Address, "$") - 2)
ElseIf IsNumeric(ref) Then
ColRef = Mid(Cells(ref).Address, 2, Len(Cells(ref).Address) - 3)
Else
ColRef = Range(ref & 1).Column
End If
End Function

Syntax:
=ColRef() returns the column letter(s) of the cell the formula is entered in.
=ColRef(column number) returns the column letter(s) corresponding with the number (i.e. 27 -> AA).
=ColRef("column letter(s)") returns the number of the column letter(s) with quotes (i.e. "AB" -> 28).
=ColRef(cell reference) returns whatever is entered in that cell (column letter(s) without quotes or column number).

@Peter Atherton
Here is an even slightly shorter version of your macro:

Function ColName(ByVal n) As String
ColName = Mid(Cells(n).Address, 2, Len(Cells(n).Address) - 3)
End Function

I use the Mid function so I don't need the Address parameters (False, False).
I replaced Cells(1, n) by Cells(n). The row argument is not required here because cells(n) points to the nth cell in the first row until 16384. 16385 will return A, the first column of row 2.

The ColName UDF with no tmp
Function ColName(ByVal n) As String
ColName = Left(Cells(1, n).Address(False, False), _
Len(Cells(1, n).Address(False, False)) - 1)
End Function

Error on the ColNumber UDF

2nd Line should read char = Right(ref, 1)

Here is the way I did it, you enter a number for the column

Function ColName(ByVal n) As String
Dim tmp As String
tmp = Cells(1, n).Address(False, False)
ColName = Left(tmp, Len(tmp) - 1)
End Function

=COLNAME(45) returns AS

Its corollary returns the column number

Function ColNumber(ByVal ref) As Variant
Dim char As String
char = Left(ref, 1)
On Error Resume Next
If Not IsNumeric(char + 0) Then
ref = ref & "1"
End If
ColNumber = Range(ref).Column
If ColNumber = 0 Then
MsgBox "Last column name is XDF" & vbLf _
& "Last column number 16384"
ColNumber = ""
End If
End Function

syntax; =COLNUMBER(cell reference)
=COLNUMBER(L1) returns 27 when l1 contains AA

In the above macro, I don't see the necessety to declare the Temp variable and then use it only once. This macro is shorter and works the same:

Function AlphaCol(c As Range) As String
Dim ad1 As String
ad1 = c.Address
AlphaCol = Mid(ad1, InStr(ad1, "$") + 1, InStr(2, ad1, "$") - 2)
End Function

Because InStr(ad1, "$") + 1 always returns 2, you can even reduce it to a one-liner that does the job equally well:

Function AlphaCol(c As Range) As String
AlphaCol = Mid(c.Address, 2, InStr(2, c.Address, "$") - 2)
End Function

Thank you for the insightful tip, Allen!