none
Calculate by Row Major Order RRS feed

  • Question

  • In the dark ages, I was an expert at Excel 1.04, building rather large iterative models for process simulations (and macros for subroutines/functions) that would step through time.  Back then, I could control calculation order by cell placement, row-by-row for time steps, and column by column to develop intermediates, and convergence (a la Gauss-Siedel).  The Excel world has passed me by, so I need some help.

    It appears that the Excel solver aims to maximize sparsity, and does not calculate all my cells.  Calculating by RowMajorOrder would seem to be what I need, but alas there is no preference checkbox to force that.

    I have grabbed some VBA code, but I am frankly at a loss as to how to execute it – the MSDN examples show the VBA code, but ignore the calling of the VBA subroutine/function to manage a calculation.

    I would expect a cell formula referencing a named range and a convergence criterion

    =IF(Not_Converged,CalculateRange(myArray),”Done”)

    And a VBA Function along the lines of

    Function CalculateRange(ByRef Cellrange As Range)
    Cellrange.CalculateRowMajorOrder
    End Function

    But this isn’t working for me.

    I appreciate your help!

    Friday, October 6, 2017 8:09 PM

All replies

  • Hi wnconlon,

    Range.CalculateRowMajorOrder Method is used to calculate a specfied range of cells. According to my test result, It would not return any value after calculation. According to your formula, it seems you want to return "Done" after calculation, you may handle Worksheet.Calculate Event (Excel) (which occurs after the worksheet is recalculated, for the Worksheet object) to do some process or update cell value.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 9, 2017 6:14 AM