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]

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

 

[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

 

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

 

[Bob Phillips]

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

 

[Bob Phillips]

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.

 

[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/d...us/vbaxl11/html/xlhowConstants_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

 

[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

 

[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

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

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.

 

[Bob Phillips]

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]

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]

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