Results 1 to 3 of 3

20150709, 19:14 #1
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 203
 Thanks
 39
 Thanked 0 Times in 0 Posts
How to pass the result of a Frequency Function to an array
This has me stumped I have written series of Macros to run a limited Monte Carlo simulation and produce charts that overlay a Normal Distribution of the data. I am now trying to clean it up (speed it up) to avoid writing all the results to a spreadsheet and just use the various arrays to generate my charts. I cannot figure out how to pass the result of a frequency calculation to another array with out first having written it to a work sheet. The following code is what I use to develop the arrays. It still has me writing the data to the spreadsheet and I want to remove that and I can except for the frequency calculation.
Code:Sub NormDistributionMO() 'Called from Sub ProcesssForm'Col1 is the First Output Column 'Step 1 is to Calculate all of the X values for the Normal Distribution ' This is the 1st X Calculated in Sub ProcessForm ArrXValues(1) = Range("FirstX") Cells(15, Col1 + 1) = ArrXValues(1) ' Row 15 is the Start Row and Col1+1 is one column to the left of DataPoints 'Step 2 is to calculate the Normal Distribution 'Range Mean and StdDev Calculated in Sub ProcessForm ArrNDValues(1) = WorksheetFunction.NormDist(ArrXValues(1), Range("Mean"), Range("StdDev"), False) Cells(15, Col1 + 2) = ArrNDValues(1) For x = 1 To NoRecalcs  1 'Minus 1 as the first Value has been set ArrXValues(x + 1) = Cells(15 + x  1, Col1 + 1) + Range("Interval") 'Interval was calcuated in the calling sub Cells(15 + x, Col1 + 1) = ArrXValues(x + 1) ArrNDValues(x + 1) = WorksheetFunction.NormDist(ArrXValues(x + 1), Range("Mean"), Range("StdDev"), False) Cells(15 + x, Col1 + 2) = ArrNDValues(x + 1) Next x 'Step 3 to Calculate the Histogram. Requirements the No Bins and the actual Data Points ArrBinHisto(1) = Range("Min") Cells(15, Col1 + 3) = ArrBinHisto(1) '1st Bin Value for Histogram 'Set up Bin Values for Histogram Start Min Value of DataPoints Steps = MaxMin Datapoints / NoBins1 for Histogram For Hi = 1 To NoBins  1 ArrBinHisto(Hi + 1) = Cells(15 + Hi  1, Col1 + 3) + Range("IntervalFreq") Cells(15 + Hi, Col1 + 3) = ArrBinHisto(Hi + 1) Next Hi Set FrequencyArr = Range(Cells(15, Col1 + 4), Cells(15 + NoBins  1, Col1 + 4)) FrequencyArr.FormulaArray = WorksheetFunction.Frequency(ArrTemp(), ArrBinHisto()) Call AddNewchartMO Worksheets("outputs").Activate End Sub
Any suggestions greatly appreciated.
Thanks Peter

20150710, 07:28 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,443
 Thanks
 166
 Thanked 651 Times in 619 Posts
Hi Peter
Instead of writing out to the worksheet, have you thought about defining the array result as a Global array variable?
Note: if any of your VBA routines exit ungracefully (i.e. any vba runtime error occurs) you will lose all values held in any global variable.
zeddy
•Standby Excel Grip

The Following User Says Thank You to zeddy For This Useful Post:
mitchbvi (20150710)

20150710, 11:18 #3
 Join Date
 Apr 2003
 Location
 Vail, Colorado, USA
 Posts
 203
 Thanks
 39
 Thanked 0 Times in 0 Posts
Hi Zeddy
The Array is already defined as Public, I think it is something to do with the syntax. I tried to set the array to the two arrays that are the feed for the frequency function but that did not work. The only thing that does (so far) is actual setting the array to worksheet cells the same size as the input.
by the way since seeking help it has occurred to me that I need the data on the worksheet to build the chart other wise once I exit the routine the Chart will no longer have any input.
Thanks for taking the time to help.
Peter