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



Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 06-02-2014, 11:51 AM
SpaceActuary's Avatar
SpaceActuary SpaceActuary is offline
Member
 
Join Date: Apr 2010
College: Drake U
Posts: 601
Default

A couple of comments:
  1. I always use "Long" variables instead of "Integers". It used to be that you needed to be careful about how much memory you're using. Nowadays, if you're running into those same issues, you probably shouldn't be using Excel in the first place.
  2. When declaring arrays, it's good practice to declare both the upper and lower bound of each dimension, like this:
    Code:
    Dim MyArray(0 to 2, 0 to 3) As Long
    That way, it's clear what the lower bound of the array is.
  3. When looping over arrays, use LBound() and UBound() as your loop ranges, like this:
    Code:
    For i = LBound(MyArray, 1) to UBound(MyArray, 1)
    For j = LBound(MyArray, 2) to UBound(MyArray, 2)
    '* Loop contents
    Next j
    Next i
    This prevents you from having to find all of the places you used the array size throughout your code if you decide later to increase/decrease the size of the array.
  4. If I'm doing a loop that has a "rows" and "columns", I use the index variables r & c instead of i & j, simply to help me remember which is which.
  5. See my earlier post about setting cell values all at once.
A lot of these are personal preferences, but I've also written a lot of VBA code, so hopefully you'll find them helpful.
__________________
And all this science, I don't understand; It's just my job five days a week...a rocket man, a rocket man...

"I'm from Iowa. I only work in space." - Adm. James T. Kirk
Reply With Quote
  #12  
Old 06-02-2014, 12:02 PM
kn005 kn005 is offline
Member
 
Join Date: Jul 2010
Favorite beer: I don't drink beer!! Johnny Walker Black Label it is
Posts: 346
Default

Quote:
Originally Posted by GreekSquared View Post
If you are really interested in VBA and have elementary programming knowledge get Walkenbach's power programming with VBA. If you need a softer intro, get the for dummies version. All these basics will be answered there.
I'm currently using vba for dummies and another online resource. so far vba for dummies is not as good as i thought. just my opinion. Too much writing and less practice. Any other good resources you recommend?
Reply With Quote
  #13  
Old 06-02-2014, 12:03 PM
kn005 kn005 is offline
Member
 
Join Date: Jul 2010
Favorite beer: I don't drink beer!! Johnny Walker Black Label it is
Posts: 346
Default

Quote:
Originally Posted by SpaceActuary View Post
A couple of comments:
  1. I always use "Long" variables instead of "Integers". It used to be that you needed to be careful about how much memory you're using. Nowadays, if you're running into those same issues, you probably shouldn't be using Excel in the first place.
  2. When declaring arrays, it's good practice to declare both the upper and lower bound of each dimension, like this:
    Code:
    Dim MyArray(0 to 2, 0 to 3) As Long
    That way, it's clear what the lower bound of the array is.
  3. When looping over arrays, use LBound() and UBound() as your loop ranges, like this:
    Code:
    For i = LBound(MyArray, 1) to UBound(MyArray, 1)
    For j = LBound(MyArray, 2) to UBound(MyArray, 2)
    '* Loop contents
    Next j
    Next i
    This prevents you from having to find all of the places you used the array size throughout your code if you decide later to increase/decrease the size of the array.
  4. If I'm doing a loop that has a "rows" and "columns", I use the index variables r & c instead of i & j, simply to help me remember which is which.
  5. See my earlier post about setting cell values all at once.
A lot of these are personal preferences, but I've also written a lot of VBA code, so hopefully you'll find them helpful.
Thanks alot man. I appreciate the help. what books or materials do you suggest for someone who wants to be an expert in vba?
Reply With Quote
  #14  
Old 06-02-2014, 12:16 PM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,134
Default

add this to your loop

MyArray(i,j) = (i+1)*10
Reply With Quote
  #15  
Old 06-02-2014, 01:07 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 37,509
Default

Quote:
Originally Posted by SpaceActuary View Post
A couple of comments:
  1. I always use "Long" variables instead of "Integers". It used to be that you needed to be careful about how much memory you're using. Nowadays, if you're running into those same issues, you probably shouldn't be using Excel in the first place.
