Code hangs Excel ... Why? ... and how can i debug??

I have written an Excel a VBA module that batch pulls HTML pages into excel
(thus converting them to spreadsheats). I then extract certain cells and
save them to a table in a master workbook, I then close origional html and
repeat the process.

So long as the batch size is small it runs fine. But If I attempt to
process to many pages Excel hangs. Leaving no clue as to why and Excel's CPU
usage is 0%.

At first I just thought that it was just "processing" some code and if I
waited it should be fine. each batch loop only takes 1 to 2 minutes.

How can I pinpoint when, where and why the code fails.

Thanks, Simon.

Hi Simon,

I think you are on the right track. Over the years I have experience similar problems with Excel hanging. This has been caused mostly by insuffient RAM on the computer. Since you mention small batches run OK, I think it is a good starting point. Check your sytem resources to see how much your using with a small versus a large batch. Also, you can put breakpoints into your code at critical points. You can then check the system stack to see if maybe an overflow condition is to blame.

Hope this helps you,
Leith Ross

What happens if you hit Esc while excel is "hanging" ? Do you have to
force-quit excel to recover?

You could try adding debug statements or writing out progress to a log
file or worksheet as the batch runs. Ie. all the ususal approaches.

Tim


"Simon May" <[email protected]> wrote in message
news:[email protected]...
>
> I have written an Excel a VBA module that batch pulls HTML pages
> into excel
> (thus converting them to spreadsheats). I then extract certain
> cells and
> save them to a table in a master workbook, I then close origional
> html and
> repeat the process.
>
> So long as the batch size is small it runs fine. But If I attempt
> to
> process to many pages Excel hangs. Leaving no clue as to why and
> Excel's CPU
> usage is 0%.
>
> At first I just thought that it was just "processing" some code and
> if I
> waited it should be fine. each batch loop only takes 1 to 2 minutes.
>
> How can I pinpoint when, where and why the code fails.
>
> Thanks, Simon.