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.
