This newsgroup is sometimes frightening

[John Coleman]

Greetings,

I was wondering if any one else is sometimes struck by the same thing:

Several times a week someone posts some flat-out bad code and/or asks
exceedingly basic questions. I have no problem with this. You have to
learn sometimes and asking questions is a good way to do so. What is
frightening is that you can sometimes infer that the code in question
is *important* to a company, and that potentially a lot of money is
riding on getting it right. I wonder how many companies have been
burned by buggy Excel programs written by people with little or no
programming background. In a sense VBA is too easy to write. With the
macro recorder and a bit of trial and error you can cobble together
something that seems to work (most of the time) without understanding
why it works or being able to anticipate when it might fail. No company
would dream of trusting C code written by a beginner, but many
apparantly trust equally unreliable VBA code.

Just a random thought.

-John Coleman

 

[Nigel]

Good point John, and a good observation about C code programs versus VBA.
My business would not accept a VB programme to be installed, but would let
users run VBA macros. I guess the difference is that most programs in VBA
are 'contained' within the Host (Excel) application and not a serious risk
or a network issue, and if it is, the problem is more than rogue VBA
programmers!

As for this newsgroup it should never be condemned for providing solutions,
guidance or help to any OP whatever the level of understanding. Until
companies take responsibility for good training and IS support, both often
sadly lacking in my experience, the problems will persist but not because of
this newsgroup!

 

[Chip Pearson]

John,

I largely agree with your points. But I would take issue with
your final assertion:

No company
would dream of trusting C code written by a beginner,

Many companies do in fact rely on C code written by beginners.
I've had to debug my share of it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[davegb]

Good point John, and a good observation about C code programs versus VBA.
My business would not accept a VB programme to be installed, but would let
users run VBA macros. I guess the difference is that most programs in VBA
are 'contained' within the Host (Excel) application and not a serious risk
or a network issue, and if it is, the problem is more than rogue VBA
programmers!

As for this newsgroup it should never be condemned for providing solutions,
guidance or help to any OP whatever the level of understanding. Until
companies take responsibility for good training and IS support, both often
sadly lacking in my experience, the problems will persist but not because of
this newsgroup!

Actually, I think there's a much bigger problem than poorly written
code. Poorly done spreadsheets themselves! I've seen all kinds of
erroneous formulas in spreadsheets, sometimes being used to determine
financial transactions. There are a lot of spreadsheets done by people
who don't even know the order of operations. And I NEVER see error
checking in the spreadsheets I work on. Everyone assumes if it's done
in Excel, it must be right. Very scary!

 

[Niek Otten]

And I've seen very experienced programmers writing bad code and young
talents writing good code.

But yes, access to VBA is very easy, so you'll find all sorts of programmers
there.

Also, I'm always surprised that spreadsheets are often hardly tested. Most
financial companies have very strict rules for testing professionally
developed systems, but don't pay any attention to the correctness of
spreadsheets.

 

[tony h]

It is a trade off. There is no substantially risk-free way of providin
software but VBA is about as risky as it gets.

