VBA - Array Trouble

jjewels119

New Member
Joined
Oct 27, 2011
Messages
17
I am trying to write a code that randomly generates accident months and year along with claim payment months and years and claim payment amounts. Then I need to make cumulative claim amounts for each accident year, then graph the results. I am trying to create the cumlative amounts on sheet 2. I haven't even started the graph yet.

I have randomly generated the months, years, and amounts like I should. I am having trouble with my accident year array which should go from 2000 to 2010 increasing by one year. Right now I am getting the array to be the correct size but it is all zeros.

(I am not very experienced in VBA. I apologize for any formatting. I bolded the part of the code that I am having the issues with. I italicized the code I have not been able to check to see if it is correct yet)

Also I don't even know how to check the value of part of an array without pasting it into excel. That makes this issue hard to debug.



Here is my code:

Sub generateARRAY()
Dim NormalMU As Integer, NormalSIGMA As Integer
Dim mu As Double, sigma As Double
Dim firstYR As Integer, lastYR As Integer
Dim NumYrs As Integer
Dim cars As Integer, claims As Integer
Dim AccMon As Integer, AccYr As Integer
Dim U As Double, v As Double
Dim x As Double, y As Double, Z As Double
Dim S As Integer
Dim K As Double
Dim pi As Double
Dim j As Integer
Dim ClaimGen() As Single
Dim AccidentYrs() As Single
Dim TotalClaim() As Single
Dim a As Integer


'Declare Starting year, Ending year, and number of cars
firstYR = 2000
lastYR = 2010
cars = 5000
claims = Int(0.13 * cars)
NumYrs = lastYR - firstYR + 1

ReDim ClaimGen(1 To claims, 1 To 5) As Single

NormalMU = 2000
NormalSIGMA = 3000

mu = Application.WorksheetFunction.Ln(NormalMU) - 0.5 * Application.WorksheetFunction.Ln(1 + (NormalSIGMA / NormalMU) ^ 2)
sigma = Sqr(Application.WorksheetFunction.Ln(1 + (NormalSIGMA / NormalMU) ^ 2))
pi = Application.WorksheetFunction.pi()

Range("a1") = "Accident Month"
Range("b1") = "Accident Year"
Range("c1") = "Claim Month"
Range("d1") = "Claim Year"
Range("e1") = "Payment"


ReDim AccidentYrs(1 To NumYrs, 1) As Single
For i = 1 To NumYrs
AccidentYrs(i, 1) = (firstYR + i)
Next i

Sheets("sheet2").Select
Range("a1") = "Accident Year"
Range("b1") = "Total Claim Amount"
Range("A2:A" & NumYrs + 1).Value = AccidentYrs()



Sheets("sheet1").Select
For i = 1 To claims
ClaimGen(i, 1) = Int((12 - 1 + 1) * Rnd + 1)
ClaimGen(i, 2) = Int((NumYrs) * Rnd + firstYR)

'K is used to determine the factor for number of years
Z = Rnd
S = Int((Application.WorksheetFunction.Ln(1 - Z) / -4) * 12)
K = Application.WorksheetFunction.RoundDown(((S + ClaimGen(i, 1)) / 12), 0)

'If the accident month + number of months til claim is GREATER than 12
If ClaimGen(i, 1) + S > 12 Then
'Claim Month = Accident month + number of months til claim - number of months in any full years
ClaimGen(i, 3) = S + ClaimGen(i, 1) - (12 * K) + 1
'Claim Year = Accident year + number of full years until claim
ClaimGen(i, 4) = ClaimGen(i, 2) + K
'If the accident month + number of months til claim is LESS than 12
Else
ClaimGen(i, 3) = S + ClaimGen(i, 1)
ClaimGen(i, 4) = ClaimGen(i, 2)
End If

U = Rnd
v = Rnd

y = Sqr(-2 * pi * Application.WorksheetFunction.Ln(U)) * Sin(2 * pi * v)

ClaimGen(i, 5) = Round(Exp(mu + sigma * y), 2)

Next i

'Formats Payment column and puts the array in Excel
Columns("E:E").NumberFormat = "0.00"
Range("A2:E" & claims + 1).Value = ClaimGen()


'Cumulative Claim Amounts
j = 1

ReDim TotalClaim(1 To NumYrs, 1) As Single
For i = 1 To claims
Do
If ClaimGen(i, 2) = AccidentYrs(j, 1) Then
TotalClaim(i, 1) = ClaimGen(i, 5) + TotalClaim(i, 1)
j = j + 1
Else
j = j + 1
End If
Loop Until j = NumYrs
Next i

Range("B2:B" & lastYR - firstYR + 2).Value = TotalClaim()


End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,232
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
ReDim AccidentYrs(1 To NumYrs) As Single

For i = 1 To NumYrs
   AccidentYrs(i) = firstYr + i
Next i
 
With Sheets("sheet2").
       .Range("a1") = "Accident Year"
       .Range("b1") = "Total Claim Amount"
       .Range("A2:A" & NumYrs + 1).Value = Application.Transpose(AccidentYrs())
End With
 

jjewels119

New Member
Joined
Oct 27, 2011
Messages
17
Thank you sooooo much! This worked. I can't believe how much trouble this bit of code has been.
 

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
If you are just trying to get the YearNumber printed out on the worksheet, you could use the following:

Code:
For I = 1 To NumYrs
        AccidentYrs(I, 1) = (firstYR + I)
        ThisWorkbook.Worksheets("Sheet2").Cells(I + 1, 1) = AccidentYrs(I, 1)
Next I

You are going thru the "For Loop" anyhow.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top