Lottery Numbers Generator

lottery generator
msofficeshare logo

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.

pic1 lottery

 

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.