PDA

View Full Version : Get Column Letter



mdmackillop
01-12-2007, 02:45 PM
File attached

Ken Puls
01-12-2007, 05:13 PM
I actually had a use for this a couple of days ago, Malcolm. I was actually trying to get the column letters in the worksheets and ended up using the CODE and CHAR functions to work out where I needed to be.

i.e. =CHAR(COLUMN()+64)

The above only works up to Column Z, of course, then you have to fool with it again. I like your method better. :)

Brandtrock
01-13-2007, 01:03 AM
:banghead: How simple you make that look!! I went the long way around to get the integers. :banghead::banghead::banghead:

I was answering a question for someone on another forum the other day. The OP was going in all sorts of directions, but the long and short of the situation was that they were retrieving data and pasting it into a worksheet range that may or may not be the same every time.

I farted around with a couple of functions that the OP seemed to be elated with. In any case, here is the code and an example workbook to play with for anyone interested.



Option Explicit
Sub Test()
Dim Reply As String
Dim Temp As Range
Dim TheCell As Range
Dim FinalAnswer As String

Randomize
Set TheCell = Cells(4, Range("MyRandom"))
Set Temp = Range(TheCell, TheCell.Offset(0, Range("MyRandom").Value))
Temp.Select
Reply = Temp.Address
FinalAnswer = ParseAddy(Reply, False)
FinalAnswer = ParseAddy(Reply, True)
Calculate

End Sub

Public Function AlphaDec(AlDec As String) As Double
' Designed to work for base 26 alphabet numbering system like Excel Column names

Dim i As Long
Dim j As Variant
Dim k As Long
Dim n As Long
Dim AlphaDecArray() As Double

n = Len(AlDec)
k = -1
ReDim AlphaDecArray(1 To n)
For i = n To 1 Step -1
j = Mid(AlDec, i, 1)
k = k + 1

Select Case j
Case Is = "A"
AlphaDecArray(i) = 1 * 26 ^ (k)
Case Is = "B"
AlphaDecArray(i) = 2 * 26 ^ (k)
Case Is = "C"
AlphaDecArray(i) = 3 * 26 ^ (k)
Case Is = "D"
AlphaDecArray(i) = 4 * 26 ^ (k)
Case Is = "E"
AlphaDecArray(i) = 5 * 26 ^ (k)
Case Is = "F"
AlphaDecArray(i) = 6 * 26 ^ (k)
Case Is = "G"
AlphaDecArray(i) = 7 * 26 ^ (k)
Case Is = "H"
AlphaDecArray(i) = 8 * 26 ^ (k)
Case Is = "I"
AlphaDecArray(i) = 9 * 26 ^ (k)
Case Is = "J"
AlphaDecArray(i) = 10 * 26 ^ (k)
Case Is = "K"
AlphaDecArray(i) = 11 * 26 ^ (k)
Case Is = "L"
AlphaDecArray(i) = 12 * 26 ^ (k)
Case Is = "M"
AlphaDecArray(i) = 13 * 26 ^ (k)
Case Is = "N"
AlphaDecArray(i) = 14 * 26 ^ (k)
Case Is = "O"
AlphaDecArray(i) = 15 * 26 ^ (k)
Case Is = "P"
AlphaDecArray(i) = 16 * 26 ^ (k)
Case Is = "Q"
AlphaDecArray(i) = 17 * 26 ^ (k)
Case Is = "R"
AlphaDecArray(i) = 18 * 26 ^ (k)
Case Is = "S"
AlphaDecArray(i) = 19 * 26 ^ (k)
Case Is = "T"
AlphaDecArray(i) = 20 * 26 ^ (k)
Case Is = "U"
AlphaDecArray(i) = 21 * 26 ^ (k)
Case Is = "V"
AlphaDecArray(i) = 22 * 26 ^ (k)
Case Is = "W"
AlphaDecArray(i) = 23 * 26 ^ (k)
Case Is = "X"
AlphaDecArray(i) = 24 * 26 ^ (k)
Case Is = "Y"
AlphaDecArray(i) = 25 * 26 ^ (k)
Case Is = "Z"
AlphaDecArray(i) = 26 * 26 ^ (k)
End Select
Next i
AlphaDec = Application.WorksheetFunction.Sum(AlphaDecArray)

End Function

Public Function ParseAddy(MyAddy As String, LeftOrRight As Boolean) As Integer
' If Left Argument is true, returns MyLefty,
' If Left Argument is false, returns MyRighty

Dim MyLefty As String
Dim MyRighty As String
Dim NewLefty As Integer
Dim NewRighty As Integer
Dim TheColon As Integer

MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "$", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "1", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "2", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "3", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "4", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "5", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "6", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "7", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "8", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "9", "")
MyAddy = Application.WorksheetFunction.Substitute(MyAddy, "0", "")

TheColon = Application.WorksheetFunction.Find(":", MyAddy)
' MsgBox ("The Colon is in position #" & TheColon)

MyLefty = Left(MyAddy, TheColon - 1)
MyRighty = Right(MyAddy, Len(MyAddy) - TheColon)

Select Case LeftOrRight
Case Is = True Or 1
NewLefty = AlphaDec(MyLefty)
ParseAddy = NewLefty
Case Is = False Or 0
NewRighty = AlphaDec(MyRighty)
ParseAddy = NewRighty
End Select
MsgBox (NewLefty & " " & NewRighty)

End Function

The Test It button simply gets a random range for me to test the functions with. I left it in for the OP to be able to see if it was going to fit the purpose he needed.

Regards,

Bob Phillips
01-13-2007, 02:50 AM
I actually had a use for this a couple of days ago, Malcolm. I was actually trying to get the column letters in the worksheets and ended up using the CODE and CHAR functions to work out where I needed to be.

i.e. =CHAR(COLUMN()+64)

The above only works up to Column Z, of course, then you have to fool with it again. I like your method better. :)

How about this

=LEFT(ADDRESS(ROW(),COLUMN(),2),FIND("$",ADDRESS(ROW(),COLUMN(),2),1)-1)

Bob Phillips
01-13-2007, 02:52 AM
This is what I use in VBA



'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

mdmackillop
01-13-2007, 03:32 AM
This is actually Cyberdudes code from a potential KB item, posted here for him to access.
Regards
MD

XLGibbs
01-13-2007, 11:51 AM
I use this in Access to convert field names that represent cell addresses for transferring carefull crafted queries into reporting templates. http://vbaexpress.com/kb/getarticle.php?kb_id=838

Public Function GetRowColumn(strRowColumn) As RowColumn
Dim lngCount As Long
Dim lngChar As String
'function to get cell references out of column names where the column names
' are equivalent to A1 style cell references (Note:R1C1 references won't work with this)
For lngCount = 1 To Len(strRowColumn)
lngChar = Asc(Mid(strRowColumn, lngCount, 1))

If lngChar >= Asc("0") And lngChar <= Asc("9") Then
Exit For
End If
Next lngCount
'this extracts the column and row from the Field Name in the recordset.

GetRowColumn.col = Left(strRowColumn, lngCount - 1)
GetRowColumn.row = Right(strRowColumn, Len(strRowColumn) - lngCount + 1)

End Function
Option Explicit
Public Type RowColumn 'function requires a user defined type
row As String
col As String
End Type

But this is for parsing a would be Access column Name (which represent it's intended location in Excel) from Access to Excel.

Tommy
01-13-2007, 12:54 PM
This is what I use when I need a column letter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
GiveColLeter Target.Column
End Sub
Public Function GiveColLeter(iNumber As Long) As String
Dim A As String, B As String
If iNumber \ 26 > 0 Then A = Chr(64 + iNumber \ 26)
B = Chr(64 + ((iNumber / 26) - (iNumber \ 26)) * 26)
GiveColLeter = A & B
End Function


a few more pennies on the stack :devil2:

Ken Puls
01-14-2007, 11:08 AM
This is fantastic! What a great variety of ways!

I have to say that I find it pretty funny on those occasions that I write 50 lines of painstaking code, only to have Bob come along and give a 50 character formula to do the same thing. :)

Brandtrock
01-14-2007, 07:04 PM
This is fantastic! What a great variety of ways!

I have to say that I find it pretty funny on those occasions that I write 50 lines of painstaking code, only to have Bob come along and give a 50 character formula to do the same thing. :)

In the immortal words of Buzz Lightyear, "You're mocking me, aren't you?!"

:rofl:

Ken, I know what you mean.

johnske
01-16-2007, 03:47 PM
This is fantastic! What a great variety of ways!

I have to say that I find it pretty funny on those occasions that I write 50 lines of painstaking code, only to have Bob come along and give a 50 character formula to do the same thing. :):rofl: I've already added it to my list (http://xlvba.3.forumer.com/index.php?act=ST&f=6&t=127&st=0#entry143)(got 7 short ones now)

Zack Barresse
01-17-2007, 04:06 PM
I use something similar to Bob's, although it works with 97 as well...

