Fortune 40 analyst seeks explanation for Excel/VBA quirk

[J.D. Hildebrand]

My wife is an analyst with a very large company. She has recently changed
groups within the company and inherited responsibility for about 400
worksheets, each of which contains a Validate subroutine. She's a good
programmer -- comp. sci. from Cornell; C doesn't scare her -- and she's been
cleaning up and extending Validate for the past few weeks. Now she's doing
testing for a new release. All 400 worksheets will be rolled out to the
users at once tomorrow, with the new Validate code in place.

Except...she ran one test too many. Here's the e-mail I got:

I have an Excel "template" spreadsheet which has several macros
associated with it.
In particular, there's a subroutine StandAloneValidate, which is
associated with a
shortcut key, which has 3 lines of code:

Dim RC as Boolean
Call Validate(RC)
End Sub

The Validate subroutine has lots of logic, calls several functions, etc.
I've added
lots of functionality to it in the last few weeks. It's been working
nicely.

Today something odd happened:

I did a bunch of testing with spreadsheets that had just been
created/updated
with the VBA in my template spreadsheet. They worked correctly.

I edited the VBA code throughout to add comments -- nothing more.

I brought this updated code into my "test" spreadsheet.

I ran the StandAloneValidate macro.

I got an error on the Call Validate(RC) line of code; the error
occured on the
return from the Validate subroutine. I didn't write the error down,
but it was
something about a problem calling a DLL function.

The error occured both from running the code in the VBA environment,
and
running it from the spreadsheet using the shortcut key. Rebooting
didn't help.

I renamed the Validate subroutine to ValidateData, and the problem went
away.

I renamed it back to Validate just to see -- well, just to see. Ran just
fine.
No problem.

Now I can't reproduce the error. Going back to an older version of the
code
and pasting in my edits doesn't cause the error. (I didn't save a copy of
the
spreadsheet when it was causing the error!)

Any idea what caused my error? Any idea of if it will recur, or how I can
prevent it from recurring? Any thoughts at all?

The real problem here is that my wife's boss is from the mainframe world,
doesn't understand that Windows programs just do this sort of thing
sometimes. He reasons -- logically -- that if it happened during testing, it
can happen during deployment. The fact that my wife has now tested her code
on all 400 worksheets -- repeated all her regression testing -- doesn't
impress him. The code failed once, after all. And no one knows why. So no
one can say that it won't happen again. That's his position, and of course
it's reasonable.

But...this is a first major project for my wife, so she is undergoing some
evaluation. Excel/VBA are undergoing ongoing evaluation, as is, I suppose,
the Windows platform itself. Excel is too good a tool to fail this
test...and yet, the boss's request for an explanation of the error is
reasonable.

