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:


Tagged with: , , , ,