How can I call the Windows API in VBA in order to programmatically set the
affinity used by Excel.
DAN

This applies to Excel 2007 only. I've not tried/tested it under earlier
versions, plus the Tools/Options setting to turn it off is not available in
earlier versions.

I'm not certain you can set the affinity to a specific CPU, but you can
control multi-threading in Excel easily enough. (Although an API call to the
OS may be able to do so).

If you don't mind always running without Multithreaded recalc, then you can
'disable it in the Office Button-Excel Options-Advanced-Formulas settings.
'Note: This change may impact calculation times for other books.

Programmatically you can do this with code similar to the following:

Dim multiThreadCount As Long
Dim multiThreadMode As XlThreadMode
Dim multiThreadEnabled As Boolean
multiThreadEnabled = Application.MultiThreadedCalculation.Enabled
If (multiThreadEnabled) Then
multiThreadCount = Application.MultiThreadedCalculation.threadCount
multiThreadMode = Application.MultiThreadedCalculation.ThreadMode
End If
'Disable multithreading temporarily
Application.MultiThreadedCalculation.Enabled = False

'YOUR CODE HERE

'Restore the Multithreaded State
Application.MultiThreadedCalculation.Enabled = multiThreadEnabled
If (multiThreadEnabled) Then
Application.MultiThreadedCalculation.threadCount = multiThreadCount
Application.MultiThreadedCalculation.ThreadMode = multiThreadMode
End If

Presumably that would all be within a Sub or Function declaration. You
could probably even set it up within the Workbook_Open() and _BeforeClose()
events with the first part to turn it off in the _Open() and the portion to
restore it in the _BeforeClose() event handler.


"Dan" wrote:

How can I call the Windows API in VBA in order to programmatically set the
affinity used by Excel.
DAN