Actuarial Outpost

Actuarial Outpost (http://www.actuarialoutpost.com/actuarial_discussion_forum/index.php)
-   Software & Technology (http://www.actuarialoutpost.com/actuarial_discussion_forum/forumdisplay.php?f=17)
-   -   Best way to learn VBA. (http://www.actuarialoutpost.com/actuarial_discussion_forum/showthread.php?t=88423)

Bama Gambler 08-09-2006 04:26 PM

Best way to learn VBA.
 
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 08-09-2006 05:49 PM

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 08-09-2006 05:54 PM

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 08-09-2006 06:36 PM

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 08-09-2006 07:33 PM

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 08-09-2006 08:40 PM

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 08-09-2006 08:41 PM

Quote:

Originally Posted by Bama Gambler (Post 1683693)
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:
Code:

    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 08-09-2006 09:00 PM

Quote:

Originally Posted by Bama Gambler (Post 1683894)
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 08-09-2006 09:15 PM

Quote:

Originally Posted by Bama Gambler (Post 1683741)
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 08-09-2006 10:44 PM

Quote:

Originally Posted by Griffin 1 (Post 1683930)
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.

IMP 08-09-2006 10:55 PM

oops, i took the poll before i read the post. i voted help, meaning live help from coworkers.

Bama Gambler 08-09-2006 10:57 PM

Bottom Line
If you have never used Excel VBA, then you will pick up more useful stuff in a shorter amount of time with a good book on Excel VBA vs. playing around with the macro recorder. I don't care if you have never taken a programming class or have a Phd in computer science. It is the fastest way to learn the stuff you will actually use. The books are dirt cheap too. Hell, you can check one out at your local library for free.

Now if all you need is one little macro and you don't plan to do anything else with VBA, then you can most likely find a quick and dirty solution faster with the macro recorder than reading a book. Although, in this case I'd recommend just posting the question on this forum.

Griffin 1 08-09-2006 11:06 PM

Quote:

Originally Posted by Bama Gambler (Post 1684057)
Bottom Line
If you have never used Excel VBA, then you will pick up more useful stuff in a shorter amount of time with a good book on Excel VBA vs. playing around with the macro recorder. I don't care if you have never taken a programming class or have a Phd in computer science. It is the fastest way to learn the stuff you will actually use. The books are dirt cheap too. Hell, you can check one out at your local library for free.

How about just leaving at "what works for one person may not work for another", since many people who are just as knowledgeable and skilled in VB and VBA as you would disagree?

Bama Gambler 08-10-2006 08:16 AM

Quote:

Originally Posted by Griffin 1 (Post 1684067)
How about just leaving at "what works for one person may not work for another", since many people who are just as knowledgeable and skilled in VB and VBA as you would disagree?

Normally I would concede that. In fact, all my "how to kick course X's ass" have that disclaimer, but in the case I feel too strongly. A good VBA book (vs. the macro recorder) is by far and away a much better place to start for someone that is looking to learn VBA. Why? Simple - there is tons of code for the reader to scan in the book. And guess what, that code is going to be a HELLUVA lot better than the code from the recorder. So if you learn by reading code, then read the code in a good book (vs. reading the code produced by the macro recorder). You will learn more and faster.

MNBridge 08-10-2006 08:24 AM

MrExcel.com is a must use site for anyone who programs in VBA.

The free advice there is unreal!

I also use the VBA recorder.
Here's the twist:
Goto the site above and say:
I used the recorder and got this, but I want to choose the column not the cell, how do I?

Within 15 minutes someone will rewrite your 'recorded' macro and give you the 'proper' coding.

P.S. Search is your friend there, I try not to bug them with stuff I can figure out on my own.

Bama Gambler 08-10-2006 08:28 AM

MNB, we can do that for you here too. :)

MNBridge 08-10-2006 08:30 AM

Quote:

Originally Posted by Bama Gambler (Post 1684278)
MNB, we can do that for you here too. :)

Yes, I often use this site as well and cross post.

Bama Gambler 08-10-2006 08:30 AM

Btw, I want to remind everyone the question isn't what do you use to write VBA code. The question is what is the BEST way to LEARN how to use VBA?

Bama Gambler 08-10-2006 08:37 AM

To those who voted for macro recorder - have you read an Excel VBA book? If so, which book?

MNBridge 08-10-2006 08:41 AM

Quote:

Originally Posted by Bama Gambler (Post 1684284)
Btw, I want to remind everyone the question isn't what do you use to write VBA code. The question is what is the BEST way to LEARN how to use VBA?

Here I think the question becomes, at what level?

I used to think I was pretty good at VBA. I could generally get it to do what I wanted it to.

Simulation macros
Building functions (Public) etc.

But my code would typically not be more than say 1 page and efficiency didn't matter.

-This could be done from my 9th grade level of BASIC programming and VBA recorder.

I'm quickly learning I suck at it. I have no idea why I get object errors, etc. (I really don't even know what an object is :) )

