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


Upload your resume securely at https://www.dwsimpson.com
to be contacted when new jobs meet your skills and objectives.


View Poll Results: What is the best way to learn VBA?
Book 109 35.97%
Help 48 15.84%
Internet 152 50.17%
Macro Recorder 115 37.95%
Multiple Choice Poll. Voters: 303. You may not vote on this poll

Reply
 
Thread Tools Search this Thread Display Modes
  #111  
Old 11-14-2014, 01:41 PM
oedipus rex's Avatar
oedipus rex oedipus rex is offline
Member
SOA AAA
 
Join Date: Nov 2002
Favorite beer: too many to list here
Posts: 15,352
Default

Please please please, above all if you are going to write VBA code, please document it thoroughly. And before writing ANY non trivial (more than 10 lines or so) code at all, please plan out your program.
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
Reply With Quote
  #112  
Old 11-14-2014, 01:51 PM
actuary21c actuary21c is offline
Member
 
Join Date: Sep 2009
Location: UK
Studying for double bass & jazz piano
Posts: 1,786
Default

Quote:
Originally Posted by oedipus rex View Post
Please please please, above all if you are going to write VBA code, please document it thoroughly. And before writing ANY non trivial (more than 10 lines or so) code at all, please plan out your program.


Also: choose meaningful names for your subroutines, functions.variables, structures and classes.
Bonus: where the names are meaningful, sometimes comments are superfluous (because you would basically just be repeating yourself).

PS Also: try and follow some naming convention (so that you know at a glance what type your variables are and their scope [local, module, static or -heaven forbid, global), indent your code (including for if statements, for next loops etc), and use error trapping/handling.
__________________
Spoiler:

Quote:
Originally Posted by Brock View Post
a21c is one of the better posters on the AO. That's not saying he's good.

UK software developer, actuary, musician, atheist. All posts in a personal capacity (unless explicitly stated otherwise in the post).

Last edited by actuary21c; 11-14-2014 at 02:05 PM.. Reason: Added the PS
Reply With Quote
  #113  
Old 11-14-2014, 01:56 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,247
Default

Quote:
Originally Posted by oedipus rex View Post
Please please please, above all if you are going to write VBA code, please document it thoroughly. And before writing ANY non trivial (more than 10 lines or so) code at all, please plan out your program.
Is this in response to this dictionary class or just a general point?
Reply With Quote
  #114  
Old 11-14-2014, 02:02 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 84,215
Blog Entries: 6
Default

Quote:
Originally Posted by oedipus rex View Post
Please please please, above all if you are going to write VBA code, please document it thoroughly. And before writing ANY non trivial (more than 10 lines or so) code at all, please plan out your program.
https://www.soa.org/library/newslett...ly/CSN0707.pdf

Starting on page 8, but my comments really start on page 9.

tl; dr version:

Quote:
Goals of Actuarial Programming

• In numerical programming I have various goals to attend to: The program calculates
what it’s supposed to.

• The program does its job in as short amount of time as possible, using the fewest possible resources.

• The program is easy to modify.

• The program has “mobile code.”
Quote:
Tips for Good Numerical Programming

1. Organize your programming flow well.

2. Create self-documenting code with good style and naming conventions.

3. Make it modular.

4. Don’t hardcode anything.

5. Have some sort of version control.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #115  
Old 11-14-2014, 02:16 PM
oedipus rex's Avatar
oedipus rex oedipus rex is offline
Member
SOA AAA
 
Join Date: Nov 2002
Favorite beer: too many to list here
Posts: 15,352
Default

Quote:
Originally Posted by dumples View Post
Is this in response to this dictionary class or just a general point?
A general point that I can't emphasize enough. It might take you a month to write a VBA program that you use for a few years, then when someone else takes it over it can become useless as no one understands how to modify it for changing inputs. I've seen this happen several times.
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
Reply With Quote
  #116  
Old 11-14-2014, 02:17 PM
oedipus rex's Avatar
oedipus rex oedipus rex is offline
Member
SOA AAA
 
Join Date: Nov 2002
Favorite beer: too many to list here
Posts: 15,352
Default

Quote:
Originally Posted by campbell View Post
https://www.soa.org/library/newslett...ly/CSN0707.pdf

Starting on page 8, but my comments really start on page 9.

tl; dr version:
That would make for a nice quick ASOP on programming...
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
Reply With Quote
  #117  
Old 11-14-2014, 02:33 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 84,215
Blog Entries: 6
Default

We don't need more ASOPs.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #118  
Old 11-15-2014, 01:46 PM
actuary21c actuary21c is offline
Member
 
Join Date: Sep 2009
Location: UK
Studying for double bass & jazz piano
Posts: 1,786
Default Comments/feedback on my VBA course are welcome, as well as questions

I should have said (and have now edited the post to add this) that comments/feedback on my VBA course are welcome.
__________________
Spoiler:

Quote:
Originally Posted by Brock View Post
a21c is one of the better posters on the AO. That's not saying he's good.

UK software developer, actuary, musician, atheist. All posts in a personal capacity (unless explicitly stated otherwise in the post).
Reply With Quote
  #119  
Old 11-17-2014, 05:30 AM
actuary21c actuary21c is offline
Member
 
Join Date: Sep 2009
Location: UK
Studying for double bass & jazz piano
Posts: 1,786
Default VBA: how to select existing or new files using Common Dialogs

I've added a new blog post (http://www.actuary21c.com/?p=3620) with free example code as to how to make it easy for a user to correctly select an existing (or new) file in VBA:

Quote:
Here is some code to help you solve two scenarios in VBA:

1. You want to read data from another file (e.g. an Excel file, or an Access database)
2. You have completed your calculations and want to save the results to an existing or new file (e.g. an Excel, Word or Access file).
How do you make it as easy as possible for the user to select the desired file and its path (ie in which folder the file resides or – for a new file – should reside)? I’ve posted some example code here using Common Dialogs (the standard Windows Explorer type forms that come up when you save or open files in Microsoft applications).
As always, comments/feedback/questions welcome, either on the blog or here if you prefer.
__________________
Spoiler:

Quote:
Originally Posted by Brock View Post
a21c is one of the better posters on the AO. That's not saying he's good.

UK software developer, actuary, musician, atheist. All posts in a personal capacity (unless explicitly stated otherwise in the post).
Reply With Quote
  #120  
Old 11-18-2014, 01:40 PM
deekay deekay is offline
Member
SOA
 
Join Date: Jun 2014
Favorite beer: IPAs and Wits
Posts: 108
Default

I really recommend this free online guide, i feel like i learned a good hunk of VBA in a few hours. Of course try to implement some code yourself aside from simply reading, and take some notes

http://www.excel-easy.com/vba.html
__________________
P FM MFE C PA LTAM
FAP Modules
Statistics Economics Finance
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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 09:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, 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.47469 seconds with 10 queries