Excel calc and VBA - Different Results = Confusion

[Alan_G]

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.

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

I would appreciate any insight that a fellow coder can provide.

Alan

 

[joeu2004]

Some obvious errors....

When you write ``Dim i, j As Integer``, only J is type Integer. I is type Variant. If your intent is for both to be type Integer, write ``Dim i As Integer, j As Integer``.

(Aside.... But type Integer is passe. Always use type Long unless you are concerned about the memory size of an array of that type.)

Similarly with ``Dim A, B As Long``. But obviously, A and B should be type Double. So write ``Dim A As Double, B As Double``.

My guess is: the latter error (which affects only B) is the root cause of the difference between Excel and VBA calculations.

But even with those corrections, it is possible to see (usually infinitesimal) differences between Excel and VBA calculations. These arise because Excel rounds pairwise operations to 64-bit binary floating-point, whereas VBA tries to utilize the internal 80-bit binary floating-point results of Intel-compatible CPUs when the operands are not type Variant.

In your posted code, I believe that difference might arise only in the calculation of PctGain(i, j).

We cannot even begin to duplicate your problem since you neglect to tell us the values of MC_trials and TotYears. BTW, you might consider using
``Const MC_trials As Long = ...`` and similarly for TotYears, unless they must be determined at runtime (e.g. from cells in a worksheet).

Also, it is unclear what you are doing in Excel to compare results. We can make intelligent guesses. But why not make it easier for us? (Rhetorical.)

If you have further questions, I suggest that you upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview error, and just download).

Caveat: Some participants cannot or will not download Excel files. In this case, you might be able to post the Excel data and formulas and the full VBA code in some form. But I suspect that might be tedious.

 

[Alan_G]

Thank you for the corrections to my code and for the reminder of my obligation to provide sufficient information for the most accurate and helpful reply. I tend to think that what I write is obvious, but alas, 'tis not always the case.

Regards,
Alan