+ Reply to Thread
Results 1 to 7 of 7

VBA Project for Programing for Actuary Class

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    Boston,MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    VBA Project for Programing for Actuary Class

    Would not let me upload the file but I can send it to you if this seems like something you can do easily. my email is [email protected] if you have any more questions. This is do very soon and I need some serious help, I much appreciate it!!


    The following are coding assignments. Modules are provided to write
    all necessary code (mdlPart1, mdlPart2, and mdlPart3). Please perform the tasks below,
    write the code to the best of your ability, including comments as you see fit.

    Part 1 (5 Points):
    1) You receive data in a file that contains dates in the following format:
    YYYYMMDD Sample: 20100101
    a) Write a function in mdlPart1 that will convert the date to
    date format: MM/DD/YYYY

    b) Call this function "DateConverter", with one pass-through variable called
    "dtYYYYMMDD".

    Sample Data Results:
    20010317
    20120809
    20130430

    c) Fill in range C18 through C20 with the function to display results
    of converting range B18 through B20.

    Part 2 (5 Points):
    2) Write a process that uses For… Next loops and an array to fill range B3:D10
    of the Part2 worksheet. Write and save your code in mdlPart2.
    a) To create the array, use the following code:
    Public strCellRefs(1 to 8, 1 to 3) As String
    Note: This is a Public variable, so it goes outside the subroutine code.
    b) Fill the array with the following text:
    Row: (row reference); Column: (column reference)
    strCellRefs(1,1) would contain the text: "Row: 3; Column: 2".
    strCellRefs(8,3) would contain the text: "Row: 10; Column: 4".
    Again, fill this array using For… Next loops.
    c) Using a single command, paste the entire array into the cells B3:D10 on the
    worksheet "Part2"

    Part 3 (10 Points):
    3) Write code in VBA that does the following with data on the Part3 tab:
    Code for this project is to be stored in mdlPart3.
    a) Creates a variable called "lngLastRow", and stores the row number of the
    last row of data in column A of the Part3 worksheet.
    Use the command ".end(xlDown" or ".end(xlUp)" to accomplish this.
    b) Creates a 12x12 array called "dblLagTriangle".
    c) Adds the claims in Part3 to the correct location in the array from part b.
    For instance $661.93 would be placed in the array position (1,3).
    Following that, $762.00 would be added to that same array position.
    d) Posts the final resulting array in I4:T15 of the Part3 tab.
    e) Creates a messagebox to the user that states the macro has completed.






    Part 2 Spread Sheet

    Fill in these cells with the Cell Reference, listed as "Row: x; Column: y":









    For example, range B13 would look like this:
    Row: 13; Column: 2


    Part 3 Spread Sheet

    Claims data: Total: $59,097.11 Total Check: 1,423.91 Date_Start Date_End Month
    Use this data to fill the lag triangle to the right. Paid Month 1/1/12 1/31/12 1
    Claim_ID Date_Incurred Date_Paid Billed_Amt Incurred Month 1 2 3 4 5 6 7 8 9 10 11 12 2/1/12 2/29/12 2
    835385 1/7/12 3/5/12 $661.93 1 1,423.91 3/1/12 3/31/12 3
    329221 1/8/12 3/9/12 $762.00 2 4/1/12 4/30/12 4
    366885 1/5/12 3/9/12 $888.73 3 5/1/12 5/31/12 5
    348844 1/4/12 3/12/12 $543.73 4 6/1/12 6/30/12 6
    884768 1/14/12 3/24/12 $745.97 5 7/1/12 7/31/12 7
    115285 1/12/12 3/24/12 $1,040.52 6 8/1/12 8/31/12 8
    994943 1/13/12 3/25/12 $788.38 7 9/1/12 9/30/12 9
    266968 1/14/12 3/25/12 $110.24 8 10/1/12 10/31/12 10
    585292 1/25/12 3/26/12 $86.26 9 11/1/12 11/30/12 11
    943221 1/2/12 3/30/12 $978.90 10 12/1/12 12/31/12 12
    305591 3/13/12 3/31/12 $1,151.37 11 1/1/13 13
    513807 1/21/12 3/31/12 $908.78 12
    438432 1/14/12 4/5/12 $524.70
    141164 1/20/12 4/6/12 $635.83
    735894 4/7/12 4/23/12 $909.87
    935419 2/28/12 4/26/12 $875.79
    989455 2/25/12 4/28/12 $307.11
    984061 4/1/12 5/10/12 $627.74
    817531 3/5/12 5/11/12 $697.05
    666218 3/4/12 5/11/12 $192.74
    319254 2/18/12 5/15/12 $602.46
    473002 3/2/12 5/15/12 $772.57
    372787 1/8/12 5/18/12 $495.57
    654002 4/24/12 5/25/12 $600.73
    747537 3/13/12 5/29/12 $809.51
    219837 3/1/12 5/30/12 $237.09
    120539 3/24/12 6/4/12 $458.87
    231830 5/4/12 6/7/12 $303.38
    639460 3/18/12 6/8/12 $582.25
    658247 3/10/12 6/9/12 $694.27
    923726 4/19/12 6/17/12 $1,192.03
    268899 2/18/12 6/19/12 $298.75
    183119 4/9/12 6/19/12 $356.36
    140469 3/17/12 6/19/12 $706.91
    263616 4/10/12 6/20/12 $639.01
    470158 4/14/12 6/21/12 $871.42
    517174 3/17/12 6/22/12 $926.14
    584761 5/4/12 6/24/12 $737.25
    719278 4/1/12 6/25/12 $1,012.70
    795811 5/22/12 6/29/12 $1,118.03
    673505 4/15/12 6/30/12 $498.90
    732059 4/15/12 7/2/12 $1,017.17
    776009 4/7/12 7/5/12 $482.79
    164799 4/25/12 7/6/12 $826.66
    787284 4/15/12 7/6/12 $286.72
    708952 5/11/12 7/13/12 $95.99
    656928 4/20/12 7/23/12 $636.86
    897125 5/29/12 7/25/12 $349.87
    426400 6/8/12 8/3/12 $1,150.61
    692447 6/16/12 8/3/12 $286.60
    319139 5/22/12 8/7/12 $815.74
    214789 7/4/12 8/9/12 $673.29
    303869 5/13/12 8/14/12 $989.33
    867235 5/21/12 8/14/12 $557.85
    698854 4/12/12 8/19/12 $440.14
    590013 6/21/12 8/20/12 $855.45
    758304 6/3/12 8/20/12 $1,068.33
    944825 5/29/12 8/21/12 $633.68
    578890 6/28/12 8/25/12 $492.80
    639506 6/21/12 8/28/12 $374.96
    655078 6/29/12 8/30/12 $433.87
    467734 6/25/12 9/2/12 $663.18
    681826 6/13/12 9/4/12 $331.79
    531556 6/29/12 9/8/12 $201.65
    405671 9/2/12 9/20/12 $83.85
    945821 6/30/12 9/23/12 $1,197.00
    861568 8/16/12 10/4/12 $193.12
    504616 6/20/12 10/8/12 $242.48
    174515 9/9/12 10/9/12 $624.76
    184664 9/5/12 10/12/12 $198.12
    250981 8/22/12 10/24/12 $164.62
    290381 8/8/12 10/27/12 $1,190.05
    466633 9/5/12 10/28/12 $928.31
    867344 8/3/12 10/31/12 $815.44
    778652 9/4/12 10/31/12 $770.41
    518056 9/5/12 11/8/12 $521.14
    188847 10/7/12 11/10/12 $1,116.76
    976994 9/13/12 11/11/12 $103.56
    473473 8/14/12 11/12/12 $465.20
    660565 8/12/12 11/13/12 $366.50
    187204 9/14/12 11/15/12 $823.26
    373863 8/15/12 11/16/12 $1,022.50
    327849 9/6/12 11/21/12 $795.10
    962591 8/27/12 11/23/12 $199.42
    784380 9/7/12 11/24/12 $120.32
    438886 9/26/12 11/27/12 $423.21
    997673 10/1/12 12/4/12 $1,089.16
    430657 10/17/12 12/5/12 $100.43
    923227 9/15/12 12/8/12 $572.08
    368662 9/30/12 12/12/12 $464.88
    363053 9/24/12 12/24/12 $856.64
    393020 10/4/12 12/25/12 $239.93
    544996 10/25/12 12/27/12 $696.02
    889994 12/6/12 12/29/12 $907.38
    623595 11/22/12 12/30/12 $788.29
    Last edited by forrest.spieler; 12-04-2013 at 11:09 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need help on simple vba project!!!!due end of this week

    Who gets the grade if we write the code?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need help on simple vba project!!!!due end of this week

    @forrest

    Welcome to the Forum!!

    @shg

    Gotcha!!!
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    12-04-2013
    Location
    Boston,MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Need help on simple vba project!!!!due end of this week

    I was able to get part one down but have spent hours on the other two parts and cant seem to figure it out. Appreciate the responses so far! I wish I could compensate you for the grade you would help me with

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help on simple vba project!!!!due end of this week

    Thank you for the title change
    Last edited by FDibbins; 12-04-2013 at 11:17 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    12-04-2013
    Location
    Boston,MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: Need help on simple vba project!!!!due end of this week

    I also cant seem to upload it on the forum to give you the document!
    Last edited by forrest.spieler; 12-04-2013 at 11:08 PM.

  7. #7
    Registered User
    Join Date
    12-04-2013
    Location
    Boston,MA
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: VBA Project for Programing for Actuary Class

    Can you help me out now that the name is changed?!?!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Calculate cost per week for project
    By kingcordova in forum Excel General
    Replies: 6
    Last Post: 04-09-2019, 07:50 AM
  2. [SOLVED] Template for tracking project completion dates and simple metrics? % Completed on time
    By Shrad013 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2013, 10:28 AM
  3. How to: identify repeat offenders within a 3 week period (week per week basis)
    By Heloc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-28-2012, 12:43 AM
  4. Using MS Excel 2K7 to manage simple project!
    By nichya88 in forum Excel General
    Replies: 6
    Last Post: 10-25-2009, 01:28 PM
  5. Replies: 0
    Last Post: 07-10-2006, 03:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1