A lot of these are personal preferences, but I've also written a lot of VBA code, so hopefully you'll find them helpful.
I agree that the array type should be long, but even when space is no object, I think that subscripts should be Integer. (Maybe my old age is showing. . .)
__________________
Carol Marler, "Just My Opinion"

Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as non-actuarial.


My latest favorite quotes, updated Apr 5, 2018.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
Originally Posted by Dr T Non-Fan View Post
Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
Originally Posted by DoctorNo View Post
Depends upon the employer and the situation.
Quote:
Originally Posted by Sredni Vashtar View Post
I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
Reply With Quote
  #16  
Old 06-02-2014, 01:23 PM
sweetiepie sweetiepie is offline
Member
SOA
 
Join Date: Apr 2007
Location: 666 desdemona
Favorite beer: porters
Posts: 8,619
Blog Entries: 81
Default

Quote:
Originally Posted by kn005 View Post
Thanks alot man. I appreciate the help. what books or materials do you suggest for someone who wants to be an expert in vba?
Read a bunch of VBA threads on this forum. It won't make you an 'expert' (you won't learn how to define an object), but it will teach best practices for the kind of programming we do, which is probably more important... What we do is generally either algorithms (loops and arrays), working with a database, and/or finding obscure functions to automate.

Last edited by sweetiepie; 06-02-2014 at 01:27 PM..
Reply With Quote
  #17  
Old 06-02-2014, 01:31 PM
bensonby2 bensonby2 is offline
Member
SOA
 
Join Date: Jan 2013
College: The Chinese University of Hong Kong
Posts: 92
Default

Another tips:
I believe you are thinking it as "I want the first row to be 10; second row to be 20; third row to be 30". (if this is not what you want to achieve, please just ignore my comment)

In this way, I advise you try to code in the way you think, without the need to specify so many repeated values.

Code:
For i = 1 To 3
  Cells(i, 1).Resize(, 4) = i * 10
Next i
__________________
Excel for Actuaries
http://host.tungsten.hk/bensonby/wordpress/?tag=actuary
Reply With Quote
  #18  
Old 06-02-2014, 03:15 PM
SpaceActuary's Avatar
SpaceActuary SpaceActuary is offline
Member
 
Join Date: Apr 2010
College: Drake U
Posts: 601
Default

Quote:
Originally Posted by JMO View Post
I agree that the array type should be long, but even when space is no object, I think that subscripts should be Integer. (Maybe my old age is showing. . .)
Integers overflow at 32,767 (2^15 - 1, since an integer is 16 bits, minus one bit for the sign).

Excel 2003 had 65,536 (2^16) rows. I spent a lot of time working with workbooks that had >32,767 rows, so I'd often have overflow issues, and I started using Longs for everything out of habit.

The potential for overflow is even greater now that Excel 2007+ has 1,048576 (2^20) rows. Of course, if you're using that many rows you're doing some other stuff wrong.

There might be some slight efficiency to be gained by using data types consistent with the "word" of your system. For example, if you're using an integer (16-bit) datatype on a 32-bit system, then the CPU is implicitly converting them to long (32-bit) variables. On a 64-bit system (& with 64-bit Office), there's a new LongLong (64-bit) datatype. For more info, Tushar Mehta has a pretty informative post here:
http://www.tushar-mehta.com/publish_...10%20VBA.shtml

I won't go as far as to say it's "wrong" or "bad" to use Integer datatypes for index/subscript variables, but I'm curious as to your reasons for saying that subscripts "should" be Integers.
__________________
And all this science, I don't understand; It's just my job five days a week...a rocket man, a rocket man...

"I'm from Iowa. I only work in space." - Adm. James T. Kirk
Reply With Quote
  #19  
Old 06-02-2014, 03:35 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Quote:
Originally Posted by kn005 View Post
Ok so i declared both i and j as integers making the code look like this and it worked. I'm not sure if that's a good way to do things. Any advice?
Being explicit is a good way to do things regardless of the programming language. Why leave anything in your code ambiguous and run the risk of getting the wrong answer? Also, being explicit aids in documenting your code.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #20  
Old 06-02-2014, 05:15 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

if you don't define your variables, including specifying their type, the type of your variables will become Variants instead, which are horribly inefficient.
__________________
Life can only be understood backwards; but it must be lived forwards. --S.K.
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 08:30 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.48644 seconds with 11 queries