So to get to the next level I will need to get a book (or take a class) and understand the language better. I would guess 99% of all Actuaries never get to or will ever use VBA at this level.
Yet many of them do consider themselves experts.

This level would include stuff like:
- Writing a platform for outside users that is pretty and can't be wrecked. (An outside user may not even realize they are in excel)
- Building a dynamic model. (Never need to hit the 'run macro' buttons)
- etc.

Griffin 1 08-10-2006 08:43 AM

Quote:

Originally Posted by Bama Gambler (Post 1684243)
Normally I would concede that. In fact, all my "how to kick course X's ass" have that disclaimer, but in the case I feel too strongly. A good VBA book (vs. the macro recorder) is by far and away a much better place to start for someone that is looking to learn VBA. Why? Simple - there is tons of code for the reader to scan in the book. And guess what, that code is going to be a HELLUVA lot better than the code from the recorder. So if you learn by reading code, then read the code in a good book (vs. reading the code produced by the macro recorder). You will learn more and faster.

A few years ago, I would have agreed with you. And I would have been trashing the recorder even more strongly than you are now. But many people learn better by doing, especially doing practical work (re ST:TNG episode 113: The Masterpiece Society).

So to someone seeking advice on how to learn VBA, I would say "Here are several different methods. Try them all out and see what works best for you."

MNBridge 08-10-2006 08:43 AM

Quote:

Originally Posted by Bama Gambler (Post 1684297)
To those who voted for macro recorder - have you read an Excel VBA book? If so, which book?


I haven't really 'read' it, though I should. But I use Walkenbach's book for reference consistently.

Bama Gambler 08-10-2006 08:44 AM

But trust me when I say you could have learned to write those simulation macros and building functions much quicker if you have started with a book. AND they would have been much cleaner code, which comes in handy if you or anyone else ever needs to make changes to the code.

Griffin 1 08-10-2006 08:45 AM

Quote:

Originally Posted by Bama Gambler (Post 1684297)
To those who voted for macro recorder - have you read an Excel VBA book?

Yes, several in fact. (Including VB and other flavors of VBA)
Quote:

If so, which book?
I like the stuff published by O'Reilly the best.

Bama Gambler 08-10-2006 08:48 AM

Alright, I'm giving up. It doesn't look like I'm going to change anyone's mind and I'm sure not changing my mind. So we will have to agree to disagree on this one.

Till next time. :toast:

Griffin 1 08-10-2006 08:48 AM

Quote:

Originally Posted by Bama Gambler (Post 1684331)
But trust me when I say you could have learned to write those simulation macros and building functions much quicker if you have started with a book. AND they would have been much cleaner code, which comes in handy if you or anyone else ever needs to make changes to the code.

I didn't start with a book or a macro recorder. I started with Lotus 2.1 and a one-line macro someone showed me during my third week on the job.

Lewick 08-10-2006 08:53 AM

I have learned VBA trough VB (i took a VB class in university), which gave me the basics only for VBA. As you get better, HELP will be your best friend.

