Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Losing values in publicly declared array in middle of sub

    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

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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Kind of hard to do this w/o the workbook in question but here are a couple of things you can check:
    1. You day the array is declared publicly...exactly how? If you are declaring it outside of any procedure with a Dim statement it is only visible in the same Module so if the Standard Deviation procedure is in another module it will be a different variable and have no data.
    2. Try using a Public statement vs Dim.
    3. Have you tried putting a Watch on the Array so the code breaks if the array gets wiped?
    4. Use this query {vba debugging watch expressions} in your browser to learn how to do watch expressions. Knowing the location of execution when the array goes blank can be a big help in finding the problem.


    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    mitchbvi (2015-07-18)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi

    Does the problem stem with the 4 lines ines beginning with
    Code:
    Cells(2, Col1).Value = WorksheetFunction.StDev(ArrTemp())
    I believe that line would result in a compilation error 'type mismatch' .

    Please tutor me if I am wrong.

    G

  5. The Following User Says Thank You to geofrichardson For This Useful Post:

    mitchbvi (2015-07-18)

  6. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Peter,

    Kind of hard to do this w/o the workbook in question but here are a couple of things you can check:
    1. You day the array is declared publicly...exactly how? If you are declaring it outside of any procedure with a Dim statement it is only visible in the same Module so if the Standard Deviation procedure is in another module it will be a different variable and have no data.
    2. Try using a Public statement vs Dim.
    3. Have you tried putting a Watch on the Array so the code breaks if the array gets wiped?
    4. Use this query {vba debugging watch expressions} in your browser to learn how to do watch expressions. Knowing the location of execution when the array goes blank can be a big help in finding the problem.


    HTH
    Thanks again, for taking the time. The array is Public and is in the same module as the code I posted. Just in case I have made an error this is the start of the module
    Code:
    Option Explicit
    
    Option Base 1
    Public NoRecalcs, NoBins, ArrXValues(), FrequencyArr, PctDone, PbarCheck, PBStart, PBEnd, ScaleMax, ScaleMin, ScaleMajor, _
    ResultCells, ArrResultCells, NoOutPuts, ArrFinal, LabelCells, ArrLabelCells, NoLabels, BinFq, ColInc, Col1, Col2, i, y, x, Hi, n, _
    ArrTemp(), ArrNDValues(), ArrBinHisto(), Response, PctDo, PctCheck, PrintData, WBtemp
    I have not used watch expressions before so I will get to work on that.

    The workbook in question is part of an estate plan so I would send it to you privately if acceptable but do not want to post it on line.

    Thanks again

    Peter

  7. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks for taking the time.


    I do not get an error when running the Module at that point or any other, the ArrTemp() simply goes blank. Also as I pointed out in my post running a test with a worksheet that has cells that recalculate it works fine. Also as I said if I copy the results from the workbook with which I have the problem to another and then run the procedure on that work book I have copied the results to it works OK.

    For completeness ArrTemp() splits the multiple ArrFinal( , ) into the number of outputs that have been selected. It changes each time the loop for outputs is run. It has been re dimensioned at the start of the sub routine I posted.

    Sorry I cannot be more explicit and thanks again.

    Peter

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Peter,

    Another thing you might consider to to Explicitly Type each of your variables. As it is you are using Variants (the default data type) which are highly inefficient and chew up memory space. Also avoid multiple variables in one DIM give each its own line, takes up space but makes things easier to read. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Peter,

    Another thing you might consider to to Explicitly Type each of your variables. As it is you are using Variants (the default data type) which are highly inefficient and chew up memory space. Also avoid multiple variables in one DIM give each its own line, takes up space but makes things easier to read. HTH
    I have tried the watch suggestion and I may not be doing it correctly , two watches first just the array name ArrTemp() and second a new line I added to check if the assignment to a cell was the problem

    CheckEmpty = WorksheetFunction.StDev(ArrTestTemp())

    Both showed in the watch window our of context and variant empty.

    Added a little test to my code to check if the array was empty
    Code:
     
             For Z = 1 To NoRecalcs           
     If IsEmpty(ArrTemp(Z)) Then
                MsgBox "Array " & Z & " is empty "
                End If
                Next Z
                    'Do Calculations
                    CheckEmpty = WorksheetFunction.StDev(ArrTestTemp())
                    MsgBox CheckEmpt
    and it did not fail but the moment I tried to calculate the StDev of the Array I had just checked to have all the elements in place it is empty.


    Thanks for the tip about defining the variants one of the reasons I added a Progress bar was to try and find where the procedure slowed down, writing to the worksheet certainly slows it. I assume that all but one of my arrays should be integer the odd one the names of the results a string or is there any other class that would speed things up.

    Did not mention before but all of the routines in this procedure are in an Addin that I call from my personal workbook just in case it makes any difference.

    Thank you again, I have been working on this routine for some time.

    Take care

    peter

  10. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Quote Originally Posted by mitchbvi View Post
    Thanks for taking the time.


    I do not get an error when running the Module at that point or any other, the ArrTemp() simply goes blank. ....

    Peter
    Hi again

    I was barking up the wrong tree anyway. Worse still .. I was wrong.
    I have learned a bit more.
    G
    Last edited by geofrichardson; 2015-07-19 at 05:24.

  11. The Following User Says Thank You to geofrichardson For This Useful Post:

    Maudibe (2015-07-19)

  12. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Geof,

    You may not be barking up the wrong tree. You made a very valid point.

    Peter,
    I have been following your dilemma since your initial post. Can I suggest that you comment out the On Error Resume Next statement then re-run you code. There may be an error being bypassed that could be causing the drop of the array variable's values. For each error encountered, check the value of ArrTemp. while in debug mode.

    You may find that you need the On Error Resume Next to get all the way through it. In that case, use On Error Goto 0 just after each error point and re-run the code while checking the variable upon the next error and moving the On Error Goto 0 to after that point.

    HTH,
    Maud

  13. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi folks

    Maude
    Even if an error was spotted after commenting out the on error .... resume next that would not explain why the code runs in Wkbk B and not WkBk A.

    Peter
    Welcome to the flat forehead society. You will have a Dohl moment at some point.

    Questions

    Does this range exist and if so where ..Range(ArrResultCells(y))
    There is only one ?
    Line 41
    Code:
     ArrFinal(i, y) = Range(ArrResultCells(y))
    What is Y1 at line 71
    Code:
    Dim y1
    You said that if you copied to a location with calculations it worked well.
    What happens if your code left calculations on auto? I know the speed will drop. But we have no idea how many rows of data you are dealing with. Grasping at straws.

    You are confident that NoRecalcs and NoOutputs are good to go?

    What do you copy when you copy material to another workbook?
    Is all the code always running out of personalworkbook?

    Are there any clashing declarations at the top of a module ... although I would expect an error alert if you tried to declare something identical.

    This is where I overwork the F8 key and have plenty of breakpoints.

    I would read Chip Pearson's material about arrays again. You never know something might go ClickSnap.

    Link 1

    Link 2


    Good luck


    Cheers
    G

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Geof,

    Again, a good point: When Peter runs it in a new workbook, it works. This tells us something is not right in the current workbook, however, we do not know what else exists in the current workbook that may be causing a possible error. IMHO, all debugging should begin at least initially with error trapping disabled.

    Maud

  15. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by Maudibe View Post
    IMHO, all debugging should begin at least initially with error trapping disabled.
    Totally agree. Also, simply sticking On Error Resume Next at the top of a routine and not turning it off again is bad practice - for this very reason.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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