RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Spreadsheet Programming: MS Excel as Component Development Environment : Page 3

Spreadsheet programming is an emerging technique for building complex business logic in MS Excel at a fraction of the effort, cost, and cycle time of hand coding such logic in a programming language. Programmers can deploy the business logic built in the spreadsheet into interactive client applications and highly scalable server applications.

Design a Spreadsheet Business Component, Then Build an Application Around It
The most critical design task in spreadsheet programming is identifying the appropriate input cells and output cells. This defines the API for your spreadsheet. Once the API has been defined, the technical design of the calling application and the underlying spreadsheet logic can proceed independently. Using spreadsheet cell arrangements and formulas, you'll find many design patterns for achieving different kinds of logic.

When building a spreadsheet-oriented application, you focus your effort in two areas: the spreadsheet and the caller application. The spreadsheet will contain the business logic, and business people can develop it. The caller application just needs to pass the right data in and out of the spreadsheet.

Testing and Maintenance
Testing is an area where spreadsheet programming really shines. Testing is required to verify two areas: (1) the business logic in the spreadsheet and, (2) the communication between the calling application and the spreadsheet. To test the business logic in the spreadsheet, first look at the input cells and the result cells, or the public API. You can create a separate worksheet that contains hundreds or thousands of rows of test data sets and expected results. With a single, generic VBA function, you can cycle through the test data, retrieve the results, and compare them with the expected results. This automated testing delivers tremendous efficiency.

The next step is to test the communication between the calling application and the spreadsheet to ensure that the correct data is moved in and out of the spreadsheet at the correct cell locations. You can do this in a variety of ways, depending on which spreadsheet engine product you use. KDCalc, for example, includes a Spreadsheet Bridge component that inserts a tabbed spreadsheet grid between your calling application and the KDCalc engine. So as your app is passing data in and out of the engine, you can peek in to see what all the values are. This method of visual debugging is much faster than stepping through the API code one line at a time because you can see all the values at once.

Maintenance of spreadsheet-oriented applications is another area with powerful efficiencies. As long as the API between the calling application and the underlying spreadsheet stays constant, the caller and the spreadsheet can be updated independently. Maintaining business logic in a spreadsheet is more efficient than maintaining code. Taking control of a spreadsheet that someone else created is also far easier that taking over someone else's code.

Kinds of Applications Suitable for Spreadsheet Programming
Sales tax calculations are an obvious fit for spreadsheet programming, but they aren't the only ones. Some other obvious fits are payroll tax deduction modules for HRM systems, calculating shipping costs based on weight, delivery method, and delivery schedule, and account transactions in financial services.

Literally anything that involves business decisioning or calculations can be built faster and more accurately with spreadsheet programming.
Spreadsheet programming is also a perfect fit for a lot of other applications that may not be so obvious. Some of these are insurance and loan underwriting, dynamic pricing calculations based on order composition and inventory levels, and pricing and trading of custom financial instruments. Literally anything that involves business decisioning or calculations can be built faster and more accurately with spreadsheet programming.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date