In the previous article here I made a custom formula, building a plane in Excel. The idea for the article was inspired from the SoftUni entrance exam. Today I have done something similar – I found a challenge, where one was asked to develop a solution for the Bulls and Cows game. For those, who have never heard of this game, in short it is the following:

The numerical version of the game is usually played with 4 digits, but can also be played with 3 or any other number of digits.

On a sheet of paper, the players each write a 4-digit secret number. The digits must be all different. Then, in turn, the players try to guess their opponent’s number who gives the number of matches. If the matching digits are on their right positions, they are “bulls”, if on different positions, they are “cows”. Example:

Secret number: 4271

Opponent’s try: 1234

Answer: 1 bull and 2 cows. (The bull is “2”, the cows are “4” and “1”.)

In my case, I had to use only 4 digit numbers and to forbid the usage of 0 in the number. So, what I wanted to build was something like this – fBullsAndCows(iGuessNum As Integer, iTargetBulls As Integer, iTargetCows As Integer, Optional bDisplayInLine As Boolean) As String

As an output I needed all the possible 4 digit numbers, that fit.

For my surprise, I managed to do it quite easily with VBA, although at first I tought that it will not be that easy. Furthermore, I added some enhancement to the function, showing in a MsgBox the result in two optional formats – in one line or each number below the previous. Anyway, I was quite satisfied with the result – check for yourself:

If there is no option for answer, e.g. we want to return 3 Bulls and 1 Cow, which is impossible with 4 digit number, then the formula simply returns “No”. As I have explained, I have provided an optional boolean parameter, called “bDisplayInLine”. If it is set to TRUE from the custom formula, then the data is displayed in line as follows:

Enjoy the code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
Option Explicit Public Function fBullsAndCows(iGuessNum As Integer, iTargetBulls As Integer, iTargetCows As Integer, Optional bDisplayInLine As Boolean) As String Dim iBulls As Integer Dim iCows As Integer Dim iCandidate As Integer Dim sCandidate As String Dim sChecker As String Dim z As Integer Dim i As Integer Dim sSolution As String iCandidate = 1111 'the minimal four digit int number without a zero Do While (iCandidate <= 9999) sChecker = Trim(Str(iGuessNum)) sCandidate = Trim(Str(iCandidate)) 'check if the number contains a 0 If Not (ContainsASymbol(sCandidate, "0")) Then iBulls = 0 iCows = 0 'Count the bulls and mark them as not available For i = 1 To 4 If Mid(sCandidate, i, 1) = Mid(sChecker, i, 1) Then Mid(sCandidate, i, 1) = "N" Mid(sChecker, i, 1) = "A" iBulls = iBulls + 1 End If Next i 'Count the cows and mark them as not available For i = 1 To 4 For z = 1 To 4 If Mid(sChecker, i, 1) = Mid(sCandidate, z, 1) Then Mid(sCandidate, z, 1) = "M" Mid(sChecker, i, 1) = "B" iCows = iCows + 1 End If Next z Next i If (iTargetBulls = iBulls) And (iTargetCows = iCows) Then If bDisplayInLine Then fBullsAndCows = fBullsAndCows & CStr(iCandidate) & " " Else fBullsAndCows = fBullsAndCows & CStr(iCandidate) & vbCrLf End If End If End If iCandidate = iCandidate + 1 Loop If Len(fBullsAndCows) < 3 Then fBullsAndCows = "No" Exit Function End If fBullsAndCows = fBullsAndCows & vbCrLf & "VitoshAcademy.com" MsgBox fBullsAndCows, vbInformation, "VitoshAcademy.com" End Function Public Function ContainsASymbol(ByVal sCheckedString, ByVal sSymbol As String) As Boolean Dim i As Integer For i = 1 To Len(sCheckedString) If Mid(sCheckedString, i, 1) = sSymbol Then ContainsASymbol = True Exit For End If Next i End Function |

🙂