Using the Debug Window in Excel

[CMF]

Microsoft Excel I have come to recognize as being the most popular program used by those who like to toss the numbers around in Lotto. Personally, I don't use it much as I favor Visual Basic these days but I still use my old Access 2000, especially when I'm just playing around with something, in which case I am most likely to use the debug window.

What some of you may not realize is that Excel uses VBA or Visual Basic for Applications just like Access. Quite often I have seen tedious use of the grid to do something that could be much more elegantly and simply presented using Visual Basic and the debug window.

Here's a simple step by step to get you started. In Excel from the Menu click Tools|Macro|Security. Change your security level to Medium - this means if you create a macro or procedure for a worksheet when opening it you will be presented with a dialog asking whether you wish to enable macros to which you select "yes".

To show the main editor from the Menu select Tools|Macro|Visual Basic Editor
or Macro for a list and then Step In. To show the debug window shorten your main editor to make some room then from the VBE menu View|Immediate Window.

You can create procedures and run them by simply clicking somewhere in the procedure and then run. You can output to the debug window by simply typing in your procedure debug.print "As an example " & myVariable. From the debug window you can call your own or built-in functions or procedures.

Some simple examples - type as shown in the debug window then press return:
?1+1
?13983816/2
? 13983816 Mod 1000

By making this post the opportunity is there for experienced programmers, who use VBA but not the Excel grid, to post some code and if they think it necessary make a reference to this thread for some background how to.

Regards
Colin Fairbrother

 

[GillesD]

Use of Excel

Colin Fairbrother

I use Excel extensively for playing around with lottery numbers, using both regular functions for simple calculations or more sophisticated applications but I will use VBA (macros) for more intensive or specific calculations. And this has paid off fairly well for me.

But the examples you provide are not very incitative to use the Immediate window in VBA. I would suggest you try working with one particular key in Excel, it is called the equal sign (=).

In a cell, just enter =1+1 or =13983816/2, and you will get the result immediately. But it can get even better. I will give you an example.
- In one cell (like A1), enter =COMBIN(49;6) to get the 13,983,816 value. In another cell (like B1) enter the value 2 and in a third one (like C1), enter the formula =A1/B1 to get the result.
- But if you want to divide by any other number (x), just replace the 2 in cell by x and the result appears immediately in C1.
- In your method, you would have to retype the whole entry like ? 13983816/x with the possible chance of making an error.

I know you must know all this but the point I want to make is that, unless you have to loop through a sequence of instructions a certain number of times or perform rather complicated conditions (with the IF ... THEN ... ELSE ... END IF instructions), you can perform rather complex analysis of lottery data with functions integrated into Excel. Otherwise, VBA either in Excel or Access can be used.

 

[CMF]

GillesD

As you say I'm well aware how to use Excel and in particular the = operator; in a matter of fact way I point out that I program using Access, Visual Basic 8 and ASP 3.5 at an advanced level - but you already knew that, didn't you. The point I was making is that you don't have to get involved with all this cell referencing to show how something can be done and for most programmers it's a real turnoff. (I think you know that too!)

As an example I suggest you finish what you started in the Line Elimination Ideas thread where what you gave as an answer to FlexAlong using cell references didn't seem to have much potential for giving the answer in a neat way to me.

As a matter of fact what you wrote prompted me to post a reply in that thread. I gave a figure of 702878 after writing a little procedure which outputted to the debug window. Let's see your procedure (no cell references necessary) and the figure you come up with.

Colin Fairbrother

 

[GillesD]

Different approaches and different answers

Colin Fairbrother

A – Functions / Formulas vs VBA
I think you and I will agree that both Excel or Access are good choices for analyzing data: Access being superior in large database applications with Excel being easier to use with its grid configuration, availability of functions and ease of use and much greater number of personns using it in day-to-day operations with VBA available in both cases.

But in a forum like here, very few people has shown interest in Access, a few are interested in VBA but in ost cases, are willing to use it if somebody else develop the code to obtain the solution to their problem. But most of those here have used Excel with functions or simple formulas and are familiar with its operation. And that's why most of the answers to questions asked are given in such a manner.

