Welcome Guest
( Log In | Register )
The time is now 11:14 am
You last visited January 16, 2017, 9:56 am
All times shown are
Eastern Time (GMT-5:00)

VBA Lesson 1

Published:

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

 
Entry #9

Comments

1.
TenajComment by Tenaj - December 20, 2007, 3:59 pm
Thank you Gasmeterguy - It's going to be hard for me - I mistakenly have Microsoft Office 2007 Excel. If I had know it was like it is I would never had upgraded. It's no way near close to the 20 years of Microsoft Office that we know. I mean nothing is the same not even new document and I've been learning it as needed.

But thank you for this - I will print it out. I have a programming background (BASIC) so it shouldn't be that difficult for me - After all VB is BASIC with pictures.
2.
time*treatComment by time*treat - December 21, 2007, 12:05 pm
Heh, I still use 'GOTO' and line numbers, too.
3.
LottoMiningComment by LottoMining - December 21, 2007, 3:44 pm
Thanks GasMeterGuy for making this a reality. I have 4 pages written (thus far) of a 1st draft primer on the Access Database (tables, queries and some code) that I should be able to get posted tomorrow.

Below is another Excel VBA building block to add to your useful building blocks. I've re-used the construct inside the For ... Next loop across
a few files over the years. I apologize if the HTML formatting of this window forced a line or two to the next line.

Sub LoopThis()
dim i as integer, Rwct as integer, AColumn as string, BColumn as string, CColumn as string
' the User Defined Function will need to be saved in a module to call the IsOdd Function

RwCt = Sheets("Sheet1").UsedRange.Rows.Count

For i = 2 To RwCt
   AColumn = "A" & cstr(i)
   BColumn = "B" & cstr(i)
   CColumn = "C" & cstr(i)
   Range(BColumn).Formula = "=if(A" & CStr(i) & "=""A"",1,0)"
   Range(CColumn).Value = IsOdd(Range(AColumn).Value)
Next

End Sub

Function IsOdd(intX As Integer) as integer
If Len(intX) = 1 Then
   If intX = 1 Or intX = 3 Or intX = 5 Or intX = 7 Or intX = 9 Then
      IsOdd = 1
   else
      IsOdd =0
   End If
ElseIf Len(intX) = 2 Then
   If Right(intX, Len(intX) - 1) = 1 Or Right(intX, Len(intX) - 1) = 3 Or Right(intX, Len(intX) - 1) = 5 Or _
      Right(intX, Len(intX) - 1) = 7 Or Right(intX, Len(intX) - 1) = 9 Then
      IsOdd = 1
   else
      IsOdd =0
   End If
End If
End Function

You must be a Lottery Post member to post comments to a Blog.

Register for a FREE membership, or if you're already a member please Log In.