Multi Threading

link - docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel


Excel 2007

Excel 2007 introduced the concept of multi-threaded recalculation of workbooks.
If a computer has multiple processors then the operating system will allocate the threads in the most efficient way.
Excel tries to identify parts of the calculation chain that can be recalculated concurrently on different threads.
When a workbook opens, the operating system determines how many processors are available and creates a separate calculation thread for each processor.
These threads can then run in parallel
The benefit of having multiple threads is directly related to the number of independent calculation trees (or formula chains) that you have in that workbook.
A workbook with one continuous chain of dependencies will not show any performance gain.
The level of improvement is workbook specific.
It depends on how many independent calculation trees each workbook contains
A workbook that contains one continuous chain of formulas will not see any improvement.
A workbook that contains several independent chains of formulas will show an improvement equivalent to the number of processors available.


Features That Do Not Benefit

The following features do not benefit from the multi-threaded capability.
*) User defined functions written in VBA.
*) User defined functions available through COM/Automation add-ins
*) User defined functions available through XLL (unless they have been updated)
*) XLM macro sheet user defined functions,
*) Cells in circular reference loops
*) Range.Calculate and Range.CalculateRowMajorOrder
*) Worksheet functions relating to pivot tables and cubes
*) Functions and operators within conditional formatting expressions
*) Functions and operators within defined name definitions used in worksheet formulas


Thread Safe Functions

All the built-in worksheet functions except:
Cell, Indirect, GetPivotData, Address, Error.Type, Phonetic, Hyperlink
All the database functions (12)
All the cube functions (7)
XLL functions registered as thread-safe
All unary and binary operators in Excel


Application.MultiThreadedCalculation

Returns an object that controls the multi-threaded recalculation settings

Application.MultiThreadedCalculation.Enabled = True 

There are 6 properties on this object.
Application - The Excel application object
Creator - A 32-bit integer that indicates which application created this object
Enabled - Allows the object to be enabled or disabled at run-time
Parent - The parent object
ThreadCount - The total count of the process threads
ThreadMode - The type of thread being applied xlThreadMode


VBA Code

Formulas > VBA - Calculation


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext