How to pass the result of a Frequency Function to an array

Topic

New Reply

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 = Max-Min Datapoints / NoBins-1 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

The fourth and third last lines are how I have it at present. I have tried setting the FrequencyArr to to the worksheet function directly also tried setting the range to the two arrays that hold the data and the bins.

Any suggestions greatly appreciated.

Thanks Peter

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote