Dealing with complexity in excel spreadsheets

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

polymer
Posts: 125
Joined: Mon Feb 04, 2008 7:14 am UTC

Dealing with complexity in excel spreadsheets

Postby polymer » Sat Feb 23, 2013 10:52 pm UTC

So I'm practicing designing and constructing software, and recently somebody offered me a job making their technology more reliable. They told me - "I don't want everything to stop because somebody deleted a cell in excel". I have never liked excel spreadsheet formulas very much. They read and write like spaghetti code. I might not understand though:

How would you design and construct complicated programs using excel spreadsheet formulas?

Are macros a reasonable solution to reduce complexity? Is an accessible implementation important?

Ben-oni
Posts: 278
Joined: Mon Sep 26, 2011 4:56 am UTC

Re: Dealing with complexity in excel spreadsheets

Postby Ben-oni » Mon Feb 25, 2013 4:28 pm UTC

My own opinion is that excel is suitable for small data-driven tools that don't need to do much. Data and logic are tightly coupled. If you start to encounter complexity, jump up a step and use Access. I would recommend doing this instead of pushing all the logic into macros. That at least eliminates the problem of the whole thing crashing when someone deletes a cell. (Generally, with Excel, you can't keep the system working when someone arbitrarily deletes cells. "Deleting" anything is the same as changing the program. Of course it crashes.)

Divinas
Posts: 57
Joined: Wed Aug 26, 2009 7:04 am UTC

Re: Dealing with complexity in excel spreadsheets

Postby Divinas » Tue Feb 26, 2013 2:19 pm UTC

I would like to repeat what Ben Oni said. If you have to create some manner of stability and fault-tolerance in excel per se, follow what he said: You really need to separate logic from data. Create a different sheet that holds all the logic, that is not 'user' readable. Write values from this sheet to the 'user' sheets. Do checks for wrong data in all your computations. That way if someone does a bad touch and screws something up in the user sheets, if your code can handle this data corruption, you can gracefully fail.

User avatar
Yakk
Poster with most posts but no title.
Posts: 11129
Joined: Sat Jan 27, 2007 7:27 pm UTC
Location: E pur si muove

Re: Dealing with complexity in excel spreadsheets

Postby Yakk » Tue Feb 26, 2013 2:55 pm UTC

Version control. Because even if you have data-driven design, random crap will generate crap.

Having access to programming tools is important whenever you are managing data. If you completely lock down the ability to write code from end users, they will be forced to create excel spreadsheets, use them to the task, then input the output of their spreadsheets into your "static" program.

All processes have to be examined in light of how the users will avoid the "high cost" portions of your process to make doing their job easier.

The power of excel spreadsheets is that it lets non-programmers program. If you take that away, you are disenfranchising users: their ability to contribute both problems and solutions is seriously mangled. Not surprisingly, the resulting code is often a mess, because the people writing it aren't being paid to write pretty code, and don't have experience at writing pretty code. Responding to this by "banning anyone who cannot write pretty code from writing code" does make your code prettier, but doesn't solve the problem that being allowed to program (even ugly code) can massively increase someone's ability to do their job.

What you want to do is capture and clean up such work, while still leave access for the end users to program and improve and fix the rough spots, if you want to maximize the ability for workers to contribute.
One of the painful things about our time is that those who feel certainty are stupid, and those with any imagination and understanding are filled with doubt and indecision - BR

Last edited by JHVH on Fri Oct 23, 4004 BCE 6:17 pm, edited 6 times in total.

polymer
Posts: 125
Joined: Mon Feb 04, 2008 7:14 am UTC

Re: Dealing with complexity in excel spreadsheets

Postby polymer » Wed Feb 27, 2013 4:34 pm UTC

Yakk's points make a lot of sense, Excel is probably being used in the context of data management, in which case logic is important. Any methodology I use to abstract the work away from the users will only make it harder for them to do their jobs. Unless the problem isn't actually data management, in which case Excel might not be the best application in the first place. In order to preserve some semblance of stability a back-up system should be in place. Excel is just going to be complicated, no matter how clever I am, none - programmers will be using it. Thanks for all of your thoughts.

Ben-oni
Posts: 278
Joined: Mon Sep 26, 2011 4:56 am UTC

Re: Dealing with complexity in excel spreadsheets

Postby Ben-oni » Wed Feb 27, 2013 5:05 pm UTC

If data integrity is truly important, use a database. You might consider linking a spreadsheet to a database, so it automatically populates. This would give you good tools for data-management, and also give end users good flexibility in how they can use the data and crunch numbers.


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 12 guests