PDA

View Full Version : Which is better? Processing speed question



klandreth
08-23-2012, 02:16 PM
I have an Excel spreadsheet that contains a 'master' list of data. Periodically, people supply me with 'updates' to the list. The 'master' spreadsheet contains routines concerned with determining if the 'update' is an 'add' or is it an 'update' to an existing record, and proceeds accordingly.

One routine in particular is concerned with validating a part number. The code resides in the Worksheet_Change Event routine. It is only triggered by a "change" to the part number, which is typically an addition of a new record to the spreadsheet.

Is this the best place for this code, or could it be moved into a subroutine that is called when necessary? Does the Change Event generate much overhead?

Currently the spreadsheet contains 25,000 records, and update files range anywhere from a few records to a few hundred (occassionally a few thousand, but I typically break those down into smaller files).

Thank you for your assistance, and your website.

Kenneth Hobs
08-23-2012, 06:43 PM
Depends. Did you use Intersect()? Did you make it to handle one change at a time or many at once? e.g. Paste one cell or delete one cell or paste to many cells or delete many cells in intersection range. Did you turn off events, calculation, and screen updates? In other words, we need to see the code.

Bob Phillips
08-24-2012, 02:29 AM
To add to Kenneth's comments, generally a well-written change event procedure will just affect a single cell target, or a multiple cell range, but not a large range. It's effect should then be on a limited number of dependent cells. In these circumstances it should be very efficient.

If the change event is likely to have a large target range, and/or have a large number of affected, dependent cells, then it is likely that a change event is not the right choice, as you do not have control over these many updates.

klandreth
08-24-2012, 02:41 AM
Thanks all. Seeing the code...it is on a system that is locked down (no USB, NO CD), so the only recourse I have is to re-type the code.

With the aid of debug.print, I see the event is triggered by changes to other cells on the 47 column worksheet, mostly formatting changes, nothing data-related. However, the event does start with an Intersect command in order to only process changes to cells in Column "O". It just seems to me needless event tripping could slow the entire process down, but maybe I'm being overly cautious.

Bob Phillips
08-24-2012, 02:54 AM
It won't be needless. Every time a cell is updated it will call the event code, but if there is a check using Intersect, such as

If Not Intersect(Target, Me.Columns("O")) IS Nothing Then

it will only do anything else when a cell in column O changes.

So, if it doesn't going off on a wild spree from there changing lots of other cells, potentially all triggering a call to this change event, it should be quick and efficient.

snb
08-24-2012, 04:12 AM
Since the updating is 'periodically' and not 'permanently' I wouldn't opt for a 'change-event'. The change-event is a 'permanent' watch for cheanges in a workbook.
A macro that will be performed only if necessary (after an update) seems to be more appropriate.