VBA - Array Trouble

[jjewels119]

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

 

[Norie]

Try this.

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]

Thank you sooooo much! This worked. I can't believe how much trouble this bit of code has been.

 

[b.downey]

If you are just trying to get the YearNumber printed out on the worksheet, you could use the following:

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.