I have a Series of Macros that Calculate a Normal Distribution and overlay the result on a histogram of the original Data Set. It works fine in Test but when I apply it to a real world issue it collects all the base data and then when it tries to do the various calculation for my chart the Array is empty.
If I however open a new workbook and copy the data from the original workbook to the new one and run my routine it works OK. The process includes a User Form and a progress bar. The code below is where I seem to loose my array if I use the original workbook. That routine calls another that calculates the Normal Distribution and that in turn calls the routine that builds the chart. I have not included this as my program does not get that far.
I have run debug.print on the array ArrTemp() and that shows it has collected the appropriate values but when it moves to the first calculation to (determine the Standard deviation of the data set using WorksheetFunction.StDev(ArrTemp())) running debug shows nothing (that is on the original WorkBook). To see if it is a name problem I have duplicated the workbook with the problem and unapplied all names so there can not be any conflicts of that type
any suggestions greatly appreciated .
I am using Excel for Mac 2011 in case that is relevant.
Peter
Sub ProcessForm() ‘Process the data from the User Form’Time Test
PBStart = Minute(Now) * 60 + Hour(Now) * 60 * 60 + Second(Now)
Col1 = 2 ‘1stColumn Output Data
Col2 = 2
ColInc = 6 ‘Allows for All the Arrays to Printed to the WorkSheet
PctDone = 0
Application.DisplayAlerts = False
On Error Resume Next
Worksheets(“OutPuts”).Delete
Worksheets.Add.Name = “OutPuts”
Unload UserFormMultiple
‘###########################
‘Redimension the Arrays from dat on the user form
ReDim ArrFinal(NoRecalcs, NoOutPuts)
ReDim ArrTemp(NoRecalcs)
ReDim ArrXValues(NoRecalcs)
ReDim ArrNDValues(NoRecalcs)
ReDim ArrBinHisto(NoBins)
‘Input Labels
Range(“A2”).Value = “StdDev”
Range(“A3”).Value = “Mean”
Range(“A4”).Value = “Max”
Range(“A5”).Value = “Min”
Range(“A6”).Value = “Count”
Range(“A7”).Value = “Bin RangeND”
Range(“A8”).Value = “Bin Range Freq”
Range(“A9”).Value = “Interval Freq”
Range(” A10″).Value = “1st X ”
Range(“A11”).Value = “Last X”
Range(“A12”).Value = “Interval”
Range(“A13”).Value = “Ratio ND to Data”
‘Sets up the Array for All outputs
For i = 1 To NoRecalcs
Application.Calculate
For y = 1 To NoOutPuts
PctDone = PctDone + 1
PctCheck = PctDone / PctDo
If PbarCheck = “Yes” Then
Call AdvancePBar
End If
ArrFinal(i, y) = Range(ArrResultCells(y))
Next y
Next i
Application.Calculation = xlCalculationManual
Range(“B15”).Select
‘###########################
‘Split the array into individual outputs
For i = 1 To NoOutPuts
Cells(1, Col1) = Range(ArrLabelCells(i)) ‘Next Output
Cells(2, Col1).Name = “StdDev”
Cells(3, Col1).Name = “Mean”
Cells(4, Col1).Name = “Max”
Cells(5, Col1).Name = “Min”
Cells(9, Col1).Name = “IntervalFreq”
Cells(10, Col1).Name = “FirstX”
Cells(11, Col1).Name = “LastX”
Cells(12, Col1).Name = “Interval”
Cells(13, Col1).Name = “Ratio”
For y = 1 To NoRecalcs
PctDone = PctDone + 1
PctCheck = PctDone / PctDo
If PbarCheck = “Yes” Then
Call AdvancePBar
End If
ArrTemp(y) = ArrFinal(y, i) ‘TempArray to enable Splitting of Outputs
If PrintData = “Yes” Then
ActiveCell.Value = ArrFinal(y, i)
ActiveCell.Offset(1, 0).Select
End If
Next y
Dim y1
‘Do Calculations
Cells(2, Col1).Value = WorksheetFunction.StDev(ArrTemp())
Cells(3, Col1).Value = WorksheetFunction.Average(ArrTemp())
Cells(4, Col1).Value = WorksheetFunction.Max(ArrTemp())
Cells(5, Col1).Value = WorksheetFunction.Min(ArrTemp())
Cells(6, Col1).Value = NoRecalcs
Cells(7, Col1).Value = (Range(“max”) – Range(“min”)) / NoRecalcs
Cells(8, Col1).Value = NoBins
Cells(9, Col1).Value = (Range(“max”) – Range(“min”)) / (NoBins – 1)
Cells(10, Col1).Value = Range(“mean”) – 3 * Range(“StdDev”)
Cells(11, Col1).Value = Range(“mean”) + 3 * Range(“stddev”)
Cells(12, Col1).Value = (Range(“lastx”) – Range(“firstX”)) / (NoRecalcs – 1)
Cells(15, Col1 + ColInc).Select ‘Start Next Output
Cells(13, Col1).Value = (Range(“lastx”) – Range(“firstX”)) / (Range(“Max”) – Range(“Min”))
‘Scaling
ScaleMax = Application.Ceiling(Range(“LastX”), 1000)
ScaleMin = Application.Floor(Range(“FirstX”), 1000)
ScaleMajor = (ScaleMax – ScaleMin) / NoBins
‘###########################
‘Calculat ethe Normal Distribution of the Data Set.
‘###########################
‘Move to the Next OutPut
Call NormDistributionMO
‘Increase Col count
Col1 = Col1 + ColInc
Next i
Range(“A1”).Select
PBEnd = Minute(Now) * 60 + Hour(Now) * 60 * 60 + Second(Now)
MsgBox “Elapsed Time ” & (PBEnd – PBStart) & ” seconds”
Unload ProgressBar
‘####################
End Sub
