Frustrated with VBA so-called help

[davegb]

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!

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[Bob Phillips]

"Tushar Mehta" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Bob Phillips]

"davegb" <[email protected]> wrote in message
news:[email protected]...
> "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 :-).

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[Tushar Mehta]

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' The former is the place where I use xlDown
the most often and the latter is a gold mine that I didn't know existed
until I searched VBA help before posting this message.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> 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!
>
>

[davegb]

"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.

[davegb]

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!

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[Tushar Mehta]

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 <[email protected]>,
[email protected] 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!
>
>

[davegb]

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.

[davegb]

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!

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> [email protected] 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!
> >
> >

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[Bob Phillips]

"davegb" <[email protected]> wrote in message
news:[email protected]...
>
> 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.

[Bob Phillips]

"Tom Ogilvy" <[email protected]> wrote in message
news:OEqsD%23%[email protected]...

> 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.

[davegb]

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.

[Bob Phillips]

"davegb" <[email protected]> wrote in message
news:[email protected]...

> 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.

[davegb]

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.

[Peter T]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[Tom Ogilvy]

go to a cell (select the cell), hit the end key and release it (note that on
the bottom of the window, "END" will appear in the right hand side) and then
hit the down arrow. That is what end(xldown) does. Try it in various
situations - such as when you have a cell in a large area of data and when
you are on an empty section. You can also do End, then Up Arrow and End,
then right arrow and finally, End and Left arrow.

If you don't know the product, then reading the help will not be near as
informative. Methods and Properties in the Excel library generally
correspond to things you can physically do in Excel.

I could have told you the Tattered Cover Bookstore wasn't going to help,
but there's no knowledge like first hand knowledge.
--
Regards,
Tom Ogilvy


"davegb" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[davegb]

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!

[Bob Phillips]

"davegb" <[email protected]> wrote in message
news:[email protected]...

> 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.

[upstate_steve]

davevb wrote

>Is -4121 just a code, kind of like machine language programming?

Actually -4121 is a switch, like in DOS.

No different than the "0" in Excel Worksheet Function MATCH(C1,A1:A10,0). (I wish I had a DOS example but I'm too young.)

Why such an unusual value? I assume that this constant is one of many similar constants, each used across various functions/statements/properties etc. That there are so many precludes the use of the usual 0, 1, 2 parameters. The whole point was to standardize values to be used for these "switches" and to assign them names for more intuitive use.

btw "Writing Excel Macros" by Steven Roman (OReilly) has a good, brief explanation of the named xl constants and their purpose. Book not with me, can't give page numbers.

[Myrna Larson]

>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".

[Bob Phillips]

I think we al know that, that has been the essence of this prolonged
discussion.


"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> >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".

[Peter T]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[davegb]

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.

[Dave Unger]

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)

[davegb]

Dave,
Maybe if we take turns asking stupid questions, they won't get
frustrated with us individually as quickly!
Best of luck!

[Chip Pearson]

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

[Dave Unger]

Thanks for the tip, Chip

Dave

[davegb]

Now if I only new someone named Tip, and he gave me a potato chip....
Nevermind!

[David McRitchie]

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" <[email protected]> wrote in message news:[email protected]...
> Now if I only new someone named Tip, and he gave me a potato chip....
> Nevermind!
>

[davegb]

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.

[David McRitchie]

Hi Dave,
Thanks for the reply, was wondering why there was no mention
of any implementation.

"davegb" <[email protected]> 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]