Lottery Number Picker

Topic

New Reply

Attached is NumberPicker.xlsm, a one-sheet workbook that lets users pick numbers for the Mega Millions and Powerball lotteries. NumberPicker picks five sets of numbers, then the user transfers them manually to a playslip.

NumberPicker works fine as far as it goes, but it is somewhat inelegant. Two improvements would polish things up, and I’m looking for help in making them:

[*]First, when presented to the user for transcribing, the five chosen numbers should be sorted from low to high. Right now, they appear in random order. (The sixth number, Mega Ball or Powerball, stands alone, and is not sorted with the others.)
[*]
[*]Second, there should be no duplicates among the five chosen numbers. Right now, an Alternate number is provided; when he fills in the playslip, the user can substitute it for the first duplicate if there is one. If there are triplicates or more, the user just picks a new set of numbers.

I have a strategy in mind for implementing the improvements, but there may be better ways to do it. My Excel and VBA skills aren’t up to implementing even my own strategy, which is:

[*]Bear in mind that the Mega Millions needs five numbers from 1-74, and a separate number from 1-15. (Powerball needs five numbers from 1-69 and one from 1-26.)
[*]Get rid of the Alternate numbers, and remove the formulas from the areas under Playslip Games A through E.
[*]Make separate command buttons for Mega Millions and Powerball.
[*]Click the Mega Millions command button to recalculate and start the process below
[*]In a remote corner of the worksheet, do the following for Mega Millions. Powerball would be similar…
[*]Use RANDBETWEEN to put 5 Mega Millions numbers, plus a Mega Ball, in a range somewhere out of sight. Call it MegRandom.
[*]Copy MegRandom to the Clipboard
[*]Using Paste Values, put MegRandom into a range of cells somewhere out of sight. Call it MegValues. (The pasting will cause the workbook to recalculate, putting different numbers in MegRandom).
[*]Sort MegValues from low to high.
[*]Look for duplicates in MegValues. If you find any, go back to Step 7.
[*]Once MegValues has been sorted and there are no duplicates, copy it and paste it into the range under Playslip Game A in the main area of the sheet.
[*]Repeat the above four times, pasting into the ranges under Playslip Games B, C, D, and E.
[*]Something similar would happen when the Powerball command button is clicked, except RANDBETWEEN would use the Powerball ranges of acceptable numbers, and the pasting would be into Playslip Games under the Powerball Numbers.

Does any of this make sense?

Reply | Quote

Replies

I know this is no fun, but you could just buy a random pick. Same odds.

cheers, Paul

Reply | Quote

NumberPicker works fine as far as it goes…

You are saying, obliquely, that it is relevant only to the United States, perhaps? 🙁

BATcher

Plethora means a lot to me.

Reply | Quote

PaulT – Yes, but some people won’t buy the Quick Picks because they suspect they are rigged. Others want to pick numbers in advance, without buying tickets just to get the numbers. (That’s me. I need to pick in advance 400 sets of numbers to use when needed HERE. It gets mighty tiresome trying to come up with new ones.)

Also, when NumberPicker is in its best form, I’m planning to put a download link to it HERE.

BATcher – Well, it’s relevant anywhere they have Mega Millions and Powerball, or, mutatis mutandis, to anywhere that similar groups of numbers are used.

Reply | Quote

Maybe you should be reading Nate Silver and Ben Goldacre about picking lottery numbers….:D

Eliminate spare time: start programming PowerShell

Reply | Quote

I’m sure they are very bright guys, but have they ever won anything in a lottery? The USS Rankin Lottery Pool has won money in over 1,400 consecutive drawings since 2007. 😎

But, like many lottery players, we are in it mostly for the fun.

Reply | Quote

1111111, 1111112, 1111113, etc… Same odds as randomly generated numbers.

Reply | Quote

Of course, if all you consider is probability theory. But read this from our Rules and Procedures:

… while it is interesting to consider mathematical chances of winning, mathematics says nothing at all about which ticket, person, or pool might win a Jackpot or any other prize. It calculates abstract chances without any consideration of luck, natural or supernatural activities favoring a given person or ticket, the possibility of being able to bring success by visualizing it, willing it, praying for it, cheating, or any similar thing.

Reply | Quote

Of course, if all you consider is probability theory. But read this from our Rules and Procedures:

I don’t know what “club” your rules are from, but if you enjoy coming up with this sort of thing then fine. And I’ll just leave it at that.

Reply | Quote

BATcher – I like two truisms:

1) The guy who doesn’t buy a lottery ticket has an infinitely smaller chance of winning than the guy who buys only one.

2) Q. What’s the difference between a man who buys a lottery ticket and a man who has an argument with his wife?

A. The guy with the lottery ticket has a chance to win. :rolleyes:

All those things being said, is there anybody out there who can help with Excel?

Reply | Quote

Of course, if all you consider is probability theory. But read this from our Rules and Procedures:

