Make a product that works

[msnyc07]

Truly you should be ashamed of Excel. I couldn't even BEGIN to itemize or
describe the utter hell of working with this application and the literally
1000s of hours it has wasted of mine due to poor and sloppy programming.
2007??? This is like a first release. From the ridiculous non-standard
cut/paste cache, to the on-again off-again 'select all' that destroys sorts,
to the disappearing Freeze Top Pane, to the 'pervasive' recalculations that
don't turn off no matter what you do and d RUIN work, to the DAILY crashes
and the need to dig through pages of work and formulas to figure out where
you left off. I'd expect this from an offshore coder I hired for $8 not
Microsoft. I am done. Here's a clue; fix the core components before adding
new features. Because you are just building on a rotten foundation.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Fred Smith]

Hope you feel better now. Let us know when you find an alternative.

Regards,
Fred

 

[Don Guillett]

There are better coders, albeit at more than $8 hourly. Perhaps you need one
to do these projects so you will have more time to devote to pursuits for
which you qualify.

 

[Glenn]

Truly you should be ashamed of Excel. I couldn't even BEGIN to itemize or
describe the utter hell of working with this application and the literally
1000s of hours it has wasted of mine due to poor and sloppy programming.
2007??? This is like a first release. From the ridiculous non-standard
cut/paste cache, to the on-again off-again 'select all' that destroys sorts,
to the disappearing Freeze Top Pane, to the 'pervasive' recalculations that
don't turn off no matter what you do and d RUIN work, to the DAILY crashes
and the need to dig through pages of work and formulas to figure out where
you left off. I'd expect this from an offshore coder I hired for $8 not
Microsoft. I am done. Here's a clue; fix the core components before adding
new features. Because you are just building on a rotten foundation.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

Do you blame the hammer when you hit your thumb instead of the nail?

 

[msnyc07]

I blame the hammer when the head falls off when I am using it or the claws
chip and blind me. I blame the hammer when the hammer maker knows that is
the case but keeps adding far less useful improvements. If you want to be
sycophants be my guest; the software is severely flawed and blaming me for
it's errors doesn't make it any better. I have friends who work for top
consulting firms and run into the very same frustrations I do and have had
projects compromised by the same issues. If it is easier to assume I am so
idiot who doesn't know what he is doing feel free to assume. It won't change
the facts.

 

[Don Guillett]

If you would learn to use the product properly you can get good results. Or,
hire me and send your "top consulting firm" people also. I'm sure they can
afford the best.

 

[JLatham]

Have you tried 2010 yet? If not, you might set up a system with it on it and
give it a go. It would appear that many of the problems that were admittedly
in 2007 have been addressed and corrected in 2010. Not saying that all that
you've mentioned are working differently, but I think it's a better product.

But other than the utter slowdowns I've experienced where it seems every
formula in a workbook was recalculated almost on a whim (not really, just
seemed that way), I haven't experienced the plethora of problems you allude
to. But then I mostly stuck with 2003 anyhow.

 

[msnyc07]

Recalculating cost me more then slowdowns it almost destroyed my work and
caused me weeks of work. A (or should I say Yet another) crash 'corrupted' my
file and it 'fixed' it by removing all formatting. I was using Indenting to
set up hierarchical tables and i had a formula column to derive that for
parsing into a DB. FORTUNATELY I had run that so even though the formatting
was gone the indents had been derived. Except Excel recalculated the Indents
using the zero'd out reformatting. So now I had a workbook with 100 sheets
and 50,000 records in various states of relations representing 1000s of hours
of work which was useless.

Note that I *had* set Calculate Option in the Menu to Manual. After
paiunfully rebuilding the document with old versions that I had to audit one
sheet by one sheet since the work has been on ongoing process I finally put
the sheet back together with the right indent values.

So it turns out that Calculate Option in the Menu are not the *full*
options, that is that Manual means 'Manual Until You Save' and if you want
'Manual Even When You Save' you need to go into the Excel Preferences where
they ALSO have a Calculate Options Menu but THIS one has an added
'Recalculate on Save' Toggle. Naturally if one saw that when setting options
one would know that that needed to either be on or off based on preferences;
it is a phenomenal oversight in development to not include that on the Menu
as well; Manual looks like just what it should mean and without an option for
Not on Save one would have no way of knowing that it was semi-manual yes?

So between the (yet another) crash and the poor design I lost weeks of work
and gained the same in frustration.

BUT it wasn't over. I shut down the PC and restarted and opened the
spreadsheet and started working and then parsed it. Lo and behold, the values
had been re-set to zero so the parse and the carefully reconstructed sheet
were useless. Why? Because Excel had re-set the Excel Options to Automatic.
Why? Who Cares? So Manual in the Menu means 'Sort of until Excel decides to
overide when you Save' and Manual in the Options means 'Sort of until Excel
decides to overide when you re-start'

This is the tip of the iceberg in my frustrations and as I mentioned I had a
friend at a consulting firm where they are whizzes with this sort of stuff
encounter the same frustration.

Will it be addressed in 2010? I don't know and I don't believe I'll be
testing it out.

Thank you for the reasonable reply though.

 

