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

DW Simpson
Actuarial Jobs

Visit our site for the most up to date jobs for actuaries.

Actuarial Salary Surveys
Property & Casualty, Health, Life, Pension and Non-Tradtional Jobs.

Actuarial Meeting Schedule
Browse this year's meetings and which recruiters will attend.

Contact DW Simpson
Have a question?
Let's talk.
You'll be glad you did.


View Poll Results: What is the best way to learn VBA?
Book 107 36.64%
Help 48 16.44%
Internet 144 49.32%
Macro Recorder 112 38.36%
Multiple Choice Poll. Voters: 292. You may not vote on this poll

Reply
 
Thread Tools Display Modes
  #131  
Old 07-27-2015, 09:39 AM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,228
Default

and while you're there turn off "Auto Syntax Check".

This controls the pop up box that says you made an error every time that you move to a new line. With it off, the line will become red, telling you that there is an error. With it on, it turns red and it pops up a box saying "you have made an error" without adding any real information. This is very tedious if you are just moving to another line to copy something.
Reply With Quote
  #132  
Old 07-27-2015, 09:43 AM
ObsidianBlackbirdMcKnight ObsidianBlackbirdMcKnight is offline
Member
 
Join Date: Jun 2014
Studying for gothic threeway
Posts: 778
Default

Quote:
Originally Posted by dumples View Post
and while you're there turn off "Auto Syntax Check".

This controls the pop up box that says you made an error every time that you move to a new line. With it off, the line will become red, telling you that there is an error. With it on, it turns red and it pops up a box saying "you have made an error" without adding any real information. This is very tedious if you are just moving to another line to copy something.
Never thought to look and see if there was an option to turn that off. You get all my internets points for the week.
Reply With Quote
  #133  
Old 07-29-2015, 11:25 AM
actu artist actu artist is offline
Member
SOA
 
Join Date: May 2008
Location: Florida
Studying for completed ASA!
College: UNL alumni
Posts: 90
Wink VBA Book

Can the forum please share their favorite book for learning VBA?

TIA
Reply With Quote
  #134  
Old 07-29-2015, 11:28 AM
ObsidianBlackbirdMcKnight ObsidianBlackbirdMcKnight is offline
Member
 
Join Date: Jun 2014
Studying for gothic threeway
Posts: 778
Default

For Excel it's Power Programming by Walkenbach
Reply With Quote
  #135  
Old 10-16-2015, 07:56 PM
redearedslider's Avatar
redearedslider redearedslider is online now
Member
CAS
 
Join Date: Oct 2015
Posts: 9,411
Default

Any recommendations for a second book on VBA? I've worked through one of Walkenbach's books and I'm fairly comfortable with throwing together simple macros but I want to move beyond hacking together frankencode and perhaps doing things in a more efficient or cleaner manner. Should I be looking into a more general programming language instead to learn best practices like python?
__________________
P FM MFE C 5 S 6 ACAS MAAA 7 8 9 FCAS FSA MBA CPA CFA CERA MS PhD

VEE Econ VEE Corporate Finance

OC 1 OC2 COP

Quote:
Originally Posted by Abraham Weishaus View Post
ASM does not have a discussion of stimulation, but considering how boring the manual is, maybe it would be a good idea.
Reply With Quote
  #136  
Old 10-16-2015, 08:37 PM
clarinetist clarinetist is offline
Member
 
Join Date: Aug 2011
Studying for Rcpp, Git
Posts: 6,625
Default

Quote:
Originally Posted by redearedslider View Post
Any recommendations for a second book on VBA? I've worked through one of Walkenbach's books and I'm fairly comfortable with throwing together simple macros but I want to move beyond hacking together frankencode and perhaps doing things in a more efficient or cleaner manner. Should I be looking into a more general programming language instead to learn best practices like python?
I would go with what you're thinking, as well as getting 101 Ready-To-Use Excel Macros. This was a lifesaver for me in my actuarial position.
__________________
If you want to add me on LinkedIn, PM me.