Or just write VBA in google and you'll get a thousand forums talking about VBA, functions, etc.

Griffin 1 08-10-2006 09:00 AM

Quote:

Originally Posted by Lewick (Post 1684375)
Or just write VBA in google and you'll get a thousand forums talking about VBA, functions, etc.

And you quickly learn to identify which sites will, after you've gotten there, say "Want the answer to this question? Pay us some money!"

Expunge 08-10-2006 09:49 AM

I've never read a VBA book. yet i know about cells(r,c). I started by recording macros, and adding in the various loops as needed. As so an i needed to loop through various cells (even down rows as opposed to columns) I knew there had to be some sort of cells(r,c). Now I use the help files when needed, and I still will read through other peoples code from time to time to see how they tackle the problems.

Arlie_Proctor 08-10-2006 10:12 AM

I agree with IMP. IMO, the best way to learn is from a coworker or from a coworker's code.

Phil 08-10-2006 11:34 AM

VBA help sucks, unless you already knew the answer and just forgot.

Example
You want to learn the For...Next syntax. You're a newbie. You go to Language reference, find "For" and their example is like:

' This example sets the Windows OLE RIMM socket to the DLL speed of the unihex DIP switch relayed by the socket connection of the LPT1 (LPT2) port (CPT[open_com] in Windows XP SP 1).

DIM windows_cgi_bin_interface_comlink as Integer
#DEFINE comlinks.count Application.OLEboundControls
FOR windows_cgi_bin_interface_comlink = comlinks.count TO LPT1.open_port.DLLlinks(,,OLE)
Debug.Print windows_cgi_bin_interface_comlink
Application.parent.ports.&opencom speed:=8008 hex2uni(OLE_bound) Mod 16 'For Windows XP SP 1 please consult user manual
NEXT windows_cgi_bin_interface_comlink

As Seinfeld says, good luck with all that.

Kazodev 08-10-2006 11:48 AM

I just go on here, post my question and wait for Captain to answer :tup:

Bama Gambler 08-10-2006 12:02 PM

Quote:

Originally Posted by Phil (Post 1684930)
VBA help sucks, unless you already knew the answer and just forgot.

Example
You want to learn the For...Next syntax. You're a newbie. You go to Language reference, find "For" and their example is like:

' This example sets the Windows OLE RIMM socket to the DLL speed of the unihex DIP switch relayed by the socket connection of the LPT1 (LPT2) port (CPT[open_com] in Windows XP SP 1).

DIM windows_cgi_bin_interface_comlink as Integer
#DEFINE comlinks.count Application.OLEboundControls
FOR windows_cgi_bin_interface_comlink = comlinks.count TO LPT1.open_port.DLLlinks(,,OLE)
Debug.Print windows_cgi_bin_interface_comlink
Application.parent.ports.&opencom speed:=8008 hex2uni(OLE_bound) Mod 16 'For Windows XP SP 1 please consult user manual
NEXT windows_cgi_bin_interface_comlink

As Seinfeld says, good luck with all that.

lol, I'm not sure what version of help you have installed, but I get the following.

For...Next Statement Example
This example uses the For...Next statement to create a string that contains 10 instances of the numbers 0 through 9, each string separated from the other by a single space. The outer loop uses a loop counter variable that is decremented each time through the loop.

Code:

Dim Words, Chars, MyString
For Words = 10 To 1 Step -1    ' Set up 10 repetitions.
    For Chars = 0 To 9    ' Set up 10 repetitions.
        MyString = MyString & Chars    ' Append number to string.
    Next Chars    ' Increment counter
    MyString = MyString & " "    ' Append a space.
Next Words


Phil 08-10-2006 12:52 PM

A useful example would be this:

DIM i as integer
FOR i = 1 TO 15 STEP 3
Debug.print i
NEXT i

will output as follows:
1
4
7
10
13

Done. Now I know how to use FOR. I'll figure out how to nest two of them or use string concatenation on my own. Or better yet, by going to help on "&", which should have an example like "straw" & "berry" ' equals "strawberry"
but in VBA help probably just refers you back to this same FOR..NEXT example.

