VBA – Excel – Bulls and Cows with Excel formula

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:

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

🙂