Few month back, I encountered a strange problem with an excel add-in project. The summary of the problem is as follows. We have an excel addin which the customer use to fetch and see the production data on excel sheet using custom functions defined by the addin. Strangely, in a filtered sheet, the data being subscribed was incorrect with duplicate values floating around. After scratching my head for a while, I pin pointed the problem to this single API call:
range.Formula = formulaMatrix;
Due to an internal bug in Microsoft’s API, if you try to set some data using the above call on a filtered excel column, it gives incorrect results. I also reproduced the problem on a self contained sample application. You can download it here. To reproduce the problem, simply build the project and open the excel sheet provided. Then click on “set Data” button to verify that the values in the cells are different from that in the code.
Given that this was simply an API call, there was nothing much I could do. But I found a workaround for this problem courtesy Starain, who suggested me to use Range.FormulaArray instead of Range.Formula for the data assignment. Unfortunately, after testing I found that it has serious performace implications given the large data customer generally plays with, I could not use the above workaround. However if you are not concerned about performance or have little data to manupulate, you can use the workaround. I also suggest to disable automatic calculation before performing the data assignment to reduce the performace hit. Good luck!