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)

oedipus rex 11-14-2014 01:41 PM

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.

actuary21c 11-14-2014 01:51 PM

Quote:

Originally Posted by oedipus rex (Post 7780323)
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.

:iatp:

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.

dumples 11-14-2014 01:56 PM

Quote:

Originally Posted by oedipus rex (Post 7780323)
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?

campbell 11-14-2014 02:02 PM

Quote:

Originally Posted by oedipus rex (Post 7780323)
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.

oedipus rex 11-14-2014 02:16 PM

Quote:

Originally Posted by dumples (Post 7780401)
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.

oedipus rex 11-14-2014 02:17 PM

Quote:

Originally Posted by campbell (Post 7780435)
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...

campbell 11-14-2014 02:33 PM

We don't need more ASOPs.

actuary21c 11-15-2014 01:46 PM

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.

actuary21c 11-17-2014 05:30 AM

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.

deekay 11-18-2014 01:40 PM

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


All times are GMT -4. The time now is 04:35 AM.

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

Page generated in 0.14152 seconds with 9 queries