Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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" skrev i melding ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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" skrev i melding ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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" skrev i melding ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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" skrev i melding ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I make excel go faster
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make reports faster in excel using ADO | Excel Discussion (Misc queries) | |||
Make Excel Work faster | New Users to Excel | |||
make my vba/excel program faster | Excel Programming | |||
make my vba/excel program faster | Excel Programming | |||
make my vba/excel program faster | Excel Programming |