Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


View Poll Results: What is the best way to learn VBA?
Book 108 36.24%
Help 48 16.11%
Internet 149 50.00%
Macro Recorder 113 37.92%
Multiple Choice Poll. Voters: 298. You may not vote on this poll

Reply
 
Thread Tools Display Modes
  #31  
Old 08-10-2006, 12:34 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

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.
Reply With Quote
  #32  
Old 08-10-2006, 12:48 PM
Kazodev's Avatar
Kazodev Kazodev is offline
Member
SOA
 
Join Date: May 2004
Posts: 3,393
Default

I just go on here, post my question and wait for Captain to answer
Reply With Quote
  #33  
Old 08-10-2006, 01:02 PM
Bama Gambler's Avatar
Bama Gambler Bama Gambler is offline
James Washer / Notes Contributor
SOA
 
Join Date: Jan 2002
Location: B'ham, AL
Posts: 17,188
Default

Quote:
Originally Posted by Phil View Post
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
__________________

We help people pass actuarial exams.

Follow us on Twitter, Facebook, and LinkedIn.

Reply With Quote
  #34  
Old 08-10-2006, 01:52 PM
Phil's Avatar
Phil Phil is offline
Site Supporter
Site Supporter
 
Join Date: Sep 2001
Location: not on board the Enterprise
Posts: 5,667
Default

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.

Last edited by Phil; 08-10-2006 at 01:55 PM..
Reply With Quote
  #35  
Old 08-10-2006, 03:56 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,153
Default

Quote:
Originally Posted by Bama Gambler View Post
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.
Reply With Quote
  #36  
Old 08-10-2006, 06:19 PM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by whisper View Post
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
__________________
Brad Gile, FSA, MAAA
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA


Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater

Last edited by Brad Gile; 08-10-2006 at 06:38 PM.. Reason: tone things down a tad.
Reply With Quote
  #37  
Old 08-11-2006, 10:30 AM
Griffin 1's Avatar
Griffin 1 Griffin 1 is offline
Member
 
Join Date: Sep 2001
Location: This is the worst kind of discrimination. The kind against me.
Favorite beer: Grizzly. No, wait - polar bears.
Posts: 10,485
Default

Quote:
Originally Posted by Brad Gile View Post
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.
__________________
I've never heard of such a brutal and shocking injustice that I cared so little about.
Reply With Quote
  #38  
Old 08-11-2006, 02:33 PM
whisper's Avatar
whisper whisper is offline
Member
CAS AAA
 
Join Date: Jan 2002
Location: Chicago
Favorite beer: Hefewizen
Posts: 34,153
Default

Quote:
Originally Posted by Brad Gile View Post
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).
Reply With Quote
  #39  
Old 08-11-2006, 04:14 PM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by whisper View Post
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.

Brad
__________________
Brad Gile, FSA, MAAA
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA


Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater
Reply With Quote
  #40  
Old 08-11-2006, 04:39 PM
Griffin 1's Avatar
Griffin 1 Griffin 1 is offline
Member
 
Join Date: Sep 2001
Location: This is the worst kind of discrimination. The kind against me.
Favorite beer: Grizzly. No, wait - polar bears.
Posts: 10,485
Default

Quote:
Originally Posted by Brad Gile View Post
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?"
__________________
I've never heard of such a brutal and shocking injustice that I cared so little about.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


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


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.32192 seconds with 10 queries