There are times when an off-the-shelf solution just isn’t good enough. It’s either too general, packed with too many features or too expensive — or all of those. On those occasions if you have the requisite application, the time and some expertise (and are happy to learn as you go along), your own custom-made solution is by far the best.
I don’t have time to go into lots of detail on each application I’ve created, but here is a sample. My point here is not to present myself as some closet uber-programmer, but simply to point out that if there is a real need, then learning how to program is far from pointless. This is a far better message to young people than the simplistic, and untrue, assertion that everybody needs to learn “coding” in order to survive and thrive in the 21st century. It’s why I advocate a project-based, problem-solving learning approach. If students can come up with a real need, a need that means a lot to them, they are more likely to learn how to program, and probably better and faster too.
Anyway, here are my examples.
My programming projects
BASIC projects
I had a bit of a love/hate relationship with BASIC. On the one hand, it was a reasonably straightforward programming language. On the other hand, I hated the fact that you couldn’t go back and correct a mistake. If the program didn’t work, you had to enter all the code again.
Sometimes I’d buy a popular computing magazine which had the listing for something like a game. I can’t tell you the number of times I’d enter 1000 lines of code, only to get the message “Error at line 70”. I’m not the only person who experienced that frustration, and all I can think of is that none of the magazine staff tested that the code actually worked!
Random Jargon Generator
My first BASIC program, circa 1987, was a jargon phrase generator. I actually came across the code listing for it a few months ago, and stored it in a safe place. Trouble is, I can’t remember where that is. Anyway, it was a very simple idea, a programmatic representation of a grid of three columns with lists of words in each one. The program selected a random word from each column, and put them together to make a phrase. The gobbledegook generator does much the same thing.
Visual Basic projects
VAT capitation calculator
In 1990 I was working in a school which had to pay VAT (value-added tax) on its purchases. As a Head of Department (of ICT and Computing) I had to add VAT onto some of my outgoings but not others.
I got tired of calculating the VAT over and over again, and wanted something handier than a spreadsheet for quick ‘back of an envelope’ calculations. With this in mind, I developed a calculator in Visual Basic that was similar to a standard computer calculator except for a few additional features.
One was the addition of a button labelled ‘VAT’. Clicking on this automatically added the VAT to an item. I know this doesn’t seem like a big deal, because how long does it take to multiple a number by 1.15 (the rate of VAT back then was 15%)? But boy does it get tedious after a while.
Another feature was that it was easy to calculate the cost of an item without losing the previous calculation, which you could store as a running total.
A third feature was that you could lock the calculator, so that if you were called away to deal with a pupil, say, before finishing all your calculations, you could leave the calculator unattended in the knowledge that nobody could accidentally erase what you’d done up to that point.
VBA calculator, by Terry Freedman
Notes application
This was a simple text editor like the standard Windows Notepad. There was no point in creating it, except to see if I could create it! It was, I recall, my very first foray into Visual Basic.
Excel
There’s a lot you can do with a fully-featured spreadsheet with programming facilities. Here are a few of the applications I’ve created with Excel.
Rota manager
When I worked as an ICT advisor, we ran a surgery every morning in which a teacher could come in on their way to school to pick up an emergency item like a printer cartridge or get a quick tutorial or advice. Each week it would take ages to devise the rota of who was going to run the surgery each day, with people saying when and when they were not free.
I created a spreadsheet that required each person to say which mornings they were available that half-term. Then a click of a button would cause the spreadsheet to populate the rota for the next six weeks. It also took into account how many times each person was allocated. So, if I was allocated three mornings this week, I would be allocated none or maybe one next week. It literally saved hours, and another bonus was that it was, and was seen to be, a very equitable solution.
Expenses calculator
In the advisor job I just mentioned, we had to claim travelling expenses for visiting schools.
This application made use of functions like VLookup. Index and Match as well as Visual Basic for Applications (VBA).
In order to work out your mileage, you had to look up a chart to find out how many miles it was from our base to a particular school, and then double that number to account for the return journey, or add on the mileage from that school to another one, and then the miles from that school back to base.
Then to calculate the expenses, you had to multiply the total miles by the rate per mile, and then enter all that on an expenses sheet.
As you can imagine, this was enormously time-consuming. So my spreadsheet worked as follows:
First, select the school you visited from a drop-down menu, using the Data Validation facility.
The spreadsheet then entered the number of miles away that was.
On the next line of the spreadsheet, select another school, or our base.
The spreadsheet would enter the number of miles from the first school to that one.
Obviously on each line you had to enter the date of the visit too.
The spreadsheet entered the expenses incurred for each visit.
The design of the spreadsheet was exactly the same as the council’s expenses sheet, so that all you had to do after entering all your visits for that week was to save the spreadsheet and then either email it, or print it off and hand it in. You didn’t have to spend additional time transferring all the data onto an expenses sheet.
This raises a useful point. The design of the spreadsheet’s appearance is important too, for time-saving reasons as well as aesthetic ones. For example, my own invoices are created using an Excel template I devised. Once I’ve completed an invoice, I just save it as a pdf as well as a spreadsheet, and email off the pdf version. A similar approach was used in my Lesson Planner, described below.
Technicians’ spreadsheet
Although programming is useful, you don’t always need it. What you do need is to be able to think in a computer programmer kind of way. That’s why I think the term ‘coding’ is so misleading, not to say limiting. Sometimes, programming can be overkill, like using the proverbial sledgehammer to crack a nut. The technicians’ spreadsheet I created is a case in point. I could have used VBA to make it work, but there was really no need for anything so grand.
One of the things IT technicians do (or should do) is keep track of faults as well as fix them. By doing so they can identify whether what seemed at first to be a one-off glitch is symptomatic of a deeper problem.
For example, if the printer in room 32 always gets jammed in period 3 on a Thursday afternoon, it’s most likely because of the way the class at that time is using it. On the other hand, if the printer in room 32 gets jammed in every lesson, that sounds more like a problem with the printer.
A spreadsheet can pinpoint patterns by using formulae like CountIf, which adds up how many times a condition is true. Combined with conditional formatting, the spreadsheet can highlight a room or item of equipment in red if the number of reported faults exceeds a certain threshold.
Of course, technicians don’t want to spend lots of time entering data. Time can be saved by creating drop-down menus using data validation, and using a function like VLookup. For example, selecting room 32 from a drop-down menu could then cause the word ‘printer’ to appear in the equipment column.
An additional feature of the spreadsheet could be the inclusion of a facility to record how long it took from the fault being reported to when it was fixed. You can then generate stats like “98% of reported faults were sorted out within one working day.”
For a silly but enjoyable use of spreadsheets, see my article on the Homework Excuse Management System.
Word
Lesson Plan Creator
In the days when Ofsted expected to see detailed lesson plans for every lesson, I created a lesson plan creator in Word, using VBA and a template.
The process of using it was as follows:
Create a new document and select the Lesson Plan template.
A large screen would cover the document. This contained several boxes, such as:
The title of the lesson plan.
The year group (using a drop-down menu).
The class.
Subject.
Key Stage.
Lesson objectives.
and so on.
Once all the relevant boxes had been populated (using drop-down menus for may of them), click a button labelled ‘Publish’. This did two things:
It saved the lesson plan, using the lesson plan title and year group and class as part of the name.
It ‘squirted’ all of the data into the relevant places in the document itself. The advantage of doing so was that the formatting of each lesson plan looked the same, and because it was easier for people to use the large screen than to enter details directly into the document (although that is a possible approach too, by making the template a form with built-in drop-down menus.
Using this kind of approach is quite flexible, because as well as having drop-down menus you can have radio buttons. For example, there might be a radio button for the question “Is this the first lesson of a new unit?”. If ‘Yes’ is selected, another box appears asking you write an introduction to the Unit as a whole.
The way the data entered in each box gets into the right place in the document is by using invisible placeholders or bookmarks in the document. Thus the title of the lesson is entered into the part of the document containing the hidden bookmark ‘Title’. This is, of course, a simplistic explanation of the process.
Word Counter
A far simpler application was a word counter. It’s easy to get information on the number of words you’ve typed in a document, by clicking on the Word Count option. You can also embed a field that will automatically update the word count when you save the document or press F9 (in Windows). So in a sense creating my own word counter was a bit pointless.
But I wanted a slightly more advanced one. Using VBA I created a box which asked me to enter the target number of words I wanted to write. That would remain there (unless I changed it for some reason), but every so often I could click the ‘Calculate’ button. This would then use the built-in word counting function to calculate the number of words in the document, and then present a message. That message would either be “Spot on!”, “Oh no, you’ve written X too many words!”, or “Good grief, come on, you still have X words to go!”.
This used really simple code: the Input, word count and Msgbox functions, plus an If-Then-Else section of code.
Access
A project management system
Finally, Access, which is a relational database, is extremely powerful even if you don’t use the programming facility. In 1993I used Access to create a project management system for my Computing department. This enabled me to allocate members of my team to different projects.
The great thing about a database like Access is the querying and reporting facilities. So, for example, I could easily remind myself of which people were working on the refurbishment project. Alternatively, I could select a group of people to find out which projects they were working on.
The nice thing about creating such an application yourself is that you can limit the functionality. That may not sound like something you’d want to do, but believe me it is! Every project management application I’ve looked at has been in the category of bloatware. Every conceivable function has been thrown in, in case someone needs it. Consequently, any individual person uses only a small percentage of them.
Conclusion
The point of this article is actually not to encourage you to create your own applications. A teacher’s job is hard enough without adding programming to the to-do list. Rather, the point is that programming is most useful, most enjoyable and most likely to be learnt when there is a real need for something.