Requirement: Visual Basic knowledge

File to download: Lottery Numbers Generator.xlsm

Related articles: Using RAND or RANDBETWEEN Functions to generate test data, Use COUNTIF function and sorting to analyze the PowerBall Playout

I seldom buy lottery ticket, no matter whether it is Power Ball, Mega Million, or Texas Lotto. I buy them by the motivation of “fear”. I usually buy them with my colleagues when the jackpot accumulates to certain large amount and one of my co-workers will start asking around to have people pooled money to buy the lottery tickets. I buy them because I am “afraid” that if my colleagues win the jackpot and quit altogether will leave me crying alone every day in the office.

I don’t buy lottery because I know that the odd is against me. There will be a much higher chance to get hit by a lightning twice than winning the jackpot of a lottery ticket. Besides the jackpot, it is not unusual that we pool $50 to buy lottery and ends up winning nothing. Among all the lotteries, the Mega Million gives you the least chance to win (1 out of 259 million). The chance of winning the jackpot of a Texas Lotto is 10 times higher than winning the one of Mega Million. I just found that it is fun to estimate the probability and create an Excel Visual Basic module to automatically generating the lottery numbers by random.

The function to be used to generating random numbers in the VB code is RND, which is equivalent to the RAND in Excel worksheet. However, there is no function in VB code that is equivalent to the RANDBETWEEN function in Excel worksheet that allows you to generate a random number between a specified range of values. Instead, we can use this formula to do the work: **Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)**.

You also need to be aware that duplicated numbers are not allowed in picking the lottery numbers other than the Power Ball and Mega Ball. As a result, a condition to check with the predecessor(s) must be enforced starting from the second random number is generated. For example, the second generated number must be compared with the first generated number. If the second number is the same as the first generated number, the second generated number must be re-run and re-generated. With the same logic, the third generated number must be compared with the first and the second generated numbers and so forth. To achieve such mechanism, an IF statement is combined with a DO loop to make sure each subsequent randomly generated number will not duplicate the prior randomly generated numbers.

The left illustration is the interface of the program. The program will randomly generate numbers for 3 lotteries: Power Ball, Mega Million, or Texas Lotto. Please choose one of them on cell B3. It has a drop down menu to allow the user to pick the type. Then click the “Go !” button to generate the lottery numbers.

Below are the codes for the VB module.

Sub LotteryGenerator()

Dim MaxValueRegular As Integer, MaxValueSpecial As Integer

Dim LotteryChoice As String

Dim RegularBall(1 To 6) As Integer

Range("ClearArea").Select

Selection.ClearContents

LotteryChoice = Range("LotteryChoice").Value

If LotteryChoice = "Powerball" Then

MaxValueRegular = Range("White_PB").Value

MaxValueSpecial = Range("Red_PB").Value

End If

If LotteryChoice = "Mega Millions" Then

MaxValueRegular = Range("White_MM").Value

MaxValueSpecial = Range("Red_MM").Value

End If

If LotteryChoice = "Texas Lotto" Then

MaxValueRegular = Range("White_TL").Value

End If

'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

RegularBall(1) = Int(MaxValueRegular * Rnd + 1)

RegularBall(2) = Int(MaxValueRegular * Rnd + 1)

Do While RegularBall(2) = RegularBall(1)

RegularBall(2) = Int(MaxValueRegular * Rnd + 1)

Loop

RegularBall(3) = Int(MaxValueRegular * Rnd + 1)

Do While (RegularBall(3) = RegularBall(1)) _

Or (RegularBall(3) = RegularBall(2))

RegularBall(3) = Int(MaxValueRegular * Rnd + 1)

Loop

RegularBall(4) = Int(MaxValueRegular * Rnd + 1)

Do While (RegularBall(4) = RegularBall(1)) _

Or (RegularBall(4) = RegularBall(2)) _

Or (RegularBall(4) = RegularBall(3))

RegularBall(4) = Int(MaxValueRegular * Rnd + 1)

Loop

RegularBall(5) = Int(MaxValueRegular * Rnd + 1)

Do While (RegularBall(5) = RegularBall(1)) _

Or (RegularBall(5) = RegularBall(2)) _

Or (RegularBall(5) = RegularBall(3)) _

Or (RegularBall(5) = RegularBall(4))

RegularBall(5) = Int(MaxValueRegular * Rnd + 1)

Loop

If LotteryChoice = "Texas Lotto" Then

RegularBall(6) = Int(MaxValueRegular * Rnd + 1)

Do While (RegularBall(6) = RegularBall(1)) _

Or (RegularBall(6) = RegularBall(2)) _

Or (RegularBall(6) = RegularBall(3)) _

Or (RegularBall(6) = RegularBall(4)) _

Or (RegularBall(6) = RegularBall(5))

RegularBall(6) = Int(MaxValueRegular * Rnd + 1)

Loop

Else

RegularBall(6) = 0

End If

SpecialBall = Int(MaxValueSpecial * Rnd + 1)

If LotteryChoice = "Powerball" Then

Range("FirstCell_PB").Select

End If

If LotteryChoice = "Mega Millions" Then

Range("FirstCell_MM").Select

End If

If LotteryChoice = "Texas Lotto" Then

Range("FirstCell_TL").Select

End If

ActiveCell.Value = RegularBall(1)

ActiveCell.Offset(1, 0).Value = RegularBall(2)

ActiveCell.Offset(2, 0).Value = RegularBall(3)

ActiveCell.Offset(3, 0).Value = RegularBall(4)

ActiveCell.Offset(4, 0).Value = RegularBall(5)

If LotteryChoice = "Texas Lotto" Then

ActiveCell.Offset(5, 0).Value = RegularBall(6)

Else

ActiveCell.Offset(6, 0).Value = SpecialBall

End If

End Sub

**Note**: To use the program, you must “Enable” the content to let the VB module to run.

**Disclaimer**: This Excel file is not intended to promote or endorse gambling. It is purely for fun and for scientific curiosity.