Software Development with Excel
Time for a software development entry.
We were at my parent’s house over Christmas, and my father was getting into sudoku. I’ve dinked around with sudoku occasionally but haven’t spent much time on it. The day before Christmas, I was working on a harder (at least for me) puzzle, and decided it would be nice to have a helper computer program that performs elementary reasoning and bookkeeping. So for example, if you are looking at a cell where the horizontal row had the numbers 9,7,4,8 filled out, the vertical row has 6,4,8,9, and the 3x3 grid has 9,2,6,8, then the program should show you that your possibilities for that cell are 1, 3, or 5. If you want, then you can punch, say, 3 into the grid, and it will show you how that restricts possibilities for the rest of the cells. Basically, I just wanted something that would replace penciling in the possibilities.
So I wrote the program, implementing it as an Excel spreadsheet. I’ve uploaded a copy of it here: home.comcast.net/~autumn-ajax/sudoku.xls. To use it, you need to enable macros (it’s virus-free, honest. If you don’t trust me, you can disable macros and still see what it looks like; you just can’t make edits). Enter your puzzle in the left-hand grid, and see the “penciled-in grid” in the right.
This gets me to the point this post, which is to tout Excel as a development platform. I’ve heard a number of people trash Excel and Excel programs (see any Excel-related slashdot post, for example), but they’re missing out.
Excel programs aren’t for everyone – in particular if your customer base uses Unix or doesn’t have access to Microsoft Office. And obviously Excel is not meant for every type of application. But if your target is in Excel’s sweet spot – data analysis and visualization – it can be a very powerful tool.
It took about an hour all told to write this program, most of which was trying to remember how to hook up a Visual Basic function to the spreadsheet, and how to reference cells. The VB code was 35 lines long, not counting comments or blanks. The spreadsheet programming itself doesn’t really translate into procedural lines of code, but all it consists of is a method call and some formatting. It’s not much.
Big deal, you say. I know some people who could write this program in an hour using VB, Java/Swing, Tcl/Tk, or whatever. And I could do it too. It would take me a bit longer, since I’m not that fluent with GUIs, but whatever, it’s not rocket science. But let’s enumerate some of the advantages of having it in Excel.
Not bad for an hour's work and forty lines of code.
In a former job, I had great success with this model. I would set up the bare-bones data input and write the code to do the calculations (generally using dlls compiled from C++ on the back end), and turn the spreadsheets over to my customers. They could do the formatting themselves or collaborate with me on it, and also add ancillary calculations, as could their customers. It made for fast feedback loops, and got the job done to everyone's satisfaction. Way easier and faster than writing a standalone application.
We were at my parent’s house over Christmas, and my father was getting into sudoku. I’ve dinked around with sudoku occasionally but haven’t spent much time on it. The day before Christmas, I was working on a harder (at least for me) puzzle, and decided it would be nice to have a helper computer program that performs elementary reasoning and bookkeeping. So for example, if you are looking at a cell where the horizontal row had the numbers 9,7,4,8 filled out, the vertical row has 6,4,8,9, and the 3x3 grid has 9,2,6,8, then the program should show you that your possibilities for that cell are 1, 3, or 5. If you want, then you can punch, say, 3 into the grid, and it will show you how that restricts possibilities for the rest of the cells. Basically, I just wanted something that would replace penciling in the possibilities.
So I wrote the program, implementing it as an Excel spreadsheet. I’ve uploaded a copy of it here: home.comcast.net/~autumn-ajax/sudoku.xls. To use it, you need to enable macros (it’s virus-free, honest. If you don’t trust me, you can disable macros and still see what it looks like; you just can’t make edits). Enter your puzzle in the left-hand grid, and see the “penciled-in grid” in the right.
This gets me to the point this post, which is to tout Excel as a development platform. I’ve heard a number of people trash Excel and Excel programs (see any Excel-related slashdot post, for example), but they’re missing out.
Excel programs aren’t for everyone – in particular if your customer base uses Unix or doesn’t have access to Microsoft Office. And obviously Excel is not meant for every type of application. But if your target is in Excel’s sweet spot – data analysis and visualization – it can be a very powerful tool.
It took about an hour all told to write this program, most of which was trying to remember how to hook up a Visual Basic function to the spreadsheet, and how to reference cells. The VB code was 35 lines long, not counting comments or blanks. The spreadsheet programming itself doesn’t really translate into procedural lines of code, but all it consists of is a method call and some formatting. It’s not much.
Big deal, you say. I know some people who could write this program in an hour using VB, Java/Swing, Tcl/Tk, or whatever. And I could do it too. It would take me a bit longer, since I’m not that fluent with GUIs, but whatever, it’s not rocket science. But let’s enumerate some of the advantages of having it in Excel.
- Easy distribution. I handed my father the spreadsheet, and he immediately knew how to load it and run with it. He didn’t have to download Java or other libraries, deal with DLL’s, etc.
- Auto save/save-as. Want to save your puzzle? Ctrl-S. Save it off to a different file if you want. All with no extra lines of code on my part.
- Undo/redo? Check. Copy/paste – individual cells or the whole grid? Check. Cost to implement? Zero.
- My father could change the font size, zoom in or out, change colors, move the grids around, etc, again with no work on my part. Even if your custom solution used some component grid that gave you that for free, there’s still the education problem – your customers have to know how to do it (or even that they can do it).
- Here’s what clinches the deal for me. The program I wrote was pretty rough; for example, it doesn’t show you if you’ve entered a clearly wrong answer. That would be nice to have. And my father can implement that, by writing his own cell formulas that refer to the “penciled in grid”.
Not bad for an hour's work and forty lines of code.
In a former job, I had great success with this model. I would set up the bare-bones data input and write the code to do the calculations (generally using dlls compiled from C++ on the back end), and turn the spreadsheets over to my customers. They could do the formatting themselves or collaborate with me on it, and also add ancillary calculations, as could their customers. It made for fast feedback loops, and got the job done to everyone's satisfaction. Way easier and faster than writing a standalone application.
posted by Jim at
9:19 PM
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home