I have a VBA program that I just created to process large amounts of
ECG data that works fine except for one little UI bug. Due to the
size of the data files which can have anywhere from 180,000+ to
600,000+ data points, I included a progress bar so that the user
could see how far along the program is when it is reading the source
data file and then down-converting (reducing the high frequency data
to low frequency data). The problem is that after a few refreshes,
the screen stops updating even though the program is still running;
thus from the user’s point of view the program appears to have
stalled despite the fact that it is still working.

This is not the first time I have had issues with the screen not
refreshing properly or Excel VBA code acting as if it is not present.
The way the program is coded, the progress bar form should be
repainted for every five percent increase in the processing of the
complete data set. At first the progress bar works fine, but about 60
to 75 percent into reading data from the source file screen refreshes
just stop. When the data is read, the progress bar should reset and
inform the user that it is converting the data set, but the progress
bar form remains frozen until the form is deactivated after data
processing is completed.

Here is a sample of the code from one of the progress bar forms:

Private Sub
UpdateECGProgressBar(progressCompleted As Double)
' Update progress bar for every five percent completed.

If progressCompleted * 100 = progress Then
progress = progress + 5
lblProgressBar.Width = progressCompleted * 236
frmProcessECGData.Repaint
End If
End Sub

Private Sub AcquireECGData()
' Get BPM and ECG Data from text file created by BSL® Pro.

Dim filename As String
Dim fileHeader As String
Dim dataline As String
Dim fileNumber As Integer
Dim index As Long
Dim timeBase As Double
Dim dTime As Double

ReDim ecgSamples(1) As ECGDataType 'reset ECG data object

progress = 0
timeBase = 0#
dTime = 1 / ecgInputRate

filename = FindFilename(ecgSourceFile)
fileNumber = FreeFile

If filename < "False" Then
'set default output filenames
ecgAvgFilename = Left(filename, Len(filename) -
4) & " - avg (ECG).txt"
ecgMaxFilename = Left(filename, Len(filename) -
4) & " - max (ECG).txt"

'open source file and strip header line
Open ecgSourceFile For Input As #fileNumber
Line Input #fileNumber, fileHeader

index = 1
While Not EOF(fileNumber)
'acquire ECG data from source file
ReDim Preserve ecgSamples(index) As ECGDataType
With ecgSamples(index)
Line Input #fileNumber, dataline
.BPM = CDbl(Left(dataline, InStr(dataline,
vbTab) - 1))
.mV = CDbl(Right(dataline, Len(dataline) -
InStrRev(dataline, vbTab)))
.sampleTime = timeBase + (index - 1) * dTime
End With

'update progress bar
Call UpdateECGProgressBar(index / nECGSamples)
index = index + 1
Wend

Close #fileNumber
End If
End Sub
Any ideas as to why Excel is choosing to ignore the
UpdateECGProgressBar code after a certain point? By the way, there
have been times when the program has worked as expected so it should
not be an issue with the code itself so much as it is an issue with
VBA Excel executing code that is clearly there.

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.
--
- K Dales


"mddawson - ExcelForums.com" wrote:

I have a VBA program that I just created to process large amounts of
ECG data that works fine except for one little UI bug. Due to the
size of the data files which can have anywhere from 180,000+ to
600,000+ data points, I included a progress bar so that the user
could see how far along the program is when it is reading the source
data file and then down-converting (reducing the high frequency data
to low frequency data). The problem is that after a few refreshes,
the screen stops updating even though the program is still running;
thus from the userÂ’s point of view the program appears to have
stalled despite the fact that it is still working.

This is not the first time I have had issues with the screen not
refreshing properly or Excel VBA code acting as if it is not present.
The way the program is coded, the progress bar form should be
repainted for every five percent increase in the processing of the
complete data set. At first the progress bar works fine, but about 60
to 75 percent into reading data from the source file screen refreshes
just stop. When the data is read, the progress bar should reset and
inform the user that it is converting the data set, but the progress
bar form remains frozen until the form is deactivated after data
processing is completed.

Here is a sample of the code from one of the progress bar forms:

Private Sub
UpdateECGProgressBar(progressCompleted As Double)
' Update progress bar for every five percent completed.

If progressCompleted * 100 = progress Then
progress = progress + 5
lblProgressBar.Width = progressCompleted * 236
frmProcessECGData.Repaint
End If
End Sub

Private Sub AcquireECGData()
' Get BPM and ECG Data from text file created by BSL® Pro.

Dim filename As String
Dim fileHeader As String
Dim dataline As String
Dim fileNumber As Integer
Dim index As Long
Dim timeBase As Double
Dim dTime As Double

ReDim ecgSamples(1) As ECGDataType 'reset ECG data object

progress = 0
timeBase = 0#
dTime = 1 / ecgInputRate

filename = FindFilename(ecgSourceFile)
fileNumber = FreeFile

If filename < "False" Then
'set default output filenames
ecgAvgFilename = Left(filename, Len(filename) -
4) & " - avg (ECG).txt"
ecgMaxFilename = Left(filename, Len(filename) -
4) & " - max (ECG).txt"

'open source file and strip header line
Open ecgSourceFile For Input As #fileNumber
Line Input #fileNumber, fileHeader

index = 1
While Not EOF(fileNumber)
'acquire ECG data from source file
ReDim Preserve ecgSamples(index) As ECGDataType
With ecgSamples(index)
Line Input #fileNumber, dataline
.BPM = CDbl(Left(dataline, InStr(dataline,
vbTab) - 1))
.mV = CDbl(Right(dataline, Len(dataline) -
InStrRev(dataline, vbTab)))
.sampleTime = timeBase + (index - 1) * dTime
End With

'update progress bar
Call UpdateECGProgressBar(index / nECGSamples)
index = index + 1
Wend

Close #fileNumber
End If
End Sub
Any ideas as to why Excel is choosing to ignore the
UpdateECGProgressBar code after a certain point? By the way, there
have been times when the program has worked as expected so it should
not be an issue with the code itself so much as it is an issue with
VBA Excel executing code that is clearly there.

K Dales wrote

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

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

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

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

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

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

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

Private Sub
UpdateECGProgressBar(progressCompleted As Double)
' Update progress bar for every five percent completed.

If progressCompleted * 100 = progress Then
progress = progress + 5
lblProgressBar.Width = progressCompleted * 236
frmProcessECGData.Repaint
doevents
End If
End Sub

might help

--
Regards,
Tom Ogilvy

"mddawson - ExcelForums.com"
wrote in message ...
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.

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!

Check out this link on memory. It depends more on what version of Excel than
on your system parameters...

http://www.decisionmodels.com/index.htm
--
HTH...

Jim Thomlinson


"davegb" wrote:


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!

Thanks, Tom. So far adding DoEvents seems to be helping, but it could
be just one of those times where the code has opted to work. One
thing that seems to be different is the execution time. Even when the
progress bar worked as coded previously, the progress seemed to slow
as more data was read, that is, the first half of the data would be
read fairly quick but then the data transfer rate (according to the
progress bar) would get slower for each five percent increase in
progress. Now the reading of the source data is consistent over the
entire read which seems to be occurring much faster.

I find it Microsoft typical that this methodology would be a
requirement as opposed to unnecessary in what is a fairly simple
program.