microsoft.public.excel.programming

RB Smissaert

12/17/2006 10:47:00 AM

Maybe doing all in SQL might be your answer.
You can do that either in Excel sheets or maybe it is better to move the
data to a database.
If you opt for the last then have a look at SQLite.

RBS


"--== Alain ==--" <nospam@noemail.com> wrote in message
news:uMGWEqbIHHA.4216@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I try to improve my VBA macros in the following fields :
> - execution speed
> - reduce the CPU usage
> - reduce the memory usage
>
> i have several sheets including around 21.000 records per sheet.
> My first idea was to implement a simple cells comparison among sheets but
> even if application.screenupdate is set to false, it tooks around 3 hours
> to perform all needed tasks.
>
> Therefore, i worked around a collection solution, but it took let time to
> perform the same tasks (around 1 hour) but it takes still a lot of CPU and
> memory resources.
>
> I tried to improve code by creating a Class module in couple with
> collection, but it really increased memory usage by 200 % :-( now i need
> around 350 Mb of RAM to execute it.
>
> So i would like to know what is the best method to :
> - compare very large "tables" (sheets) e.g : around 21.000 records each
> the main tasks are :
> - comparison between 3 sheets data
> - scanning 1 sheet based on criteria from another sheet
> - to limit CPU and Memory usage
> - to increase execution code.
>
> thanks a lot,
>
> Al.