Setting Multi-Threaded Calculation To 3 Processors Using Add-In?

Hey,

Recently the company I work for moved onto Citrix and we found unless you limit the number of processors that Excel can use you can end up with performance issues. We believe the performance issues are caused by Excel taking system resource away from the network controller on the virtual PC and causing lookups to essentially hang Excel. So I want to set the number of processors Excel can use to 3 which leaves 1 alone, when we do this for users who are having troubles this has always fixed the issue. However as Microsoft wont let currently control the number of processors used by Excel with group policy or registry the only option I have found is using an Excel add-in which I found on this website: http://www.remkoweijnen.nl/blog/2012.../#comment-5633

Below is the code I am using in my add-in, which works perfectly for any new documents which are created in Excel. My problem is I can't get the add-in to apply to existing documents and I can't understand why. I have saved the add-in as an XLA file and placed it into the C:\Program Files\Microsoft Office\Office14\XLSTART folder. If I open the existing document and then double click the add-in, then it applies I just can't get it to run automatically.

Private Sub Workbook_Open()
' Manual Mode '
Application.MultiThreadedCalculation.ThreadMode = xlThreadModeManual

' Use 3 CPU '
Application.MultiThreadedCalculation.ThreadCount = 3
End Sub

Cheers,

John

Thanks Andy,

After doing some testing I worked out the problem was the event was running before the workbook opens. This changed the setting, but when the workbooked opened it changed the setting back. So we added two additional events to cover all eventualities.

Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
' Use 3 CPU Threads'
Application.MultiThreadedCalculation.ThreadCount = 3

End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
' Use 3 CPU Threads'
Application.MultiThreadedCalculation.ThreadCount = 3

End Sub

Private Sub Workbook_Open()
Set App = Application
' Use 3 CPU Threads'
Application.MultiThreadedCalculation.ThreadCount = 3

End Sub

There is a simpler and more effective solution for RDS/TS environments.
Look for a free application called Thread Locker.
Its a dotnet app that runs as a service and allows you to set processor affinity on the fly for a specific process or processes.
This was a life saver for me at one time but I suspect it is more commonly needed as apps become multi core processing capable.
Hope this helps.