In this post we see how to create the game of Wordle in Excel using VBA macros. The macro picks a 5-letter word randomly and compares player entries letter by letter for every attempt. It uses VBA string functions to loop through letters and do the comparison. As a result, the interior color of cells is formatted accordingly to show which letters match. Follow the link at the bottom to download Excel Wordle. The file includes a fairly complete dictionary of 5-letter words in English.
Wordle game history
Wordle is a web-based word game created and developed by the Welsh software engineer Josh Wardle. In the game, players have six attempts to guess a five-letter word, receiving feedback through colored tiles that indicate correct letters and their placement.
Wordle was publicly released in October 2021. It gained widespread popularity in late 2021 and continues being very popular today. The game's success spurred the creation of numerous clones, adaptations in other languages, and variations with unique twists. This post shows another such clone of Wordle, this time in Excel, intentionally designed to be part of the Excel Macro Fun portfolio, and to help readers learn Excel VBA the fun way.
Wordle layout in Excel
The layout for Wordle in Excel consists of a group of 30 cells that span 5 columns and 6 rows in a worksheet (range B2:F7). See below a simplification of the layout with two buttons to pick a new word and check word match (see later).
The downloadable version of Wordle in Excel is formatted more nicely and has buttons to input letters. Each letter can also be added manually in the selected cell using the keyboard keys. In both cases, a worksheet change event triggers a routine that checks whether a proper character has been entered (a letter in the relevant alphabet) and formats the cell to show upper case letters.
The available version of Excel Wordle uses worksheet protection to avoid players entering letters outside the board. It´s not password protected, so you can unprotect it if needed, but note that the macro protects the sheet again when starting the game and also each time a given word is checked. That´s because the macro locks each row that has been checked in this version of Excel Wordle.
Pick target word
A dictionary of 5-letter words and descriptions in English is available in the second worksheet. This can be adapted as needed, for example, removing very difficult words to make it easier to play, or changing the dictionary altogether to have it in a different language.
The first macro gets a random row number to pick the word in column A. The Rnd function considers as many rows as cells with content in column A (given by the variable lastRow). The word is stored in the variable "targetWord", which is declared at module level (outside the procedure) so that it keeps its value while playing.
lastRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
Randomize
r = Int(Rnd * (lastRow - 1)) + 2
targetWord = Sheet2.Range("A" & r).Value
Check given word
Players enter a word letter by letter either using the keyboard or pressing the buttons for each letter available directly on the sheet. In a simplified version, an event procedure is not even needed; the "Check Word" button is pressed when ready to check the word. The version of Excel Wordle available for download here uses an event procedure to anticipate keyboard input. This gives the advantage of being able to check whether a correct character is entered (a letter in the alphabet and not a number or special symbol). Furthermore, the routine formats the letter as upper case and checks whether all 5 letters have been entered to prompt a dialog that allows players to quickly run a word check.
Either way, the check routine will first see if all 5 letters are there, and then, if the 5-letter word is in the dictionary to move forward with word comparison. The macro below does just that in a simplified way. For example, it searches the whole column A in Sheet2 to check if the given word is valid. We could get the last row with content in Sheet2 as done in the macro above to limit the search.
lastBoardRow = Cells(Rows.Count, 2).End(xlUp).Row
'check if 5-letter word
If WorksheetFunction.CountA(Range("B" & lastBoardRow & ":F" & lastBoardRow)) < 5 Then
MsgBox "Add 5 letters"
Else
word = GetAttemptWord(lastBoardRow)
'check if valid
Set dictWord = Sheet2.Range("A:A").Find(word)
If dictWord Is Nothing Then
MsgBox "Not a valid word"
Else
'compare with the target word
Call CompareWord(word, lastBoardRow)
End If
End If
The macro calls a Function procedure (GetAttemptWord) that returns the given word by concatenating all five letters. That´s achieved using a loop through each cell. If the given word is in the dictionary, it calls another macro (CompareWord) to compare the given word against the target word and see if it matches (see next).
Dim wordLetters As String
For Each cell In Range("B" & lastRow & ":F" & lastRow)
wordLetters = wordLetters + cell.Value
Next cell
GetAttemptWord = UCase(wordLetters)
End Function
Compare words
The last macro compares the given word against the target word letter by letter, unless it´s an exact match. Hence, the very first condition checks if it´s the same word, otherwise it starts a loop to compare each letter. A simplified version of that macro is shown below. The macro is more sophisticated in the downloadable version of Wordle for Excel because it formats not just the cells but also the keypad buttons on the sheet.
If word = targetWord Then
Range("B" & lastRow & ":F" & lastRow).Interior.Color = vbGreen
MsgBox "Yes, that's the word"
Else
'loop through letters
For pos = 1 To 5
wordLetter = Mid(word, pos, 1)
If targetWord <> "" Then targetWordLetter = Mid(targetWord, pos, 1)
If wordLetter = targetWordLetter Then
Cells(lastRow, pos + 1).Interior.Color = vbGreen
ElseIf InStr(targetWord, wordLetter) > 0 Then
Cells(lastRow, pos + 1).Interior.Color = vbYellow
Else
Cells(lastRow, pos + 1).Interior.Color = RGB(200, 200, 200)
End If
Next pos
End If
End Sub
Note that the procedure "CompareWord" accepts two parameters: the given word and the last row in the board, while the target word (targetWord) value is kept in memory. That makes it very simple to compare the two.
The loop moves from cell to cell and letter to letter using the variable "pos" and the Mid VBA function. If two letters match, the cell gets a green background. If they don´t match but the target word contains that letter, it gets yellow background. That´s checked using the InStr VBA function. If the target word does not contain that letter, the cell gets a gray background.
Check also Word Search Pro for Excel
No comments:
Post a Comment