Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,624
Default 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   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"