VBA get ColumnName

[michiel.pleijte]

It's frustrating to get columnname from a label in Excel. So I strolled
through some user forums, looked at some code (read: copy & paste ;-)
and came up with this:

Function SplitString(s As String, del As String) As Variant
Dim a() As Variant
Dim i As Integer
Dim y As Integer
Dim sTemp As String
sTemp = s
i = 0
y = 0
Do
ReDim Preserve a(i)
y = InStr(sTemp, del)
a(i) = Left$(sTemp, y - 1)
i = i + 1
sTemp = Mid$(sTemp, y + 1)
Loop While InStr(y, sTemp, del) > 0
ReDim Preserve a(i)
a(i) = sTemp
SplitString = a()
End Function

Function GetColumnNameFromLabel(label As String) As String
Dim i As Integer
Dim aArray As Variant
Dim CellAddress As String
Dim ColName As String

CellAddress = Range("ALLOWED_LENGTH").Address
aArray = SplitString(CellAddress, "$")
For i = 0 To UBound(aArray)
If i = 1 Then ColName = aArray(i)
Next
GetColumnNameFromLabel = aArray(1)
End Function

As an example for using this:
Dim ColName As String
ColName = GetColumnNameFromLabel("ALLOWED_LENGTH")
MsgBox ColName

Hope someone finds this as useful as I do.

 

[JE McGimpsey]

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

 

[Guest]

I seem to forget that a lot. I've never used enough columns to make it to AA.