Pointing out the risks of this to one FD his view was that spreadsheet
have only a shortlife after the person who created them has left. Whe
they did the next person would re-write them. The safty was in that th
author was actually operationally responsible for the numbers create
(as opposed to a programmer who wasn't) and this ensured "reasonabl
reliability".

But personally I find that the degrading of the high-standards is
real conceptual problem. Particularly when trying to evidence th
good-quality of work my work against some of the charlatans puttin
themselves forward as experts.

I remember one person coming for an interview as an excel "expert"
asked the question what "formula would you use to give the total of th
numbers in the cells A1 to A10". No answer was given to this or my othe
two "make them feel confortable" questions. I despair.

Think you hit one of my nerves with this thread - better stop before
rant

 

[Peter T]

Also, I'm always surprised that spreadsheets are often hardly tested. Most

financial companies have very strict rules for testing professionally
developed systems, but don't pay any attention to the correctness of
spreadsheets.

Surely that can't be true

http://www.eusprig.org/stories.htm

Regards,
Peter T

 

[snax500]

I would have to disagree. I am one of these so called novices that use
VBA at my company. VBA should be encouraged. It has saved me and my
company countless hours. Sure I make errors in programming but I am
sure we all on occasion make errors on something we are working on.
This can not be avoided, we are humans. The benefits of using VBA
overwhelming outweigh not using it. I encourage all Excel users to pick
up as much VBA as possible.

 

[Niek Otten]

Yes, in the company I used to work for I've seen very encouraging examples.
But I insist on proper testing as stated in my previous post.
And that does not just mean apply the spreadsheet to a large number of
cases; it means testing all decision points, just as in professional testing
of IT-built systems.

IT departments should make their testing skills available to other
developers, instead of just trying to fight them.

 

[davegb]

Surely that can't be true

http://www.eusprig.org/stories.htm

Regards,
Peter T

Fascinating stuff! I've always known that this sort of thing must be
happening all over the place. Good to see it documented. Let's face it,
for most businesses, QA/QC is not even a consideration.

 

[Guest]

The point about an organization taking the responsiblity to make sure their
people are well versed at the position they were hired for is all to common.
Mentoring is the answer. This just doesn't get done. At least not in the
industry I work in.

I was given ownership of two Excel files with extensive VBA and both files
have some major issues. With only one semester of introductory VB 6 under my
belt, I know enough to be dangerous, not effective. I can't even get our
onsite EDS people to provide support on this one. They want to rework these
into applications that they create from $cratch.

Enough for now. . .

Hal

:

 

[John Coleman]

Surely that can't be true

http://www.eusprig.org/stories.htm

Regards,
Peter T

Very interesting link, but traditional programmers shouldn't gloat.
None of the cases resulted in death, unlike the notorious case of the
therac-25 radiation therapy machine in the 80s or the patriot missle in
the Gulf War.

-John Coleman

 

[davegb]

Very interesting link, but traditional programmers shouldn't gloat.

Neat! I don't consider myself a "traditional programmer", so I guess I
can still gloat! BTW, what is a "traditional programmer"?

 

[John Coleman]

Neat! I don't consider myself a "traditional programmer", so I guess I
can still gloat! BTW, what is a "traditional programmer"?

A figment of my imagination? I guess I meant programmers of traditional
compiled languages like Fortran, C, C++, etc.

 

[davegb]

A figment of my imagination? I guess I meant programmers of traditional
compiled languages like Fortran, C, C++, etc.

I don't think I ever did enough programming to be considered
"traditional", so I'll still gloat.

 

[Peter T]

I don't think I ever did enough programming to be considered
"traditional", so I'll still gloat.

Not sure gloat is quite appropriate, and most certainly not towards the
examples John quoted earlier or other serious system errors I could also
cite.

However a certain schadenfreude might be permitted on reading some of the
stories on that page, even beneficially if that helps prevent more of the
same.

The wry smile that preceded the link I posted was prompted by -

Robert Brust, Kodak's chief financial officer, called it "an internal
control deficiency that constitutes a material weakness that impacted the
accounting for restructurings."

I can only wonder if he's been promoted to head of the Spin dept.

Regards,
Peter T

 

[tony h]

I suppose the "fear" is that while some of the spreadsheets are useful
and fairly harmless there is no measure of complexity or quantity of
spreadsheets used.

As part of a Y2K programme I included an xla that logged for each
spreadsheet, who when and for how long a spreadsheet was used.

It showed that most of the 5000 spreadsheets known were unused in a
twelve month period. many were used frequently but some very complex
ones were run once or twice a year and no-one knew how to check the
results.

And talking about traditional programming. There was a programme used
regularly to check a floating platform for stability. This was used
consistantly in the oil-rig industry. A new manager brought this
programme to a traditional designer who then set about validating the
results with his own. The programme was found to give dangerously
incorrect results in a small number of cases. No matter that it was
written in C by traditional programmers and subject to QA procedures.

The main problem is that prople believe the machines give the right
answer all the time.

 

[amitntu]

completely agree ... saved one full day , yesterday only...

 

[Myles]

No company would dream of trusting C code written by a beginner, bu

many
apparantly trust equally unreliable VBA code.


Just a comment or two on John's assertion quoted above. While th
transparecy and relative "friendliness" of VBA ensure that nearl
everyone can have a crack, its docility equally guarantees that a badl
written code (by a charlatan) can be readily exposed. Contrast this wit
the activities of a self-styled programming nerd who uses a hig
language. It is not hard to imagine that such a quack poses a fa
grater danger to his unsuspecting clients, if only because, he coul
take cover in the mirk of the rarefied and esoteric nature of th
language and remain undetected.

I'd rather quacks and fakes with VBA as their tool be easli
detectable than have a handful of shifty "experts" float around
dabbling in high programming languages. These latter, potentially, ar
the ones that cut a more frightening picture