I was asked to give some advice on a spreadsheet for my father-in-
law. From an electronic measuring device it pulls in 4 sheets each
containing 400,000 rows of two columns; a time value and then a
measured value. Once the data is in the workbook a routine is run
that churns it all upp and spits out some results, a process that
takes about 20 minutes to run. A good chunk of the processing logic
is VBA code, which so far as I can tell runs single threaded.

This machine is a 3 Ghz quad core with 8 GB of DDR-2 memory and a fast
SATA HDD and the memory usage rarely goes above 600MB, CPU% is about
30 and the hard drive blinks only intermittently.

Is there any way I can make Excel better utilize the system resources
and run faster?

The description of what you are up to is rather thin. If you are processing
1.2 million records though there is no way you will do that quickly. As for
Multi-thread that is not going to happen with VBA. In terms of speed and
memory here is an excelent resource...

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

As for the code if you posted it we might be able to give it a few tweeks to
speed it up...
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> I was asked to give some advice on a spreadsheet for my father-in-
> law. From an electronic measuring device it pulls in 4 sheets each
> containing 400,000 rows of two columns; a time value and then a
> measured value. Once the data is in the workbook a routine is run
> that churns it all upp and spits out some results, a process that
> takes about 20 minutes to run. A good chunk of the processing logic
> is VBA code, which so far as I can tell runs single threaded.
>
> This machine is a 3 Ghz quad core with 8 GB of DDR-2 memory and a fast
> SATA HDD and the memory usage rarely goes above 600MB, CPU% is about
> 30 and the hard drive blinks only intermittently.
>
> Is there any way I can make Excel better utilize the system resources
> and run faster?
>

1) If you are doing a statistical analysis of the data, it may be possible to use a subset of the
data rather than the full data: i.e., pull one out of every 10, 25, 100, 400, 1000 records - depends
on the relative speeds of the data collection versus data change - measuring room temperature every
millisecond won't give your any better average than measuring it once every minute.

2) If you are doing calcs in VBA, consider using Excel formulas instead - they are MUCH faster. For
example, stepping through values to find the Max value is waaaaay slow compared to
Application.Max(Range(...))

3) Post your code...

HTH,
Bernie
MS Excel MVP


<(E-Mail Removed)> wrote in message
news:ac9bc3b2-d079-42dd-9957-(E-Mail Removed)...
>I was asked to give some advice on a spreadsheet for my father-in-
> law. From an electronic measuring device it pulls in 4 sheets each
> containing 400,000 rows of two columns; a time value and then a
> measured value. Once the data is in the workbook a routine is run
> that churns it all upp and spits out some results, a process that
> takes about 20 minutes to run. A good chunk of the processing logic
> is VBA code, which so far as I can tell runs single threaded.
>
> This machine is a 3 Ghz quad core with 8 GB of DDR-2 memory and a fast
> SATA HDD and the memory usage rarely goes above 600MB, CPU% is about
> 30 and the hard drive blinks only intermittently.
>
> Is there any way I can make Excel better utilize the system resources
> and run faster?

On Sep 29, 1:31*pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:
> 1) If you are doing a statistical analysis of the data, it may be possible to use a subset of the
> data rather than the full data: i.e., pull one out of every 10, 25, 100, 400, 1000 records - depends
> on the relative speeds of the data collection versus data change - measuring room temperature every
> millisecond won't give your any better average than measuring it once every minute.
>
> 2) If you are doing calcs in VBA, consider using Excel formulas instead -they are MUCH faster. *For
> example, stepping through values to find the Max value is waaaaay slow compared to
> Application.Max(Range(...))
>
> 3) Post your code...
>
> HTH,
> Bernie
> MS Excel MVP
>
> <es33...@gmail.com> wrote in message
>
> news:ac9bc3b2-d079-42dd-9957-(E-Mail Removed)...
>
>
>
> >I was asked to give some advice on a spreadsheet for my father-in-
> > law. *From an electronic measuring device it pulls in 4 sheets each
> > containing 400,000 rows of two columns; a time value and then a
> > measured value. *Once the data is in the workbook a routine is run
> > that churns it all upp and spits out some results, a process that
> > takes about 20 minutes to run. *A good chunk of the processing logic
> > is VBA code, which so far as I can tell runs single threaded.
>
> > This machine is a 3 Ghz quad core with 8 GB of DDR-2 memory and a fast
> > SATA HDD and the memory usage rarely goes above 600MB, CPU% is about
> > 30 and the hard drive blinks only intermittently.
>
> > Is there any way I can make Excel better utilize the system resources
> > and run faster?- Hide quoted text -
>
> - Show quoted text -

To both posters: I apologize on the lack of details. The spreadsheet
was actually written by somebody else and while he complains about the
speed my f-i-l is so tired of hearing the complaints that he asked if
I could do anything. I got to see it run so I'll add some details.
First, this is Excel 2007 on Vista 64

1. While the monitoring tool produces 4 sets of data all of them are
copied to a single sheet by a macro.

2. The data must all be used; no sampling is possible because one
part of the process scans the column searching for the largest value.
There are actually several of these scans performed as the spreadsheet
calculates many other columns (at least 14) and scans those output
values as well.

3. A great number of the cells are formulas and some are fairly
involved. If I read it right there are several fields that are linear
regessions on hundreds of data pairs.

I will provide additional info if I get any.