Best way to learn VBA.

[Bama Gambler]

I'll rank my choices.

1. Good book. Any of the VBA books by Walkebach will do. This will help you write clean, readable code.

2. VBA Help files. Type what you want to do in help. They have some good examples.

3. Internet. Google it or post your question on a discussion board.

Avoid the macro recorder until you have honed your skills. It writes sloppy code and there is no macro recorder in Access.

[Bama Gambler]

For those voting for macro recorder, can you say why you believe it's a good way to LEARN vba? I agree it's a good tool for those who already have a good foundation in vba, but to use it as a learning tool is just not smart IMO.

For example, I couldn't find how to change the datasource from the Object Browser or help files, but a quick record of creating a pivot table lead me to the solution.

[Bama Gambler]

For example, if someone was to learn using the macro recorder then .Select would be all throughout their code. They wouldn't learn how to declare variables, create functions, or useful things like Cells(row, column). I could go on and on.

[2.718281828]

Another way to learn how to code is to have lots of deadlines at work. That way your livelihood (sp?) depends on figuring it out and fast.

[Brad Gile]

In addition, write a ton of experimental code to see
(1) what it does,
(2) what it does NOT do,
(3) what unexpected things it does.

Then find ways to modify that code to make it stable and practical, yet be self-explanatory as possible with good commentary where needed.


Brad

[Bama Gambler]

whisper, why do you consider the macro recorder a good learning tool for vba? This is a sincere question. You've contributed to many a VBA questions over the years on this forum.

[Griffin 1]

Originally Posted by Bama Gambler

For those voting for macro recorder, can you say why you believe it's a good way to LEARN vba? I agree it's a good tool for those who already have a good foundation in vba, but to use it as a learning tool is just not smart IMO.

Well thanks for asking! If you know how to program, then it's a good tool. If you don't know how to program, it's not. Programming is not something you learn all over again with each new language or software. As I said in the other thread, Actuaries are supposed to be smart. That means they can figure things out.

No one has said you should record a macro and leave it at that. What they (or at least I) have said is that it often gives you a good place to start. A person who knows his or her way around Excel and is smart enough to be an Actuary who sees:

    Range("A1:A10").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False

can probably figure it out. You should give people more credit than what you do.

As for the lack of a recorder in Access, the any VBA you learn [b]will[/i] transfer, regardless of how you aquired the knowledge. And since Actuaries tend to spend more time in Excel than they do Access, they will also tend to learn their VBA in Excel first. So it's not usually going to be a problem.

[whisper]

Originally Posted by Bama Gambler

whisper, why do you consider the macro recorder a good learning tool for vba? This is a sincere question. You've contributed to many a VBA questions over the years on this forum.

The recorder is a two edged sword. It is very verbose. So, it needs to be edited to be useable. On the other hand, it is so verbose it can show the student all the other things possible.

I've actually used the recorder recently to try to figure out how to do something because I knew the recorder was so verbose that somewhere in the code it had what I wanted, I just had to read it and figure out what that one thing was.

[Griffin 1]

Originally Posted by Bama Gambler

For example, if someone was to learn using the macro recorder then .Select would be all throughout their code. They wouldn't learn how to declare variables, create functions, or useful things like Cells(row, column). I could go on and on.

That's where the "Actuaries are smart" part comes in. When has anyone ever suggested that you're finished when you hit "Stop Recording"?

[Bama Gambler]

Originally Posted by Griffin 1

That's where the "Actuaries are smart" part comes in. When has anyone ever suggested that you're finished when you hit "Stop Recording"?

I know actuaries are smart, but you'll never find Cells(r,c) in a recorded macro. I use that more than anything else and I learned it from a book (or online, can't remember which). Most of the stuff I use (with regard to the Excel specific VBA) on a regular basis I got from a book, help or the internet. Only things I've gotten from the macro recorder are very obsure things (like the pivot table thing) and formatting stuff. So 90% of the stuff I use I didn't (and can't) learn from macro recorder. The other 10% isn't needed by someone trying to LEARN vba. So I'd tell that person to get a good foundation before using the macro recorder, even if they are an advanced programmer.

When I first started VBA programming I was already pretty good in VB. I used the macro recorder to learn the excel specific stuff at first and I was writing code like:

dim i as integer

for i = 1 to 10
range("A"&i).select
activecell.value = i
next i

Columns were of course a *****.

I knew there had to be a better way so I bought a book and of course started writing better code immediately. Can you learn from the macro recorder? Of course. Is is the BEST way to learn? Hell no. Even for the best programmer in the world, he'd be better off scanning a book, then recording stuff.