Function ColLet(lngCol As Long) As String
'---------------------------------------------------------------------------------------
' Procedure : ColLet
' DateTime : 6/2006
' Author : Zack Barresse
' Purpose : Return a column letter from column index value. 97 Compliant
'---------------------------------------------------------------------------------------
Dim strAddy As String
ColLet = "ERROR!"
On Error GoTo 0
strAddy = Cells(1, lngCol).Address(False, False)
If Err <> 0 Or strAddy = "" Then Exit Function
ColLet = Left(strAddy, Len(strAddy) - 1)
End Function

Of course, the downside, if there is not an activesheet, it fails.

Bob Phillips
01-18-2007, 06:13 AM
I use something similar to Bob's, although it works with 97 as well...

So does mine!


Of course, the downside, if there is not an activesheet, it fails.

Downside of using built-ins.

johnske
01-18-2007, 07:58 AM
8...

Sub ColumnLetter3()
Dim Addy As String
Addy = ActiveCell.EntireColumn.Address(0, 0)
MsgBox Left(Addy, Int(Len(Addy) / 2))
End Sub

Zack Barresse
01-19-2007, 02:11 PM
So does mine!
The Split() function works in 97??

Ken Puls
01-19-2007, 02:16 PM
The Split() function works in 97??

No....

Zack Barresse
01-19-2007, 03:10 PM
...

I'm trying to understand how Bob's function works in 97, as I do not have it installed to test it. Bob, Ken, anyone mind filling in the gaps for me?

Bob Phillips
01-19-2007, 03:55 PM
#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & """}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function
#End If

Ken Puls
01-19-2007, 04:03 PM
Bob,

Not to split hairs, but doesn't the conditional compilation mean that it is essentially ignored in 97? I'd hardly say that qualifies as "works".

Let me know if I'm missing something...

Zack Barresse
01-19-2007, 04:10 PM
I see now. Yeah, technically it will work in 97 through the use of a UDF to simulate the use of the (post 97) native Split() function. I should have said my function will work - natively - from 97 and up. ;)

Ken Puls
01-19-2007, 04:18 PM
Let me know if I'm missing something...

Ah! I focussed on the conditional compilation, and missed the fact that there was an entire function nested in there! :doh:

Very clever

Bob Phillips
01-19-2007, 04:19 PM
Bob,

Not to split hairs, but doesn't the conditional compilation mean that it is essentially ignored in 97? I'd hardly say that qualifies as "works".

Let me know if I'm missing something...

No it means that it is not ignored in 97.

And it works. The VBA routine that I provided runs in any version of Excel, just so long as you also have the conditional code.

Bob Phillips
01-19-2007, 04:22 PM
I see now. Yeah, technically it will work in 97 through the use of a UDF to simulate the use of the (post 97) native Split() function. I should have said my function will work - natively - from 97 and up. ;)

Now that really is splitting hairs. You add code beacuse some function is not built-in, and then complain because someone else adds more code because some function is not built-in to a particular version!

Callbacks don't work in 97, but it is possible to write code to emulate them, just as that code emulates Split.

Ken Puls
01-19-2007, 04:28 PM
No it means that it is not ignored in 97.

I totally mis-read your reply. What prompted my answer was that I *saw* this:


#If VBA6 Then
'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error Goto 0
ColumnLetter = sColumn
End Function
#Else
#End If

... which is, of course, not at all what you actually posted. One look at the conditional blew me totally off course here. And for some reason, once that got into my head, I just couldn't see through what you actually wrote. Weird the way the brain can do that to you. :think: At any rate, very much my bad. :)

Having re-read (and understood) your answer, I agree that it does work. It's not native, (and doesn't need to be,) but I quite like it. :yes

Zack Barresse
01-19-2007, 05:06 PM
Now that really is splitting hairs.
I don't think it is really splitting hairs, I was only trying to point out an issue which I feel is quite valid. I know there is a workaround, but if I can make a call with one function rather than two, I'd rather do that. Personal preference only. :)


You add code beacuse some function is not built-in, and then complain because someone else adds more code because some function is not built-in to a particular version!
I see where you're going, but I remain steadfast that I was not complaining, merely pointing out an issue. You never mentioned that you needed to substitute a UDF for the later-native Split() function. So, as I assumed that others may assume (double-assumption??), I only mentioned the fact that my function would work (I should have added the keyword, "alone") in 97 and up.

Bob Phillips
01-19-2007, 05:08 PM
I totally mis-read your reply.

I realised that when I saw your follow-up. I think we were composing them at the same time.


Weird the way the brain can do that to you. :think:

Tell me about it!



Having re-read (and understood) your answer, I agree that it does work. It's not native, (and doesn't need to be,) but I quite like it. :yes

