Where do you learn this stuff

[PatK]

Ok..maybe a bit of venting here, as you guys are the BEST. I guess this is
more aimed at our friends at MS: WHERE IN THE HECK TO YOU LEARN THIS STUFF!

By this I mean, without these newsgroups, how could anyone very do learning
by research? Example:

I see a bit of code like:
FinalValue=WSM.Range("A65535").End(xlUp).Row

Now most of you great folks out there, I am sure, know EXACTLY what
".End(xlUp).row does, but how does a noob reseach and learn such a thing. It
is definitely not in the help, unless you know what it can be attached to
(like a range method or whatever), but even then, the help assume you know
too much already.

Help me out: where is a great book that breaks these things down so I can
know what is "possible" (not just what you all show me...and for which I
still often do not comprehend). I hate doing "what I see." I would rather
see an example, learn how it works, and then, maybe, stores some bits of
understanding my brain, for future reference.

Suggestions?

(ok..end of rant). As always, I could not survive without you all. This
newsgroup is the best "reference site" to help with excel, or any MS VBA app,
but should it be?

thanks, all,
Patk

 

[RyanH]

I am actually learning myself. I use the help, read other peoples posts in
this forum and try to see other peoples coding styles. I have read a few
books too, VBA Excel for Dummies (because I am one), Microsoft Excel 2002
Visual Basic For Applications Step by Step. Although book do teach you the
core stuff and sprinkle in code every now and then, practicing is where you
really learn. I also like to use Chip Pearson website at times,
www.cpearson.com

Hope this helps and good luck!

 

[PatK]

Thanks Ryan...sounds like you are operating much as I am. I mean, I "do" get
stuff done, and I do learn (over time), but sometimes I just don't have a ton
of time to figure stuff out. Hey, and I will check out Chip's site,
too...nice lead! Thanks!

Patk

 

[Dave]

Thanks Ryan...sounds like you are operating much as I am. I mean, I "do" get
stuff done, and I do learn (over time), but sometimes I just don't have a ton
of time to figure stuff out. Hey, and I will check out Chip's site,
too...nice lead! Thanks!

Patk

I use similiar resources. I'm still working with XL2000 (work for the
state, we will finally upgrade in the fall), so I have XL2000 Programming
for Dummies and XL2000 Programming with VBA by John Walkenbach, which is
very good.

I've also run across Chip's site and it helps a lot. When I really get
stuck on something, I post my code here and the very generous people who
know this stuff bail me out of trouble yet again. Virtually everything I've
written in the past 3 years has been with their help.

I finally got my boss to spring for $500 for a 2 day basic XL VBA class a
couple of months ago. It really helped. A lot of stuff that you just don't
figure out on your own. I'm planning on talking her into sending me for
another 2 days now that the new fiscal year has begun and we have a new
budget. I highly recommend you find someone good (meaning not-a-pushbutton
instructor) and take a class. If you choose wisely, I'm sure it'll be worth
your investment.

Good to know there are others struggling with this too. I've done some
programming years ago (Fortran, Basic and some very obscure stuff), but VBA
seems more daunting to me. Not sure if it's just my age or it really is
more abstruse. But it is challenging and I enjoy it. Most of the time...

 

[JNW]

"Excel VBA Programming for Dummies" is what got me started.

There are a lot of books out there, but nothing will make sense until you
start messing around and seeing what works and what doesn't (then come to the
forums for help!).

 

[Les]

A book very worth getting is "Power Programming with VBA" writen by Mr
Spreadsheet John Walkenbach. I have the 2003 version, but i am sure there are
more.

 

[PatK]

Thanks Dave! You sound like me! I also have one of John Walkenbach's book
for XL2003 (wonder if there is one for 2007 yet)? Another good book is VBA
and Macros for Microsoft Excel from Bill Jelen and Tracy Syrstad. Finally, a
good on that covers MS 2007 apps, VBA, and how thing work between them is
Paul McFedries' VBA For the 2007 Microsoft Office System. Even given all of
these books, however, none go into great detail about all the various
parameters, constants, etc (give me ONE example and explain it, please. That
is what is so good about these forums).

I think you may have hit on something though, and that is how long we have
been at this. You sound like a guy that has been around the block, as well
(ever heard of RPG programming? LOL). Did my stint in Basic, Cobol, SPL,
Assembler, and other such wunderkind compilers of my time. My point is, I
try to "relate" what worked then, with what I see now, and that is where
things fall apart.

 

[PatK]

