You may encounter multiple errors while performing large number of object model calls from a VSTO 3.0 Excel add-in

  • Article

Suppose you have a VSTO 3.0 add-in for Excel, which performs some Excel object model calls. You notice that it works fine with less number of object model calls but starts failing when the number of object model calls is huge.

Eg. You may be iterating/writing through large number of rows(around 1000) in your Workbook.

The most surprising aspect is that you get different error every time and this aspect of multiple errors makes the issue very puzzling.

Some of the errors you may encounter are - 

Type mismatch - DISP_E_TYPEMISMATCH

Out of memory

Operator NOT is not defined

Return argument has an invalid type

  

The reason for the above errors may be that you have encountered an edge case where the LCID proxy feature of VSTO projects for Excel causes exceptions when iterating through large numbers of Excel objects.

The feature Excel1033Proxy creates proxy objects (wrappers) to make calls using the en-US (1033) culture. While doing so, it seems to be leaking these wrapper objects. This in the excessive automation (large number of OM
calls), becomes a significant leak leading to erratic behavior.

 To work around this issue, you can disable the LCID proxy feature. This feature was introduced to allow Excel solutions created by using VSTO to work seamlessly on end user computers that are not running in the en-US locale.

For more details about this feature, see https://msdn.microsoft.com/en-us/library/ms268748.aspx.

To turn off this feature, you can disable it globally for your entire project by going into the AssemblyInfo file and changing the [assembly:ExcelLocale1033(true)] attribute to false. However, you should note that if your
solution is going to be used in locales other than en-US, then the thread locale will be the user’s locale (and not en-US) hence the arguments (such as date time, numbers) being passed to the Excel Object Model calls

need to be in the user’s locale.

This is a known bug in VSTO 3.0 and the good news is that this problem has already been fixed in VSTO 4.0.