I have 97 versions of InstrRev and Join as well.

Bob Phillips
01-19-2007, 05:14 PM
I don't think it is really splitting hairs, I was only trying to point out an issue which I feel is quite valid. I know there is a workaround, but if I can make a call with one function rather than two, I'd rather do that. Personal preference only. :)
Split is a function call whether it is a built-in function or a hand-carfted function, it is still a function call. And the hand-crafted only comes into play in 97.



I see where you're going, but I remain steadfast that I was not complaining, merely pointing out an issue. You never mentioned that you needed to substitute a UDF for the later-native Split() function. So, as I assumed that others may assume (double-assumption??), I only mentioned the fact that my function would work (I should have added the keyword, "alone") in 97 and up.
Nobody mentioned 97 when I posted it, so it is not surprising that I didn't mention it needed a custom function (not a UDF). Neither works alone, they need to invoke a Split function, which ... see above.

Zack Barresse
01-19-2007, 05:31 PM
You're right, Split() is a function call. But it is not in every OM, whereas I believe Left() is. The point being, less work, more portability, less maintenance and less troubleshooting, all with the same results. It's splendid that one can create a UDF to emulate the native Split() function (and I happen to love the native function, great add) although I see no monetary gain, especially here. IMO, the function I have posted is superior due to these facts.


Neither works alone, they need to invoke a Split function...
Well of course they all invoke calling other functions! Why would one need a UDF if they did not! :D

johnske
01-19-2007, 06:12 PM
('97 compliant)
Function ColumnLetter(Col As Long) As String
ColumnLetter = Left(Columns(Col).Address(0, 0), Int(Len(Columns(Col).Address(0, 0)) / 2))
End Function

Bob Phillips
01-20-2007, 05:05 AM
You're right, Split() is a function call. But it is not in every OM, whereas I believe Left() is. The point being, less work, more portability, less maintenance and less troubleshooting, all with the same results. It's splendid that one can create a UDF to emulate the native Split() function (and I happen to love the native function, great add) although I see no monetary gain, especially here. IMO, the function I have posted is superior due to these facts.
Split is in my version of 97 just as much as it is not in yours. Would it be more acceptable to you if I stuffed it in a DLL and you didn't see my FUNCTION? After all, this is exactly what MS have done, their Split is buried somewhere in the code that Excel links to.

It is totally portable, because the function is in the workbook that uses it. I stick it in a separate 97 module, it has been debugged, I have been using it for many years and haven't touched it since it was written. It is portable, no maintenance, no troubleshooting.

What has monetary gain got to do with it?


Well of course they all invoke calling other functions! Why would one need a UDF if they did not! :D
I repeat, it is a function, not a UDF. Other than that, I don't understand that sentence at all.

mdmackillop
01-20-2007, 07:41 AM
While not intended as a question, there have been so many solutions, I've moved this to the Excel forum.

Shazam
01-20-2007, 09:09 AM
I think this will work as well.

Return Column Letter.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")


Return Cell Address

=ADDRESS(ROW(),COLUMN())

Zack Barresse
01-20-2007, 10:23 AM
Split is in my version of 97 just as much as it is not in yours. Would it be more acceptable to you if I stuffed it in a DLL and you didn't see my FUNCTION? After all, this is exactly what MS have done, their Split is buried somewhere in the code that Excel links to.
No, if I were using 97 and had a use for the Split() function I would be using that as well. But I'm not, so I won't. I don't try to compare to native functionality. Not really sure why you do either.


It is totally portable, because the function is in the workbook that uses it. I stick it in a separate 97 module, it has been debugged, I have been using it for many years and haven't touched it since it was written. It is portable, no maintenance, no troubleshooting.
I understand it is portable, and that is handy I'm sure. It's still more work than you need to do, and I'm not into making more work for myself.


What has monetary gain got to do with it?
Maybe that wasn't the best way to describe it. Maybe I should have said "advantage". I see no advantage to using a UDF where built-in functionality is available.


I repeat, it is a function, not a UDF. Other than that, I don't understand that sentence at all.
Not a UDF? How do you figure that Bob? You lost me there..


I also had some interesting time trial tests using XL 2003. I also threw in Johnske's because, well, I like his function too.

Ken Puls
01-20-2007, 08:27 PM
Honestly, Zack, sometimes I think you argue for the heck of it... But if you want to argue... :dunno

I like Bob's approach, simply for the fact that it is forward compatible with native functions in later versions that I would use. By using the conditional, we gain the efficiency of the native version in later Office installs.

:)

johnske
01-20-2007, 11:43 PM
I think this will work as well.

