none
Recalculate formulas concurrently ... RRS feed

  • Question

  • Hi, I have a question about concurrent formula calculations.  My Excel add-in creates formulas (i.e. custom UDFs) on the worksheet.  When the user clicks on the "Refresh Formulas" button, I'd like my formulas to be calculated concurrently (e.g. 4 calculations at a time).  What is the best way to do this? Do I need to create my own threads? Thank you.
    Tuesday, July 26, 2011 4:26 PM

Answers

  • You cannot make Excel run a UDF in a thread. It is decided by Excel.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by DragonFly999 Tuesday, August 2, 2011 12:50 PM
    Thursday, July 28, 2011 5:33 AM

All replies

  • Hello,

    The only way I know is to create a thread-safe XLL-based UDF. Not sure if Excel DNA supports this, Add-in Express as well as Excel SDK do support this for sure.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, July 26, 2011 5:11 PM
  • Actually, I use both VSTO and Add-In Express (for different projects).  Let's say I could use Add-In Express for my current project.  How would I trigger the XLL-based UDFs to be calculated concurrently? Thanks.
    Tuesday, July 26, 2011 5:17 PM
  • You cannot make Excel run a UDF in a thread. It is decided by Excel.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    • Marked as answer by DragonFly999 Tuesday, August 2, 2011 12:50 PM
    Thursday, July 28, 2011 5:33 AM
  • Andrei, based on your answer, I wrote the test code below to get Excel to update the UDFs concurrently.  It seemed to work but can you see anything wrong with it? Note that the test code below triggers recalculation on cell A1, A3, A5, A7, and A9 (using 2 threads).

          // Save the old multi-threaded calculation options.
          bool oldMultiThreadedCalculationEnabled = ExcelApp.Application.MultiThreadedCalculation.Enabled;
          Excel.XlThreadMode oldMultiThreadedCalculationThreadMode = ExcelApp.Application.MultiThreadedCalculation.ThreadMode;
          int oldMultiThreadedCalculationThreadCount = ExcelApp.Application.MultiThreadedCalculation.ThreadCount;
          try
          {
            // Set the multi-threaded calculation options (i.e. only use 2 threads).
            ExcelApp.Application.MultiThreadedCalculation.Enabled = true;
            ExcelApp.Application.MultiThreadedCalculation.ThreadMode = Excel.XlThreadMode.xlThreadModeManual;
            ExcelApp.Application.MultiThreadedCalculation.ThreadCount = 2;

            // Recalculate UDFs.
            Excel.Worksheet sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
            Excel.Range formulaRange = sheet.get_Range ("A1,A3,A5,A7,A9", Type.Missing);
            formulaRange.Dirty ();
            formulaRange.Calculate ();
          }
          finally
          {
            // Restore the old multi-threaded calculation options.
            ExcelApp.Application.MultiThreadedCalculation.ThreadCount = oldMultiThreadedCalculationThreadCount;
            ExcelApp.Application.MultiThreadedCalculation.ThreadMode = oldMultiThreadedCalculationThreadMode;
            ExcelApp.Application.MultiThreadedCalculation.Enabled = oldMultiThreadedCalculationEnabled;
          }

    Thursday, July 28, 2011 11:41 AM
  • Hello,

    I don't think that Excel allows/expects a UDF to modify those properties. If it does allow doing this, then calling Range.Calculate within the current calculation loop isn't wise. I'd suggest using the COM Add-in + XLL construct here: the XLL UDF modifies the thread-related properties, calls a method in the COM add-in, the COM add-in waits a little (using a timer or a custom Windows message), calls Range.Calculate() and restores the settings.

    Nevertheless, all this machinery will not necessarily cause your functions to run in threads: Excel decides this, not you.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Thursday, July 28, 2011 1:35 PM
  • I guess I didn't explain myself clearly.  The test code in my previous reply gets run in my COM add-in (when the user clicks on a button in my add-in's GUI).  In other words, the test code doesn't get run in the UDF.
    Thursday, July 28, 2011 2:30 PM
  • Hello,

    I don't see anything that could prevent this from functioning. However, I would like to know this for sure. Have you tested this?


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Monday, August 1, 2011 3:31 PM
  • It seemed to work fine on Excel 2007 and Excel 2010 (based on my limited testing).  I used Add-In Express for my test but I suppose it would have worked with VSTO also.
    Tuesday, August 2, 2011 12:49 PM