|
Frustrated with VBA so-called help
I ran across the "xldown" feature (don't know if it's a method,
property or a kind of orange) in another thread, and tried to look it up in John Walkenbach's book and in VBA help. John's book had one reference cited (that wasn't very helpful), VBA help doesn't recognize it at all. Does anyone have any suggestions on where someone can find information on objects, properties, methods, functions, etc? I guess my approach is different, but when I see these things, I want to know what it is (object, property, function, method, etc.) and that the arguments are, if it has them, and some samples of how to use them. I can find them in the Object Model, usually, but it has no useful (to me, at least) information. It just tells me it exists! I usually come in here and do a search, and always find examples. But just the code with no explanation of what it does isn't enough, I'd like some kind of description of what it does. Do I have to buy another book that actually lists these things with some useful description and sample code? Is there an online reference? Any ideas here would be greatly appreciated. It's very difficult learning when there are no definitions for the tools! |
Frustrated with VBA so-called help
The object browser is your friend.
Open the OB, type xlDown in the dropdown left of the binoculars, and then click the binoculars icon. It will show where xlDown is a member. -- HTH RP (remove nothere from the email address if mailing direct) "davegb" wrote in message oups.com... I ran across the "xldown" feature (don't know if it's a method, property or a kind of orange) in another thread, and tried to look it up in John Walkenbach's book and in VBA help. John's book had one reference cited (that wasn't very helpful), VBA help doesn't recognize it at all. Does anyone have any suggestions on where someone can find information on objects, properties, methods, functions, etc? I guess my approach is different, but when I see these things, I want to know what it is (object, property, function, method, etc.) and that the arguments are, if it has them, and some samples of how to use them. I can find them in the Object Model, usually, but it has no useful (to me, at least) information. It just tells me it exists! I usually come in here and do a search, and always find examples. But just the code with no explanation of what it does isn't enough, I'd like some kind of description of what it does. Do I have to buy another book that actually lists these things with some useful description and sample code? Is there an online reference? Any ideas here would be greatly appreciated. It's very difficult learning when there are no definitions for the tools! |
Frustrated with VBA so-called help
"With friends like these....."
Sorry, Bob, couldn't resist. Yes, I know that I can find out if something exists in VBA by using the Object Browser. By looking it up, I now know it's a sub of xlDirection, also not defined in VBA help. But I was already pretty certain it existed, or the code someone showed me wouldn't have run. What I need is someplace to go to find out what xlDown does, so that I might be able to use it myself in the future. I'm beginning to realize, from the lack of responses to previous queries, that no such reference exists. Is VBA so primitive that the only way to actually find out these things is by modern day word-of-mouth, like this NG? Frightening thought! It certainly gives new import and meaning to this, and other, newsgroups! They are the modern day equivalent of the "story tellers" from the days of pre-history. Otherwise, no one new to VBA programming would know "xlDown" existed, unless they perused the Object Browser for a hobby, and accidentally discovered it. Even then, they could only speculate what it is unless they could find someone who had used it before to explain. In any case, it makes me very grateful that this forum exists. I'll keep plugging and eventually, with a lot of help here, I'll figure out what xlDown, and about a thousand other things, do. Just would be nice if I could look them up. |
Frustrated with VBA so-called help
It is an Argument to the END method. So look for End in help and find it as
a method of Range As Bob advised, the object browser is very helpful, although in this case I would look for range, then find the End method, click on it and hit F1. -- Regards, Tom Ogilvy "davegb" wrote in message oups.com... I ran across the "xldown" feature (don't know if it's a method, property or a kind of orange) in another thread, and tried to look it up in John Walkenbach's book and in VBA help. John's book had one reference cited (that wasn't very helpful), VBA help doesn't recognize it at all. Does anyone have any suggestions on where someone can find information on objects, properties, methods, functions, etc? I guess my approach is different, but when I see these things, I want to know what it is (object, property, function, method, etc.) and that the arguments are, if it has them, and some samples of how to use them. I can find them in the Object Model, usually, but it has no useful (to me, at least) information. It just tells me it exists! I usually come in here and do a search, and always find examples. But just the code with no explanation of what it does isn't enough, I'd like some kind of description of what it does. Do I have to buy another book that actually lists these things with some useful description and sample code? Is there an online reference? Any ideas here would be greatly appreciated. It's very difficult learning when there are no definitions for the tools! |
Frustrated with VBA so-called help
"davegb" wrote in message ps.com... "With friends like these....." Sorry, Bob, couldn't resist. Yes, I know that I can find out if something exists in VBA by using the Object Browser. By looking it up, I now know it's a sub of xlDirection, also not defined in VBA help. But I was already pretty certain it existed, or the code someone showed me wouldn't have run. What I need is someplace to go to find out what xlDown does, so that I might be able to use it myself in the future. xlDown is just a constant which is used to tell the property. The constants will not be defined in help, that is anything starting with xl (for Excel), vb (for VB), or mso (for Office), so you need to look at the 'thing' that the constatnt is being used with, probably the End property in this case. Look up End in help and you will see xlDown defined in there. You need to use the old grey matter, and work some things out. I'm beginning to realize, from the lack of responses to previous queries, that no such reference exists. Is VBA so primitive that the only way to actually find out these things is by modern day word-of-mouth, like this NG? Frightening thought! It certainly gives new import and meaning to this, and other, newsgroups! They are the modern day equivalent of the "story tellers" from the days of pre-history. Get out of here. If that was the case, how would anything new ever happen, there would be no-one to tell the creative person how to do it. Do you honestly believe that the likes of Stephen Bullen or Harlan Grove got their knowledge of Excle, VBA and VB by reading from these NGs alone? No, they did the investigations, tried things out, read the published material (such as the OB and help), and used their brains. Otherwise, no one new to VBA programming would know "xlDown" existed, unless they perused the Object Browser for a hobby, and accidentally discovered it. Even then, they could only speculate what it is unless they could find someone who had used it before to explain. as before. In any case, it makes me very grateful that this forum exists. I'll keep plugging and eventually, with a lot of help here, I'll figure out what xlDown, and about a thousand other things, do. Just would be nice if I could look them up. Well at least you are happy :-). |
Frustrated with VBA so-called help
"Tushar Mehta" wrote in message om... xlDown is a mnemonic that represents some constant value. So, as such it can never be used by itself but will always be part of some method or property. Searching for 'xldown' (w/o the quotes) in XL VBA help for xl2003 shows a bunch of suggestions including 'End property' and 'Microsoft Excel Constants' Unfortunately, I didn't find it in XL2000 VBA help, which surprise me, I didn't think help would be significantly upgraded. |
Frustrated with VBA so-called help
Thanks, Marsha! At least I know that someone has attempted to address
this problem! Unfortunately, I'm stuck with XL2000, and VBA help doesn't even recognize xlDown. After reading your reply, I went into help and looked up Constants, which was mostly more confusing, but at least there, and End Property, which was a little brief but helpful. I just wish I could have found it without having to go online and ask and wait for a reply. I've been looking on Amazon for a book that might have more info on this kind of thing (for XL2000), but didn't find anything that looked promising. I just made an appointment with myself to head over to the Tattered Cover bookstore (huge bookstore here in Denver) this weekend and look at everything they have. Got to be something better than what I'm doing now. I can't remember ever in my 57 years having so much trouble finding good reference materials for something like a programming language! Seems to me, MS should have created help with every object, property, method, function, constant, etc defined at the very least. I guess they don't see any direct profit in making their product easier to use. If I find any good reference manuals, I'll list them here. Thanks again! |
Frustrated with VBA so-called help
Dave,
I found this page in MSDN which looked hopeful, http://msdn.microsoft.com/library/de...HV01049962.asp Even though it was for XL2003, it stated that '... This topic lists all constants in the Microsoft Excel object model ...' But, <lol< guess what. xlDown isn't directly listed here., as it lists the enumerations, so you need to know that xlDown is a member of xlDirection, to expand that. Don't you dare say ... told you so ... <vbg Bob "davegb" wrote in message ups.com... Thanks, Marsha! At least I know that someone has attempted to address this problem! Unfortunately, I'm stuck with XL2000, and VBA help doesn't even recognize xlDown. After reading your reply, I went into help and looked up Constants, which was mostly more confusing, but at least there, and End Property, which was a little brief but helpful. I just wish I could have found it without having to go online and ask and wait for a reply. I've been looking on Amazon for a book that might have more info on this kind of thing (for XL2000), but didn't find anything that looked promising. I just made an appointment with myself to head over to the Tattered Cover bookstore (huge bookstore here in Denver) this weekend and look at everything they have. Got to be something better than what I'm doing now. I can't remember ever in my 57 years having so much trouble finding good reference materials for something like a programming language! Seems to me, MS should have created help with every object, property, method, function, constant, etc defined at the very least. I guess they don't see any direct profit in making their product easier to use. If I find any good reference manuals, I'll list them here. Thanks again! |
Frustrated with VBA so-called help
Dunno who Marsha is {g} but for all my criticism of MS's commitment (or
lack thereof) to good help for Office products, one should keep in mind that the scope of the programming environment -- not just language -- is simply vast. As much as I would like to see everything documented, I also recognize the enormity of the task. [As an aside, if you think Office 2000 help is bad wait until you see 2003.] Of course, I might not consider the problem you face as severe because intrinsically I'm a lazy person and between the XL macro recorder and VBE's intellisense capability I care little about things like syntax and such. About the only time I want to know the value of a mnemonic is when I'm coding in an environment where the editor is unaware of XL (in another Office app w/o setting a reference to XL or in VBS which doesn't support the capability). In such cases, I create the code in XL, paste into the other editor and replace unknown mnemonics with constants. The easiest way to find the value of a mnemonic is the Immediate window in the parent application. For example, ?xlDown in the XL VBE's immediate window will give you the value associated with it. Then, in the other editor adding 'Const xlDown as Long ={whatever}' or 'Const xlDown ={whatever}' (as appropriate) means the rest of the code can remain untouched. As far as a book goes, I could be wrong but I suspect it will be a fruitless search. Hard to imagine any publisher willing to commit dozens (hundreds?) of pages to a seemingly endless list of constants. Especially since that list should grow with each subsequent version of the product. Personally, for things like this I find msdn.microsoft.com far more rewarding; though I have to admit that at times it too can be very frustrating. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article . com, says... Thanks, Marsha! At least I know that someone has attempted to address this problem! Unfortunately, I'm stuck with XL2000, and VBA help doesn't even recognize xlDown. After reading your reply, I went into help and looked up Constants, which was mostly more confusing, but at least there, and End Property, which was a little brief but helpful. I just wish I could have found it without having to go online and ask and wait for a reply. I've been looking on Amazon for a book that might have more info on this kind of thing (for XL2000), but didn't find anything that looked promising. I just made an appointment with myself to head over to the Tattered Cover bookstore (huge bookstore here in Denver) this weekend and look at everything they have. Got to be something better than what I'm doing now. I can't remember ever in my 57 years having so much trouble finding good reference materials for something like a programming language! Seems to me, MS should have created help with every object, property, method, function, constant, etc defined at the very least. I guess they don't see any direct profit in making their product easier to use. If I find any good reference manuals, I'll list them here. Thanks again! |
Frustrated with VBA so-called help
xlDown is just a constant which is used to tell the property. The
constants will not be defined in help, that is anything starting with xl (for Excel), vb (for VB), or mso (for Office), so you need to look at the 'thing' that the constatnt is being used with, probably the End property in this case. Look up End in help and you will see xlDown defined in there. You need to use the old grey matter, and work some things out. So by using my "gray matter", I could have figured out that xlDown is a constant, which is used to tell the property? I don't see this as simple inductive or deductive reasoning. You tell me that all I had to intuitively know was that it is a constant, "probably with the End property in this case". I disagree with you here, Bob. It's obvious to you because you been doing it a while, but certainly not intuitive or something one can "figure out" by using their "gray matter", at least not until they've worked with VBA for a while. Well at least you are happy :-). Please, Bob, let's not get sarcastic. It won't help. I respect your knowledge of VBA, and appreciate your help. But I am very frustrated that the built in help assumes I have fairly extensive knowledge of the inner workings of VBA. The help for VBA, at least in XL2000, is intended for those knowledgeable in VBA. I'm only complaining that some fairly obvious things could and should have been done by MS from the beginning, and that it's frustrating for a beginner to try and ferret them out. I don't think I'm being unreasonable. I've learned programming languages before, albeit a long time ago, and always started with a reference that at least had something on ALL the commands, parameters, etc. Obviously, MS themselves have recognized the problem as evidenced by the fact that the info I need is now in Help. I believe they could have made this process much easier for me and others by spending some additional time and effort on the Help file. |
Frustrated with VBA so-called help
Thanks, Tom!
I still find the Object Browser less than helpful. Until I know that xlDown is under the End Property (it is End Property, not Method, isn't it?), the browser isn't much help. In the Object Browser, it's under xlDirection, which is under XL. Which tells me nothing of any use. Knowing to look under End Property is the key! |
Frustrated with VBA so-called help
There is the old Excel VBA reference that microsoft published (last for xl97
I believe). It basically is just a printed version of the help files, but you can read it where you don't have a computer handy. (but you can't search the printed version). -- Regards, Tom Ogilvy "Tushar Mehta" wrote in message om... Dunno who Marsha is {g} but for all my criticism of MS's commitment (or lack thereof) to good help for Office products, one should keep in mind that the scope of the programming environment -- not just language -- is simply vast. As much as I would like to see everything documented, I also recognize the enormity of the task. [As an aside, if you think Office 2000 help is bad wait until you see 2003.] Of course, I might not consider the problem you face as severe because intrinsically I'm a lazy person and between the XL macro recorder and VBE's intellisense capability I care little about things like syntax and such. About the only time I want to know the value of a mnemonic is when I'm coding in an environment where the editor is unaware of XL (in another Office app w/o setting a reference to XL or in VBS which doesn't support the capability). In such cases, I create the code in XL, paste into the other editor and replace unknown mnemonics with constants. The easiest way to find the value of a mnemonic is the Immediate window in the parent application. For example, ?xlDown in the XL VBE's immediate window will give you the value associated with it. Then, in the other editor adding 'Const xlDown as Long ={whatever}' or 'Const xlDown ={whatever}' (as appropriate) means the rest of the code can remain untouched. As far as a book goes, I could be wrong but I suspect it will be a fruitless search. Hard to imagine any publisher willing to commit dozens (hundreds?) of pages to a seemingly endless list of constants. Especially since that list should grow with each subsequent version of the product. Personally, for things like this I find msdn.microsoft.com far more rewarding; though I have to admit that at times it too can be very frustrating. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article . com, says... Thanks, Marsha! At least I know that someone has attempted to address this problem! Unfortunately, I'm stuck with XL2000, and VBA help doesn't even recognize xlDown. After reading your reply, I went into help and looked up Constants, which was mostly more confusing, but at least there, and End Property, which was a little brief but helpful. I just wish I could have found it without having to go online and ask and wait for a reply. I've been looking on Amazon for a book that might have more info on this kind of thing (for XL2000), but didn't find anything that looked promising. I just made an appointment with myself to head over to the Tattered Cover bookstore (huge bookstore here in Denver) this weekend and look at everything they have. Got to be something better than what I'm doing now. I can't remember ever in my 57 years having so much trouble finding good reference materials for something like a programming language! Seems to me, MS should have created help with every object, property, method, function, constant, etc defined at the very least. I guess they don't see any direct profit in making their product easier to use. If I find any good reference manuals, I'll list them here. Thanks again! |
Frustrated with VBA so-called help
Property or method, is pretty much academic to me. But the object browser
says it is a property. So since you've seen it used and you know it is a property, then assume you had an example such as: set rng = Range("A1").End(xldown) Even light experience with excel VBA help should tell you that constants are not defined under their own name, but one must go to the method or property with which they are associated. so put that in the VBE and highlighting End(xldown) in the vbe, then hitting F1 puts me at the help screen. (it also says END Property <g) Just sharing techniques that work for me. -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... Thanks, Tom! I still find the Object Browser less than helpful. Until I know that xlDown is under the End Property (it is End Property, not Method, isn't it?), the browser isn't much help. In the Object Browser, it's under xlDirection, which is under XL. Which tells me nothing of any use. Knowing to look under End Property is the key! |
Frustrated with VBA so-called help
"davegb" wrote in message oups.com... Well at least you are happy :-). Please, Bob, let's not get sarcastic. There was no sarcasm in that statement. Your preceding paragraph had been lauding the help you get in the NGs, so I ended (trying to) reflect that, after basically suggesting that learning VBA required work on your part as well, and I added a smiley to show the intended friendliness of it. Read my other response after Tushar's contribution, you will see more of my (supposed) humour. It may not be funny, but it ain't sarcasm. |
Frustrated with VBA so-called help
"Tom Ogilvy" wrote in message ... so put that in the VBE and highlighting End(xldown) in the vbe, then hitting F1 puts me at the help screen. (it also says END Property <g) Well I'll be blowed (again). I nearly always try it from the immediate window, and typing End(xldown) in there, highlighting it all, and then F1, gets 'Keyword not found'. In fact, even more spookily, if I highlight it from back to front, hit F1, it gives me the help on End statement. What is going on? That's not really a question, more sharing Dave's frustration with MS. |
Frustrated with VBA so-called help
Wow! Did I stir up a hornet's nest or what! Someday maybe I'll stop
doing that. Or not. Thanks for all the replies. I'll respond to the more salient replies. Bob P wrote There was no sarcasm in that statement. Your preceding paragraph had been lauding the help you get in the NGs, so I ended (trying to) reflect that, after basically suggesting that learning VBA required work on your part as well, and I added a smiley to show the intended friendliness of it. Sorry if I heard sarcasm where none was intended. I was a bit nettled at your repeated remarks that all it took was "using your gray matter". I'm not dumb, and I've been working pretty hard at this, spending a lot of time in this NG reading, copying code, asking questions. Been reading Walkenbach and have made several trips to the nearby Barnes & Noble to see what refrence materials they have. None of them have any direct reference to xlDown! Or a lot of other "things" in VBA. I've even had dreams about VBA this week! It's been frustrating. And then to be told I'm not using my brain.... And I wrote my reply before I saw your other reply with the <vbg in it. I apologize for the misunderstanding. Tom O wrote: Even light experience with excel VBA help should tell you that constants are not defined under their own name, but one must go to the method or property with which they are associated Well, light experience is a few months away, so I'm glad you told me this! Once I learn which constants are associated with with methods or properties, it will get easier I'm sure. That's what I thought the Object Browser should do. Foolish me! Tushar: I agree with your comments about the size of the undertaking of describing everything in VBA. In my opinion, that's what MS is supposed to do. Every thing I've read about writing good code says to document it. Doesn't that apply when creating a code creating device like VBA? As for your comments on how you write code, you must be extremely bright. I have no idea what you meant by it. I don't think that I can write code that way. Wish it were otherwise. I suspect you have previous experience with OOP. I have used all the tools that you and others have mentioned here, watches, breaks, immediate window, with mixed results. Certainly entering ?xlDown and getting a result of "-4121" was of no help. In other cases, it's helped a lot. I appreciate everyone's comments and efforts. Probably I'm just spoiled, but in most instances, by this time, I'd have a better understanding of what I'm doing in most of the things I've tackled in my life, including graduating from one of the most difficult engineering schools in the world (ok, except for Chemical Thermodynamics class!). But in most of those efforts, I've found better reference material that I have for VBA. I find that frustrating. Again, I'm grateful I can come here and get useful information to fill in these major gaps. I've been a major contributor to other forums, and hope someday to be more of a helper than helpee here as well. |
Frustrated with VBA so-called help
"davegb" wrote in message
oups.com... Sorry if I heard sarcasm where none was intended. I was a bit nettled at your repeated remarks that all it took was "using your gray matter". I'm not dumb, and I've been working pretty hard at this, spending a lot of time in this NG reading, copying code, asking questions. I agree, but I hope I wasn't sayimg that all it took., I was suggesting that you should, in the nasty common vernacular here at the moment, take a bit more responsibility, and step back and try to see the wood for the trees,. But I did try not to be entirely negative, so that was why I tried to lighten it. Been reading Walkenbach and have made several trips to the nearby Barnes & Noble to see what refrence materials they have. None of them have any direct reference to xlDown! Or a lot of other "things" in VBA. I've even had dreams about VBA this week! It's been frustrating. And then to be told I'm not using my brain.... I am not claiming to be any brighter than you, but I have only evere read one Excel book (John Green, Stephen Bullen and Rob Bovey's Excel V2000 BA Programmers Reference), and that was to help move to another level, not to start learning, so if I can acquire a good level of VBA skills, I am sure that you can also.Be positive, work with what you have got (sorry for the pseudo-psycho-babble). And I wrote my reply before I saw your other reply with the <vbg in it. I apologize for the misunderstanding. Glad we gave a better understanding. Just one last homily. MS is not some paternalistic benefactor, but it wants us to succeed, because the more of us that succeed, that means the more products they must be selling. And Tushar's words are very appropriate. |
Frustrated with VBA so-called help
I did as I promised and went to the Tattered Cover bookstore this
weekend, spent 30 min or so looking at every book they had on XL VBA, from skinny "Dummies" books to huge tomes I could barely lift (I'm not getting any younger). I even looked through the ones on newer versions of XL than 2000, which I am working with here. Not a single book listed in the index "xlDown". Or xlUp, or xlDirection. I'm forced to conclude that the documentation on VBA is limited, to put it kindly. I still don't know what xlDown does. will have to try to intuit it from examples of code gleaned here. I have to admit, this is difficult for me. I'm much better at understanding a comcept, then seeing how it is applied, than the other way around. Just the way my limited mind works. I guess I need the mental exercise! Thanks for all the help. I'm still plugging. |
Frustrated with VBA so-called help
I still don't know what xlDown does.
It doesn't do anything! It's just the representation of a number, ie a constant, that's all. You could replace it with it's value -4121. In a new Sub type the following (don't cut/paste) Dim rng2 As Range set rng2 = activecell.End( When you get to the "(" you should see the choices available to you, one of which is xlDown, and instructs "End" which direction to go. Whenever you see xlEtc it's a constant, I think always a "Long" (what's a Long ??) Re your comments about VBA Help. It's best to treat this as a reference manual with some examples. But it's not intended as a tutorial, yes some errors and limitations. Having said that my own view is it is excellent. Imagine sitting down and writing it from scratch, even with a team of 100. In the above example select "End" and press F1. What more could you ask! Regards, Peter T "davegb" wrote in message ups.com... I did as I promised and went to the Tattered Cover bookstore this weekend, spent 30 min or so looking at every book they had on XL VBA, from skinny "Dummies" books to huge tomes I could barely lift (I'm not getting any younger). I even looked through the ones on newer versions of XL than 2000, which I am working with here. Not a single book listed in the index "xlDown". Or xlUp, or xlDirection. I'm forced to conclude that the documentation on VBA is limited, to put it kindly. I still don't know what xlDown does. will have to try to intuit it from examples of code gleaned here. I have to admit, this is difficult for me. I'm much better at understanding a comcept, then seeing how it is applied, than the other way around. Just the way my limited mind works. I guess I need the mental exercise! Thanks for all the help. I'm still plugging. |
Frustrated with VBA so-called help
Thanks for all the excellent explanations. I did your "experiments"
Peter and Tom, and it helped. As far as your question, "What more could you ask?", I could, and have, asked for an explanation. And now I have one. What confused me, and still does, is why a constant is used to tell XL to go to the end of a range. When I want to go to the end of the range from the keyboard, as in your instruction, Tom, I don't type in "-4121". I do an End arrow (Actually, I've always done a Ctrl arrow, same thing). Is -4121 just a code, kind of like machine language programming? (The first computer programming I ever did, back in the 60's, in engineering school, was machine language, strictly numeric.) If it's a code, then there has to be a particular syntax that tells XL that this is not the number -4121, but an instruction. So I need to go through the NG and look at as many examples of xlDown, and it's brethren, and figure out that syntax in order to use it. Am I on the right track here, or headed down another dead end? Tom wrote: I could have told you the Tattered Cover Bookstore wasn't going to help, but there's no knowledge like first hand knowledge. Unfortunately, there isn't. And for those of us with extra skull thickness (to protect a somewhat smaller brain inside), it's even more true! |
Frustrated with VBA so-called help
"davegb" wrote in message oups.com... What confused me, and still does, is why a constant is used to tell XL to go to the end of a range. Because there is more than one direction you can go in. How would VBA know? When I want to go to the end of the range from the keyboard, as in your instruction, Tom, I don't type in "-4121". I do an End arrow (Actually, I've always done a Ctrl arrow, same thing). Is -4121 just a code, kind of like machine language programming? You don 't in VBA either, you use xlDown, xlToLeft, etc. (The first computer programming I ever did, back in the 60's, in engineering school, was machine language, strictly numeric.) If it's a code, then there has to be a particular syntax that tells XL that this is not the number -4121, but an instruction. So I need to go through the NG and look at as many examples of xlDown, and it's brethren, and figure out that syntax in order to use it. Am I on the right track here, or headed down another dead end? I think so, most examples will do the same thing. You need to understand where it is used (within a range) and get to understamd that object, and what you can do to it (methods), and what attributes it has (properties). Again, Help and the object browser are very useful here. |
Frustrated with VBA so-called help
You don 't in VBA either, you use xlDown, xlToLeft, etc.
You don't usually, but you can. Those words are just names for the corresponding numbers. Everything is numbers, "under the hood". |
Frustrated with VBA so-called help
I think we al know that, that has been the essence of this prolonged
discussion. "Myrna Larson" wrote in message ... You don 't in VBA either, you use xlDown, xlToLeft, etc. You don't usually, but you can. Those words are just names for the corresponding numbers. Everything is numbers, "under the hood". |
Frustrated with VBA so-called help
I can see you have a mental blockage with this. I get those often which
usually means time to look sideways. Anyway, doubt you are the only one who's ever been confused with this. First confusion is you are comparing what happens when you press End arrow in Excel and cell.End(xlDown) in vba. Or rather, you are ignoring all the process's involved with that key press. The first thing Excel has to do is work out and return the destination reference of the "End" cell relative to the cursor cell, before actually going there. And that, and only that is what cell.End(num) does. Except it doesn't need to be the active cell, could be any cell. At this stage vba has no idea what you want to do with this reference. Maybe you only want to know it's row number, its value or perhaps you want to go there. This is a second process that you have to instruct, eg. nRow = Range("A1").End(xlDown).Row MyVal = Range("A1").End(xlToRight).Value Range("A65536").End(xlUp).Select Second confusion - the xlDown thing Yes you could think of it either as an instruction or a code. But try and think of it this way. "End" is a function that requires one argument. This argument must be any one of four particular numbers. If say xlDown / -4121 is received, eventually after a series of internal If's it will be recognized. Ah, user wants me to return a reference, or rather a Range object, to the end-down cell, relative to the cell-ref that End is attached to. Wonder what the user wants to do with or know about this "range object", but that's not my concern. All this is explained in vba help, better and certainly more concisely, but maybe gives a different angle. Good luck, Peter T "davegb" wrote in message oups.com... Thanks for all the excellent explanations. I did your "experiments" Peter and Tom, and it helped. As far as your question, "What more could you ask?", I could, and have, asked for an explanation. And now I have one. What confused me, and still does, is why a constant is used to tell XL to go to the end of a range. When I want to go to the end of the range from the keyboard, as in your instruction, Tom, I don't type in "-4121". I do an End arrow (Actually, I've always done a Ctrl arrow, same thing). Is -4121 just a code, kind of like machine language programming? (The first computer programming I ever did, back in the 60's, in engineering school, was machine language, strictly numeric.) If it's a code, then there has to be a particular syntax that tells XL that this is not the number -4121, but an instruction. So I need to go through the NG and look at as many examples of xlDown, and it's brethren, and figure out that syntax in order to use it. Am I on the right track here, or headed down another dead end? Tom wrote: I could have told you the Tattered Cover Bookstore wasn't going to help, but there's no knowledge like first hand knowledge. Unfortunately, there isn't. And for those of us with extra skull thickness (to protect a somewhat smaller brain inside), it's even more true! |
Frustrated with VBA so-called help
Thank you all! It's beginning to make sense. See, even the thickest of
skulls eventually yields to a strong, sharp power drill! I re-read Walkenbach's section on Constants, which went over my head the first time I read it, and it makes sense now too. Having an engineering background, I had the engineering concept of "contstant" (i.e., pi, e, Avagadro's Number) confused with VBA's concept of constant. (I also played with looking at the value returned in using other VBA constants, like vbOkonly and they returned numeric values too.) Having taught a lot of technical and semi-technical classes over the years, I realize how difficult it can be when you have a previous understanding of a term and then having to grasp an entirely diffrent meaning. I've seen it so many times in my students, and found the best way around it is what happened here. Just try to explain it in several different ways until one of them, or the acculmulation of them gets through. Tom, Tushar, Bob, Peter & Myrna - all of you, I appreciate your patience. You've gotten me over this hurdle. On to the next. |
Frustrated with VBA so-called help
Dave,
Just adding my 2 cents worth - I'm kind of in the same situation as you are, kind of a newbie to VBA, and gather bits of info here and there. I did order one of John Walkenbach's books, and all though I find them helpful, they are for the most part, a collection of examples. It's NOT the reference book that I'm looking for (and so far haven't found) that lists all the objects, functions, etc with a good explanation of each. However, I must say how much I appreciate and rely on the people on this and similar NGs who are so generous with their experience and knowledge. I just hope they won't get tired of answering some of my questions while I'm still deep in "learning" mode. Dave (Unger) |
Frustrated with VBA so-called help
Dave,
Maybe if we take turns asking stupid questions, they won't get frustrated with us individually as quickly! Best of luck! |
Frustrated with VBA so-called help
NOT the reference book that I'm looking for (and so far haven't
found) that lists all the objects, functions, etc with a good explanation of each. Try "Excel 2002 VBA Programmers Reference" by Stephen Bullen, John Green, et al. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dave Unger" wrote in message ups.com... Dave, Just adding my 2 cents worth - I'm kind of in the same situation as you are, kind of a newbie to VBA, and gather bits of info here and there. I did order one of John Walkenbach's books, and all though I find them helpful, they are for the most part, a collection of examples. It's NOT the reference book that I'm looking for (and so far haven't found) that lists all the objects, functions, etc with a good explanation of each. However, I must say how much I appreciate and rely on the people on this and similar NGs who are so generous with their experience and knowledge. I just hope they won't get tired of answering some of my questions while I'm still deep in "learning" mode. Dave (Unger) |
Frustrated with VBA so-called help
Thanks for the tip, Chip
Dave |
Frustrated with VBA so-called help
Now if I only new someone named Tip, and he gave me a potato chip....
Nevermind! |
Frustrated with VBA so-called help
Hi Dave,
Actually you would get a lot more out of the newsgroups if you indicated what you wanted to do instead of I'm looking for something that sounds or looks like... But I can't find it. When John Walkenbach had his column in PC World, you could be sure a lot of people scanned his column without reading it then a few days later asked the exact question. Can't just be all coincidences. I'd expect it would take 2 months between when he wrote something and when it gets published. My guess is that you are looking for something like the following macro that would take you to the last cell used in a column. If you wanted the next available cell in a column you would use OFFSET with it. Sub GotoBottomOfCurrentColumn() 'Tom Ogilvy 2000-06-26 Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select End Sub You might use xlDown if you wanted to get to the bottom of the current region (block). More information about both in Toolbars, Custom Buttons and Menus http://www.mvps.org/dmcritchie/excel/toolbars.htm Anyway when you find something of interest in HELP you can check things out in the newsgroup archives or websites and anything you find outside of Excel you should check HELP as well (like you were trying). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "davegb" wrote in message oups.com... Now if I only new someone named Tip, and he gave me a potato chip.... Nevermind! |
Frustrated with VBA so-called help
David M wrote:
Actually you would get a lot more out of the newsgroups if you indicated what you wanted to do instead of I'm looking for something that sounds or looks like... But I can't find it. Actually, this thread is an offshoot of another, in which I asked a "How do I..." question, and got some code with the xlDown constant in it. I tried to research it to figure out what it did and how to use it, and couldn't. Then I started this thread, originally intended to find out more about xlDown as well as to find out how I could find out more about it without having to ask here. It took off from there. |
Frustrated with VBA so-called help
Hi Dave,
Thanks for the reply, was wondering why there was no mention of any implementation. "davegb" wrote... Actually, this thread is an offshoot of another, in which I asked a "How do I..." question, and got some code with the xlDown constant [clipped ...research... clipped] |
| All times are GMT +1. The time now is 02:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com