There resides on my computer a folder called “Useless utilities”. This contains various VBA programs I’ve knocked up which are, in themselves, pretty pointless.

For example, there is one that enables you to insert some data in the next available cell in a spreadsheet. So, say you have 278 rows populated, and you want to add some data to just after the last row, instead of scrolling down until you reach the empty row, you just enter the data in a box and as soon as you press Enter it will be put there for you.

A favourite of mine is the Random Punishment Selector. Stumped for ideas about how to deal with some miscreant? Enter the random punishment selector! It’s something I rustled up based on an idea from a colleague of mine called Theresa.

There’s another one that will take all the entries in column A and create worksheets corresponding to each one.

Then there’s one that does the opposite: it takes all the worksheet names and creates an index of them.

Now, some of these functions have since been made possible without having to do any programming yourself, through Asap Utilities, but that isn’t the point. I would still recommend trying to do these things, and getting your students to attempt similar pointless programming. Why? For several reasons:

• We all know that you can learn a lot by doing it yourself.

• It can be a lot of fun being challenged, even if it’s yourself doing the challenging.

• When you finally work out how to do something that’s challenging, you feel a great sense of achievement.

• Such useless utilities and pointless programs may not be so pointless after all.

I’d like to expand on that last point. Over the years, every time I’ve thought to myself that I need some code that does X, I remembered that I had actually created such a code. It’s a bit like having your own code library.

For examples of utilities I’ve created in which I was able to employ the techniques I discovered see Make your own.

Prompts

What I’d like to see in computing lessons, or at least some parts of them, is exercises that are the equivalent of writing prompts. For example, a writing prompt I came across was to write a six-word memoir. This is, clearly, pointless from a purely functional point of view: how can you write a memoir about someone in only six words? But when you have only six words, each one has to work to earn its place there. Indeed, all writing prompts are intended to jolt you out of your usual everyday thinking and writing.

So why not introduce a programming equivalent, say when someone has completed a piece of work and there isn’t time, or it’s not sensible, to embark on the next piece of work?

You never know when they might find a use for the code they come up with, as part of a larger project.

The User ID generator

This is an example of a utility I created using the kind of utilities I’ve mentioned.

Problem

Creating user IDs for schools’ networks, avoiding clashes, and organising the names of the pupils and their login details automatically.

Solution

The UserID Generator was a spreadsheet programmed in VBA. It did the following:

Asked you for the name of the school.

Once you entered the name, the program created a folder bearing the name of the school. Inside the folder, the spreadsheet was saved using the school’s name and the date the file was created.

Next, you enter the pupils’ names. This could be automated, as long as you specified where the CSV file could be found, or you could enter the names manually. Once the names have been loaded or entered, you clicked on Continue.

Here’s one I created earlier:

This is what the program did:

1. From the first name and surname entered, it created a User ID and also the full name. The UserID was created from the whole of the first name, and the first letter of the surname.

2. If two pupils had the same name, it would use the first two letters of the surname for the second pupil.

3. As you can see from the screenshot, some pupils are in different year groups. The program created a worksheet corresponding to each year group, and then copied each entry into the relevant sheet, as well as keeping a central record of all the pupils and their User IDs.

This is all very tame these days, when you have advanced information management systems that will do all this for you. But at the time I wrote this program, there was nothing available that worked exactly as we wanted. This little spreadsheet literally saved hours and hours.

And it was put together using all those useless and pointless bits of code I’d cobbled together over the years!