BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Posted by Andy Brown on 02 February 2021
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Building a word search system in Excel VBA
If you just want to use WOWSER (the Wise Owl Word Search Excel Replicator) to generate your own word searches, click here. This blog is designed to give hints and tips from my 30+ years of experience of building systems on how I went about building this system, which you can download here.
If you enjoy this, you might like to also consider creating your own logic puzzles, as described in this blog from Dick Kusleika.
Step 1 - analysing the problem
When you break it down, creating a word search involves 3 main ideas. Firstly, you have the grid itself:
An Excel worksheet makes an ideal word search grid.
Secondly, you have a list of words or phrases you need to put into the grid:
Again, an Excel worksheet is an ideal way to hold the list of words you want to bury within your grid.
And thirdly, you have to position each word:
For each word, you can start on any cell and try to fit it in any one of 8 directions.
Based on these components, I then created the worksheets my final system would use.
A confession: this wasn't my first attempt at writing a word search system in VBA. Wise Owl's collective experience of writing computer systems is that the only way to get them right is to write the same system 2, 3 or 4 times (each time you learn from your mistakes in the previous iteration). Whether this advice is practical is another question!
Step 2 - creating the worksheets
The next thing I did was to create the worksheets I thought I'd need. Firstly, the list of words:
For each word or phrase you'll need to strip it of punctuation and blanks, find out how long it is and then have a flag saying whether or not it's already been included in the grid.
We'll then need a grid sheet:
A macro will have to recreate this, according to the length of the word list.
To manage the directions, I'll create a worksheet listing them:
For each directions I've included the number of rows to go down and the number of columns to go across.
Purist programmers would store this information in arrays to make the system run more quickly. I made a conscious decision when specifying this system that speed wasn't going to be an issue (I'm more than capable of using arrays, but accessing the information directly from cells will make the system easier to understand).
I then chose to create a worksheet containing the colours to use:
Each background number corresponds to the colour shown.
Finally, I created a sheet listing parameters for the system:
The system uses range names to keep track of information (so the NumberRows and NumberCols ranges will hold the height and width of the grid, for example).
Here are the final worksheets:
I've added a menu sheet to hold the buttons to click - more on this below.
Step 3 - naming the worksheets
It's much easier in VBA if you can name your worksheets to make them easier to reference:
I've created an extra documentation sheet called Range names to hold a list of the range names I've used, then given each of my worksheets internal names in VBA.
Step 4 - writing the outline code
Time now to think what the system will do. You can break this down into six parts:
|1 - prepare words||Remove any spaces and punctuation, find the length of each "word" and then sort them into order so we can put the longest words into the grid first|
|2 - create grid||Create a grid of the right dimensions, and format it to make it look OK.|
|3 - fit words||An iterative process of trying to fit words at given letters in the grid, using trial and error.|
|4 - completing grid||At some point you may want to automate the process of filling any remaining words into the grid.|
|5 - checking grid||Running a macro to double-check that all of the words in the list have been found.|
|6 - fitting phrase||Adding a missing phrase into the unused letters to add a certain je ne sais quoi to the final puzzle.|
I created one module for each stage:
I also created a module called modPublic, to hold any public constants.
We can then create the outline of the different macros for each stage - for example:
'fit any remaining words in the grid
This will allow us to create the front menu buttons for each of these steps:
You can even assign your (empty) macros to the correct buttons at this stage.
The above step sums up good computer system design: breaking a large complex problem down into a series of less complicated ones, then repeating this process at the next level down (and so on recursively until you have manageable problems at the bottom).
Step 4 - writing the algorithms
Time now to work out what the different programs will do. Here's what the program to prepare your words should do:
'Go to the sheet of words
'For each word in column A
'strip out spaces and punctuation, and
'put the result in the cell to its right
'put the length of this stripped text in
'Work out how many words (and letters) there
'are, and suggest a grid size to user
Here's what the macro to create the grid might look like:
'Check user is happy to reset everything
'Go to the old grid sheet and delete its contents
'Go to the list of words and mark each as not found
'Hide all the rows below (and columns to the right)
'of the grid (taking the sizes from the parameter
'Set the cell alignment and row height/column
'width of each cell in the grid
Here's my outline algorithm to check (for any cell) if it can start any word fitting into the grid in any direction:
'check that the user has one cell selected, and that
'it's within the grid
'Loop down the list of words which haven't yet been
'found, starting with the longest. For each such word:
'Loop over the column of directions on the Directions
'sheet, getting the row and column offset for each. For
'Check how many cells there are in this direction. If
'Create a string variable containing the "word" spelt
'if you follow the letters in this direction. Compare
'this to the word you're looking for. Do they match?
'Yes ==> colour each cell of the word and show a
'dialog box on screen asking the user if they want
'to accept the word (if they do, reset background
'colour to white and end macro; otherwise, go on
'to next direction)
'No ==> go on to next direction
'end the condition
'If reach this point, no words found (or at least,
'no words acceptable to user) - say so
'check if there are still words left to find, and
'show message if there aren't
To show you what this last algorithm might look like in practice, here's how I started:
My original algorithm for trying to insert one word.
These instructions - half VBA, half English - are often called pseudo-code. If you can't write down in English what your macro will do, you have no hope of converting your instructions into VBA!
Step 5 - writing the code
As I mentioned at the start of this blog, you can download my final Excel workbook and see the code for yourself. Here are some things I didn't do:
|Testing||There may (will!) be bugs in the code, because I'm not releasing it as a commercial product! There's also the question of who will be using the system (if you're the only one who'll be using your system, you don't have to dot all your i's and cross your t's).|
|Speed trial||Sometimes when writing systems I have speed in mind all the time, and optimise everything to run as quickly as possible. For this system the volume of data and nature of how the system will be used meant that speed wasn't going to be an issue, so why waste time coding to save a few seconds? Instead I've written the system to be easy to understand and use, at the expense of a few extra seconds of processing time.|
This gives the last lesson: tailor your system to your user base. The stages I missed out above would at least double the development time of the system, so it makes sense only to do them if my application is either mission critical or if it will be used by many different users.