Array trouble in VBA

[jjewels119]

I feel so stupid. I don't know what I'm missing, or even if what I am attempting to do is correct. I need to create data for the number of claims in each year (the number of claims per year follow a Poisson distribution and claim amounts follow a lognormal distribution).

So I do my array for one year then I tried to create a loop that would allow me to do the same thing for multiple years. When I paste in my code I get the headers in A1 and B1. I get 4 zeros in range(a2:b3), then "N/A" in range (a4:b10). I tried transposing the array and had the same result. Here is my code:

Sub generateARRAY()
Dim NormalMU As Integer, NormalSIGMA As Integer
Dim mu As Double, sigma As Double, pi As Double
Dim firstYR As Integer, lastYR As Integer
Dim NumYrs As Integer
Dim cars As Integer, claims As Integer
Dim percent As Double
Dim U As Double, v As Double, y As Double
Dim X As Integer
Dim K As Integer
Dim ClaimGen() As Single
Dim AccidentYrs() As Single
Dim TotalClaim() As Single

'Declare Starting year, Ending year, number of cars, and percent
firstYR = 1970
lastYR = 1972
cars = 50
percent = 0.13

NumYrs = lastYR - firstYR + 1
K = 1
X = 0

'Declare Normal mu and sigma
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()

'Create an array with Claim dates & Claim Payments

Do
Sheets("sheet1").Select

claims = cars * (Exp(-percent) * (percent ^ K)) / (Application.WorksheetFunction.Fact(K))
ReDim ClaimGen(1 To 2, i = 1 To claims) As Single
For i = 1 To claims

U = Rnd
v = Rnd
y = Sqr(-2 * pi * Application.WorksheetFunction.Ln(U)) * Sin(2 * pi * v)
    
ClaimGen(1, i + X) = K + firstYR - 1
ClaimGen(2, i + X) = Round(Exp(mu + sigma * y), 2)

Next i
X = X + i
K = K + 1

Loop Until K + firstYR - 2 = lastYR


'Formats Payment column and puts the array in Excel
With Sheets("sheet1")
.Range("A2:B" & X + 1).Value = ClaimGen()
.Range("a1") = "Claim Year"
.Range("b1") = "Payment"
End With

Columns("b:b").NumberFormat = "0.00"
End Sub

 

[shg]

At a glance

        ReDim ClaimGen(1 To 2, [COLOR=red]i = 1[/COLOR] To claims) As Single

should be

        ReDim ClaimGen(1 To 2, 1 To claims)

 

[jjewels119]

I get an error: Run-time error '9'": Subscript out of range

I think it doesn't like the part of my code:

ClaimGen(1, i + X) = K + firstYR - 1
ClaimGen(2, i + X) = Round(Exp(mu + sigma * y), 2)

Can you add a variable to i when you use arrays?

 

[shg]

Sure, if the result is within the bounds of the array.

This would be a good time to read http://www.cpearson.com/excel/debug.htm, and then step through the code.

EDIT: You could replace Application.WorksheetFunction.Ln with VBA's native Log function.

EDIT2: It's clear that the second subscript is going to go out of range when i goes from 1 to claims, and then you add a positive value (i.e., X)

 

[jjewels119]

Okay, I read that and I took it a little slower and found that it is the code you revised for me that it will not go past after the first loop. It goes through all the i's but when x = x + 1 and it goes through the loop it stops at this line of code:

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

Also, I was wanting to check to see if the values of my array seem reasonable. I tried the immediate window and typed, "?claimgen(1,1).value" and hit enter, but it said that no sub had been defined. How could I use the immediate window to check a value of a particular array cell?

 

[jjewels119]

Okay, I see what you are saying. How should my logic change then? My orginial thinking was to create a single year of data then loop to create a second year, third year, etc. I don't want it to write over the first year's data, but attach at the bottom of that data. I want this code to be as flexible as possible because I plan on changing the values for the variables, "firstYr, lastYr, number of cars, etc"

 

[shg]

I tried the immediate window and typed, "? claimgen(1,1).value" and hit enter, but it said that no sub had been defined.

Scalar variables aren't objects; they don't have properties, they have simple values:

? claimgen(1,1)

... but it would be easier to just look in the Locals window.

How should my logic change then?

I don't have a clue what you're trying to do.

You have unused variables, undeclared variables, and variables that should be constants:

Sub generateARRAY()
    Const pi        As Double = 3.14159265358979
    Const firstYR   As Long = 1970
    Const lastYR    As Long = 1972
    Const cars      As Long = 50
    Const percent   As Double = 0.13
    Const NormalMU    As Long = 2000
    Const NormalSIGMA As Long = 3000
 
    Dim mu          As Double
    Dim sigma       As Double
    Dim claims      As Long
    Dim U           As Double
    Dim v           As Double
    Dim y           As Double
    Dim X           As Long
    Dim K           As Long
    Dim ClaimGen()  As Double
    Dim i           As Long
 
    mu = Log(NormalMU) - 0.5 * Log(1 + (NormalSIGMA / NormalMU) ^ 2)
    sigma = Sqr(Log(1 + (NormalSIGMA / NormalMU) ^ 2))
 
    K = 1
    X = 0
 
    'Create an array with Claim dates & Claim Payments
    Do
        claims = cars * (Exp(-percent) * (percent ^ K)) / WorksheetFunction.Fact(K)
        ReDim ClaimGen(1 To 2, 1 To claims)
        For i = 1 To claims
            U = Rnd
            v = Rnd
            y = Sqr(-2 * pi * Log(U)) * Sin(2 * pi * v)
 
            ClaimGen(1, i + X) = K + firstYR - 1
            ClaimGen(2, i + X) = Round(Exp(mu + sigma * y), 2)
        Next i
 
        X = X + i   [COLOR=red]' I'd guess that should be 1, not i[/COLOR]
        K = K + 1
    Loop Until K + firstYR - 2 = lastYR
 
    With Worksheets("Sheet1")
        .Range("A2:B" & X + 1).Value = ClaimGen()
        .Range("A1:B1").Value = Array("Claim Year", "Payment")
        .Range("B:B").NumberFormat = "0.00"
    End With
End Sub

 

[jjewels119]

I have unused variables because after I generate claim data for these years, I am going to create bins with cumulative claim amounts then create a histogram. I left that out because it is after the code I wrote and I'm not having a problem with that yet.

I did mean for it to be 'i' not '1'. My thinking was if I didn't offset the array when I do my loop it would write over my data. I want my final data set to look something like,

claims years : claim amount
1970: 562.71 (or whatever amount)
1970: 362.01
1970: 52.62
1971: 1243.34
1971: 3402.24
1972: 12.32


So I have my array set up to do each year because the number of claims are dependent on the Poisson distribution. Then I have it looping to do each year. I would like this to be one array and not multiple arrays.

 

[shg]

Then you have to dimension the array large enough to contain all those values.

 

[jjewels119]

Okay, I figured out how to do most of it. I'm still having trouble with pasting my values into excel. I am only getting years "1972" to appear, the rest of the values are zero. Here is my updated code.

Sub generateARRAY()
Dim NormalMU As Integer, NormalSIGMA As Integer
Dim mu As Double, sigma As Double, pi As Double
Dim firstYR As Integer, lastYR As Integer
Dim NumYrs As Integer
Dim cars As Integer, claims As Integer
Dim percent As Double
Dim U As Double, v As Double, y As Double
Dim X As Integer
Dim K As Integer
Dim ClaimGen() As Single
Dim AccidentYrs() As Single
Dim TotalClaim() As Single

'Declare Starting year, Ending year, number of cars, and percent
firstYR = 1970
lastYR = 1972
cars = 50
percent = 0.13

NumYrs = lastYR - firstYR + 1
K = 1
X = 0

'Declare Normal mu and sigma
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()

'Create an array with Claim dates & Claim Payments

Do
    Sheets("sheet1").Select
    claims = cars * (Exp(-percent) * (percent ^ K)) / (Application.WorksheetFunction.Fact(K))
    
    ReDim ClaimGen(1 To cars * NumYrs, 1 To 2) As Single
    For i = 1 To claims
        
        U = Rnd
        v = Rnd
        y = Sqr(-2 * pi * Application.WorksheetFunction.Ln(U)) * Sin(2 * pi * v)
            
        ClaimGen(i + X, 1) = K + firstYR - 1
        ClaimGen(i + X, 2) = Round(Exp(mu + sigma * y), 2)
    
    Next i
  
'This code accounts for the rest of the cars that did not have a claim (note: claim amount = 0)  
    For i = i To cars - claims
    
        ClaimGen(i + X, 1) = K + firstYR - 1
        ClaimGen(i + X, 2) = 0
    
    Next i
    
    X = X + i
    K = K + 1

Loop Until K + firstYR - 2 = lastYR


'Formats Payment column and puts the array in Excel
With Sheets("sheet1")
.Range("A2:B" & X + 1).Value = ClaimGen()
.Range("a1") = "Claim Year"
.Range("b1") = "Payment"
End With

Columns("b:b").NumberFormat = "0.00"
End Sub