Generally speaking, though I can only speak up to Excel 2003, Excel does NOT
do well calculating data from other *CLOSED* workbooks, even if the links
are valid. To avoid some of these types of issues, I have had to set the
link updates to "Don't display alerts and don't update automatic links."
under the Edit>Links...>Startup Prompt
If your workbook has a large number of calculations and/or more
sophisticated formulas, you may want to consider using manual calculation as
otherwise, Excel does take a longer time opening workbooks as there are
redundant calculations involved when Excel is calculating, though the amount
of that redundant calculation has reduced drastically in later versions from
Excel 97.
Example:
Excel 97 prior to me ever using VBA.
Another person had a total of 18 files that were interconnected to each
other on paper, but not via formulas or any other electron means. This was
at a time when one person (the one that maintained the data) was being moved
to Accounting from Production as I was going from Accounting to Production
and the other person (who maintained the system) was leaving the company.
1 raw data file
1 intermediate process file
16 individual machine center files
Initially, I was to setup the links between these files, and then take over
the processing of this data. Prior to me doing this, they had 2 different
people working the system, one maintaining the data while the other
maintained the system itself.
Once I put in all of the links, the following steps were performed.
Update the raw data file from paper logs
Open the remaining 17 files and allow Excel to do the calculations
Save all files and close them out.
Even though the calculations themselves weren't that bad, would you believe
it took between 40 and 50 minutes just to open up all 17 files and have all
of those files calculated?
After that happened, that's when I decided it was time to learn about
macros, as at that point, I knew redundant calculations were taking place.
I knew practically nothing about VBA other than what little bit of what I
knew about BASIC, which are somewhat similar in the through process, but the
structure is in many ways different too. After so many weeks learning VBA,
I finally got it to open each workbook one by one, calculate them sheet by
sheet, save the workbooks, and close them out.
How much time did it take after I made this change?
For VBA to do this for me, though I was very much a novice at the VBA
coding, it only took Excel 97 3 to 5 minutes to process and save all 18
files. Quite a difference to go from taking 40 to 50 minutes using
automatic calculation to taking just 3 to 5 minutes using manual calculation
mode and VBA process the those files separately. Of course, not all of it
was due to calculation, some portion I'm sure also had to do with amount of
RAM being used too. Note, this change took place in Winter of 1998-1999.
One more thing, after things got to be more detail level and plenty of other
reports got added to the list of things to be done in Excel, most of which
was from data pulled from the DB system as those machine center files had
been transformed to get it's source from a DB rather than from log papers.
So by August 1999, it got to the point that it took our query program 30
minutes to process the data, then it took Excel 97 an hour to process the
data.
In the process though, I ran into various chart issues, and MS confirmed
those chart issues, which then they sent me Office 2000 free of charge as a
fix to the chart bugs in XL97, which they weren't about to fix in XL97. I
loved the fact that it cleared up the chart issues as well as many of the
other technical issues that I had with XL97, SR2, but one thing I didn't
expected and really loved about XL2000, those same reports with nothing
changed other than going from Excel 97 to Excel 2000, instead of it taking a
full 1 hour to process the data, it only took 20 minutes to process the
data. That was on the same computer with the same set of VBA codes and
spreadsheet formulas in the same production files. That's a case where the
combination of the VBA compiler improvements and Excel calculations not
being as redundant really helped out in that surprise performance boost.
In essence, I initially reduced the reporting job from a 2 person job to a 1
person job, and now it only takes about 10% of my total time to run the
system and on an as needed basis to maintain it. The only thing I have to
do extra outside of maintenance to the program is once a year, manually
archive the data, which I could have had that automated, but don't want to
risk data loss, so that's the one task I have left manually, but then the
first time the program is ran once it's becomes week 2 of the new year, the
program automatically switches itself to the new year, so I only have that
one week to gather the data, verify it, and archive it. Of course that's
done by business requirements. That's also the one week of the year that I
have deemed as can't take off from work cause of the crucial timing and that
particular week may already be shorted to a maximum number of business days
of 3 due to New Year's Eve and New Year's Day paid holidays, which in that
case, I only have that 2nd and 3rd business day to make sure all data is
updated and verified. Some data, I don't even get until the 2nd business
day of the week for the prior week, which use to be not until the 3rd
business day of the week, so it has gotten a bit easier from that stand
point of view.
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
