#### jjewels119

##### New Member

- Joined
- Oct 27, 2011

- Messages
- 17

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()

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()

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