Ah! I focussed on the conditional compilation, and missed the fact that there was an entire function nested in there!Originally Posted by Ken Puls
Very clever
Ah! I focussed on the conditional compilation, and missed the fact that there was an entire function nested in there!Originally Posted by Ken Puls
Very clever
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
No it means that it is not ignored in 97.Originally Posted by Ken Puls
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.
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!Originally Posted by firefytr
Callbacks don't work in 97, but it is possible to write code to emulate them, just as that code emulates Split.
I totally mis-read your reply. What prompted my answer was that I *saw* this:Originally Posted by xld
[vba]
#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[/vba]
... 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. 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.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
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.Originally Posted by xld
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.Originally Posted by xld
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
I realised that when I saw your follow-up. I think we were composing them at the same time.Originally Posted by Ken Puls
Tell me about it!Originally Posted by Ken Puls
I have 97 versions of InstrRev and Join as well.Originally Posted by Ken Puls
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.Originally Posted by firefytr
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.Originally Posted by firefytr
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.
Well of course they all invoke calling other functions! Why would one need a UDF if they did not!Originally Posted by xld
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
('97 compliant)
[vba]Function ColumnLetter(Col As Long) As String
ColumnLetter = Left(Columns(Col).Address(0, 0), Int(Len(Columns(Col).Address(0, 0)) / 2))
End Function[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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.Originally Posted by firefytr
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?
I repeat, it is a function, not a UDF. Other than that, I don't understand that sentence at all.Originally Posted by firefytr
Last edited by Bob Phillips; 01-20-2007 at 08:32 AM.
While not intended as a question, there have been so many solutions, I've moved this to the Excel forum.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I think this will work as well.
Return Column Letter.
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Return Cell Address
=ADDRESS(ROW(),COLUMN())
SHAZAM!
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.Originally Posted by xld
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.Originally Posted by xld
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.Originally Posted by xld
Not a UDF? How do you figure that Bob? You lost me there..Originally Posted by xld
I also had some interesting time trial tests using XL 2003. I also threw in Johnske's because, well, I like his function too.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Honestly, Zack, sometimes I think you argue for the heck of it... But if you want to argue...
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.
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
That makes 10 I've got in my 'collection' nowOriginally Posted by Shazam
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
NEVER?
So, if you are coding to 97, and there is a need to get an array from a string, what would you do?
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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?
Ken Puls, CMA - Microsoft MVP (Excel)
I hate it when my computer does what I tell it to, and not what I want it to.
Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar
This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!
Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables