login about faq

To prove you're not a spammer, email newuser.lgqa@gmail.com with the subject "Account Request" to request an account.


How fast can your Mac/PC run this Excel Lotto Macro? | This Excel Lotto Macro wheels ALL 13,983,816 Lotto Combinations of 06 out of 49, then displays how long it took.

Please specify: Mac or PC, Processor Type & Speed, Amount of RAM, and Version of Excel.

alt text alt text I shaved 58 seconds with Excel 2010?!

My HP TabletPC, AMD Turion X2 Ultra Dual-Core Mobile ZM-80, 2100 Mhz, 3GB RAM, Excel 2007

It would be appreciated if Owners of Powerful Systems would kindly run the Excel Lotto Macro. I'm also curious how fast Mac Pros perform in comparison to PCs. Intel -vs- AMD? Thank you in advance.

13,983,816 Lotto Combinations in 2751 Seconds. 30499.053 cells per second. 83,902,896 cells total.


Sub fill()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim x As Integer
Dim n As Long
Dim r As Integer
Dim o As Integer
Dim timebegin
Dim timeend
Dim a As String 'new
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
timebegin = Now()
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Executing..."
a = ActiveSheet.Name 'new
n = 0
r = 0
o = 0
For i = 1 To 49
For j = i + 1 To 49
For k = j + 1 To 49
For l = k + 1 To 49
For m = l + 1 To 49
For x = m + 1 To 49
n = n + 1
With Worksheets(a) 'change
.Cells(n, r + 1).Value = i
.Cells(n, r + 2).Value = j
.Cells(n, r + 3).Value = k
.Cells(n, r + 4).Value = l
.Cells(n, r + 5).Value = m
.Cells(n, r + 6).Value = x
End With
If (n = 65535) Then
n = 0
r = r + 7
o = o + 1
If r >= 251 Then 'new
Sheets.Add after:=Worksheets(Worksheets.Count) 'new
Worksheets(Worksheets.Count).Activate 'new
a = ActiveSheet.Name 'new
r = 0 'new
End If 'new

'Application.StatusBar = "Executing..." & Format(65535 * o / 962598 * 100, "00") & "% done"
End If
Next x
Next m
Next l
Next k
Next j
Next i
timeend = Now()
With Application
.StatusBar = False
.DisplayStatusBar = oldStatusBar
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
MsgBox "Done in " & DateDiff("s", timebegin, timeend) & " seconds.", vbInformation
End Sub

asked Jul 10 '10 at 17:49

r0bErT4u's gravatar image

r0bErT4u
31.0k515672938

closed Jul 17 '10 at 20:16

Appears that ALL Apple Macs are unable to numbercrunch!?!

ALL the Apple Fanboys' Macintoshes are used - FOR WHAT?!?

(Jul 11 '10 at 07:53) r0bErT4u r0bErT4u's gravatar image

4-time Texas lotto winner rich with money, mystery

BISHOP, Texas – The odds that Joan Ginther would hit four Texas Lottery jackpots for a combined nearly $21 million are astronomical. Mathematicians say the chances are as slim as 1 in 18 septillion — that's 18 and 24 zeros.

(Jul 13 '10 at 20:37) r0bErT4u r0bErT4u's gravatar image
1

Also, Excel 2010 will only use two cores (but only for certain things):

Multi-Core Processing Additional investments were made to take advantage of multi-core processors and increase performance for routine tasks. Starting in Excel 2010, the following features use multi-core processors: saving a file, opening a file, refreshing a PivotTable (for external data sources, except OLAP and SharePoint), sorting a cell table, sorting a PivotTable, and auto-sizing a column.

For operations that involve reading and loading or writing data, such as opening a file, saving a file or refreshing data, splitting the operation into two processes increases performance speed. The first process gets the data, and the second process loads the data into the appropriate structure in memory or writes the data to a file. In this way, as soon as the first process beings reading a portion of data, the second process can immediately start loading or writing that data, while the first process continues to read the next portion of data. Previously, the first process had to finish reading all the data in a certain section before the second process could load that section of the data into memory or write the data to a file.

http://msdn.microsoft.com/en-us/library/ff700514.aspx

(Jul 14 '10 at 19:47) r0bErT4u r0bErT4u's gravatar image

The question has been closed for the following reason "Other" by r0bErT4u Jul 17 '10 at 20:16


12next page »

I have an AMD ATHLON X2 DUAL CORE 3.2GHZ, 4GB DDR2 800MHZ RAM. (and was playing music at the same time lol) I got ...

Done in 1455 seconds. woop woop :)

answered Jul 10 '10 at 23:54

Liamalxd's gravatar image

Liamalxd
1464610

WOW! BRAVO!! That's 21.6 minutes faster than my TabletPC!!!

(Jul 10 '10 at 23:59) r0bErT4u r0bErT4u's gravatar image

i'm running windows 7 64-bit btw :)

(Jul 11 '10 at 00:00) Liamalxd Liamalxd's gravatar image

I noticed that NO Mac Pros even attempted to run the Excel Lotto Macro?!?

(Jul 11 '10 at 02:12) r0bErT4u r0bErT4u's gravatar image

Doubt there are really that many Mac Pro owners here on Lockergnome, judging by the average age of the active users. Macro errors out in Excel 2008 on latest version of OS X, by the way. Something to do with VB support. Haven't tried it on the Windows side so I can post results for my iMac i7 with Win7 64bit installed.

(Jul 11 '10 at 02:20) ageekmom ♦ ageekmom's gravatar image

I'd be interested to see how any Mac performs, but these youngsters probably don't have the patience and/or their Macs are choking on the macro =0p...

(Jul 11 '10 at 02:24) r0bErT4u r0bErT4u's gravatar image

Office for Mac is a pain in the arse. I prefer Open Office on the Mac side.

(Jul 11 '10 at 02:26) ageekmom ♦ ageekmom's gravatar image

ageekmom,

Thanks for trying to run the Excel Lotto Macro. I wonder why the Macs are choking?!

(Jul 11 '10 at 14:27) r0bErT4u r0bErT4u's gravatar image

VB support, as I stated.

(Jul 11 '10 at 22:24) ageekmom ♦ ageekmom's gravatar image

Hey, I can't see to be able to work out how to do this. I have absolutely no experience with Excel. I have downloaded the document, but can't work out how to run it. I've hit the "Calculate now" button, but nothing seems to happen. Please help. :)

(Sep 24 '10 at 06:18) Blind Fury Blind%20Fury's gravatar image
showing 5 of 9 show all

I have an AMD Phenom II X4 quad core 955 3.2GHz, 4GB DDR3 1333MHz RAM. I am running Windows 7 Home Premium 64 bit and Excel 2010 beta. (and was playing music at the same time) I got 1358 seconds. alt text

answered Jul 11 '10 at 11:09

quinny707's gravatar image

quinny707
1.5k102342

SWEET! Thank you!! Congrats on the Fastest Time!!!

(Jul 11 '10 at 11:54) r0bErT4u r0bErT4u's gravatar image

Thanks! (CHARACTERS)

(Jul 12 '10 at 13:24) quinny707 quinny707's gravatar image
  • AMD Phenom II X4 920 at 3.0GHz, 8GB of DDR2 1066.
  • Windows 7 Ultimate 64bit, Excel 2010 (final version) 64bit.

Result: 1392 seconds

I think I can beat quinny707's time with a tweak or two...

answered Jul 12 '10 at 00:51

Leapo's gravatar image

Leapo
2.2k92246

Nice! No need!! 34 seconds difference!!!

(Jul 12 '10 at 00:59) r0bErT4u r0bErT4u's gravatar image
1

Increased my overclock from 3.0GHz to 3.22GHz.

Result: 1208 seconds

I'd like to add that this macro is not optimized for multiple cores (it's only using 25% of my quad core). It could potentially run 4 times faster on these quad core processors if reworked.

Screenshot: http://a.imageshack.us/img697/1617/96506288.png

(Jul 14 '10 at 02:44) Leapo Leapo's gravatar image

You're the FASTEST!!!

(Jul 14 '10 at 02:47) r0bErT4u r0bErT4u's gravatar image

20 minutes 08 seconds ... AMAZING!!!

I'm asking why Excel is only using 25% CPU. In a prior test, Task Manager showed 2 Cores used out of 8 Cores.

Once we break this barrier ... Vegas better watchout

(Jul 14 '10 at 10:21) r0bErT4u r0bErT4u's gravatar image

Crash

An error happened.

Syntax error

Because of a syntax error.

answered Jul 13 '10 at 20:50

archaeme's gravatar image

archaeme
2.3k334277

edited Jul 13 '10 at 20:50

Err... Kinda slow today, so:

alt text

answered Jul 15 '10 at 14:19

archaeme's gravatar image

archaeme
2.3k334277

Thanks! That's still faster than my rig =0p...

(Jul 15 '10 at 14:27) r0bErT4u r0bErT4u's gravatar image

I so want to try this on my Macbook, but I don't have excel :(

Will it work in OpenOffice/NeoOffice/IBM Symphony?

answered Jul 12 '10 at 13:27

TheFogul's gravatar image

TheFogul
31191520

Not sure. A link to the Excel Lotto Macro has been provided, and I posted the Microsoft Visual Basic code.

ageekmom has issues running the macro?!

So far, no Macs have posted results.

(Jul 12 '10 at 23:15) r0bErT4u r0bErT4u's gravatar image

It wont even run on the mac version of Excel (I got a free trial).

Virtual Basic is a Windows thing I guess. Somebody with Windows installed in Bootcamp would have to test it out that way.

(Jul 13 '10 at 17:51) TheFogul TheFogul's gravatar image

I'm certain it'll run on the Windows side since running Windows via Bootcamp is running the OS & MS Office natively, but I just haven't been sufficiently inspired to log out of everything and boot into Bootcamp just to post results for this question.

(Jul 13 '10 at 18:06) ageekmom ♦ ageekmom's gravatar image

Then get Chris to do it. He has Office on his Mac.

(Jul 15 '10 at 13:50) archaeme archaeme's gravatar image

Fixed it!

alt text

answered Jul 13 '10 at 21:00

r0bErT4u's gravatar image

r0bErT4u
31.0k515672938

How to Enable All Cores:

  • Open Excel Lotto Macro
  • File Tab
  • Excel Options
  • Advanced
  • Formulas
    CHECKED > Enable Multi-Threaded Calculation
    Selected > Use All Processors on this Computer

alt text

answered Jul 14 '10 at 11:31

r0bErT4u's gravatar image

r0bErT4u
31.0k515672938

That's what it was set to by default on my system, and this script still only uses one core.

I believe it's an issue with the way the script is written, Excel can't figure out how to spread it out.

(Jul 14 '10 at 13:56) Leapo Leapo's gravatar image

How about Selecting Manual, then Set it to 4/8 Processors?

(Jul 14 '10 at 14:50) r0bErT4u r0bErT4u's gravatar image

No change, still using only 25% CPU.

(Jul 14 '10 at 21:14) Leapo Leapo's gravatar image

Excel 2010 will only use 2 cores in most instances =0/

(Jul 14 '10 at 21:24) r0bErT4u r0bErT4u's gravatar image

It's not a problem with Excel, it's a problem with the way the calculation is written. It has to run consecutively, meaning it can't be split into multiple threads.

If it were rewritten in such a way that multithreading were possible, we'd see some impressive performance gains.

(Jul 14 '10 at 22:40) Leapo Leapo's gravatar image

Hmm?!? I see. The macro is counting as fast as it can from:

{1} Start 01-02-03-04-05-06 -to- {13,983,816} End 44-45-46-47-48-49

I could rewrite it so that half the macro would count ascending, the other half would count descending, and they'd meet in the middle ...

(Jul 14 '10 at 23:24) r0bErT4u r0bErT4u's gravatar image
showing 5 of 6 show all

i don know the specs, did the test in my friends computer. he has a really fast one and is water cooled.

windows 7

http://img819.imageshack.us/i/92343109.jpg/

answered Jul 14 '10 at 12:15

nomyoh's gravatar image

nomyoh
(suspended)

876 Seconds! GOOD GOD ALL MIGHTY!! Kindly post the specs of your friend's computer!!!

(Jul 14 '10 at 12:34) r0bErT4u r0bErT4u's gravatar image

I'm guessing it's either a mildly overclocked Core i7, or a Phenom II running at close to 4GHz.

(Jul 14 '10 at 14:11) Leapo Leapo's gravatar image

im gonna ask him to send me the screen shot of his specs

(Jul 14 '10 at 15:56) nomyoh nomyoh's gravatar image

i didnt think it would take this long! im running a q6600 overclocked from 2.4 to 3.6GHz 4GB memory windows 7 ultimate, office 2010 pro. My screen res was too high and photobucket has reduced the quality of image so - if you cant see that it reads 1440 seconds

alt text

answered Jul 14 '10 at 15:59

Headwards's gravatar image

Headwards
4.5k7288126

Thanks! That's still better than my TabletPC?! With all the computing power available, I want to crack the lottery!!!

(Jul 14 '10 at 16:10) r0bErT4u r0bErT4u's gravatar image

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported


Tags:

×216
×109
×29
×16
×7
×7
×6
×2
×1

Asked: Jul 10 '10 at 17:49

Seen: 4,015 times

Last updated: Sep 24 '10 at 06:18