Paul The Programmer

You know, how hard can it be?

If I ran a bus fleet (can't imagine why I'd want to but there you go) I'd make sure it was the best damn bus fleet in the world. The bus services where I live are as reliable as Willy Wonkas cuckoo clock, you just don't know what your going to get.

Manifesto for the best bus fleet in the world

In keeping with the general theme of this blog, I though it best to be positive, and offer bus fleet managers a draft manifesto of how a good bus service should be run. Please feel free to comment or add to it as you see fit.

• Keep passengers informed, at all times, where the bus is and when it will arrive
• Make sure the driver is well trained, friendly and helpful
• Have plenty of change, noone likes an inquisition whenever the driver has to change notes
• Ensure passengers are warm and comfortable, especially when waiting for the bus
• Stick to the timetable like glue
• Have plenty of space for bags, baby buggies and wheelchairs
• Monitor when people have to stand, and then putting extra buses on
• Use smaller, cheaper to run buses, to imrove frequency of service
• If a bus is pulled off route, offer passengers an alternative service
• Respond to enquiries like you mean it

How hard can it be?

Hi one and all, happy new 2009!

Did you get a new computer for xmas? Maybe you got MS Office, Excel, Word etc?,

Perhaps your boss at work decided to make you the office Excel expert and you feel that you don't know enough?

Or maybe 2008 didn't work out too well and you want to learn a new skill to shine on your CV and give you a confidence boost?

"..they've a new found confidence in their abilities.."

If so, you've come to the right place (after all that searching of thousands of other internet places, you finally found YOUR place). I want to help people learn and grow. Grow becaue they've learned a useful life skill (VBA), because they've a new found confidence in their abilities and because they want to transform a less than excellent 2008 into a marvellous 2009.

So come on over and jump into the Basic pool, the water's lovely.

Start recognising your worth.

So, its 1982, I'm 12 years old and get my first computer. I'm hooked, like a salmon on the river tweed. Its a ZX81.

If you haven't seen one, then may I suggest you go down to you nearest technology museum and marvel at the sleek, black 8 inch square of plastic, with its flat, white, touch sensitive keyboard. I still have it, in the garage. My kids have no idea that their dad earns every penny he has because of that little box, oh yes and of course Mr Spock (I love science too).