whisper 08-10-2006 02:56 PM

Quote:

Originally Posted by Bama Gambler (Post 1684243)
Normally I would concede that. In fact, all my "how to kick course X's ass" have that disclaimer, but in the case I feel too strongly. A good VBA book (vs. the macro recorder) is by far and away a much better place to start for someone that is looking to learn VBA. Why? Simple - there is tons of code for the reader to scan in the book. And guess what, that code is going to be a HELLUVA lot better than the code from the recorder. So if you learn by reading code, then read the code in a good book (vs. reading the code produced by the macro recorder). You will learn more and faster.

I started learning VBA because I wanted to make certain activities I had to perform in a workbook easier. I could have spent time reading the book, and figuring out all the pieces individually - or I could just record the action that I wanted replicated in VBA recorder and use that.

Brad Gile 08-10-2006 05:19 PM

Quote:

Originally Posted by whisper (Post 1685763)
I started learning VBA because I wanted to make certain activities I had to perform in a workbook easier. I could have spent time reading the book, and figuring out all the pieces individually - or I could just record the action that I wanted replicated in VBA recorder and use that.

Unfortunately, many actuaries using VBA have done the same thing, with the following results:
1. Crap code - sometimes from the recorder, sometimes from the bliss of ignorance of the coder.
2. Often, no variable declarations - again, due to total ignorance on the part of the coder.
3. Sloppy or nonexistent documentation of the code, leaving a mass of spaghetti for the poor slob that inherits that code.


Brad

Griffin 1 08-11-2006 09:30 AM

Quote:

Originally Posted by Brad Gile (Post 1686271)
Unfortunately, many actuaries using VBA have done the same thing, with the following results:
1. Crap code - sometimes from the recorder, sometimes from the bliss of ignorance of the coder.
2. Often, no variable declarations - again, due to total ignorance on the part of the coder.
3. Sloppy or nonexistent documentation of the code, leaving a mass of spaghetti for the poor slob that inherits that code.

Which means their code really isn't much different than what you see from many "professional" coders.

whisper 08-11-2006 01:33 PM

Quote:

Originally Posted by Brad Gile (Post 1686271)
Unfortunately, many actuaries using VBA have done the same thing, with the following results:
1. Crap code - sometimes from the recorder, sometimes from the bliss of ignorance of the coder.
2. Often, no variable declarations - again, due to total ignorance on the part of the coder.
3. Sloppy or nonexistent documentation of the code, leaving a mass of spaghetti for the poor slob that inherits that code.


Brad

Yes, many actuaries have used the VBA recorder to generate code.
Many of these are not trying to learn VBA, just generate code that works. The question isn't is the VBA recorder a bad way to generate code (which it is), but is it useful in learning how to program (which it is).

Brad Gile 08-11-2006 03:14 PM

Quote:

Originally Posted by whisper (Post 1687766)
Yes, many actuaries have used the VBA recorder to generate code.
Many of these are not trying to learn VBA, just generate code that works. The question isn't is the VBA recorder a bad way to generate code (which it is), but is it useful in learning how to program (which it is).

But my point, which you ignore, is that a lot of people generate the code with the recorder and routinely use it as is! Often, when this is done, the code will not always work they way the user thinks it will. IOW, the code does NOT "work"! That's not using the recorder as a learning tool.

Moreover, I agree with Bama that the recorder is highly limited as an educational tool. It is most useful for those who already have good programming techniques.

But, whatever.:D

Brad

Griffin 1 08-11-2006 03:39 PM

Quote:

Originally Posted by Brad Gile (Post 1688062)
Moreover, I agree with Bama that the recorder is highly limited as an educational tool. It is most useful for those who already have good programming techniques.

"What's the best way to learn programming?" <> "What's the best way to learn VBA?"


All times are GMT -4. The time now is 05:55 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

Page generated in 1.08339 seconds with 9 queries