[Niek Otten]

I can understand that you're frustrated. Indeed there are a few problems
with Excel. But there are a few problems with every software product I know,
and often more than a few.

What I can't understand is that you invest so much time and rely completely
on a product that you apparently don't know very well. And that you use it
for something that you may expect it to do (and that may even be
documented), but what it certainly wasn't meant for primarily.

I agree with the advice to seek professional consultancy. "Top consulting
firms" would not be my first choice to look for it. Real Office expertise is
more often found with independent consultants or small companies. Quite a
few experts are regular posters in this group.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Glenn]

Well, I would agree, if the head was falling off, but I doubt that is the case.
There would be a lot more complaints like yours if the problem(s) were as
critical as a hammer with a head that falls off.

The facts appear to be that you have a complex set of worksheets and your
environment (including Excel) doesn't appear to be completely stable. I have
numerous worksheets that have calculation set to manual, due to long
recalculation times and many updates, but none of them would fail just because
they were recalculated automatically.

Excel rarely, if ever, crashes for me, and usually it happens when I am trying
something unorthodox, searching for a solution to a problem posted in this group.

I don't assume you are an idiot, just that you are frustrated with a less than
perfect solution. It's possible that what you are trying to do is better suited
for a different tool. It's also possible that a fresh set of eyes would help
eliminate some of the problems you are encountering.

 

[msnyc07]

That isn't a minor bug Niek and nor is the crashing (and nor in fact are the
other 'bugs'). I am not developing a website with Excel nor trying to
develop a sophisticated inventory management system.

And I know the software well enough. Should I seek a consultant to help me:

Add a bunch of simple 4 field records to Excel
Indent as needed
Write a simple formula (check and return indent)

Are you saying that is beyond the scope or purpose of the software? Or that
someone with moderate Excel Skills shouldn't be able to do that without
paying someone?

I am not a VBA expert nor a formula expert though I have learned those as I
need them. That is not where i ran into problems though.

I shouldn't need a consultant to tell me that the Calculation Options in the
Formula Menu are not complete should I?

I couldn't use a consultant to keep Excel from crashing 10x a week could I?
Am I really pushing it's envelope that much with 50,000 records and a single
formula???

I did have a friend offer to write a macro to keep the Freeze Panes Frozen.
But isn't that an idiotic thing to need someone to write? Is it my lack of
knowledge, if Freeze Panes becomes unfrozen by the software INTENTIONALLY and
not because of bad coding, certainly the software should let you know at
least the first time yes ('Freeze Panes is disabled when it gets below 40
degrees" or some such)

Ditto Select All which often does not Select All. I have not made sense of
that but again, if that is an intentional design, it should be explained when
it first happens, as if i am selecting a 50,000 record sheet with 20 columns,
it is quite easy for me to miss the fact that Select All Selected Some and my
sort has now ruined my data. If it is a flaw it should be fixed as it is
major, if it is a matter of me 'not knowing the software well' then I put to
you that I shouldn't have to know the software well enough to figure THAT
out. How to use formulas, do complex manipulations, sure. But use Select
All???

Having spend 1000s of hours on this spreadsheet I can tell you that a good
20% of my time is spent dealing with these issues large and small. That is
not an issue of my not knowing it it is an issue of poor design. I get it
does a lot of fancy things well. But again, those are less then useful if
the underlying functions aren't attended to. Certainly the developers who
added 'Recalculate on Save' to the Excel Formula OPTIONS should have thought
to add it to the Formula Menu. You can't turn around and blame the user
because they didn't know you weren't standardinzing the functionality and
including it where it was most often and most obviously used and say 'Well
you should know that Manual Calculation is not fully implemented in the Menu;

Blaming me is a cop-out. Blaming the scope of my 'project' is as well. If
Excel is truly such a difficult program to gather and store simple records
with out formulas of functions, and you need a 'consultant' to do it,
something is truly Rotten in Denmark.

 

[Don Guillett]

I am responding purely on your excel setting the calculation to automatic.
Excel takes the option from the first workbook opened.
You could put a workbook_open macro to set calculation to MANUAL.

 

[msnyc07]

Thanks for the reply, it helps.

I am sure you can still see that is someone were to set their sheet to
'Manual' and THEN figure out that they had to further dig into to the Excel
Option to set it to Manual AND No Save that it would come as a surprise if
they then closed Excel, opened an earlier sheet they hadn't set to manual,
opened their Manual Sheet in an app they also set to Manual and were suddenly
in Automatic Mode and their formulas they took such care to have NOT update
updated which can, as you see by my posts, be an irreversibly damaging event.
Not a THING about that makes sense on any level. And each time I encounter
some thing like this, I am told I can make a Macro. Not sure why with
Document AND Application Settings I'd need to find out the hard way and then
program what should be the working and obvious functionality myself, anymore
then I should have to program a 'Keep Freeze Panes On when it is On' Macro.

Thank you though for the tip. Truly this is the last job in Excel it is
like dating a person who can go psycho on you at any time, and you just have
to anticipate and work around all the 'eccentricities'. No one and no program
is worth it, both will land you in the looney bin.