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:
|
Part |
Notes |
|
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:
Option Explicit
Sub FinishGrid()
End Sub
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:
Here's what the macro to create the grid might look like:
Here's my outline algorithm to check (for any cell) if it can start any word
fitting into
the grid in any direction:
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:
|
Stage |
Notes |
|
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.
