How to Keep
Excel Running Smoothly
If Excel sometimes grinds to a halt when you're working with a
lot of CDXZipStream or CDXStreamer data, here are a couple of
suggestions.
CDXZipStream and CDXStreamer usually return data in
the form of Excel formulas or array formulas. Regardless of the
sources of the formulas, however, Excel does tend to slow down, or
even seem to freeze, when there are a lot of formulas in a worksheet.
Remember that Excel automatically recalculates all affected
formulas whenever a change is made in a worksheet value, and
depending upon the formulas and the number of affected cells, this
can take a long time.
So first
consider removing formulas from your worksheet if you don't need them
anymore. Formulas are great for copying and applying to new
data, but if you are done using them you can copy the formula range
and paste it back to the same location as values. The formulas
disappear, and no recalculation will occur again. (If desired, you
can also retain a small part of the data as formulas for future use.)
For more detail on how to do this, please see this short tutorial from Microsoft.
When using the
address verification function in CDXStreamer, you can also check the
"Set Array to Values" option and all the data will be
returned as values, not formulas.
However, if
you want to retain formulas in your worksheet, you can control when
recalculation occurs by turning off automatic recalculation, and then
recalculating only when it's convenient. In later versions of
Excel, click on the File tab (or Office button in Excel 2007), and
from the "Options" area select "Formulas", and
then the desired setting under "Workbook Calculation".
The workbook will recalculate only when saving, or you can
force recalculation by using F9 or the "Calculate Now"
command under the Formulas tab.
Whenever you
have a lot of data and related formulas in a worksheet, manual
recalculation can save you a lot of waiting time. Consider
turning off automatic recalculation (whether you're using our add-ins
or not) to be in full control of your worksheet data and make your
Excel sessions less frustrating and more efficient. For more
information on using manual recalculation in all Excel versions,
please see this Microsoft article.