B – Request by Flexalong
For this request, I provided an answer using formulas with cell references rather than a macro since I was not exactly sure why he wanted that information and in what format (one number for all possibilities or a single number for each number that could be the product of two other numbers). Yes, my choice might be longer to set up but is more flexible if somebody wants to know how often the third number is the product of the first two, or the fourth number the product of any two of the first three, or …

C – Overall answer to Flexalong request
So finally, I did write a macro providing an overall answer to the question asked by Flexalong. But here, we have a problem: you come up with 702,878 combinations but I get a slightly higher number with 708,856 combinations, not a large difference but still one (5,978).

So I reverted back to my approach of searching the answer for individual numbers (in position #3, 4, 5 and 6). The sum of these numbers still gives me 708,856 combinations with 108,210 combination with the third number being the product of the first two numbers, and 220,179 combinations where the sixth number is the product of any 2 of the first 5 numbers.

Using the debug window (or any other means), can you provide me the individual number of combinations for the 4th and 5th number being the product of any two of the preceding ones. This will tell us where the logic is faulty and either you or me can adjust his method of calculation. And my code will be posted here for all to use as usual.

 

[CMF]

GillesD

You're right it is 708,856 - I rechecked and easily found I left out a possibility.

A lot of the things I do in Lotto can only be justified in terms of the time spent by regarding it as an hobby. As such using a modern computer (although my desktop would be considered slow compared to the current crop) and involving random numbers it can be a challenging and stimulating one. If you like just as recreational mathematics has been around for around 3,000 years so recreational programming will be with us forever and a day but you still need a focus which Lotto can provide.

One could not justify the time by the money return which in my case is practically nothing; the people that make the most money in this area do so by bending the truth or outright lying and they mainly get their message across by direct marketing means other than the web.

Using the debug window has a lot of advantages and I'm sure some of the people that use Excel that visit this site will quickly see the advantages of using VBA at least some of the time. You can stop when you like and in VBA even change a variable. The main point is that the code is much easier to follow for oneself or somebody else. My error was picked up straightaway just by re-looking at it.

Looking forward to seeing your procedure without cell references.

Regards
Colin Fairbrother

 

[GillesD]

Code

GillesD

... Looking forward to seeing your procedure without cell references.

Regards
Colin Fairbrother

Going through all 13,983,816 possible combinations with formulas is beyond my knowledge, but looping through them in a VBA procedure is rather easy and fast. So this is the code I used:

Option Explicit
Public A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Public N() As Double

Sub Prod_Comb()
Range("A1").Select
Application.ScreenUpdating = False
ReDim N(6)
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
N(1) = N(1) + 1
Call AllComb
Call IndComb
Next F
Next E
Next D
Next C
Next B
Next A
Range("A1").Value = N(1)
Range("A2").Value = N(2)
Range("A3").Value = N(3)
Range("A4").Value = N(4)
Range("A5").Value = N(5)
Range("A6").Value = N(6)
Application.ScreenUpdating = True
End Sub

Private Sub AllComb()
If C = A * B Or D = A * B Or D = A * C Or D = B * C Or E = A * B Or E = A * C Or _
E = A * D Or E = B * C Or E = B * D Or E = C * D Or F = A * B Or F = A * C Or _
F = A * D Or F = A * E Or F = B * C Or F = B * D Or F = B * E Or F = C * D Or _
F = C * E Or F = D * E Then N(6) = N(6) + 1
End Sub

Private Sub IndComb()
If A * B = C Then N(2) = N(2) + 1
If A * B = D Or A * C = D Or B * C = D Then N(3) = N(3) + 1
If A * B = E Or A * C = E Or A * D = E Or B * C = E Or B * D = E Or C * D = E Then N(4) = N(4) + 1
If A * B = F Or A * C = F Or A * D = F Or A * E = F Or B * C = F Or B * D = F Or _
B * E = F Or C * D = F Or C * E = F Or D * E = F Then N(5) = N(5) + 1
End Sub

I agree, this is not the most effective code with long IF statements and calls to 2 procedures. But since I wanted to get the number of combinations for individual positions (#3, 4, 5 and 6) and also the total number of combinations in one pass, this is what I came up with.

As it is, the output in A1 to A6 is:
- N(1): the overall number of combinations evaluated (always a good thing to run to make sure you have gone through all of them);
- N(2): the number of combinations in position #3;
- N(3): the number of combinations in position #4;
- N(4): the number of combinations in position #5;
- N(5): the number of combinations in position #6;
- N(6): the number of combinations for all positions, although not obtained by adding N(2), N3), N(4) and N(5) but by the AllComb procedure.

But I also disagree with two aspects in your last post:
- first, the arguments you give in support of the debug window could be rewritten in support of Excel functions and formulas by changing just a few words;
- "the code is much easier to follow for oneself or somebody else", understanding the code of somebody else is often not that easy if he produces what is called "spaghetti code" with a lot of GOTO statements or a multitude of procedures. Usually a formula is easier to understand, especiallly if short or it refers to names assigned to specific ranges. But I have one application (not lottery related) with one formula with over 800 characters. It is not easy to understand but it works.

 

[CMF]

GillesD

It's actually quite interesting to see how someone like yourself who is very proficient in Excel goes about tackling a task like this. There is no doubt in my mind that if a few other members of this website post some code here you will easily adapt to a more "VBA" way of approaching it. Here's the code I used - no doubt someone else will have a faster way (but not always as easy to follow).

Sub Product2IntegersNEAnyOtherIntegerInComb()
'Task: For all the 13,983,816 combinations in a Pick 6,
'Pool 49 Lotto game isolate a set where the product of two integers
'equals another in the combination.
'Note: Since maximum product of first two integers must be less than
'49 introduce exit for when this is exceeded.

Dim a, b, c, d, e, f As Integer
Dim g As Long
Dim cnt As Long
Dim comb As String
Dim StartTime As Date
Dim FinishTime As Date
Dim Duration As Variant

StartTime = Time

For a = 1 To 44
For b = a + 1 To 45
If a * b > 49 Then
Exit For
End If
For c = b + 1 To 46
For d = c + 1 To 47
For e = d + 1 To 48
For f = e + 1 To 49
cnt = cnt + 1
comb = cnt & "," & vbTab & Format(a, "00") & vbTab & Format(b, "00") & vbTab & c & vbTab & d & vbTab & e & vbTab & Format(f, "#,###,##0") & vbTab

If a * b = c Or a * b = d Or a * b = e Or a * b = f _
Or a * c = d Or a * c = e Or a * c = f _
Or a * d = e Or a * d = f _
Or a * e = f _
Or b * c = d Or b * c = e Or b * c = f _
Or b * d = e Or b * d = f _
Or b * e = f _
Or c * d = e Or c * d = f _
Or c * e = f _
Or d * e = f Then
g = g + 1
End If

If cnt Mod 1000000 = 0 Then
Debug.Print comb & g
End If
Next
Next
Next
Next
Next
Next

FinishTime = Time
Duration = DateDiff("s", StartTime, FinishTime)
Debug.Print "Number of combs in set: " & g & vbCrLf _
& "Start Time:" & StartTime & vbCrLf _
& "Finish Time:" & FinishTime & vbCrLf _
& "Time taken to run: " & Duration & " secs" & vbCrLf _
& "Index and last combination in set: " & comb

End Sub

Here is what is printed to the debug window: -
1000000, 01 09 14 26 31 37 47397
2000000, 02 04 20 32 39 41 122115
3000000, 02 16 26 42 43 47 324067
4000000, 03 11 13 22 35 45 494157
5000000, 04 11 16 17 18 46 627823
Number of combs in set: 708856
Start Time:3:38:54 PM
Finish Time:3:41:52 PM
Time taken to run: 178 secs
Index and last combination in set: 5753593, 06 08 46 47 48 49

Regards
Colin Fairbrother

 

[GillesD]

Faster and easier - why not

Our approach is basically the same but the means are a little different. Yes, Access is more powerful for very large databases but it lacks the flexibility and ease-of-use of Excel. VBA is available to both programs with minor differences as far as I know (I never really used Access).

Applications in Access are usually outputted to a printer and I can now see why you like the debug statement: this allows you to see the result of the program on screen. Why waste paper for a single result like 708,856. This is the advantage of Excel. You can output the result to a cell (or many if necessary) and save the file with the macro and its results. You can then come back later to modify the macro or eventually improve it depending on your needs.

I did go back to my file to see if I could improve my macro to reduce calculation time and this is the result:

Sub Prod_Comb2()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Double, Time1 As Date, Time2 As Date
Range("B1").Select
Application.ScreenUpdating = False
Time1 = Time
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
If C = A * B Or D = A * B Or D = A * C Or D = B * C Or E = A * B Or E = A * C Or E = A * D Or E = B * C _
Or E = B * D Or E = C * D Or F = A * B Or F = A * C Or F = A * D Or F = A * E Or F = B * C Or F = B * D _
Or F = B * E Or F = C * D Or F = C * E Or F = D * E Then N = N + 1
Next F
Next E
Next D
Next C
Next B
Next A
Time2 = Time
Range("B1").Value = N
Range("B3").Value = Time2 - Time1
Application.ScreenUpdating = True
End Sub

Note: it is best if you apply a second-only format (with one or no decimal) to cell B3.

This gives the number of combination for the conditions specified by Flexalong (708,858) along with the calculation time. On my computer, this macro runs in about 15 sec. while the previous one ran in about 45 sec. So yes, it is faster than yours (although the computer used has a direct impact on this) but I strongly disagree about your comment that "no doubt someone else will have a faster way (but not always as easy to follow)". I believe my macro is as easy (if not easier) to follow than yours.

I also think that you use your output to the debug window for intermediate results is just a safety net to make sure your program is not caught in an endless loop. What is the use the combinations after 1MM, 2MM, etc.? My first macro outputted intermediate data but that gave how many combinations applied to the 3rd, 4th, 5th and 6th number. This could be of some use to somebody.

 

[CMF]

GillesD

Between the code you initially posted and the last code I see big differences which are easily discernible by any reader but practically none compared to what I wrote apart from stripping out the progress reporting, formatting and printing to the debug window.

Colin Fairbrother

 

[GillesD]

Tools are only tools

But then I could answer back:

Between the code you (CMF) initially posted and the first code I posted (GillesD), there are some differences which are easily discernible by any reader but practically none compared to what I wrote apart from formatting and outputing to cells in Excel.

Sorry I took your words (most of them at least) but it applies so well to your post after I had given my code.

The real difference is that the code you posted was not your original one since that code gave a wrong result (702,878 combinations instead of 708,856).

But now we are on the same wavelength and this should close the discussion. Both Access and Excel are good tools for analysis of lottery data. Each of us on this site should use the one he is most familiar with.

Cheer up.

 

[CMF]

GillesD

Since you're not prepared to concede the point about copying my code I need to point out you left something out which saves 13 seconds on my computer.
If A * B > 49 Then
Exit For
End If
Yours runs in 22 seconds; mine in 9 seconds.

When optimizing or generally when writing code I dimension each variable separately ie A As Integer, B as Integer etc.

GillesD, for those whose opinion I value your measure has been revealed by your words; there is no need for me to go into detail.

Colin Fairbrother

 

[GillesD]

Coding for best solution

Since you're not prepared to concede the point about copying my code I need to point out you left something out which saves 13 seconds on my computer.

I have to agree that if I insert a line with the statement IF A * B > 49 THEN EXIT FOR in my macro, it saves a lot of time. It cuts the time to 7 sec. from 15 sec. Thank you for this.

But PLEASE, do not ask me to concede copying your code. I posted my code in this thread first, asking you to review yours since the end result was different than mine. You did this to find out I was right and I posted my code as I said I would.

I agree that my code is not very original (going through all combinations and verifying if it meets certain conditions is rather straight foward) and I do not take credi for it (and why should you). I always posted my code here for all to see and use. Go back a long way for my posts (and I have been here a long time) and you will see I have used the type of coding many, many times before.

So cheer up a bit.

 

[CMF]

GillesD

From a gloating position where you thought the code you posted was faster than mine for naive and erroneous reasons to one where you concede my code is 100% faster is quite cheering.

Thank you for cheering up my day - now you may go and drown your sorrows.

Colin Fairbrother

 

[GillesD]

Misunderstanding

GillesD

From a gloating position where you thought the code you posted was faster than mine for naive and erroneous reasons to one where you concede my code is 100% faster is quite cheering.

Thank you for cheering up my day - now you may go and drown your sorrows.

Colin Fairbrother

I am sorry I misunderstood quite a few of your posts in this thread. I know I am getting old and my view is not as good as before.

But is it not you that:

A - First posted the value 702,878 as the answer to Flexalong's question, only to reconsider and admit that you had made an error and that I had the right answer?

B - Having seen my code, you then posted your own with these 3 lines near the end:
- Start Time:3:38:54 PM
- Finish Time:3:41:52 PM
- Time taken to run: 178 secs
Sorry I must have misread but to me "178 secs" (your quote) is slightly slower than my "15 secs" (my quote). I am sorry I shouldn't have never taken your word for granted and I should have verified it myself. I never ran your macro except for a few minutes ago. Actually it took 79 seconds to output the results to the debug window (in Excel of course).

C - I never conceded than your code is 100% faster than mine. What I said is "I have to agree that if I insert a line with the statement IF A * B > 49 THEN EXIT FOR in my macro, it saves a lot of time. It cuts the time to 7 sec. from 15 sec. Thank you for this." (quote from one of my post). And maybe I should take credit for this as your code took 3 lines and mine only one (this is a joke).

D - Now you are quite proud of saying "Yours runs in 22 seconds; mine in 9 seconds." (again your quote). You must have made tremenduous improvements to your code to go down from 178 secs to 9 secs. But this is still unexplained and I doubt it will unless you recognize another error or maybe a change to a much faster computer.

I will now let this thread rest in peace and let others make their own judgment.

P.S. By the way, actually I am quite cheered up with the changes made by the Canadiens (those from Canada will understand) and things in this matter (not lottery-related) could now only improve. Tough luck for the Leafs and the Senators (at least for this year). GO HABS GO!

 

[CMF]

GillesD

This thread is called, "Using the Debug Window in Excel" started by me to make Excel users aware of how they can run a macro or procedure and output to the debug window progressive values. While the procedure is running it can be paused by pressing Ctrl Break and you can have a look at the values for variables etc. For those that program this is all routine stuff that has to be gone through when debugging before releasing any program.

A google search will find plenty of material on this subject and for those that class themselves as being above power user level in Excel knowing and using VBA is part and parcel of that.

This thread is not about the ego of you, GillesD. Whether it be this forum or others a member only has to write the word "Excel" and you feel compelled to reply even when you don't have much to offer. In particular I was appalled at the answer you gave Flexalong where he wanted to eliminate blocks where any of two integers in that block when multiplied together equaled another. Your example answer was to add the integers not multiply them. Obviously, Flexalong saw some benefit in this and thanked you. I was intrigued and spent no more than a few minutes knocking up a procedure that went about it in the right way.

Regarding your points: -

A I conceded that the result I initially gave was wrong and corrected this before your code was posted.

B The code that I posted gave the correct figure and illustrated printing to the debug window, a requirement that you refused to follow, by giving progressive values and generally showing how to give some meaningful data including the time taken. No attempt was made to optimize the time taken. The code was run in Excel.

C If my code runs in 9 seconds and yours in 22 seconds in Excel then it is more than 100% faster.

D No improvements were necessary. The core of the procedure is in the If block and the faster time is all down to my If a*b>49 block. As I noted the revised code you posted was basically following my layout without some of the progressive printing to the debug window which slows things down and is removed when optimization is required along with fully dimensioning the variables. Your original code in this thread had public variables and called two other procedures.

It would be nice if you could leave the matter as it now stands without another pointless reply.

Perhaps someone else has a question regarding procedures and printing to the debug window. One procedure I have seen crop up repeatedly on various forums is storing all the 13,983,816 blocks in an array. How fast can you do it in?

Colin Fairbrother