How Can I make excel go faster

I have a programme that I use quite often that uses heaps of Loops to do
certain things however it is at the point of where it takes a long time to
get to the next thing from 30 seconds to about 2 minutes.

That depends on what Certain things are. Turning off Calculation, Events and
Screen updating usually helps. But bad structured code may run for minutes
anyway, so may database queries to Oracle and things like that.

HTH. Best wishes Harald

"Jason Zischke" <[email protected]> skrev i melding
news:[email protected]...
> I have a programme that I use quite often that uses heaps of Loops to do
> certain things however it is at the point of where it takes a long time to
> get to the next thing from 30 seconds to about 2 minutes.

Hi Jason,

You'll have to provide some more details to enable us to help you. What is
your code? What are the volumes of the data processed? etc.
What time do you think is reasonable and why do you think so?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Jason Zischke" <[email protected]> wrote in message
news:[email protected]...
>I have a programme that I use quite often that uses heaps of Loops to do
> certain things however it is at the point of where it takes a long time to
> get to the next thing from 30 seconds to about 2 minutes.

Hey Harald Staff Thanks for the help, I've tried turning off the calculation
and that has knocked off about half the time of the code but where I find the
place to trun off Events ?

"Harald Staff" wrote:

> That depends on what Certain things are. Turning off Calculation, Events and
> Screen updating usually helps. But bad structured code may run for minutes
> anyway, so may database queries to Oracle and things like that.
>
> HTH. Best wishes Harald
>
> "Jason Zischke" <[email protected]> skrev i melding
> news:[email protected]...
> > I have a programme that I use quite often that uses heaps of Loops to do
> > certain things however it is at the point of where it takes a long time to
> > get to the next thing from 30 seconds to about 2 minutes.
>
>
>

Disabling events will only speed up code that uses event handler like the on
change event or such. Basically it avoids recursive calls (where on change
makes a change and then calls itself becuase of th echange). If your code
does not have any event handlers then this code will not speed up your
procedures even in the slightest.

application.enableevent = false

and at the end

application.eneableevents = true

Make darn sure you rememeber to turn it back on and whenever you are
toggling these settings it is a good idea to use an error handler to turn
them back on if something goes south on you... Not that that would ever
happen... . While you are debugging rememeber that if you hale execution
before you have reset these values excel will not do what you think it will.
Also if any of your code relies on change event or... events rememeber these
will not run.

HTH

"Jason Zischke" wrote:

> Hey Harald Staff Thanks for the help, I've tried turning off the calculation
> and that has knocked off about half the time of the code but where I find the
> place to trun off Events ?
>
> "Harald Staff" wrote:
>
> > That depends on what Certain things are. Turning off Calculation, Events and
> > Screen updating usually helps. But bad structured code may run for minutes
> > anyway, so may database queries to Oracle and things like that.
> >
> > HTH. Best wishes Harald
> >
> > "Jason Zischke" <[email protected]> skrev i melding
> > news:[email protected]...
> > > I have a programme that I use quite often that uses heaps of Loops to do
> > > certain things however it is at the point of where it takes a long time to
> > > get to the next thing from 30 seconds to about 2 minutes.
> >
> >
> >

Harald Staff, don't worry about my last post I found how to do it and will
try it as soon as I have access to it, Thanks For your help.

"Jason Zischke" wrote:

> Hey Harald Staff Thanks for the help, I've tried turning off the calculation
> and that has knocked off about half the time of the code but where I find the
> place to trun off Events ?
>
> "Harald Staff" wrote:
>
> > That depends on what Certain things are. Turning off Calculation, Events and
> > Screen updating usually helps. But bad structured code may run for minutes
> > anyway, so may database queries to Oracle and things like that.
> >
> > HTH. Best wishes Harald
> >
> > "Jason Zischke" <[email protected]> skrev i melding
> > news:[email protected]...
> > > I have a programme that I use quite often that uses heaps of Loops to do
> > > certain things however it is at the point of where it takes a long time to
> > > get to the next thing from 30 seconds to about 2 minutes.
> >
> >
> >

>Disabling events will only speed up code that uses event handler like the on
>change event or such.... If your code does not have any event handlers then
>this code will not speed up your procedures

I've often wondered about that. I can envision 2 scenarios:

1. When an event happens, Excel looks for event handlers; if not found, then
"go about it's business"? If that's the case, then perhaps EnableEvents tells
it not to bother looking, and the statement WOULD speed things up.

2. Excel scans your workbook for the presence of event handlers when it first
loads and sets some sort of flag to indicate the result. In that case
EnableEvents would not make a difference, as you say.

Hi Myrna,

Myrna Larson wrote:
>> Disabling events will only speed up code that uses event handler
>> like the on change event or such.... If your code does not have any
>> event handlers then this code will not speed up your procedures
>
> I've often wondered about that. I can envision 2 scenarios:
>
> 1. When an event happens, Excel looks for event handlers; if not
> found, then "go about it's business"? If that's the case, then
> perhaps EnableEvents tells it not to bother looking, and the
> statement WOULD speed things up.

I think this scenario is probably closer to reality. I would guess that the
EnableEvents property tells the Application object and its child objects to
avoid raising events altogether - thus, there is nothing for the event
handlers to "listen" to.

I didn't think setting EnableEvents to False would speed things up much, but
it did speed things up quite considerably in my limited testing. My test
consisted of writing values to a cell 100,000 times under these 2
environments:

1) ScreenUpdating=False, Calculation=xlCalculationManual, EnableEvents=True

2) ScreenUpdating=False, Calculation=xlCalculationManual, EnableEvents=False

I ran this test about 20 times. My tests indicated that environment 2
(events disabled) resulted in a decrease in execution time of about 25%.

The performance gain will obviously depend on how many things in your code
would trigger Excel-related events. Since every action in this test would
trigger the Change event, I would guess that 25% is the maximum performance
gain you could expect in the wild.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

That's good...as long as your code is not using the contents of the
worksheet(s) for further analysis.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Hey Harald Staff Thanks for the help, I've tried turning off the calculation
> and that has knocked off about half the time of the code but where I find the
> place to trun off Events ?
>
> "Harald Staff" wrote:
>
> > That depends on what Certain things are. Turning off Calculation, Events and
> > Screen updating usually helps. But bad structured code may run for minutes
> > anyway, so may database queries to Oracle and things like that.
> >
> > HTH. Best wishes Harald
> >
> > "Jason Zischke" <[email protected]> skrev i melding
> > news:[email protected]...
> > > I have a programme that I use quite often that uses heaps of Loops to do
> > > certain things however it is at the point of where it takes a long time to
> > > get to the next thing from 30 seconds to about 2 minutes.
> >
> >
> >
>