VBA Execution Speed for row paste sometime is SLOW

[photonblaster]

Hi THere!

I have a vba calculation problem. I have VBA code that accepts data input and stores it in a row in one sheet. I have another sheet that I add a row of cells to with formula to analyze this data set. Since there can be thousands of sets of data, and the analysis row for any one set is very complex( probably 5000 logic and arithmetic terms in the row, and I use almost all of the 256 available columns) the worksheet can get very big very fast. Fortunately, after the analysis is done on a set of data, that row will never change value so I can replace that row with its values. I use this code line, which seems to faster than copy/pase special values even if I use a range rather that do the whole row:

wsAnalysis.Rows(iData_RowM1).Formula = wsAnalysis.Rows(iData_RowM1).Value

This works great, and calculation time for each new set of data is very acceptable, about a quarter of a second. Except.....

The problem I am having is that every once and a while (about once every ten or twenty rows), the time it takes to convert a row of formulas to their values goes crazy, and can take several seconds. As the workwheet gets bigger, these anomalies get worse, and can get much longer. This is unacceptable in the application, since the data comes in about twice a second.

Accurate timing analysis pinpoints the problem to the the paste operation, everything else seems to work OK.

I have what I think are all the usual "tricks" in place, screen updating is off, calculation is manual, all formating is removed, background error checking is off. and I empty the Office clipboard every iteration.

I am guessing that Excel every once and a while has a big increase in overhead while it updates the interdependencies of the cells, or something like that.

Does anyone have suggestions on how to track down the cause of this anomalous cycle time and how to get rid of it?
OR, is there a better algorithm to use for what I am doing to keep the workbook size under control?

 

[photonblaster]

update
Since I have gotten no advice, I decided to use the (to me, horrible) work around of pasting the incomming data into a fixed location (this includes the previous 30 rows of data and analysis of that data since the formulas use history of data/analysis as well as current values). This eliminates the copy of formula values onto the same row as the formulas that gives the semi-random long paste times, but unfortunately it means I have to have a second workbook to look at all the data/analyis during the development phase. At least it eliminates the VBA/EXCEL problem. I find VBA to be, by a wide margin, one of the most unintuitive, quircky implementations of software I have ever come accross, and would not be using it if I have any real choice (I need the flexibility of the worksheets in my project). I hope that the new version makes the results more predictable.