Return Column Letter.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
...That makes 10 I've got in my 'collection' now :rotlaugh: :thumb

Zack Barresse
01-21-2007, 10:01 AM
LOL! Okay, I've made the points I've wanted to make, so I'm done. I'm not arguing to argue, I don't like Bob's solution very much and I would never use it. Conditional or not. That is only my opinion. :)

Bob Phillips
01-21-2007, 10:13 AM
NEVER?

So, if you are coding to 97, and there is a need to get an array from a string, what would you do?

Zack Barresse
01-21-2007, 10:21 AM
No Bob, you misunderstand. If I had a need for the Split() function in 97, I would think about the conditionally compiled UDF, of course. For something such as this [thread], no, I would never use it.

Ken Puls
01-21-2007, 10:24 AM
Can we do a conditional compilation to test for Excel 2007?

Think Application.Filesearch. Theoretically, we could create something similar for this function to avoid re-writing the function in the initial upgrade of a file. You bemoan the loss of Filesearch, would you use it for that?

Zack Barresse
01-21-2007, 10:26 AM
No Ken, the VB version is still 6, you must only do a version check. Excel 97 was still based on VB5, hence the compilation check. And as sad as it is about FileSearch, I must abandon that as well. I prefer to use code that is compatible in many versions. I hate narrowing the margin.

Ken Puls
01-21-2007, 10:38 AM
No Ken, the VB version is still 6, you must only do a version check. Excel 97 was still based on VB5, hence the compilation check.
I realize that. The question really was "is there any way we can create a conditional for 2007". I didn't think testing on version would work, but then I haven't played with conditional compliation much.


And as sad as it is about FileSearch, I must abandon that as well. I prefer to use code that is compatible in many versions. I hate narrowing the margin.
I'll tell you... if I had to convert a huge number of files to work with 2007, I'd do it. Then go back and modify them later... if I felt I needed to. As Bob had said (or eluded to) before, I don't see why it's any less valid a solution though.

Zack Barresse
01-21-2007, 10:54 AM
No, I'm not saying it is not a valid solution, just not one that I would ever use in this specific instance. As for creating a [compilation] conditional for 2007, like I said, we cannot check the VB version, so we wouldn't be able to check it like that. The [basically] only hook we have is the version number. Especially since MSFT decided to not update VB anymore (as of yet), or any other emulated OM.

Bob Phillips
01-21-2007, 12:20 PM
No Bob, you misunderstand. If I had a need for the Split() function in 97, I would think about the conditionally compiled UDF, of course. For something such as this [thread], no, I would never use it.

But it was YOU that raised 97 in the first place!

Bob Phillips
01-21-2007, 12:24 PM
No, I'm not saying it is not a valid solution, just not one that I would ever use in this specific instance.
No, you said that you would NEVER use it.


... Im not arguing to argue, I don't like Bob's solution very much and I would never use it. Conditional or not ...

Ken Puls
01-21-2007, 04:10 PM
... Im not arguing to argue...

Yes, you are. :p

(sorry, couldn't help myself)

Krishna Kumar
01-22-2007, 12:23 AM
MsgBox Split((ActiveCell.Address(1, 0)), "$")(0)

Zack Barresse
01-27-2007, 10:52 AM
No, you said that you would NEVER use it.
And I amended it to say I would never use it in this specific instance, to clear up any confusion. ;)


Yes, you are. :p
I'll take that as a compliment from you, Ken. :devil2:

forumMail
06-02-2008, 07:39 AM
This is what I use when I need a column letter:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
GiveColLeter Target.Column
End Sub
Public Function GiveColLeter(iNumber As Long) As String
Dim A As String, B As String
If iNumber \ 26 > 0 Then A = Chr(64 + iNumber \ 26)
B = Chr(64 + ((iNumber / 26) - (iNumber \ 26)) * 26)
GiveColLeter = A & B
End Function


a few more pennies on the stack :devil2:

Hi Tommy,

Tried to use your code and realized that it can't do column 26 (Z). Instead it returns A@.:banghead:
Modified your code:
Public Function GiveColLetter(iNumber As Long) As String
Dim A As String, B As String
If iNumber > 26 Then
A = Chr(64 + iNumber \ 26)
B = Chr(64 + (iNumber - 26))
Else
B = Chr(64 + iNumber)
End If
GiveColLetter = A & B
End Function

Regards.

Bob Phillips
06-02-2008, 07:53 AM
As posted earlier (the one that got us into this mess!). Does column 27 on nicely.



'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error Goto 0
ColumnLetter = sColumn
End Function