Frustrated with VBA so-called help

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

 

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

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.

 

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

 

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

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]

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

 

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