The Layout
Similar to most of the previous macros, we will apply some common
Cells and Range-related VBA formatting properties to create the Excel Word
Search grid. Additionally, validation to select the Excel Word Search Theme
will be added based on data in worksheet “Themes”. That can be updated anytime
and users can add as many themes as wished, with the only limitation of having
no more than 14 words per theme, and words no longer than 12 letters, in order
to easily fit in the designed grid. The subroutine “SetupLayout” will only run
upon clicking the “Clear All” button.
Please note the headings and formula bar are being removed.
If you wish to restore them you may go to View-Show and tick the respective
boxes, or set the corresponding VBA properties to True as follows:
Application.DisplayFormulaBar =
True
ActiveWindow.DisplayHeadings =
True
Adding Words
Words are taken from the worksheet “Themes” for a given
selected theme. As discussed earlier, the user can freely add new themes and
words (with some length limitations) that will become available to create the Excel Word Search. It is important to keep the layout of the
“Themes” worksheet as it is for the macro to work correctly. This means, the
theme’s name is always in the first row and words are underneath in the following
rows within the same column.
The “Main” module contains the “AddWords” subroutine that will pick up words for the chosen theme and randomly add them to the grid with the RANDOMIZE/RND VBA functions that we have used many times in previous macros on this blog. The word location is determined by RowStart and ColStart coordinates variables and word direction by the RowIncr and ColIncr.
The separate
subroutine “WordPlacementCheck” will go letter by letter through the grid
checking for either an empty cell or a cell with the same letter. Each word
loops as needed to find the right placement. In case there’s no
physical space to accommodate the given word, the variable “PlacementLoops” has
been introduced to count and exit the loop if it runs more than 999 times. The
“FinalWordCount” variable will represent the final number of total words placed
in the generated Excel Word Search.
As mentioned earlier,
adding the words to the puzzle takes place letter by letter into each cell. That’s achieved
with the “Mid” function, which works similarly to the “Left” and “Right”
functions but it adds a third variable to specify the position to start within
the string: Mid(String, Position, Length). The For loop to place the words once
checked goes as follows:
For
j = 1 To WordLength
WS.Cells(RowStart,
ColStart).Value = Mid(iWord, j, 1)
RowStart =
RowStart + RowIncr
ColStart =
ColStart + ColIncr
Next j
Finishing the puzzle
Once all the theme
words are placed, it is copied to a separate sheet with the subroutine “CopyToSolution”. In
order to finalize the Excel Word Search we just need to add random letters in
the empty spaces now. That’s the purpose of the “FillUpLetters” subroutine, which
will loop through the grid cells and randomly select the ASCII decimal
character code of the upper case alphabet letters (ASCII code between 65 and
90) to assign the value to the empty cells via Chr(code) as shown below. Find the full list of ASCII codes in this other page: ASCII Characters Code Set
Sub
FillUpLetters()
For r = 2 To
16
For c = 2 To
17
If WS.Cells(r,
c).Value = "" Then
Randomize
iLetter = Int((90 - 65 + 1) * Rnd
+ 65)
WS.Cells(r, c).Value =
Chr(iLetter)
With Sheets("Solution").Cells(r,
c)
.Font.Bold
= False
.Font.Size
= 12
.Value =
Chr(iLetter)
End With
End If
Next c
Next r
End Sub
Hi. Can this code be upgraded so it accepts numbers, for numbers search puzzle? Thank you.
ReplyDeleteSure can. You just add the series of numbers instead of words in the second sheet (Themes), and update the procedure FillUpLetters to add random numbers instead of letters in the remaining cells. In that procedure, letters are added with ChrW from a random numeral. Good luck!
Delete