Hey, thanks Les! I got that book open on my desk as I type. Very good book.
And I probably should restate something...I have done a lot of VBA coding,
and made some really cool apps/add ins, etc....it is just it seems there is
so much more under the hood, if you you know it is there. But often, when I
make a post here, someone shows me a really neat way of doing something, and
I have never even heard/seen it before anywhere. I guess I want that BIG
CATALOG OF EXAMPLES (anyone want to co-author???LOL right..........

Patk

 

[Rick Rothstein \(MVP - VB\)]

The problem is the sheer number of properties and methods available. The
worse one, size-wise, is the Range Collection with some 90+ properties and
over 70 methods available. The printed documentation on that one collection
would probably be larger than a normal sized book. The only suggestion is to
read the description of the various properties and methods available for the
various objects and collections... not to memorize them (heavens, it would
take a photographic memory to do that), rather, to be aware they are
there... don't worry about understanding them completely nor their syntax...
just know they exist and approximately what they do... then, when a
situation comes up that one of them may handle well, you will
sort-of-remember you saw it somewhere before and can then search it down.
That is all I do, and yes, there are lots I don't remember and miss using in
a response... usually I see someone else post it and then, afterwards, it
tends to "stick" in my memory.

Rick

 

[J Smith 555]

Patk,

I too am still learning how to code VBA for Excel. The one great tip that I
like to give is:
"Record your way to success"

Go into Excel, turn on the Macro recorder and just type in whatever into
whatever cell / range of cells. Then open up the VBA and see what it
recorded. From there you can see what is used and how it used.

Once you see that Excel records:
Range("D6").Select
ActiveCell.FormulaR1C1 = "Test Line of code"

You can change it to:
Range("D6").FormulaR1C1 = "Test Line of Code"

This is how I've learned most of my code. That and reading a lot of forums
too .. I would recommend this one along with ozgrid.com (forgive me Microsoft)

 

[PatK]

you know...(and this tells you how long I have been mucking with this), I
used to do just exactly that, and have since forgotten. This might help me
in my current problem in fact.

As Rick wrote, I am in the throws of a RANGE nightmare, just trying to
figure out what sheet the range refers to, reference the data in the
range....etc)....

But you know....I am going to try recording, just to see what happens!

Thanks, Patk

 

[ryguy7272]

Here are some great resources:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.andypope.info/charts.htm
http://peltiertech.com/Excel/Charts/ComboCharts.html
http://www.cpearson.com/excel/topic.aspx
http://www.contextures.com/tiptech.html
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro
http://www.mvps.org/dmcritchie/excel/colors.htm

I think books are great for beginner types and people learning
intermediate-level Excel stuff. For really advanced stuff, I’ve found no
better resource than this discussion group. This resource is really a
‘collective intelligence’ of the best and brightest Excel users on the
planet. I still learn new Excel-related things each and every day. However,
these days I’m not learning as much Excel stuff as I was learning a few years
ago. Since the learning curve has flattened, I spend some of my spare time
sharing my knowledge with others at this discussion group, and I spend a
certain amount of time learning Access because inevitably you get to a point
where you need to do things that Excel literally can’t do, but Access can do
quickly and easily.

In conclusion, don’t give up when it gets difficult. It hasn’t been so long
that I forgot what things were like when I was just beginning to learn all
this stuff. Sometimes it is hard to learn some of the advanced features of
Excel, but it is rewarding too! Personally, I just like to solve problems
and Excel is a great tool for problem solvers.

 

[PatK]

Thanks, RyGuy...definitely going to add some bookmarks today. OH, and I have
more VBA experience in Access, then excel (not that I will win any prizes),
but I am way more comfortable in a DB env, then a spreadsheet env.

Thanks for the input...this has become a pretty good resource thread, all on
it's own!

Patk

 

[salgud]

Patk,

I too am still learning how to code VBA for Excel. The one great tip that I
like to give is:
"Record your way to success"

Go into Excel, turn on the Macro recorder and just type in whatever into
whatever cell / range of cells. Then open up the VBA and see what it
recorded. From there you can see what is used and how it used.

Once you see that Excel records:
Range("D6").Select
ActiveCell.FormulaR1C1 = "Test Line of code"

You can change it to:
Range("D6").FormulaR1C1 = "Test Line of Code"

This is how I've learned most of my code. That and reading a lot of forums
too .. I would recommend this one along with ozgrid.com (forgive me Microsoft)

I checked this one out, it looked interesting. But I don't have months of
studying to learn how to post (How many pages of posting rules are there?),
I need answers more quickly than that. I'm glad this NG is here, the
experts here have helped, and continue to help me out. And there's no
encyclopedia of rules to memorize! I guess I'm just lazy.