Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Vail, Colorado, USA
    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.

    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
    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 522 Times in 498 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.

    •Standby Excel Grip

  3. The Following User Says Thank You to zeddy For This Useful Post:

    mitchbvi (2015-07-10)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Vail, Colorado, USA
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts