VBA Resources for learning, but taught by a teacher

[Starkman]

Hello all,

I'm now interested in learning VBA; however, the problem with many VBA books, as is the case with software manuals in general, is that the writer usually writes from a programmer's mind and a programmer's thinking method. Those of you who are "naturals" at thinking like this find little difficulty in following a book written like that. The rest of us, however, are shot to you-know-what in a handbasket at just the thought of trying to wade through a programmer-thinking book.

I've gone though Wallenbach's book on Excel formulas. Wasn't bad. But, once again, though he was pretty clear, his method of writing was more of passing along information and not "teaching" it. For people with his mind, it's like, "See, it's simple." No, it's not, if you don't think that way and need everything spelled out.

I'm going to start with "VBA for Dummies" (I mean, that's about as simple as it's going to get, I suppose), but I'd like your thoughts about some good sources with a teacher's mind behind the writing.

Thanks very much,

Starkman

 

[TinaP]

You might want to try Walkenbach's Excel VBA Programming for Dummies. His Dummies books seem to be easier to comprehend than the others.

 

[PolarBear]

I'd suggest you try to supplement "book larnin'" with a tutor. Are there any computer user groups near you? You might find someone who is willing to teach (paid/unpaid is up to you!) in such a group.

I'm the only one in my office who understands VB, so I use boards like this to get answers to specific questions, but if you can find a person who understands VB even a little, he/she can help you work out what's going in a textbook.

The toughest part for novice programmers is to get their minds to "slow down", and think out all the intermediate steps. For example, I have a ton of files where I need to test all the rows in a database. It's easy to say to a human "Start at the top and go until you reach the end of the data", but how do you tell Excel how to do that when the number of rows changes from day to day? Or, as was asked in another thread, how do you tell Excel to copy a formula into a column, but skip every fifth row? Again, if you told a person to do that, they'd be able to do so easily; telling Excel takes a bit (not much, mind you) more effort.

The other thing you'll learn when coding is computers are very stupid. You need to tell them exactly what to do. For example, you have code that pops up a box "Enter a number from 1 to 10". Murphy's Law guarantees someone will enter "11". If you don't have code to validate the input, and specify what to do if an error occurs, your program will bomb. Those are all the little pain in the butt steps that humans process automatically.

You mention "a programmer's mind", but really it's a "mindset". There's nothing superhuman about coding; it's just being methodical (and of course, learning the syntax and the commands!). At the end of the day, it's actually kinda fun. Good luck, and let us know how you're doing!

 

[erik.van.geit]

Hello,

about "a programmer's mind"
call it what you want, but those guys who are programming have some other way of thinking than the average man in the street (as we would call that in Dutch): a good teacher has the ability to translate this mindsetting to "ordinary thinking"

for example: how many % of the "ordinary people" would imagine that DEFINING a NAME would be done through a menu called INSERT ?

a way of learning which is almost never recommended:
browse all menus and submenus (I'm still doing this from time to time) and figure out what they are made for: this will lead you to some items which seem interesting for you: then you can go to the HELP, which is about the best "book" you can find

my two cents :wink:
Erik

 

[TinaP]

A programmer's mind is a scary thing. PolarBear is right, the most important part of programming is teaching yourself to break down every single step. I once wrote a program that the user told us was very simple, but time-consuming (she was right). Telling the computer to only show every third column took forever to get it right. A human only needed to count to three repeatedly.

Another good way to learn VBA is to record simple macros, then view them in the VB editor.

 

[SydneyGeek]

I agree with the comment that computers are stupid and you have to break it all down. I tell beginners (they are usually the scared ones) that computers are high-speed morons; all they do is count to 1 very fast.

So, how do you teach yourself? A couple of ideas.

1. Pick a problem that matters to you. If it doesn't matter, you won't follow it through.

2. Work out all the bits of the problem. For example, you want to transfer a filtered and sorted list of information from a table to a new worksheet. So, you'll need to
Activate or Select the correct sheet
Define the region to sort and filter
Sort the table
Filter the table
Copy the visible rows
Paste them to a new worksheet

3. Create a new macro in a fresh module, and call it Sub SortAndFilter
For each of the steps outlined above, type that into the macro but start each line with an apostrophe, so they become comments (green text). Leave a couple of lines between each.

4. Record macros that do all the steps. Those macros will end up in a different module, with names Macro1, etc. You can copy the code and paste it into the blank spaces that you left in the main macro.

A hint: with the table selection, use the method that will always select the whole table, regardless of size. With the recorder on, select A1 (the top left of the table), then press Ctrl+* (or Ctrl+Shift+8 if you don't have a keypad). That selects the whole table, and records code that will do the same.

The rest of this macro comes from using the menu commands as Erik suggested, with the recorder on.

Piece it together, see if it works, then add or delete rows and try it again.
The first macro you create this way will have some unnecessary stuff in it: you will be selecting ranges all over the place, which isn't actually required, but as you get more skilled you can remove the selection steps and just refer to the ranges / sheets.

Yes, try to find a course or a helpful expert to ease the process for you. Having someone to talk to is a huge help. But use this site as a resource too. It's great.

Denis

 

[Richard Schollar]

1. Pick a problem that matters to you. If it doesn't matter, you won't follow it through.

This is seriously the best advice anyone could be given, who wants to learn Excel/VBA.

 

[Starkman]

Thanks very much...

Thanks very much to all those who inputed on this thread.

I've got some books on order from the library, so that's a start. I'll also work on making simple macros and then looking at the code.

Again, thanks,

Starkman