How widely used are Excel recorded macros 3

[MichaelintheUK]

Hello

I wonder if you could help me with your views and experience of how widespread the use of recorded macros with minimal or no intervention into VBA code actually is. In my experience most organsations make little or no use of them - I have seen many cases where formulae are not used and Excel sheets are just a convenient way of storing lists.

I agree that sometimes recorded macros can aid the learning of the Excel specifics of VBA and that useful programming applications can be built.

My real purpose is to gain an impression of how widespread the use of recorded macros in day to day use is e.g. as a % of overall users.

Thanks

Michael

 

[SkipVought]

Mike,

My experience is that 90%+ of Excel spreadsheet users UNDER-USE and MISS-USE spreadhseets. Academic studies by such experts as Panco indicate that the percentage of spreadsheets with various kinds of errors in corporate use is alarmingly high, when such companies are basing a variety of decisions on such data.

Not very many users attempt to use even simple self-made macros, in my experience.

In almost every case that I have seen, better workbook and sheet design would solve 70-80% of a user's problems and well constructed procedures can increase productivity significantly.

How's that?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[markcroft]

Are the panco studies that you mention available on the web?

 

[SkipVought]

Yes. Several of his papers are and others as well.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[markcroft]

have you got any links/urls for them?

 

[SkipVought]

http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm

http://www.gre.ac.uk/~cd02/iirc/projects/webspred.htm

http://www.computer.org/proceedings/hicss/7327/73270336abs.htm

http://jmis.bentley.edu/articles/v13_n3_p45/

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MichaelintheUK]

Hello Skip

Thanks for your comments and the references to the Panko research.

Thanks

Michael

 

[markcroft]

Yeah skip can also say that I found the links useful
mark

 

[tj63069]

Skip is dead on about the errors and mis-use in Excel. Here's a good example.
I restored a lost spreadsheet for a friend of my that has a auto repair business and a very sucessful business too. I ran his spreadsheet through a series of checks and found that his columns were missing the first 2 lines of data amounting to $4,000.00 worth of income per month on average over the past 16 months. His "Bookeeper/Wife" was inserting rows ever month and didn't check the totals!! I built him an Access database and received free auto work for the next year:-}

 

[Rob999]

There's a relatively infamous story about several years ago a Forturne 500 company (don't remember the specifics) had to restate their income for a year due to multi-MILLION dollar spreadsheet errors! You had to watch CNBC type cable news to hear about it, but it was amazing. Where I work we had a "Quick and Dirty" accountant who was fired for stepping on formulas and recording poorly thought out macros to the point that his work was reliably wrong.

Macros are great, but only for shortcut keys such as adding new ranges or rows to a spreadsheet and putting in Default labels and numbers - it's a keystroke recorder. Also good to use when you want to see what Excel is doing to accomplish something that you can't readily find in a book or in the online help.

The problem is when "Quick and Dirty" guys copy a bunch of macros and don't worry about what happens next period when the labels are different, or something different needs to be done. They don't know enough about fixing them so they or someone else breaks them and starts "working around" them and all the sudden you have an unreliable spreadsheet.

I build a lot of spreadsheet apps, but they're just like Visual Basic programs that come with Excel's built in functionality. They include forms, menus, help, etc - sometimes to the point the user doesn't even know he's in Excel. The code is fully documented, planned out and maintainable. The menus go away when the user either closes my file or switches to another spreadsheet. The only thing I use macros for is to occasionally see how Excel is accomplishing some task and use some of that code snippet.

But that old accountant has given me a lot of job security fixing his work.

 

[MichaelintheUK]

Hello Rob and tj6309

Thank you for your relies.

Michael