Building a word search in Excel VBA
So you want to build a snowman word search?
A typical grid contains a number of related words, all running
horizontally, vertically or diagonally.
Introducing WOWSER (the Wise Owl Word Search Excel Replicator).
Download this workbook and follow the numbered
steps below!
If you want to see how to go about building a system like this, read this alternative blog
explaining how I went about constructing this system (it includes lots of generally
useful programming tips based on my 30+ years of system-building experience!).
Step 1 - prepare your list of words
Go to the Words worksheet in the workbook and type in your
words:
For this example I've gone for an owl theme. Don't worry about spaces or punctuation
characters - these will be removed.
Lists of words work best when you have a range of different lengths, as for the above
example. It's a good idea to avoid three-letter words, as these can be
very hard to find.
On the menu, choose to prepare this list:
Click on this button to strip out punctuation and prepare your words.
In this case you'll see this message:
I could have got away probably with an 8 x 8 grid, were it not for the pesky
African fish and Andean pygmy owls.
This is now the list that the system will search against:
When the system finds a word it will flag this in column D.
Step 2 - Prepare your grid
Next, go to the Parameters worksheet and check you're happy
with the choices there:
I've reduced the number of rows in the final grid to 8, but left 11 columns.
Now choose to recreate a blank grid:
You can click on this button at any time to start again, while keeping your word list.
The system creates (after a warning and confirmation message) your blank grid:
Ready to start adding words!
Step 3 - adding words
Click on any cell in your grid and press Ctrl
+ Q to run the FillOne VBA
procedure:
I tend to start at the bottom right.
The system will find the longest word that it can, and show it to you as a
suggestion:
You can now decide if you want to keep this.
I chose to keep this word and then added two more (selecting cells
A1 and K7 respectively, and pressing Ctrl
+ Q each time):
I've now selected the blank cells G5 as shown, and pressed Ctrl + Q to run the macro again.
The system suggests another owl, but not the one with the shortest name:
The system will try to create as many cross-checking letters as possible - so it chooses
TAWNY in preference to BARRED (which would
also fit in, and is longer) because it cross-checks the letter Y.
For each suggestion you can choose one of three options:
|
Button |
What it will do |
|
Yes |
Permanently add this word to the grid in its current position. |
|
No (the default) |
Either rotate the current word into a new position, or show
the next one which will fit. |
|
Cancel |
Abandon the attempt to find a word. |
Step 4 - finishing the grid
You can keep adding words individually like this, but there's a short-cut:
Click on this button to add the rest of the words in your list (where possible) into your grid.
You could do this right at the start, but you will end up with many of the
words starting in the top left corner.
Choose the length of word you'll accept:
The idea behind this is to fit the longer words into the grid first, otherwise the system may use up all of the shortest words leaving no space for the longer ones later.
A glance at my list of words shows that I need to fit in a 9-letter and 6-letter
owl first, so I went for 6 in the dialog box above:
The owls I've got left to fit in.
I then refused to accept the first words I was offered, to get this:
By not accepting the first words at the top left, I've got a reasonably balanced grid.
I then ran this macro again (clicking on the button a second time), this time
setting a minimum word length of 0 to get:
Your
final grid will depend on which word suggestions you accepted.
Step 5 - testing your grid
To give yourself the confidence that all your words have been found, click on
this button:
Only click on this when you think your grid is complete.
You'll see a message about your unused cells:
I've got 32 cells I didn't use.
The macro then uses different colours for each word:
Your macro should then display a reassuring message that all your words have been found.
Step 6 - filling in a missing phrase
To make your word search more interesting you could ask people to spell out
the phrase constructed from the unused letters (reading left to right and top to
bottom). First enter the phrase:
Keep entering your phrase until the stripped out version beneath it contains exactly the right number of characters (this can take a bit of time - use trial and error).
You can then put the missing phrase in the unused cells:
Click on this final button to add the missing phrase into your grid.
Once you manually remove the background colour from your cells, your grid will
be good to go!
Once you remove background colouring you can send out your grid.
You can use this system to create a word search for any purposes, but the
system itself remains the intellectual property of Wise Owl Training, and you
may not copy or distribute it in any form without our prior written permission.