“without any consideration of luck, natural or supernatural activities favoring a given person or ticket, the possibility of being able to bring success by visualizing it, willing it, praying for it, cheating, or any similar thing.”

Well that would be because none of those things has anything to do with their chances of winning.

Reply | Quote

”without any consideration of luck, natural or supernatural activities favoring a given person or ticket, the possibility of being able to bring success by visualizing it, willing it, praying for it, cheating, or any similar thing.”

Well that would be because none of those things has anything to do with their chances of winning.

With all respect, I don’t think there has been much research on the “none of those things” stuff. Those who win aren’t about to reveal their secrets, either. And when you think about it, it’s not beyond them to suppress the research, or to pay the researchers to keep quiet, or even to promote the idea that nothing but “chance” is involved.

And there’s no denying that the USS Rankin Lottery Pool has won money in over 1,400 consecutive Powerball and Mega Millions lotteries.

We DO have one example of using special means to win a jackpot. You can read all about it HERE. 😎

Reply | Quote

I rather like the truism that the chances of a person winning the lottery are only minimally greater if they had actually bought a ticket than if they hadn’t.

BATcher

Plethora means a lot to me.

Reply | Quote

I’d do the calculation in a macro as this makes it easy to check for duplicate numbers – generate the next number and compare it with all previous, if it matches, regenerate. You could set rules on the sheet that the macro uses to calculate the numbers – in case they change the numbers used.

I’d scratch something up for you if I had Excel…

cheers, Paul

Reply | Quote

Good idea! The lotteries DO change their formats from time to time. So far, the changes have all been in the ranges of numbers used for the fields of five numbers and those for the special balls.

I guess I need four constants in the macros: Highest number used for Powerball “field”, highest number used for Powerball “special ball”, highest number used for Mega Millions “field”, and highest number used for Mega Millions “special ball”.

The lowest number for all of them has always been 1, and it’s hard to imagine that that will ever change.

Reply | Quote

it’s hard to imagine that that will ever change

Fateful words, as any programmer knows !

Reply | Quote

Fateful words, as any programmer knows !

I stand corrected! :huh:

Reply | Quote

Hi Lou,

The following revised spreadsheet has code that generates unique picks negating the need for alternate numbers and also sorts each game in order. I have create two buttons in which each runs the code for the specific game and removed all formulas. The code was modified from here to meet the specifications of your project.

HTH,
Maud

43405-Lou

Reply | Quote

Very nice Maude. All Lou has to do is set values in the sheet for the numbers used by the games and pick them up in the macro.

cheers, Paul

Reply | Quote

Great job, Maude! It’s exactly what I need. And I’m good even if the numbers in the layout change.

What do we owe you if we win a Jackpot? Oh, wait.. that’s probably best left up to the generosity of our players… 😉

Reply | Quote

Maudibe: Your workbook has a second sheet consisting of a column of numbers. Sorting it shows that they are all the integers from 1-74, except that 36 is missing. I assume that this was some sort of test page, and I’m a bit worried by the missing number. Wassup??

Also, I’ve protected the main sheet by unlocking all the cells with numbers in them, then using Review > Protect Sheet with all boxes unchecked. Interestingly, when I protected the page before unlocking those cells, clicking a button resulted in an error message.

I’m wondering if there’s a way to protect the macros, so nobody can meddle with them. Best would be if people can see them (so they can know where their numbers come from) but not change them.

Reply | Quote

See this post.
http://windowssecrets.com/forums/showthread//173865-Prevent-Transfer-of-Excel-Workbook?highlight=protect

cheers, Paul

Reply | Quote

Nice pickup Lou. I,LL make the adjustments tonight.

Reply | Quote

Lou,

You do not have to leave any of the cells unlocked. I have added the following code line to the ThisWorkbook- Workbook_Open event routine that will allow VBA to work on locked cells.

Code:

Private Sub Workbook_Open()
Worksheets(“Sheet1″).Protect Password:=”LSander5”, userinterfaceonly:=True
End Sub

You’ll notice that the attached revised workbook has all cells locked, yet the code still works on them. I am sure you have gathered that the code to unlock the sheet as well as the VBA project is “LSander5” without the quotes.

You were right that the second sheet was a test page and I can assure you that all of the numbers are being used including 36. I have deleted the sheet.

Lastly, you wanted to enable the users from seeing the code but not able to manipulate it. This is best done by locking down the VB Editor and creating a third button that displays the code in a form.

HTH,
Maud

Reply | Quote

Lastly, you wanted to enable the users from seeing the code but not able to manipulate it. This is best done by locking down the VB Editor and creating a third button that displays the code in a form.

I’ve tried and failed to find the way to protect/lock the VB editor. Also, it may be easy, but I don’t know how to make that button display the code in a form.

Reply | Quote

Got it! Now, how do I unlock the VBA so I can change the password to something more familiar to me? (My dog’s name, probably)

If it’s easier, you can just change it to Dexter

Reply | Quote

Lou,

