Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN.XLA!Random Arguments
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) "Randy" wrote in message ups.com... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN.XLA!Random Arguments
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN.XLA!Random Arguments
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ATPVBAEN.XLA!Random Arguments
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
atpvbaen.xla cound not be found | Excel Discussion (Misc queries) | |||
Atpvbaen.xls | Excel Worksheet Functions | |||
ATPVBAEN.XLA!Random Arguments | Excel Programming | |||
ATPVBAEN XLA [Number casual] | Excel Programming | |||
ATPVBAEN.XLA!Random Call in Macro | Excel Programming |