VBA:  Lesson 2

This time we are going to sum our five numbers, then determine which one is odd and which one is even.  We are going to build on what we have already.

So just how do we go about summing five numbers?  First we need a variable to hold the answer.  That variable will be called SUM1.  This variable will be a whole number so our variable will be an integer.

Open your code by pressing alt + f11.  If you don’t see your code, you must call it up.  Click on VIEW at the top of your screen.  Click on PROJECT EXPLORER.  Click on MODULES.  Click on VIEW again.  Click on CODE.  If your screen is still blank, follow the step again but click on SHEET1 instead of MODULDES. 

We must put the variable into the DECLARATIONS part of our program.  So insert this line anywhere in the Declarations section of your code.

 DIM SUM1 AS INTEGER.

Now that we have our variable defined, we can do something with it.  Right after line 70 will be where we begin this arduous task.  To keep things separate so we can more easily find them later if we have a need to, let’s put a demarcation line across the screen. 

We will use “remarks” to do this.  A remark is just a sentence letting us know what the code is doing at that point.  We use the apostrophe  ( ’) to insert a remark.  So the line will look something like this”

‘****************************************************

You can copy and paste this line into your program.  The computer ignores everything after the apostrophe there is no need to fear.  You can put anything after the remark and it will not affect your program.

Below the remark line we begin summing our 5 numbers.  The code is below.

For X = 1 to 5

SUM1 = SUM1 + A(5)

Next

This is called a for-next loop.  There are other loops but I like the for-next loop because I am in total control of how many times the program will loop.  In the above line, I am telling the computer to loop 5 times beginning with 1.  During each loop the computer will take the variable SUM1 and add A(1) to it.  Then on the second loop it will take SUM1 and add A(2) to it.  And so on until all five elements of the A(X) have been added to SUM1.

Now that I have summed all five numbers from my number set, I have to determine if the sum is odd or even.  While there are several ways to do this I prefer the simple one.  I will first divide SUM1 by 2.  If the sum is even, dividing by 2 will result in a whole number, one with no decimal places.  If the sum is odd, dividing by 2 will result in a number with decimal places.  Using this fact, I can then determine, using BASIC commands, whether it is odd or even.

First do the dividing by 2.  Here is the code

J = SUM1 / 2

Simple, right.  “J” will now be a number with decimal places or a number without decimal places.  Our job now is to determine which.

BASIC has an operator called ABS.  ABS tells the computer to return a value with decimal places as a value without decimal places.  In order words, it will round up or down your number, in this case J.  In VBA this command (ABS) is INT.

Here is the code.

J = SUM1 / 2

I = Int(J)

Now I (the letter, not me) will either be the same as J or, should J have decimal places, just J rounded up or down.  Let’s compare the two.  If J  =  I then our answer will be even.  If J <> (does not) = I then our answer will be odd.  Here is what we are striving to accomplish.

If J <> I Then ODD1 = ODD1 + 1  ‘if they are not equal, the sum is odd

If J = I Then EVEN1 = EVEN1 + 1 ‘if they are equal, the sum is even

Do you see a potential problem in the lines above?  I do.  While the computer might makes sense of these lines and do precisely what we want, there might come a time when we have forgotten what it was we were trying to do.  If we forget, and believe me you will forget, especially when your code gets long and complicated, then all sorts of bad things will happen.  What we should do is make the variable “even” distinctive, and also the variable “odd”.  So put the number 1 after one of them  like I did above.

In the code above, we have introduced two new variables, even and odd.  These two variables should be included in the DECLARATIONS section of your program.  You should know how to do this by now so, stop, go to the top of your code and put them in.  I will leave it to you to determine if they are integers or something else.  Remember, integers are whole numbers.  Also, while you are up there, add the variables “J” and “I”.  Your program will not work without them.  You will get a “COMPLE ERROR”  if they are not declared.

Having gone this far is good, very good, but what value is there if we don’t know or can not see what we have computed?  Not much.  So we have to put our results somewhere on our Excel spreadsheet so we can see how many even and how many odd number sets (some people like to say “lines”) we have.

