mddawson - ExcelForums.com wrote:
K Dales wrote:

How are your system resources when this happens? And is there a
correlation
between the amount of data and the behavior (do the times when it
freezes
correspond to the times you have more data to process)? You might
simply be
overwhelming your computer' s processor and memory.
That's the weird part, I have more windows open now and the progress
bar has worked correctly for the past few runs of the program. The
computer I am using has a 2.2 GHz Pentium 4 and 768MB of RAM, so the
system resources should not be overwhelmed. I will look into this per
your suggestion.

[b:40bf563f54]Jim Thomlinson[/b:40bf563f54] wrote:

Excel is a little thin when it comes to processing 180,000 to
600,000 rows.
Thanks Jim, this program was written to deal with the 65,535 row
limit. We realized that there would be a problem with the ECG data
because the software we use samples at 1000 Hz and the trials we were
running with the birds were either 5 minutes (300,000 samples) or 10
minutes (600,000 samples) long, excepting the reference (datum)
trials which were only 60 seconds. Even more of an issue was that we
needed to be able to recreate the ECG chart in Excel as most of the
people involved in this project do not have the ECG software, but
Excel has an arcane 30,000 point limit for charts.

What this program does is read the source data created from Biopac
Student Lab Pro® into an object called ecgSamples() then it
re-samples the data to a lower frequency while maintaining signal
information integrity by using moving maximums. (We typically create
output files at 10Hz down from the 1000Hz source.) Everything occurs
on Excel's back-end so data is never sent to a worksheet. In the end
an output file is generated with a data set of a size that fits well
within the maximum limits for the number of data points in an Excel
chart.

As I stated in my original post, the program handles the data
processing as expected, but the updating of the form which contains
the progress bar has a tendency to freeze even though program
execution continues. As it can take some time for the data to be read
and processed, I added the progress bar so that anyone using the
software would not think that the program has stalled, but when this
occurs it has the opposite effect-the progress bar stalls, the
program does not.

The routines which call the progress bar update subroutine call the
routine every time a data line is read from the source file (shown in
the code provided) or a block of data is re-sampled to the output data
frequency (subroutine not shown). To eliminate flicker, the progress
bar updating routines were intentionally set up to repaint the form
only for each five percent increment of loading/processing progress.
At random times, it appears that Excel simply chooses to ignore the
repainting code.

I had another project a few months ago where Excel behaved in much the
same manner. Certain subroutines had obviously executed as they should
have, but certain lines of code would simply be ignored by Excel as
was evidenced by custom menus being rendered improperly or not
displaying at all, default menu bars not being restored, toolbars not
being restored, etc. The reason I know that Excel was selectively
ignoring lines of code was because none of these operations were in a
vacuum and the code around them had clearly been executed. In fact,
while the final program worked perfectly fine on my computer (most of
the time) it would not work correctly if run on a number of other
machines with the same version of Excel. I have been programming for
over two decades and I have never seen anything this haphazard until
VBA Excel.

Not exactly a big surprise to me, though I've only been doing VBA a few
months. I have one particular program that takes a raw datafile,
downloaded by SPSS, cleans up some garbage left by SPSS, changes
column headers and saves the file under a modified name in another
directory. Normally, there are 8 files to do, it seldom can do all 8 in
one run. XL crashes. I have to check to see how many files it processed
(can be 0 to 8), remove the ones it has processed from the source
directory and run it again. Sometimes it does all 8 in a single run
(rarely), and sometimes it takes 10 or 12 tries to get all 8 files
done. I have no idea what determines how many runs it takes. Got 1G of
memory, so it's not a memory issue. Have shut down other apps when I
run it, no effect. Came on here and got advised to clean out my Temp
directory, but I can't tell it it's made any noticeable difference. I'm
not impressed with VBA'a robustness!