Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|