Browse 551 attributed reviews, viewable separately for our classroom and online training
Using the WOWSER Excel VBA system to build a word search
Follow this blog to construct your own word search, complete with a missing phrase spelt out by the unused letters!

Posted by Andy Brown on 01 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 in Excel VBA

So you want to build a snowman word search?

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:

List of 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:

Preparing your words

Click on this button to strip out punctuation and prepare your words.

 

In this case you'll see this message:

Grid size 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:

Final list of words

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:

Rows and columns

I've reduced the number of rows in the final grid to 8, but left 11 columns.

Now choose to recreate a blank grid:

Creating the 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:

The 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:

Starting filling

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:

Suggestion for word

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):

More suggestions

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:

Next owl

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:

Finishing grid

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:

Choosing minimum word length

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:

Remaining owls

The owls I've got left to fit in.

I then refused to accept the first words I was offered, to get this:

Balanced grid

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:

Final grid

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:

Finding all the words

Only click on this when you think your grid is complete.

You'll see a message about your unused cells:

Blank cells

I've got 32 cells I didn't use.

The macro then uses different colours for each word:

Colouring words

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:

Entering a 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:

Adding missing phrase

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!

The phrase in the grid

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.

This blog has 0 threads Add post