How to build a bulletproof application Using excel

[Bugs]

Excel is the most flexible software to build, in a very fast way,
calculation tool (workbooks), even for unexperience programmers.
But when you try to share the workbook with someone, you realise that
the resulting application is not bulletproof with respect to an
unexpert use (the recipient may unvoulontary cancel some formula
cells). Things get even worst when you should distrbute it to more
than a user with a common dataset. Or when you should modify or bugfix
an already distributed workbook which as been modified by the users.
Could you please give me some references in which I can find answers
to this problems? In other words I need some directions in order to
get a bulletproof and mantainable application from an excel
workbook.

 

[Peter T]

As you say the most carefully constructed Excel app will work fine until a
user is allowed to touch it! In general, if the potential exists for
something to go wrong - it will. There's no one size fits all type answer
but typically Excel app's may include the following -

Input
Output
Data
Calculation/processing (eg formulas and/or code)

Aim for the user only to be able to touch Input cells (or controls on a
form). All the rest should be out of reach and/or protected. Some use the
term "lock(ed)-down". In addition, Input may require some form of validation
before processing or transferring to Data. Don't let user insert rows or
columns, particularly where formulas are included say at the ends.

All this of course is very general advice and your app may need to be
approached in a different way. One book you might find useful is "Excel
Professional Development" (Bullen, Bovey & Green), it includes working
examples along the lines of the above. There's a 2007 edition but even the
old 2003 version would be worth picking up S/H (make sure the CD is
included).

Regards,
Peter T

 

[Don Guillett Excel MVP]

Excel is the most flexible software to build, in a very fast way,
calculation tool (workbooks), even for unexperience programmers.
But when you try to share the workbook with someone, you realise that
the resulting application is not bulletproof with respect to an
unexpert use (the recipient may unvoulontary cancel some formula
cells). Things get even worst when you should distrbute it to more
than a user with a common dataset. Or when you should modify or bugfix
an already distributed workbook which as been modified by the users.
Could you please give me some references in which I can find answers
to this problems? In other words I need some directions in order to
get a bulletproof and mantainable application from an excel
workbook.

Another idea is to have a macro restore your formulas
range("d3").formula="=d1*d2"
or even NOT have formulas. Just a macro button to make the
calculations and show them in the cells, ie: Protect the macros
project

Sub doformula()
range("d3").Value = Range("d1") * Range("d2")
End Sub

 

[(PeteCresswell)]

Per Bugs:

In other words I need some directions in order to
get a bulletproof and mantainable application from an excel
workbook.

One approach would be to write a user interface using something
like MS Access or .NET.

That UI would manage the user's interaction and make calls to
Excel.

This would also address the issue of bug fixes, since they could
be implemented by rolling out a new version of the application.

 

[rhoRo]

<snip>...
Could you please give me some references in which I can find answers
to this problems? In other words I need some directions in order to
get a bulletproof and mantainable application from an excel
workbook.

try using a spreadsheet compiler. googling 'excel compiler' will give
you several hits.

good luck!
r.