(Yes, we'll be making screen shots of error messages from now on. Whoops.)

 

[Tim Williams]

No error, no code.... I doubt anyone would be able to provide a "spot on"
explanation for this.

Is the Validate routine exactly the same in all of the files?

Tim

 

[J.D. Hildebrand]

Tim,

There was the 12:30 version of the Validate routine, which ran without
error.

There was the 1:30 version of the Validate routine, which had *only
comments* added, but no executed code changed. This version gave an error.

There was the 2:00 version of the Validate routine, which was identical to
the 1:30 version except that the Validate routine was renamed to
ValidateData. This ran without error.

There was the 5:00 version of the Validate routine, which was identical to
the 1:30 version: I renamed ValidateData back to Validate. This ran without
error.

All of these were within a single spreadsheet, abcde.xls, my "testing"
spreadsheet.

In addition, I tested 140 spreadsheets with the 2:00 version of the Validate
routine -- yes, the identical version -- and all ran without error.

The fact that the 1:30 version and the 5:00 version had the *identical* VBA
leads me to believe that the problem is not in my code. I suspect there is a
problem with Excel, or with my spreadsheet, that it is somehow sort of
corrupted.

I found a website, http://www.dotxls.com/excel-recovery/40/ , which says the
following:
“During the process of creating VBA programs, a lot of “junk” builds up in
your files. If these files aren’t cleaned periodically you will begin to
experience strange problems caused by this extra baggage. Cleaning a project
involves exporting the contents of all its VBComponents out to text files,
deleting the components, then importing the components back from the text
files.” “You will often see a dramatic reduction in file size once you’ve
done this. Even after recompiling the code….500k-600k projects routinely
drop 100k if they haven’t been cleaned recently. This is all useless, error
prone garbage getting thrown out of your file.”
Reduce Excel File Size / VBUSERS Excel File Rebuilder
“The rebuilder has been primarily designed for developers to help stabilise
and compact Excel Workbooks. Rebuilding workbooks containing VBA code and
references to COM objects results in the workbooks becoming more stabile and
reducing in size by up to 70%. The rebuilder also overcomes many common
problems including unexplained ‘Bad DLL Calling Convention’ errors, Excel
locking up while saving workbooks and ‘Application undefined error’. The
rebuilder will also automatically check all references and if necessary
repoint any invalid references.”

I don't know if they're just trying to sell a product or not. Does their
explanation make sense to you, as a way of explaining what happened to me?

Thanks.

Naomi

 

[Tim Williams]

Naomi,

It is a common finding (in this newsgroup at least) that the process
described by the site you visited does help. During the develoment process
Excel files do build up "junk" which can only be removed by exporting and
re-importing code modules. The utility they recommend is free, so there is
no ulterior motive on their part, and many other posters here have confirmed
it does work.

We all manage at some point to introduce errors in our code which later -
sometimes after some "unrelated" changes - cannot be reproduced. This in
iteslf is not a failing of the platform, but a normal part any coding
(no-one is perfect). As long as you have a set of tests which your code
passes, and you feel the set of tests is enough to judge "production"
quality, then that would seem to be sufficient.

The original post mentioned something about a dll error - does the code call
procedures in an external library (API calls?)

Regards,
Tim

 

[Naomi Hildebrand]

Tim,

Thanks. The reassurance about the utility -- and the problem it was designed
to fix -- is helpful.

I agree that complicated code can sometimes be flaky. If you can't exactly
reproduce the input conditions, you can't reproduce the error. The input
conditions might have depended on all kinds of things in the outside
environment. Earlier in my career I encountered this in complex embedded
programs. These are actual subtle coding errors, not errors in the platform.

However, I am dealing here with pretty straightforward code. It's not
event-driven, it doesn't do any file or database manipuation, it simply
looks through the Excel worksheets and verifies that the data passes some
simple tests. In an example like this, it's hard to imagine that identical
code, run on identical data, would give different answers. To me, it's more
reasonable to look at the platform in this case.

And no, there are no DLL calls in the program. As I say, it's really very
simple code.

I completely agree that this code is production worthy. My problem right now
is political -- convincing my manager (I'm new to this project, and haven't
fully earned my new boss's respect) that the code has been tested enough,
and the explanation I'm giving him (basically: Look, this is Windows
programming, sh*t happens) is believable. In the programming world he comes
from (mainframe & unix), if you run into a problem, you don't release your
code until you've reproduced the error, and then fixed whatever caused it.
I'm trying to tell him we just can't do that here. (And running a third
party utility, where he has no access to its source code, would probably
give him a heart attack!)

I appreciate the time you're giving me. Thanks.

Naomi

 

[Guest]

My suspicion is that VBA is confusing your Validate Sub with a .Validate
method for some object reference. This would explain why renaming it makes
it work, and would also explain the error associated with the .dll file
because it is looking for the library routine "Validate". Excel has a
heirarchy for deciding which Sub to run when there are duplicate names, so it
may be looking at some reference first to find that name, and the referenced
library may no longer be there. And yes, this could be a holdover from
something that was in there previously, but I would also check the references
to see if there is perhaps some object model in there that has a .Validate
method associated with it. If so, and if it is not needed, clean it out.
But your code should be OK; the safe thing to do if possible is avoid the
name Validate and call your Sub something different.

 

[Tim Williams]

If your boss has qualms about running an external ultility then you can just
perform the required steps manually: export and re-import all of your code
modules.

I might agree with your boss if the codebase which produced the error had
not been modified since the problem was observed: if you made changes which
fixed the error then it seem odd to persist in trying to reproduce that
particular error....

 

[Guest]

I downloaded and ran the program on a few of my worksheets. It did reduce
the file size by a large ammount but it also removed all comments and
indentation so I could no longer easily work with my code. I didn't fix the
problem I had which happens to be unrelated to one the Naomi is having.
Anyway, I think anyone in the future who reads this should at the very least
know that. Im sure it has its uses but as far as I can tell you could
probably only use it when you are totally done with your work.

 

[Tim Williams]

In my version of the tool (4.4), stripping comments and blank spaces is an option and *unchecked* by default....

 

[Guest]

Ah yeah. True. I enabled it because I thought those blank spaces were
screwing up a sub of mind. Trying to put a space into an integer variable.
Turned out I was just reading the wrong Cell. Five hours of troubleshooting
the sub for nothing =p.

 

[Tim Williams]

Time spent debugging = 1/(complexity of the discovered problem)