VBA get ColumnName
 

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.

VBA get ColumnName
 

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

VBA get ColumnName
 

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


"JE McGimpsey" wrote:

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