This machine got me started on the road to designing, writing and debugging code. I used to buy computer magazines with REAL program listings in them, such as 1k chess (way before t'internet). These little 1k listings might be a game, new feature or a poke/peek into the computers memory. Once I recall turning the ZX81 into an organ, by poking some memory address or other to make the TV hum a different pitch (yes, computer used to plug into the TV back then).

From these listing I started to experiment. First I'd remove some code lines, then add my own. Eventually I started to understand the lines of code, what the keywords (instructions/commands) meant and how they could make the computer do different things. Its like learning a foreign language by trial and error. I loved it.

Years have gone by now. I've got a degree and 20 years work experience, but I'm still passionate about that little machine and what I learned from it.

Now I like to teach. I concentrate on teaching Visual Basic for Applications (VBA - Very Basic), not because its the best language, the most useful language nor the fastest. Mainly its because I loved that ZX81. I get a buzz when students write their first tiny program, or understand how functions work or what a variable is. Now if I can just instill an ounce of that zest back into them, I'll have done my job right.

Have you any fond memories of the ZX81, or perhaps you hated it? Let me know.

In Lesson 4 I promised to talk about functions, OK then, you asked for it, here we go.

A function acts in a similar way to a bicycle messenger. We send it off with a piece of information; the information is delivered; some processing occurs; and the messenger is sent back with a reply. Unless he’s gone off for pizza or to meet a date. Our functions return results, as a variable (remember them, little boxes of goodies). Take a look at this:

In an earlier lesson we met with Mr Integer, the variable for carrying around whole numbers. In this lesson, I thought it about time I showed you a number of other variable types which carry around values you’ll more than likely want to store and certainly need in your future code.

Firstly, we have the string, which keeps a track of characters, such as names, addresses, zip codes, product descriptions, she is allocated like this

Dim strIsPaulSexy as String

strIsPaulSexy = “NO, Paul has no sex appeal”

if we print out the value of strIsPaulSexy, the computer will tell us NO, Paul has no sex appeal – yeah, thanks a lot.

Then we have Mr Double. He can hold floating point numbers, which are numbers with a decimal place, like Pi or the money in your bank account

Dim dblPi as Double

dblPi = 3.14159265

MsgBox (dblPi * 10 & “cm”)

Run this code and you will get a message box showing you the circumference of a 10cm diameter circle. Remember the difference between an integer and a double. Integers will round up or down to the nearest whole number (e.g. 3.14.. = 3,  or 4.501 = 5)

Then there is the Gemini like Boolean, the True/False variable of two halves. Useful as a switch within our code, making something happen in one position, or ignoring the instructions in another perhaps.

Dim boolIsPaulSexy as Boolean

boolIsPaulSexy = False

If boolIsPaulSexy = True Then

    MsgBox (“Paul has sex appeal”)

End If

The computer, like most ladies, will say nothing.

When we write code that actually works (greatest feeling in the world) we most often want to make a decision or choice. Essentially, in human terms (or Martian, if there are any Martians reading this), we would say “when this happens then do this” or “if this had occurred, do that”. We wrote pretty much the same thing in our code from lesson 7, but like this:-

   If intBoxOfApples = 8 Then

Previously, we saw that you can drive yourself loopy, and repeat instructions over and over again, a time saver once you get it right. But what if you want to stop the loop early because something has happened?

These are called conditional loops. Easy real world definition:

Going with your girl, stopping because you have an argument, getting back together again, another argumental split and so on.

Expanding a bit on the PutAppleInBox subroutine, we can add an IF….THEN…..ENDIF clause (not Santa). We do this to get the routine to “jump” over a few numbers, once a certain number or condition has been reached (my age went from 15 to 18 every Friday night so I could go to the pub):

Sub PutAppleInBox()

Dim intRepeatThisLoop As Integer

Dim intBoxOfApples As Integer

For intRepeatThisLoop = 1 To 10

   If intBoxOfApples = 8 Then

      intBoxOfApples = 10

   End If

   intBoxOfApples = intBoxOfApples + 1

Next

MsgBox ("There are " & intBoxOfApples & " apples in the box")

End Sub

Using the code (above), once intBoxOfApples reaches 8, the code makes intBoxOfApples = 10 and completely misses out 9. This ends the For loop early.

There are many uses of If:

• If only she went out with me
• If only I hadn’t gone out with him
• If only The tooth fairy hadn’t pulled out all my teeth the night I slept with my head under the pillow.

 Let us recap, quickly.

Your writing VBA code, you know where to find the VBA editor, how to create a subroutine, what a variable is and how to get a message box on screen. Not bad for 5 minutes work, it sure took me a lot longer than that to learn it! What were you worried about? VBA is easy.

Now we?re going to learn how to make instructions repeat themselves, we call this a loop. If you have OCD, then this is the Lesson for you. There are a lot of ways to do loops in VBA, the simplest is to say "Do these instructions, repeat this many times". In code we'd write whats called a For ... Next loop, like this

For intLoop = 1 to 10

 ?.Some instruction or other

Next intLoop

So, if we put a loop in our PutAppleInBox subroutine, we get this

Sub PutAppleInBox()

Dim intRepeatThisLoop
Dim intBoxOfApples As Integer

For intRepeatThisLoop = 1 To 10
   intBoxOfApples = intBoxOfApples + 1
Next

MsgBox ("There are " & intBoxOfApples & " apples in the box")

End Sub

Go try it now, modify your previous program, remember to press F5 key to run the code. I changed "There is" to "There are", just to make the sentence look right. Now you've gone loopy too.

You can change the 1 to 10 to any number you like; you can even go backward like this

For intRepeatThisLoop = 10 to 1 step -1

By now, you're probably thinking either

1. This guys a genius and teaches VBA really easily
2. This guys a nutcase and should be committed
3. Lets do another lesson (that's what I was thinking)

Perhaps the most interesting things in any program are the variables. Variables are like little bundles to information, tied to a name that humans can easily understand (we have difficulty remembering 1245642, but we can easily remember NumberOfBloomers)

Imagine you have a box of apples and people can put apples into the box or take them out. Any number they like, at any time. How many apples are currently in the box?

That?s where a variable comes in. A variable will hold the current number of apples (or any other unit, money, time, number of characters for example), and we can ask the computer for this number at any time during our program. You?d think the computer would get fed up asking for the same number a thousand time, but it never does.

Whole numbers (numbers without a decimal point) are called integers (shortened sometimes to int, just to add to the confusion). We create an integer variable like this?

Dim intBoxOfApples as Integer

To add an apple to the box we would write

intBoxOfApples = intBoxOfApples + 1

and guess what, to take an apple out of the box we would write

intBoxOfApples = intBoxOfApples - 1

So a program to put an apple into a box, and pop up a message to tell us about it, would look like this ?

Sub PutAppleInBox()

Dim intBoxOfApples as Integer

intBoxOfApples = intBoxOfApples + 1

MsgBox ("There is " & intBoxOfApples & " apple in the box")

End Sub

To try it out, swap the Aunt Marys Bloomers code from Lesson 3 and run the code once you have typed it in (and debugged it). The result will look like this ?

As you saw during Lesson 3, a program looks a lot like (actually just like) this...

Sub AuntMarysBloomers()

MsgBox ("Aunt Mary wears bloomers")

End Sub

The Sub part acts as a nice neat container for the 'to do' list. We always have to contain the list, otherwise it might run off the screen and get married. Sub is a shortened version of Subroutine (code is nearly all about short things, my wife entirely understands short).

A Sub always ends in an End Sub, otherwise it wouldn't know when to stop. I would have just said ?when the code runs out? but apparently Microsoft never thought of it.

There is another container called Function that we'll come to later. Strangely, they didn?t shorten it to Fun, I think that might have been more accurate.

A Sub always needs a name, or it would never know when to do its job. In this case we called it AuntMarysBloomers. The name can be almost anything you like except for words the computer already uses to do other things. Do not panic. If the computer doesn't like your word it will let you know by not running your code.

 The brackets ( () ) are containers too. They can hold information (often known as gibberish) that we can get the code (we tried to write) to work on.

Confused? Me too. Time for a coffee, doughnut and a lie down by now. Just let it sink in slowly, we have a long way to go.