ATPVBAEN.XLA!Random Arguments

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Sorry to bother this group, but I am completely stuck. I am having a
problem with the add-in ATPVBAEN.XLA!Random that I believe can be
traced to misuse of the arguments. I have extensively searched both
Excel help and the Microsoft web site and cannot find anything to give
me a list of these arguments. Can anybody provide this or give any
suggestions on resources.

Thanks.
Randy Eastland
 
Randy,

That is why we are here!

Could it be because there is no Random function? There is a Rand, which has
no arguments.

If you open the function list dialog, you can see all functions there with
their arguments, including ATP ones.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Here is a short procedure to show what is going wrong. Paste theis
into a new workbook and run it to see what I am talking about.

This ATPVBAEN.XLA!Random function (and yes, it does exist RP), is
supposed to generate a series of random values normally distributed
around a mean and standard deviation. The function works quite well
when called from Excel (via the Data Analysis addin: "Tools - Data
Analysis - Random Number Generation"). I got the initial line of code
from the recorder and then modified it to work within my VBA procedure.

The problem seems to arise when the function is called multiple times.
There seems to be no magic number of iterations when the problem first
occurs, but eventually, I begin to get exact duplicate values coming
back from the function call. In this case, I am trying to lay out a
series of 41 random values per iteration. There is no way possible
that I would ever get a completely duplicated set of 41 random values.
But I do, assuming that I run the simulation enough times. In my real
procedure, the simulation runs through 10,000 iterations, so it's
pretty critical that these simulations be unique and not just repeats.

In this subroutine below, what it does is to generate 66 of these 41
random number sets. FYI, the data is supposed to be representative of
41 years of randomly occuring stock market returns. The mean is .106
and the standard deviation is .203.

I have set the procedure to run 5 times, just to create the scenario
that I am witnessing where enough runs occur where the error begins to
happen.

At the very bottom of the random numbers, I have summed each of the 66
columns. Then I compare column A (the first simulation) to each of the
other column totals. When there is an exact match, this represents a
statistical impossibility, and I put the value "TRUE" below the
matching column total. Where you see this, compare it to column A an
you will see this exact same value. After that, keep looking at each
total to the right. You will now see that the next column is identical
to column B, the one after that identical to Column C, and so on.

So, either I am simply misusing the arguments for this
ATPVBAEN.XLA!Random in such a way as to cause this reoccurrence of
random numbers (very likely), or this thing has a bug in it. I am
stumped.

Sorry for the exceedingly long explanation. Anybody have any
experience with this function or ever have the same problem?


If the error described doesn't occur, try increasing the max value of z
to 10 or even 50. The problem usually doesn't arise the first few
iterations. But after it runs a few times, I get duplicate results.

Thanks,
Randy Eastland

Sub BellCurve()

For z = 1 To 5
Application.StatusBar = "Iteration " & z
Sheet1.Select
Range("A1:bn48").ClearContents

'Column Headers
Range("A1").Select
For x = 1 To 66
ActiveCell.Offset(0, x - 1).Value = x
Next

'Tools - Data Analysis - Random Number Generation
'Random returns for Stocks
Range("A2").Select
For x = 1 To 66
Application.Run "ATPVBAEN.XLA!Random", ActiveCell, 1, 41, 2, ,
0.106, 0.204
ActiveCell.Offset(42, 0).FormulaR1C1 = "=sum(R[-42]C[0]" & ":"
& "R[-2]C[0])"
If x > 1 Then
y = "=if(" & ActiveCell.Offset(42, 0).Address & "=" &
ActiveCell.Offset(42, -(x - 1)).Address & ",True,False)"
ActiveCell.Offset(44, 0).Formula = y
End If
ActiveCell.Offset(0, 1).Activate
Next

Next
Calculate
Range("A1").Select

End Sub
 
Randy -

In the most recent versions of Excel, the worksheet functions RAND and
NORMINV have better numerical properties.

So, you could use =NORMINV(RAND(),Mean,StDev) to obtain dynamic normally
distributed values on a worksheet.

Or, in VBA, use RND instead of RAND, with WorksheetFunction.NormInv.

- Mike

www.mikemiddleton.com
 
Thank you, Mike. Not only is this method MUCH more reliable than the
add-in, it also sped up my code execution by a factor of about 15.
That makes a huge difference when running thousands of iterations.
Great suggestion. Can't thank you enough.
 
Back
Top