VBA Lesson 1

To avoid confusion in the future, I suggest you close all Excel windows except for the one you will be using for this instruction.  The reason will become evident as we progress.  Also, I suggest you print out this lesson and read it a couple of times before trying anything on your own.  This will help you to understand what you are doing

First open Excel.  I am assuming you are somewhat familiar with the layout so I will not be discussing it here.  Now to get directly to VBA.

To get to programming mode press “alt+f11”

 A whole new window has opened up.  This is where you write your code.

 Now, using your cursor, go to the top of the page.  Click “view” then click “code”  Please do not minimize your screen right now.  All we want to see now is the page that has “Declarations” on the right hand side.  Click on “Declarations”.  Nothing should happen.  Declarations are just one part, the first part on your journey.  Here is where we list all the variables, constants, and other nefarious items you will learn about.  Type or copy and paste the sentence below onto your VBA page.

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

What have we done?  You may well ask.  We have created an array with 5 variables.  Now think of a variable as a dinner plate, an empty dinner plate.  We have created 5 of them, 5 individual dinner plates.  Right now there is nothing in those dinner plates but that will change in a hurry.  Now copy and paste the sentence below onto your VBA page.

Public Sub main()

Wow.  A lot just happened.  A line was drawn across the screen and the words End Sub were added.  That will happen each time we add a sub-procedure to our program.  Don’t worry.  VBA takes care of the housework.

Now we have a Declarations section and a sub procedure section.  I called the first sub procedure “Main” for this is where all the work will be done.  Other sub procedures will contain information to be used by our Main procedure and we will be adding them when the time comes.

The first step we need to take is write a program that will go thru all the possible combinations in the 5/39 game and have those numbers show up on our Excel page.  Here is what we are going to do.  We are going to initialize the A(X) array.  Copy and paste the next few lines onto your VBA page in the sub section “Main”

A(1) = 1

A(2) = 2

A(3) = 3

A(4) = 4

A(5) = 4

I say copy and paste but you should type everything onto your VBA page.  Doing the work trains your fingers to do some of the thinking for you.  But copying and pasting is much easier and faster.  Do what you want.

Notice the A(5) is equal to 4 as is the A(4).  What is up with that?  Well, our very next line will increase the value of A(5) to 5 and put us right where we want to be.  Otherwise we would be wasting computer time considering two 4”s in our number line.  Here are the next lines.  Now these you need to copy and paste.  Paste them just below the “A (5) = 4” line.  You might care to leave a space to make reading the lines easier.

45

A(5) = A(5) + 1

If A (5) > 39 Then GoTo 50

50

A(4) = A(4) + 1

If A(4) > 38 Then GoTo 55

55

A(3) = A(3) + 1

If A(3) > 37 Then GoTo 60

60

A(2) = A(2) + 1

If A(2) > 36 Then GoTo 65

65

A(1) = A(1) + 1

If A(1) > 35 Then GoTo 900

Note the use of line numbers.  This is a hold over from the days of BASIC, QUICKBASIC, GEE-WHIZZ BASIC and all the other BASIC’s out there.  I still use them because they make sense to me.  I understand line numbers better than what passes for line numbers today.  And I use the GOTO statement as well.  Good programmers never use the GOTO statement.  I guess that means I am a poor programmer.  So sue me.

There is one aspect of programming that you should always have.  That is the END word.  So copy and paste the next sentence at the bottom of your MAIN sub procedure but in front of the “End Sub” line

900

End

This tells the computer you have reached the end of the program and control should return to the operator.  Without it, you might find yourself waiting for a program to end when it will never end.  Then you have to re-boot, a time wasting exercise if every there was one.  So always have an END to your program.

We can run the program at this point but you won’t see anything.  Why?  Because we have yet to tell the computer where to put the information so we can see it.  Let’s do that now.

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)

The above lines will put values onto our Excel spreadsheet.  Don’t worry if you don’t understand the “Cells(x,y)” concept.  That will come later.

 So how do we run our program.  Click on TOOLS>MACRO>MACRO>RUN

What did you see?  On the second row, in the second columns you see 2 – 3 – 4 – 5- 5.  This is a far cry from going thru all the possible combinations.  The reason.  Our program did not function correctly and here’s why.  The computer reads the first line, does something, then reads the next line.  Unless we send the program off to some other line the computer will blindly drop down to the next line, do something, and drop down to the next line, do something, and so on.  If you study the program beginning at line 45 and then go down the lines, you will see why the computer dropped all the way down to line 900 and End.

So how do we fix this.  Add the following line: GoTo 75 but we have to add it in just the right place.  So the first few lines should read…..

45

A(5) = A(5) + 1

If A(5) > 39 Then GoTo 50

GoTo 75

Keeping this in mind, below is the corrected version of the program.  I suggest you use your finger and trace the operation down each line.  Do this until you understand what is happening.  A lot of bugs are created because we think we did one thing when the computer sees something else and does something else.  Being able to find typing or logical errors is necessary when programming.  Logical errors are difficult to find because even though our eyes see “Cells” and our brain reads “Cells”, we may have spelled  “Cell”.  To a computer the difference is between success and failure

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) > 36 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)

GoTo 45

Note the very last line, the “GoTo 45” after cells.  This is to return the program to the beginning.  If we did not have this line the computer would drop down to the next line, which is “END”

You can run this program right now.  I did.  My computer took 35 minutes to run and that was just to go from 1-2-3-4-5 to 35-36-37-38-39 and all points in between.  My computer runs at 2.8 GHz.  What this means is your program will take a minimum of 35 minutes to run.  If your computer takes longer, you have a slower computer.

As we add more things that we want to computer to do, the time will get longer and longer.  Oh, well, it might translate into money if we do this right.

Now for a word about Cells.  We need to have place to put our results.  We want to see the results, right?  Right!  So we put the information in cells on the Excel spreadsheet.  But if you have just a beginner’s knowledge of Excel, you see numbers running down the left side of the spreadsheet and letters running across the top.  The CELLS function takes the arguments row and column.  Look at “Cells(2,6).  What we are telling the computer is “Look in row 2, column 6 and put the information in that particular cell.  We can also get information from a cell.  Look at the format below.

Cells(2, 6) = A(5)  this function will place the value of A(5) into the cell at row 2, column 6.

A(5) = Cells(2, 6)  this function will take the value out of the cell at row 2, column 6 and make A(5) that value.

There will be times when you will need to know what number represents the column.  Right now, there are letters representing the columns.  There is a way to change the letters to numbers.  Click on “TOOLS>OPTION”.  A dialog box will open.  Click on the “GENERAL” tab.  In the upper left hand corner there is a check box. “R1C1 REFERENCE STYLE”.  Click on this box then click “OK”.  Now look at your Excel spreadsheet.  The letters, which once ran across the top of your spreadsheet, have changed into numbers.  Now finding the proper cell to place any future data will be easier to find.

To escape from a program that might be taking longer than you wish, just press the “ESC” key.  A window will pop up asking if you wish to end the program or continue.  Click the appropriate button.

Have fun with this.  I am sure you will encounter some programming problems.  If you get it right the first time, then you should consider a career in programming.  This first thing to check for is spelling.  Make sure all the words are typed correctly. 

In lesson two, we will sum five numbers and determine if the sum is odd or even.  Below is the complete program.

Option Explicit

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

 Dim B(5)

 ----------------------------------------------------------------------------

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) > 36 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)

GoTo 45

900

End

End Sub