Using Excel And VBA to Create Your Own Lotto Numbers

Using Excel And VBA to Create Your Own Lotto Numbers

Latest Casino News 10 Apr , 2017 0

All numbers in lotto are drawn completely at random so the chances of winning a lottery are several million to one. But that doesn't mean you can't improve your odds with common sense and a little knowledge of Excel spreadsheets and VBA.

The Coin Toss Analogy And Lotto Results

An example of a coin toss makes an interesting comparison. Each toss must give a random outcome; heads or tails with a fifty percent chance of either result.

But if you've tossed five heads in a row what are the chances of a another head? The answer is still fifty percent, but common sense tells you the more heads in a row, the more likely the next toss will produce tails, even though this logic is incorrect.

The answer to this paradox lies in the old adage; in a world of infinite time and possibilities, all outcomes are possible.

Lotto Number Combinations To Avoid

How does the coin toss analogy impact on improving our chances in lotto? Just as we wouldn't expect a run of heads to continue, there are certain combinations we can assume won't occur in the near future. Here are some examples:

  • Consecutive numbers such as 1,2,3,4,5,6
  • Multiples like 4,8,12,16,18
  • Too many odd or even results

You can probably compile your own list of unlikely outcomes. The paradox tells us these combinations will occur as some point, but our life experience tells us we might wait a long time.

Using An Excel Spreadsheet And VBA To Produce Lotto Numbers

A computer program such as Excel and VBA can shortcut the process of filtering out unlikely combinations and producing numbers more likely to produce a result.

First, you'll need to know how to create random numbers. VBA make this easy with the following code:

x =clng( 39 * Rnd() + 1)

The code creates a random number between 1 and 40, but we'll need a little more code to create a list of 6 numbers. For example, we need to make sure each number is unique. We'll add the number to a dictionary object only if it meets our criteria.

define variables and dictionary object

Dim x, y As Long

Dim addno As Boolean

Dim dict

Set dict = CreateObject("Scripting.Dictionary")


create our 6 numbers

For y = 1 To 6

addno = False


x = 39 * Rnd() + 1

only add the number if it doesn't exist already in the dictionary object

If Not dict.Exists(CLng(x)) Then addno = True

program other filters in here

if the boolean addno is true, then we add the number to the list, exit the loop and 'find the next number

If addno Then

dict.add y, CLng(x)

Exit Do

End If



print the output

For y = 1 To dict.Count

Debug.Print dict(y);


The code produces a series of 6 random unique numbers between 1 and 40. If we need more lines of numbers, we simply repeat the process.

Using VBA To Create Your Own Filtered Lotto Numbers

Now you can apply your own rules to filter out unwanted combinations. For example, if you didn't want a "1" in the list, just apply the following:

if clng(x)=1 then addno=false

The above line of code asks the program to find another random number to satisfy the filter.


This article has explored the connection between random lotto draws and the possibility of filtering out unlikely number combinations using Excel and VBA. The types of filters and unwanted combinations are endless and limited only by your imagination.

Source by A. Lewis Gibson


Your email address will not be published.


Currently you have JavaScript disabled. In order to post comments, please make sure JavaScript and Cookies are enabled, and reload the page. Click here for instructions on how to enable JavaScript in your browser.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.