Excel as a development Platform? pros and cons?

[Freddy]

Can somebody please enumerate through the pros and cons of developing an
application completely in Excel? By this I am referring to hijacking the
user interface and adding custom menus, toolbars, data validation etc etc.
I'd like your analysis to be based on a commercial grade application with
extremely complex calculations as opposed to a simple "mortgage calculator".
Thanks in advance. I am in possession of an application developed in 100%
Excel which was developed by an "Excel Purist". It is upgrade/enhancement
time and I am weighing the benefits of keeping it as is or going with a more
robust professional looking VB.Net / VB6 front end..

 

[Harlan Grove]

Can somebody please enumerate through the pros and cons of developing an
application completely in Excel? By this I am referring to hijacking the
user interface and adding custom menus, toolbars, data validation etc etc.
I'd like your analysis to be based on a commercial grade application with
extremely complex calculations as opposed to a simple "mortgage

calculator".
....

Pros
- Excel provides a very capable grid control.
- If all calculations can be done using worksheet formulas, it may be much
more efficient in terms of development time and execution time.

Cons
- It's not exactly impossible to break macros.
- If users disable macros, apps such as you describe can't load their own
interfaces. This also leads to a lack of security.

 

[Keith Willshaw]

Can somebody please enumerate through the pros and cons of developing an
application completely in Excel? By this I am referring to hijacking the
user interface and adding custom menus, toolbars, data validation etc etc.
I'd like your analysis to be based on a commercial grade application with
extremely complex calculations as opposed to a simple "mortgage calculator".
Thanks in advance. I am in possession of an application developed in 100%
Excel which was developed by an "Excel Purist". It is upgrade/enhancement
time and I am weighing the benefits of keeping it as is or going with a more
robust professional looking VB.Net / VB6 front end..

If you want to market it as a commercial grade application
I'd suggest moving it to a .com add-in.

That will give you better security for your code while minimising
re-work.

Keith

 

[onedaywhen]

Excel has a very powerful and feature rich UI. 'H' it, for example to
accept user input to cells on a worksheet, involves disabling the
effects of most of these features. Remember that the advanced Excel
user will know that these features have been disable and will expect
them to be available when they switch to another workbook. I have done
the hijacking a few times when specifically requested; it has taken a
lot of work to achieve and the results have been merely acceptable to
users. Where a large degree of control over user interaction is
required, I've found a form based UI to be a happier experience for
both developer and end user.

Where 'extremely complex calculations' are involved the major issue is
usually the integrity and maintenance of the calculations rather than
the time it takes the machine to crunch the numbers. It is often a
different professional (e.g. actuary) who writes the calculation to
the one (e.g. software developer) who adds the UI functionality; if
the former professional is comfortable with working with Excel
formulas it's a good arrangement to keep the calculations within Excel
cell formulas on hidden worksheets.

Bear in mind that it is trifling easy to unhide and unprotect
password-protected sheets so you need to give consideration encrypting
your sensitive data and obfuscating your proprietary calculations.

You could write a standalone (i.e. exe) app that references your
workbook or you could integrate forms into the workbook i.e. an Excel
app. For the latter, you can use embedded VBA and useforms or write a
dll in Visual Studio (i.e. compiled component) referenced by the
workbook. Something worth considering is VS Tools for Microsoft Office
2003 i.e. write a .NET assembly which directly hooks the Excel
workbook's events. For details look on MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/vstointro.asp