UNLOCK THE PROJECT
Press Alt-F11 then double click the VBA Project in the Project window (left top); a password box will open. Enter “LSander5” without the quotes > OK. The project will become unlocked

CHANGE THE WORKSHEET PASSWORD
In the project window, expand the Microsoft Excel Objects by clicking the + sign to it left. You will see 2 modules under it: Sheet1 and ThisWorkbook. Double click ThisWorkbook. In the large white code window to the right the Workbook_Open event routine will be visible. Change “LSander5” to the password you want but still keep the quotes.

CHANGE THE VB PROJECT PASSWORD
Click on Tools in the menu bar > VBAProject Properties… > Protection Tab > enter new password twice > OK. Close the VB editor and save. The next time you open the workbook and press Alt-F11, the editor will open with the project locked using your new password.

HTH,
Maud

Reply | Quote

Here’s the ultimate version (so far:)). The instructions are, IMHO, thorough and crystal clear. They even hold a clue to the passwords, for when I inevitably forget them (my dog is Dexter).

There’s one tiny bit of polishing that may or may not be possible:

When you click the View Code button, is there any way to make both code windows open at the top of the code? That might be easier for people who aren’t very familiar with VBA.

Reply | Quote

They even hold a clue to the passwords, for when I inevitably forget them

Why don’t you put the password in your password manager?

cheers, Paul

Reply | Quote

Lou,

You have a trained eye. I added the following code to run when the workbook opens:

Code:

Private Sub UserForm_Activate()
 TextBox1.SetFocus
 TextBox1.CurLine = 0
  TextBox2.SetFocus
 TextBox2.CurLine = 0
End Sub

Now it open with the code at the top.

HTH,
Maud

Reply | Quote

I do know how things should work!

I guess this thing is perfect now, at least until I look at it again in the morning. :p

We’ll postpone the final element until we win some sort of Jackpot: A button that lets your printer fill in the playslips. (It’s probably a little much to ask it to print the colored playslips themselves, and it might not sit too well with the lottery officials.) Also, with 48 different states and 96 slightly different playslips, it might take ol’ Maudibe an entire weekend to come up with the code. He’d probably build in GPS awareness, so the user wouldn’t even have to enter his address.

Don’t hold your breath until we win that Jackpot.

Reply | Quote

I guess this thing is perfect now, at least until I look at it again in the morning. :p

Well, I found three things. 🙁

First, I did some rewording in the instructions and moved the playslip images around. No big deal.

Second, I noticed that, when I used Number Picker, a cursor kept appearing in one of the visible cells. I took that to be the place where the cursor was when I protected the sheet. I fixed it by moving the cursor behind the playslip images, where it can’t be seen. Problem solved.

Third, a mystery: I happened to have Lottery34.xlsm open at the same time as Number Picker. Lottery34.xlsm is so big that recalculating it takes maybe 0.2 seconds, which happens whenever I enter a number into it. No big deal. But when it’s open at the same time as Number Picker, a similar delay happens every time Number Picker makes a calculation. Since there are a lot of calculations in picking a number, a new set of numbers takes about 20 seconds to pick.

I’m guessing that for some reason, recalculating Number Picker causes other open worksheets to recalculate as well. You can see this for yourself by downloading Lottery34Dummy.xlsm (personal info removed) HERE.

It would be nice to know what’s happening here. Maybe it’s easily fixed, maybe not. If it ain’t easy, it ain’t worth fixing.

Reply | Quote

Don’t have one.

Can you suggest one?

Reply | Quote

Don’t have one.

Can you suggest one?

Windows Secrets have KeePass on their top utilities list.
http://windowssecrets.com/best-utilities/windows-secrets-ultimate-utilities-list-2015/

cheers, Paul

Reply | Quote

You’re too funny Lou. If you are looking for a password manager, I use Last Pass. Works perfectly, packed full of features, and totally inexpensive.

Let me know if I can be of anymore help.

Maud

Reply | Quote

Just another reason why I prefer to use VBA over formulas!

Reply | Quote

Trying to develop my understanding:

Lotsa formulas and lotsa macros in Lottery34.xlsm.

Turning off automatic recalculation seems to turn it off for every open workbook(!)

There’s a VBA statement called Option Private Module that might have some sort of place on one or both of these workbooks.

Some sources identify the INDIRECT statement as a culprit in slow recalculation. There are a bunch of those in the Summary tab of Lottery34, and a BIG bunch of them in the WinningNumbers tab. Summary is needed with every drawing, but WinningNumbers is only needed sporadically. Maybe there’s a way to get rid of WinningNumbers until it’s needed. (Maybe cut most of the statements out and park them in a different workbook. When they are needed, paste ’em back in.)

New results: Deleting the WinningNumbers tab markedly speeds up the calculations in NumberPicker.

Reply | Quote

Lou,

You could turn Automatic Calculation off and then just recalculate the portion you want in VBA:

43452-LouCalculate

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote