I have a simple polynomial equation that uses an input random probability to calculate the expected gain for a given calendar year. As a VBA newbie, I decided to use Excel to back check my VBA code. Every time I do the calculation I get a near-constant difference in the results of 0.0436(+/- a small epsilon) between the Excel spreadsheet answer and the VBA answer using the same functions. In the grand scheme of things, this difference will not make or break my use of the code, but as a new user trying to figure out VBA, it has me seriously bugged.
In the code below, MC_trials and TotYears are declared as public and values are established. I get a full 2D array without problems.
I would appreciate any insight that a fellow coder can provide.
Alan
In the code below, MC_trials and TotYears are declared as public and values are established. I get a full 2D array without problems.
Code:
Sub Prob1()
'Generates a 2D array of random numbers for all trials and all years
ReDim Prob1(MC_trials, TotYears) As Double 'Random number sequences for (Trial, Year) pairs; calc annual investment gain
ReDim PctGain(MC_trials, TotYears) As Double 'Annual % gain of investment and IRAs for (Trial, Year, Prob1) triads
Dim i, j As Integer
Dim A, B As Long
A = 6.2605032
B = -2.95642
For i = 1 To MC_trials
For j = 1 To TotYears
Prob1(i, j) = Rnd()
Sheets("Prob1").Cells(i + 1, j + 1) = Prob1(i, j)
PctGain(i, j) = A * (Prob1(i, j) ^ 0.5) + B
Sheets("PctGain").Cells(i + 1, j + 1) = PctGain(i, j)
Next j
Next i
End Sub
Alan