In column 1 of your Excel spreadsheet and on row 4, type the word “even”.  Drop down one row and type “odd”.  We will be putting the results in the cell just to the right of those words.  After our program has run, we will know just how many odd and even we have.  The results should be about half of the total 575,757 or 287,878 give or take one.

OK, so we now have even1 and odd1 at our disposal.  Where did I say we were going to put them?  Hold on while I check my Excel spreadsheet.  Oh, yes, column 1, row 4 and 5

Even1 = cells(4,1)

Odd1 = cells(5,1)

Whoops!  I made a mistake.  If I keep the two lines above in my code, what I will be doing is setting even1 equal to whatever value there is in cell(row 4, column 1).  Right now that value is zero.  That’s not what I want.  So……..

Cells(4,1) = even1

Cells(5,1) = odd1

That’s  better.  It should work now.

The program will be getting a bit long as we progress so I hesitate to add the complete program after each and every lesson.  This will be the last time I will be adding the complete program.  If in the future you get confused, PM me and I will try to put you back on the right track or I just might PM you with the corrected program.  Who knows?

There are some changes I have not discussed in the program.  See if you can find them.  Anyway, if you did save the previous program and did some fooling around with it, delete it and put the one below in its place.

Question:  Where did the line “GoTo 45” get moved from and where is it now?  Why?

Question:  Why is the variable J declared as Single and not Integer?

 Option Explicit

 Dim SUM1 As Integer

 Dim EVEN1 As Long

 Dim ODD1 As Long

 Dim VAR1 As Integer

 Dim X As Integer

 Dim J As Single

 Dim I As Integer

 Dim count1 As Long

 Dim A(5) ' THIS WILL HOLD THE NUMBER SET UNDER CONSIDEATION

 Dim B(5) ' THIS WILL HOLD SOMETHING ELSE LATER

Public Sub main()

A(1) = 1

A(2) = 2

A(3) = 3

A(4) = 4

A(5) = 4

45

A(5) = A(5) + 1

If A(5) > 39 Then GoTo 50

GoTo 70

50

A(4) = A(4) + 1

If A(4) > 38 Then GoTo 55

A(5) = A(4) + 1

GoTo 70

55

A(3) = A(3) + 1

If A(3) > 37 Then GoTo 60

A(4) = A(3) + 1

A(5) = A(4) + 1

GoTo 70

60

A(2) = A(2) + 1

If A(2) > 5 Then GoTo 65

A(3) = A(2) + 1

A(4) = A(3) + 1

A(5) = A(4) + 1

GoTo 70

65

A(1) = A(1) + 1

If A(1) > 35 Then GoTo 900

A(2) = A(1) + 1

A(3) = A(2) + 1

A(4) = A(3) + 1

A(5) = A(4) + 1

GoTo 70

70

Cells(2, 2) = A(1)

Cells(2, 3) = A(2)

Cells(2, 4) = A(3)

Cells(2, 5) = A(4)

Cells(2, 6) = A(5)

80

count1 = count1 + 1

Cells(2, 7) = count1

'************************************************************

' SUM THE FIVE NUMBERS

SUM1 = 0 ' SET THE VARIABLE TO ZERO.

For X = 1 To 5

SUM1 = SUM1 + A(X)

Next

Cells(2, 9) = SUM1

J = SUM1 / 2

I = Int(J)

Cells(2, 10) = J

Cells(3, 10) = I

If J <> I Then ODD1 = ODD1 + 1

If J = I Then EVEN1 = EVEN1 + 1

Cells(4, 2) = EVEN1

Cells(5, 2) = ODD1

GoTo 45

900

End

End Sub

In running this program to find all the bugs and get rid of them, I discovered something I think is illogical.  There were 171 more odd sums than even sums.  This could not be.  Logic tells me there should be an equal number of odd sums and even sums.

 Try as I might, I can find no bug in the program.  I have posted a thread regarding this discrepancy and hope to get an answer.  Until then, I am assuming there are more odd sums than even sums in a 5/39 game.  Go figure.

Later, guys and gals.