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.


Reply
 
Thread Tools Display Modes
  #11  
Old 11-29-2017, 03:29 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by dumples View Post
Here's some code that I've written:
Thanks for sharing, dumples!
Reply With Quote
  #12  
Old 11-29-2017, 03:33 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,244
Default

Quote:
Originally Posted by Freebird View Post
Thanks for the tip, dumples.

I see what they were saying about declaring an object at the start. I do always use Option Explicit, so I am forced to declare variables. Will this require me to declare objects as well? I may not be understanding the concept yet, but early binding sounds like a good idea.
Your code is early binding - you use statements like

Dim rs As ADODB.Recordset
set rs = new ADODB.Recordset

Late binding would be

Dim rs as Object
set rs = CreateObject("ADODB.Recordset")

Early binding requires that your project has a reference (Options>references)
Late binding makes no requirement. One particular problem that I've encountered with Early Binding is other office applications. If you create a reference to a PPT 2013 library and someone opens your file with office 2010, that can lead to problems. The latebinding method looks the same either way. So like I said, it's fine to develop with early, but switch to late before you ship it to someone else.


Either way, all variables should be defined.
Reply With Quote
  #13  
Old 11-29-2017, 03:57 PM
act_123 act_123 is offline
Member
CAS
 
Join Date: Dec 2013
Posts: 2,087
Default

Quote:
Originally Posted by Freebird View Post
Thanks. That helps a lot.

Regarding Powerpivot, I am surprised that you said it was a lot cleaner and easier than a macro. I'm a pretty old-school guy, but I never thought macros would already be old school.

Seriously, though, I don't use pivot tables in Excel. I was wondering if PowerPivot has a long learning curve.

I use Excel 2016, and I was aware that PowerQuery was baked into Excel now, instead of being an add-in, like in previous versions. Is PowerPivot available in the base Excel 2016, or do I need to download an add-in?
Macros are not old school. I do a lot of vba programming.

What is cleaner is that you don't bog down your excel workbook with data since it is stored in the PowerPivot.

It is built into Excel 2016. You just need to go to add-ins and turn it on. Similar how you would turn on solver.

Regarding the learning curve, that is dependent on how you use PowerPivot. To bring in data is not so complicated.

The easiest way to get the data out of PowerPivot is with a pivottable. You can use OLAP functions that will do the same thing (similar to GetPivotData).
__________________
ACAS 7 8 9
Reply With Quote
  #14  
Old 11-29-2017, 04:28 PM
Abused Student's Avatar
Abused Student Abused Student is offline
Member
SOA
 
Join Date: Feb 2007
Location: The Eighth Circle of Hell
Favorite beer: Cold and lots of it
Posts: 42,143
Default

One way you could do this that might better suit your needs if you don't want it in the spreadsheet would be to do something like dimming an integer and then using an input box which will pop up a box when the code runs that you can then enter the year you want and then put that in to the sql.
__________________
GAME ON!!!!!!! Let your ness show. Join the D&D fun. Started but applications still accepted


Officially assigned the role of Dictator, 9/30/09. Bow to my whims.
Reply With Quote
  #15  
Old 11-29-2017, 05:37 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by dumples View Post
Your code is early binding - you use statements like

Dim rs As ADODB.Recordset
set rs = new ADODB.Recordset

Late binding would be

Dim rs as Object
set rs = CreateObject("ADODB.Recordset")

Early binding requires that your project has a reference (Options>references)
Late binding makes no requirement. One particular problem that I've encountered with Early Binding is other office applications. If you create a reference to a PPT 2013 library and someone opens your file with office 2010, that can lead to problems. The latebinding method looks the same either way. So like I said, it's fine to develop with early, but switch to late before you ship it to someone else.


Either way, all variables should be defined.
This is really good stuff, dumples. Thanks!

The entire actuarial department is now on 2016, and we don't normally have people outside the department run our macros. But I do see your point about late binding, and that's the way I think I'll go.
Reply With Quote
  #16  
Old 11-29-2017, 05:40 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by Abused Student View Post
One way you could do this that might better suit your needs if you don't want it in the spreadsheet would be to do something like dimming an integer and then using an input box which will pop up a box when the code runs that you can then enter the year you want and then put that in to the sql.
OK, yeah, that makes sense. Thanks for the suggestion!
Reply With Quote
  #17  
Old 11-29-2017, 05:47 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by act_123 View Post
Macros are not old school. I do a lot of vba programming.

What is cleaner is that you don't bog down your excel workbook with data since it is stored in the PowerPivot.

It is built into Excel 2016. You just need to go to add-ins and turn it on. Similar how you would turn on solver.

Regarding the learning curve, that is dependent on how you use PowerPivot. To bring in data is not so complicated.

The easiest way to get the data out of PowerPivot is with a pivottable. You can use OLAP functions that will do the same thing (similar to GetPivotData).
Very helpful, act_123. Thanks!

I don't actually use SQL itself, even though we still use Access a lot. Thus, I can't write OLAP functions, but it looks like I am good to go with PowerPivot.
Reply With Quote
  #18  
Old 11-30-2017, 06:35 AM
BadWolf BadWolf is offline
CAS
 
Join Date: Sep 2012
College: Kellogg School of Management, Northwestern University
Posts: 2
Default

Quote:
Originally Posted by Freebird View Post
This is really good stuff, dumples. Thanks!

The entire actuarial department is now on 2016, and we don't normally have people outside the department run our macros. But I do see your point about late binding, and that's the way I think I'll go.

I have many applications that are distributed to users where I don't know their exact Excel/Office setup, so late-binding is a must. But early-binding is very helpful during development. I use conditional compilation statements to switch my application back and forth between the two, so something like:

#IF lateBinding=0 then
dim rs as ADODB.Recordset
set rs = New ADODB.Recordset
#ELSE
dim rs as Object
set rs = CreateObject("ADODB.Recordset")
#END IF

Then, in the VBA Project Properties, I set the Conditional Compilation Argument to 0 or 1 (anything other than 0 really, but 1 in practice), so something like "lateBinding=1".

Sometimes there are other parameters I want to vary between development/testing and Production. In that case I'll set up a Conditional Compilation parameter like "Prod" and use that instead of the lateBinding parameter.
Reply With Quote
  #19  
Old 11-30-2017, 06:41 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Quote:
Originally Posted by BadWolf View Post
I have many applications that are distributed to users where I don't know their exact Excel/Office setup, so late-binding is a must.
That's a great suggestion, BadWolf. Thanks!

You might consider changing your screen name to GoodWolf.
Reply With Quote
  #20  
Old 12-05-2017, 05:30 PM
Freebird's Avatar
Freebird Freebird is offline
Member
SOA AAA
 
Join Date: Dec 2001
Location: Topeka, Kansas
College: Graduated from Ohio State too many years ago to remember
Favorite beer: Newcastle
Posts: 727
Default

Now that I have all this helpful information, I went back to Excel and Access to build the macro. The first thing I found out was that I can't find where the parameter is input in Access. The Access database is several years old, and the person who built it is no longer here.

I looked at the query I was trying to import into Excel, and could find no "Current Year" field, which is the parameter prompt. When I run the query in Access, the first thing that happens is a pop-up message box that shows this:

Enter Parameter Value ?
Current Year

I then enter 2017 and the query runs ok. However, I get exactly the same query results if I put in 2014 for the current year, which leads me to believe that the parameter has no effect, and it may be some kind of bug in Access.

Does anybody have any suggestions about where to go from here?
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 03:19 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.33338 seconds with 9 queries