Why I hate Microsoft Access.

Studying/Reading: Statistical Inference, Bayesian Data Analysis
Reply With Quote
  #137  
Old 10-22-2015, 06:30 PM
actuary21c actuary21c is offline
Member
 
Join Date: Sep 2009
Location: UK
Studying for double bass & jazz piano
Posts: 1,786
Default VBA is OK, but I would recommend moving to Python (or VB.net or C#)

VBA is ok if you want to do something quick and dirty, but if you want to invest for the future, I recommend ALSO starting to use a proper programming language, eg


Python (http://inventwithpython.com/*seems to have some excellent books on using python, including (in "Automate the boring stuff with Python") reading from and writing to Excel.)

VB.net (the easiest way to work with Excel in a .net language, because it is closest to VBA), or

C# (less closely integrated with Excel, but I much prefer it over VB.net because I can keep things tidy by limiting the scope of variables to as small as possible via the use of { } within a method.

I've just ported a project written 12 years ago in VBA (calling some C++ code) to C# (a tedious exercise [which I partially automated using a VBA to C# conversion program that I wrote a few years ago] but the code is much much more maintainable and extensible).

For all new projects, I use C# (or possibly VB.net for VSTO [Visual Studio Tools for Office] projects, but the deployment of those is still very buggy and poorly documented). But at the same time I'm experimenting with using Python, IronPython (a version of Python that can call .net code) and PowerShell for scripting purposes (scripting is a way of extending your code so that people who use it can customise it, thus making your code more valuable/useful for your clients). (clients can include users within your own organisation, although I accept that in most actuarial corporate environments, only a few users - if any - are sophisticated enough to want to / have the ability to - extend/customise apps).
__________________
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
  #138  
Old 10-22-2015, 06:44 PM
redearedslider's Avatar
redearedslider redearedslider is online now
Member
CAS
 
Join Date: Oct 2015
Posts: 9,411
Default

Quote:
Originally Posted by actuary21c View Post
VBA is ok if you want to do something quick and dirty, but if you want to invest for the future, I recommend ALSO starting to use a proper programming language, eg


Python (http://inventwithpython.com/*seems to have some excellent books on using python, including (in "Automate the boring stuff with Python") reading from and writing to Excel.)

VB.net (the easiest way to work with Excel in a .net language, because it is closest to VBA), or

C# (less closely integrated with Excel, but I much prefer it over VB.net because I can keep things tidy by limiting the scope of variables to as small as possible via the use of { } within a method.

I've just ported a project written 12 years ago in VBA (calling some C++ code) to C# (a tedious exercise [which I partially automated using a VBA to C# conversion program that I wrote a few years ago] but the code is much much more maintainable and extensible).

For all new projects, I use C# (or possibly VB.net for VSTO [Visual Studio Tools for Office] projects, but the deployment of those is still very buggy and poorly documented). But at the same time I'm experimenting with using Python, IronPython (a version of Python that can call .net code) and PowerShell for scripting purposes (scripting is a way of extending your code so that people who use it can customise it, thus making your code more valuable/useful for your clients). (clients can include users within your own organisation, although I accept that in most actuarial corporate environments, only a few users - if any - are sophisticated enough to want to / have the ability to - extend/customise apps).
Could you give an example of what sort of projects you do in C#? I went back and forth for a while between picking C# or Python as a language to learn and I ended up with Python because I plan on doing a lot of data projects and python is currently the go to language for that.
__________________
P FM MFE C 5 S 6 ACAS MAAA 7 8 9 FCAS FSA MBA CPA CFA CERA MS PhD

VEE Econ VEE Corporate Finance

OC 1 OC2 COP

Quote:
Originally Posted by Abraham Weishaus View Post
ASM does not have a discussion of stimulation, but considering how boring the manual is, maybe it would be a good idea.
Reply With Quote
  #139  
Old 10-22-2015, 07:04 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 redearedslider View Post
Could you give an example of what sort of projects you do in C#? I went back and forth for a while between picking C# or Python as a language to learn and I ended up with Python because I plan on doing a lot of data projects and python is currently the go to language for that.
C# is still my favourite language (having used all of Basic, Fortran, C, C++, VBA, Visual Basic, VB.net, Perl, Php, F#, Javascript and more recently Scratch and Python). I find it very neat and tidy and gives me full access to the very powerful .net framework. Having said that IronPython looks promising, but I like the fact that C# is strongly typed (so the compiler helps me a lot in my coding).

I have done lots of things in c#, in fact one of the most enjoyable aspects of my work as a software developer is the variety, here are a few from the last few years:

Program to partially convert VBA to C#
Program to partially convert Fortran to C#
Pensions valuation / cashflow projection software (see pensionsconcerto.com) (NB currently for UK pensions, not US!)
Wealth planner (to help UK Independent Financial Advisors project cashflows and assets/liabilities for individuals, or numerate individuals to do that themselves) (ifa.tools)
An online app to help parents and their children manage pocket money /learn to save by having virtual accounts (webpocketmoney.com)
An online time recording system (on the web, but only used internally at the moment)
An online accounting system for charities/membership organisations
Gratuity/Leave encashment valuation calculation software (for use in India an elsewhere)
Loads of websites

I could probably do all the above in Python (probably IronPython because I would want to use the .net framework).

For those thinking of just using VBA:
It would be very hard (if not impossible: eg to run a website) to do most of the above using VBA. (I know some of it IS possible, eg about 20-10 years ago most of the code I wrote had some VBA or Visual Basic for the front end, with C++ as the back end, but it is so much easier to do things using c# or another professional language).

PS: you mentioned data, and I realised I hadn't covered that aspect. I think most programming these days requires interaction with a database, data queries, manipulation and reporting, and again I found the .net framework (and particularly LINQ) very useful to do that.
__________________
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; 10-22-2015 at 07:18 PM.. Reason: Added that it is UK pensions, not US software. Also a PS re data
Reply With Quote
  #140  
Old 05-14-2016, 07:36 AM
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: 75,796
Blog Entries: 6
Default

I've got some webcasts coming up....
Spoiler:


June 8, 2016, 1:00 – 2:30 EST

1.8 SOA/EA CPD, CAS CE Credits/Training Hours


For the complete beginner in VBA, this session is going to look at the basics of how to use VBA in Microsoft Excel. For those with some VBA experience, this session will give you the comprehensive overview needed to fully understand what VBA can do for you. Instructor Mary Pat Campbell will provide an overview of VBA's features so you can take the knowledge and apply it to your workflow.

Specific topics covered will include:
  • Setting up the VBA environment in Excel
  • Navigating around the VBA editor
  • VBA "Stuff": Objects, Methods, and Events
  • Using the Macro recorder
  • Writing your first subroutine
  • Writing your first user-defined function
  • Resources for growth

Please Note: Early Registration pricing expires on May 25, 2016 for this webinar.

------------


June 23, 2016, 1:00 – 2:30 EST

1.8 SOA/EA CPD, CAS CE Credits/Training Hours


Need to update that monthly report in Excel that you mess with manually? Why not get VBA to do it for you! Assuming no programming experience on the part of the participant, we'll look at the steps of using VBA to automating processes:
  • Quick refresher on VBA subroutines and the macro recorder
  • Walking through macro results - what to keep, what to throw
  • Loops and Control Logic
  • Don't copy that range! How to transfer results best
  • Making your sub run faster
  • Working with Events
  • More resources

Please Note: Early Registration pricing expires on June 9, 2016 for this webinar.

------------

The registration fee includes access to the webcast recording and supplemental materials for up to 180 days.

Discount for Full-Time College Students:
Full-time university students are eligible for a 30% discount. In order to access this discount, please e-mail a copy of your current semester's registration from your university e-mail address to OnlineCourses@ActexMadRiver.com for further instructions.


Sign up today on ActexMadRiver.com!
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 10:27 AM.


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.31860 seconds with 10 queries