Author: Peter Schmitz

This article, we will be dealing with ways to have your program perform the same task a number (not necessarily known at the time you start programming) of times.

For… Next…

Using the For.. Next statement, you can set code to run a predefined number of times. As an example, let’s build a small application that asks the user for a number. The application will then add up all the even numbers from 0 to that number (or all odd numbers if that’s what the user wants).

We start by creating the interface. Add the following controls to your form.

We use the frame to more or less group the two optionbuttons. That way, when a user sees the application, it will immediately be clear that the two optionbuttons are ‘grouped’, and that it’s only possible to select one of the two. (As an added bonus, VB will recognize the OptionButtons are grouped, and if one is selected, the other will automatically be deselected).

You do this by first drawing the frame on the form, and then drawing the OptionButtons in the Frame. The end result should look something like this.

Ok, so we have the interface. Let’s spend a minute thinking on what the Program is supposed to do. The user will enter a number, select either odd or even, and our program will add all the numbers from 0 to the specified number. The way we will handle this is by using the For.. Next statement. First of all, let’s look at the syntax for the statement:

For counter = start To end [Step step]

[statements]

Next [counter]

Basically, you specify a counter (usually a variable), and you specify the inital (start) value, as well as the end value. The code to be executed every time is specified in between the ‘For’ and the ‘Next’ statement (The [statements] in the above quote). The Step-part can be used in case you want to skip a certain number of items in between. (Note: You can also use a negative number here).

If we look back at our program, if a user selects ‘Even’, we will calculate all values from 0 to whatever number was entered. The tricky part is that not all numbers are odd. We could code a way to determine whether or not a number is odd or even, but why not keep it more simple than that? We know that every other number will be even. So we just start at 0, skip the odd number 1 (which we will achieve by setting the ‘Step’ part of the For… Next statement to 2), and add the number to another variable.

If the user picked odd, we start at 1 instead of at 0, and also always skip a number. That’s basically it.

In code this would look something like this:

Private Sub cmdCalculate_Click()
‘ Declare variables
Dim intRange As Integer
Dim intResult As Long

‘ Check whether we need to calculare odd or even numbers
‘ Even was selected
If optEven.Value = True Then ‘ Even
For intRange = 0 To Val(txtNumber.Text) Step 2
intResult = intResult + intRange
Next intRange
‘ Odd was Selected
Else
For intRange = 1 To Val(txtNumber.Text) Step 2
intResult = intResult + intRange
Next intRange
End If

MsgBox “Result:” & intResult
End Sub

Let’s look at what we did here. First we declare two variables, intRange, which will be the number used to ‘remember’ where we are when we loop between 0 (or 1) and the number specified by the user. We’ve made this variable an integer, which (as you know) limits the range of the to anything between -32,678 and 32,676. The other variable was made a Long. This is done because obviously the number will de bigger than the number contained in the integer.

Next, we check which OptionButton was selected. If it was the Even one (which is the default), we start intRange at 0. Else, we start at 1. (This is nice about using two OptionButtons. If the first one is NOT true, then the other must be).

Then comes the For Next statement:

For intRange = 0 To Val(txtNumber.Text) Step 2
intResult = intResult + intRange
Next intRange

We specify that intRange will contain anything between 0 and the specified number. This is where the program starts looping. First it sets intRange to 0, and runs the piece of code inbetween the For and the Next statements (in our case: intResult = intResult + intRange. It’s not hard to see what we do here, is it? We just use intResult to store the value in between. We just add the (at that moment) current value of intRange to it).

Finally, the Next intRange statement is called. To see this in a bit more detail, let’s look at all the things that would happen if we’d set the number to 10, and want all the even numbers added up.

# intRange will be set to run from 0 to 10.
# The loop begins (No, not a giggle loop). intRange is, at this point 0. intResult is also 0.
# 0 + 0 = 0, so intResult remains 0 for the moment. (You might find this a bit redundant, but from a mathematical perspective, this is the proper way to tackle this problem, I think).
# Next is called. intRage is now set to 2 (The step-part was set to 2, remember?).
# 0 + 2 = 2. intResult = 2.
# Next is called. intRange is set to 4.
# 2 + 4 = 6.
# Next is called. intRange is set to 6.
# 6 + 6 = 12.
# Next is called. intRange is set to 8.
# 12 + 8 = 20.
# Next is called. intRange is set to 10. (Not that 10 is the final value in the range. The For… Next-loop will end after this).
# 20 + 10 = 30.
# The user sees a MessageBox which displays the result

It is possible to exit the loop in between, by using the ‘Exit For’ statement. (If, for instance, you’d be trying to find a certain condition or number, and the number would be reached, you can prevent your program from running longer than necessary by using Exit For).

That’s it for For… Next. Let’s now take a look at Do-loops.

Do… Loop

Another way to deal with code that has to be used several times in a row is by using a Do… Loop-statement. You can choose from Do – While or Do – Until. Both accept a condition. While the condition is not true( when you use Until), the loop continues. Lets look at another example:

Dim x As Integer
Do Until x = 10
x = x + 1
Loop

MsgBox x

We could rewrite this code to make it into a Do… While statement like this:

Dim x As Integer

Do While x < 10 x = x + 1 Loop MsgBox x As you see, the While form is slightly different. While the condition is true, the loop continues. So what's the difference between Do... Loop and For... Next loops? Well, with For... Next, you usually know how many times something has to be looped, while using Do... Loop usually means you just know that a certain condition needs to be True.

Welcome back to our series of articles about Visual Basic programming. Last article explained the basics of using variables and constants, which were (as we saw) little areas of memory in which we could store values, in order to quickly perform alterations on the values, or just temporarily maintain values, in order to make sure our Form would not be completely filled with TextBoxes that had data in them.

This week, we will be looking at a different type of variables, namely arrays. I know I promised last week to also tackle procedures, but I’ve decided against that, seeing that the discussion of arrays took a little more space than I intended in the first place. Also, I don’t want to create confusion by linking arrays to procedures. I’ll tackle procedures next week. That’s also when we’ll add some functionality to our calculator project.

Arrays

All right. We saw we use variables to store data in. We could store the name of a book, we could store a date, numbers, etc. What though if we wanted to store two values that are related? For instance, suppose we want to make a list of friend’s birthdays. If we’d do that in Word or HTML, we’d most probably use a table, and would come up with something like this.

Yes, these are their actual birth dates, and yes, programmers get to hang with the cool people 🙂 . Don’t forget to send them a card!

Anyway, as you can see, we’re talking two dimensional data here. It’s not just a name, and it’s not just a date; it’s a combination of both. We could store those values in separate strings and separate data-variables, but we’d end up with a whole lot of them, and it would be rather difficult to see which birth date belongs to which person. Visual Basic luckily offers another way to store two- (or more) dimensional data in: arrays .

Ok, so how would we go about using an array to store the list of friends and their birthdays, as shown above? Simple. We declare an array just the same way as we would declare any variable. The only difference is that we will specify exactly how many dimensions our array will have. In the above example, we have two columns, and five rows. The statement to declare an array for this would be:

Dim strFriends(5, 2) as String

I chose for string here, because of the date. If we’d try and add a date in the format we used (01/01/2002), because of the slash (/), Visual Basic would think we’d want to divide the numbers

The dimensions of an array are quite simple. It might take a little effort to get used to it, but if you’ve dealt with matrices before, this ought to be a rather easy approach. We’ll take the above example again, but this time I’ll show the dimensions next to it.

If we want to set or retrieve data from an array, we always start with the row number, and then use the column number (Same as in Excel. In excel, you deal with cell A1 first). In our example, the name ‘Gary Oldman’ has the ‘coordinates’ (For lack of a better word) 5, 1. Bill Clinton’s birthday’s coordinates are 2, 2.

Let’s put the data from the birthday table into an array in VB. Just start a new EXE-project.

Right under ‘Option Explicit’, we’ll declare the variable:

Dim strFriends(5, 2) As String

Go Back to the Form (double-click it’s name in the Project Explorer), and double-click on the Form. We’ll be taken into the Form_Load event. (This is a From’s basic event, and will be triggered when we actually start a project. As soon as the form becomes loaded, the event fires).

Once we have declared the variable, we can start adding data to it:

strFriends(1, 1) = “Alicia Silverstone”
strFriends(1, 2) = “10/4/1976”

strFriends(2, 1) = “Bill Clinton”
strFriends(2, 2) = “08/19/1946”

strFriends(3, 1) = “Bill Gates”
strFriends(3, 2) = “10/28/1955”

strFriends(4, 1) = “Claudia Schiffer”
strFriends(4, 2) = “08/25/1970”

strFriends(5, 1) = “Gary Oldman”
strFriends(5, 2) = “03/21/1958”

Ok, so now we defined the array, and stored our little friends list in it. But what use is that really, unless we can actually retrieve the values in there? Let’s extend our Form a little. Add the following controls to your Form.

Notice I didn’t name the labels the proper way. Labels are not likely to be addressed in code. I know it’s not conform the standard I brought up a little while ago, but personally, I only use label-naming when I know I’ll change it’s value from code. Also, the TabIndex property might need a little explanation. TabIndex is the order in which you can use the TAB-key to move from one control to another. The control with the lowest TabIndex will first receive focus when the program loads. In other words: In this example, if we start our program, the cursor will be in the txtRow-TextBox. If you’d press TAB, it would move to txtColumns, and lastly to the CommandButton. The last TextBox (txtValue) also has a few new properties. BackColor we saw before. Enabled means that a user can click the Box, and see the cursor move into there. If ‘Enabled’ is ‘False’, this is not possible. ‘Locked’ means a user can’t type data into the TextBox. I combined these three here, to achieve the effect that the boxes’ appearance indicates that it’s only purpose is to provide output, and not input. Lastly, the ‘Default’ property for the CommandButton indicates that this button will activate when the user presses the Enter key. Since this is the only button, we set Default to True. Your form now ought to look similar to this.

See how the bottom TextBox appears to say: “You can’t enter data into me. I’m just meant to present output to you”? Anyway, double-click on the CommandButton, to get us into the Click Event for the button. There, we add the following code:

txtValue.Text = strFriends(Val(txtRow), Val(txtColumn))

If you run the program now, you’ll see that the TabIndex works wonderfully. Also, if you use the Enter key, it’s as if you clicked the button; the Click event will be fired. Enter a number between 1 and 5 in the first box, and 1 or 2 in the second one, and the bottom TextBox will show you the result. Notice that if you use ‘bigger’ coordinates, you will receive an error. That’s because the array is only 5 by 2 for now.

The form of arrays we discussed today is known as ‘Static Arrays’. I.e. we define beforehand exactly how big the array will be. There’s other forms of arrays as well, namely ‘Dynamic Arrays’ (which the dimensions of are not known during the coding), and Control Arrays ( which are arrays of Active-X Controls). We’ll discuss Dynamic Arrays in a future article.

Control Arrays

A Control Array is an array of Active-X Controls. Well, that doesn’t help much, does it? A Control Array exists of a number of the same Controls (For instance buttons) that share the same name, and events. Ah, that’s a lot better, Peter. So what’s the difference between them? Simple. Like the ‘coordinates’ in the array we used, Control Arrays also have dimensions. Controls in a Control Array can be uniquely identified by their ‘Index’-number.

So why would we want to use Control Arrays? Simple. Two reasons:

# Control Arrays are less memory-intensive than the same number of regular controls.
# Control Arrays give you the ability to write one shared Event for all of the controls. This

means you have to write code once in order to respond to the same event for all the buttons (or other control-types) in the Control Array.

In order to create a Control Array, you can do two things:

# Add a control to the Form. Add another control of the same type, and give it the exact same name as the first Control.
# Add a control to your Form. Use Copy (CTRL + C), and then Paste (CTRL + V).

Both of the times you’ll receive a question if you want to create a Control Array. Next week, we’ll make a Control Array out of the buttons on our calculator, and the whole concept might get a bit clearer.

In the last article, we took a look at what the term ‘event-driven’ means. We saw that Visual Basic will only process code after a certain event takes place, and we saw a few tips that can make your programming-life easier. We also started off with a very basic calculator program.

This article, I’ll explain what variables are, why and how to use them, what different types of variables you can use, the scope of variables and finally we’ll look at constants.

Variables described

What are variables? According to the MSDN, a variable is A location in the computer’s memory where data is stored. Anybody got that? Let’s try clarifying that a little. Think of a variable as a wallet; a place where you temporarily store various things in (coins, bills, credit cards, pictures, business cards). Whenever you need one of those things, you retrieve it from the wallet, and when you’re finished with it, you can put it back in.

The same can happen with data in applications. Last article, we did not do anything with the number that was provided by the user. We just left it in the TextBox. We could have stored it in our computer’s memory, and cleared the TextBox. Another way to use variables would be by storing text that’s provided by a user.

Why use variables?

Good question. Why not just use the TextBoxes to store the data in? Well, look back at the calculator. If we’d store all numbers provided by the user in text boxes, we’d soon have a screen full of text boxes with various numbers in it. Instead, in the future, we will store the number in a variable and then clear the TextBox (thus indicating to the user that our program is ready to receive a new number).

Another reason to use variables would be because the memory is readily available for fast access. If something is stored in memory, it can be called or altered with great speed. If we’d just store our data in text boxes, the program would first have to read the number in the TextBox, then alter it, and finally put it back into the TextBox (Granted, this might seem like a marginal fraction of time, but in programming, you’re ideally looking for code that’s as optimized as can be. In the end, every millisecond you can spare counts).

Finally, Visual Basic offers a number of functions that can be used to alter data. Using variables, you can easily store the data, and use those functions on it.

How to use variables

Before you can use a variable, you have to declare it. That is, you have to let Windows know you want to reserve memory for storing data in, supply a name for the variable, and you have to tell Windows what type of data it will be (discussed in the variable-types-section). The way to do this is by using the keyword Dim. (Dim stems from earlier days, and is an abbreviation for Dimension, because you reserved a dimension of the memory. If you like, think of Dim as meaning ‘Declare In Memory’, which is a nice little shortcut that makes sure you’ll remember the ‘Dim’-keyword for now. (In a few weeks, you won’t even have to think about using Dim, it’ll be an automatic thing)).

To declare a variable that we’ll be using to store text in (A so-called string), we would use the following piece of code (the name of the variable would be strInput):

Dim strInput As String

This is called Explicit Declaration. Remember that when we installed VB, we checked the ‘Require Variable Declaration’ CheckBox? (This causes ‘Option Explicit’ to be written in the code window). Well, by checking that box, we made sure that every variable we will be using will have to be declared with the Dim statement. This ensures that if you try assigning data to, or calling data from a variable, Visual Basic will generate an error if the name of the variable doesn’t exist. When you create a variable, Visual Basic will (silently) create the variable with value ‘0’.

If you want to assign a value to a declared variable, you can do that the following way:

strInput = txtInput.Text or
strInput = “Hello!”

The text “Hello” is between quotes (“) because it’s a string. We have to let Visual Basic know exactly what text we want to store in the string, because the internal commands, functions, and keywords of Visual Basic could also be used within a string. Consider the following (try it out in VB if you want. You’ll receive an error):

Dim strText As String
strText = This is an example of how to use the keyword Dim in VB

See? Visual Basic has no idea of whether it would have to use Dim to declare something, or whether you were trying to store it within a string. The code will work if we add the quotes:

Dim strText As String
strText = “This is an example of how to use the keyword Dim in VB”

The quotes only have to be used in the case of strings. All the other variable-types require no quotes to be used.

Types of variables

Because there are various data-types to store, there are also various variable-types. The table below shows respectively the types of variables, the number of memory-bytes required to store the variables in, and the range of the values for each variable-type.

As you can see, any possible value you would ever want to store has been thought of. The default type for a variable, by the way, is Variant. So if you’d just declare a variable like this:

Dim strInput

The type would be Variant (which takes up most memory). Try to save a copy of the table somewhere close by. Try and always pick the variable type that would require the least bytes of memory to be reserved. Remember, the more efficient you use the memory, the faster your application will run.

Scope of variables

We saw that variables are declared with the ‘Dim’-keyword. If you use Dim, a variable will only exist within the procedure in which it was declared. (As an example, if we’d declare a variable within a button-click event, as soon as all code within that event is processed, the variable will cease to exist, and the data inside the variable will be lost).

Hmmm. What if we want to use a variable to store a number for a bit longer? (Think back to our calculator. If the variable would lose it’s data after the click event was processed, we wouldn’t have much use for it, would we?). Well, it’s possible to declare a variable that is created right when the program starts, and will exist as long as the program is running. A variable like that is called a static variable. That kind of variable is declared by using the ‘Static’ keyword:

Static strInput as String

This variable will remain active within the procedure it’s created in (Again, a single click -event for example). Lastly, it is also possible to declare a variable that’s valid for the whole application. Those are called public variables, and are accessible from anywhere within the application. They’re declared with the Public keyword:

Public strInput as String

A Public variable can only be declared at the top-level of the code (Under ‘Option Explicit’). If you try and use it anywhere else (for example within a click-event) you will receive an error if you try to run the application. Same as with picking the right variable-type, always pick the most limited scope. This prevents memory from being used unnecessary, and mistakes from happening.

Constants

Well, as we know now, variables are used for temporarily storing data. What though if we would have a variable that stays the same within the entire lifespan of the application, and is used all-through the application? (Think of a filename that is not going to change within the program, and on which multiple procedures rely). Well, this is also possible. We can use a specific type of variable called a Constant to achieve exactly this.

A constant is declare like so:

Const cstrFilename As String = “C:\autoexec.bat”

As you see, we immediately assign the value of the constant to it. This is because the value will be constant. It will not change. The c is to show this is a Constant. This makes it easier for you to recognise the Constant within the application.

Last week we took a look at some of the standard controls that come with Visual Basic. We saw that we can change the way a control looks, by fiddling around with it’s properties.

This week, we will look at some other possibilities of controls; events. We will build a very basic program, and I’ll offer some practical tips in the process.

We will build a little calculator during this article. Fire up Visual Basic, and select ‘Standard Exe’ from the templates. You’ll notice that Visual Basic supplied some very basic and vague names for the form and the project name. (How could it not? VB doesn’t know what we will be working on). Since we want to be able to recognize the project in the future by a useful name, we’ll change the project’s name first.

In the Project Explorer Window, click on Project 1. (The uppermost-item, or ‘root-item’). In the Properties Window, you will see that ‘name’ is the only property of the project we can change. We’ll set it to ‘Calculator’ for now. As soon as you press Enter, you will notice that in the Form Designer, the new name will be reflected.

Next, we will change the Form’s name and Caption to something a little more descriptive. Click on the Form (Either in the Project Explorer, or in the Form Designer), and change it’s Caption to ‘Calculator’. We’ll set the name of the form to frmCalculator.

Hold it. Stop right there. frmCalculator? Has Peter finally managed to go mad? No. In fact, Peter’s quite stable at the very moment. This is the first tip I want to offer you.

The Hungarian Naming Convention

To improve readability in your code, and make it easier for you to look at a project some months after you last worked on it, it would be nice to have some useful names, right? If we would never ever change any names, and just use the ones that VB creates for us, in no time we would get lost. Imagine you have 5 different CommandButtons on your form. 1 for opening files, 1 for saving files, 1 as cancel, 1 for exiting the program, and 1 that makes your screen flash purple. If, in your code, you have to look at Command1 through Command5, you’ll soon be lost. However, if you supplied useful and descriptive names, your code would almost be as easy to read as plain English.

Now, to enhance readability, one of Microsoft’s programmers introduced a naming convention for programming and database objects. The basic idea behind his naming convention is to use a short abbreviation that describes the object before the name of the object as used in your program. For a form, this would be frm, for a CommandButton, it would be cmd. After the prefix, which is written completely in lower case letters, you start the actual name with a capital letter. If you have to use more than one word, you will use capitals to show you started a new word.

Looking back at the earlier example (the 5 buttons), your code would be a lot more readable if you had to look for cmdOpen, cmdSave, cmdCancel, cmdExit, and cmdFlashScreenPurple. In one glance, you would exactly know which button was being used, and it would make the understanding of a particular piece of code a lot better.

If you’re interested in a few articles with lists of widely-used prefixes, check the Microsoft Knowledge base, articles Q173738 and Q110264. This is by no means a list you want to memorize; we will see various prefixes that are commonly used in future articles anyway. One thing to keep in mind though is that you should consistently use the same prefix for the same object throughout all your code.

Back to our calculator. Your Form Designer will now look like this.

We’ll add a TextBox to the form, and 4 buttons. (This will be a very basic calculator that can add, subtract, multiply and divide only).

Set the following properties for the commands.

Leave the caption for all the controls blank. (A quick way to do this is by double clicking on ‘Caption’ in the Properties Window, and then pressing the Delete-key. (Double- clicking will automatically highlight the complete Value)). And for those wondering; Yes, I did do the above scheme in Excel. I suck at HTML-tables :). You’ll be seeing more schemes like that in future articles. Oh, and with ‘Vb-folder, I mean where ever you installed Visual Studio. (Remember, I use Visual Studio Enterprise Edition. You have to have picked ‘Graphics’ during installation. If you do NOT have the graphics, don’t set the picture property, but use the appropriate characters in the Caption property. If you want, change the font size slightly to make the character a little more visible).

Your form ought to look like this by now.

Pretty impressive, huh? Let’s save our work for now. This is where another nifty tip comes along.

Projects folder

I’d strongly recommend making a separate folder where you store your Visual Basic projects. Personally, I use a folder called ‘_Projects’ on a particular drive. The underscore makes sure it’s always the first folder to show up (Unless of course, you’d have a folder with 2 underscores). While that is nice, let’s enhance the tip somewhat. To always start in your newly defined project folder, you need to pull of a crazy little trick. (Otherwise you’ll be doing a lot of clicking; the default start-up folder for Visual Studio is the folder it got installed into. (This tip works on NT or 2000, but I’m quite sure it ought to work on other version of Windows as well).

Find the hyperlink to your VB-icon in the Start-Menu (in your profile). Right-click it.(In the newer Windows versions (NT4, SP 6+, I think), you could also just expand your Program Files folder from the Start Menu, look up VB there, and then right-click the VB-icon). Select Properties. Change the Start-In-folder to your _Projects-folder.

From now on, when you start Visual Basic, it will always offer to save projects in the new folder. I personally prefer to use sub directories for each single project. That just helps to keep things separated and visible.

Back to the calculator. We were going to save our work. Click File / Save As, and save your work somewhere. Notice you’ll be prompted both for the project (which is like the cover of a book; It keeps everything together), and the form (Which could be considered as an element (read: Chapter) of a book).

Well. We have our interface now (An interface is what the user visually gets; In our case, a small form with a text-box and 5 buttons), but we never added any code. If we’d run the code now (use F5, or the button in the ToolBar), we’d see the interface, but it wouldn’t do much. This is where Events step in.

Events

Let’s analyze what a user will be expecting when he sees your interface. He’ll notice he can use the TextBox to enter numbers, and the buttons for performing various mathematical functions. When he types a number made up out of more than one number, nothing has to happen; it’s only when he clicks a button that something will happen. Schematically, the following events will happen.

First, the user will enter a number. Nothing happens, because we cannot be sure of whether or not the user wants to type more numbers; instead, we’ll wait until the user presses a button, and take it that at that point, he wants to perform a calculation on that number. For this week, we will just add functionality to the plus, minus, divide and multiply buttons. We will deal with the ‘=’ button in future articles. To keep things simple, this week we will just make the TextBox show the updated number every time a user clicks one of the buttons.

Now that we know the basic scheme (We’ll enhance this in future articles), let’s look at the events (user-actions) in this scenario. We already determined that when text is entered, nothing will happen. However, when a user clicks a button, we do want something to happen. In Visual Basic terms, this is the definition of an ‘event’. An action, recognized by an object, for which you can write code to respond. . (Taken from the MSDN). For a CommandButton, the click-even is the default event. If you’d double-click the first button (cmdPlus), you’d be taken into the Code Window, and specifically to the ‘cmdPlus_Click()’ event. (Click is the default event for a CommandButton). It means the user supplied a number, and then wants to add another number to it. So what we do is:

# We will take the current value of whatever is in the TextBox.
# We then use add the same number to itself.
# Lastly, we update the TextBox to show the new number.

Not very useful really, but for this example, it will do the trick.

The code for that would be this:

txtInput.Text = txtInput.Text + Val(txtInput.Text)

What we do here is we say the Text-property of the TextBox is equal to the value of the current Text-property, plus the value of the current Text-property. (The period tells Visual basic that we’re referencing an object, and want to access something belonging to that object. (In this case, the Text-property). Note that with value I’m talking about it’s numeric value, and not the value of the Text-property. We use the numeric value, because if a user would input a letter instead of a number, it wouldn’t really make sense. Suppose a user entered the letter ‘a’ instead of a number. a + a would not really make sense to a calculator. Val() is a Visual Basic function designed for that purpose. (We’ll see what Functions are in future articles).

Let me introduce you to another programming concept at this point.

Indentation

If you just typed in the code as supplied, your code would now look like this:

Private Sub cmdPlus_Click()
txtInput.Text = txtInput.Text + Val(txtInput.Text)
End Sub

Imagine hundreds, if not thousands of lines of code in that way. That wouldn’t be readable, would it? Instead, let’s throw in a TAB before the line we typed:

Private Sub cmdPlus_Click()
txtInput.Text = txtInput.Text + Val(txtInput.Text)
End Sub

Notice how that looks much better? It’s like a book; If Tolkien had written lord of the rings in plain text, with no formatting at all, no one would have gotten through the first few pages. Instead, a clever author uses paragraphs, chapters, and what not to make sure that whatever they say is structured. Do the same thing when you’re programming. Make sure that you, or anyone else reading your code will have an easy time reading the code. Take a look at how other people program.

Back to the calculator.

Go back to the Form. (In the Explorer Window, double-click on it). We’ll see the Click- events for our other buttons now. Just go through the same procedure as before (double-click the button, and add the code (Just with different mathematical functions added to it). In the end, your Code-Window will look like this.

Run the program again, punch in some numbers, and use the buttons. You’ll see that by adding code to the ‘Click’ event, we basically told our program what to do whenever a user clicked a button. Other events could be selecting a value from a presented list of options, closing the program, clicking a menu-option, etc. Basically every action a user takes can be seen as an event.

By the way. You might have noticed that when you were typing in the code, after you pressed the dot (‘.’), a little scroll able list popped up.

Intellitype

Because you were assigning a value to the TextBox’s property, Visual Basic recognized you were accessing the TextBox-control, and offered as nice list with possible properties. Quite handy when you’re not actually sure which properties are available, or which one you are looking for. Intellitype also showed up when you were working with the Val-function. It showed you the syntax for the Function there. Intellitype is your best friend while programming; it makes sure you don’t have remember everything about Visual Basic.

ADO is Microsoft’s latest way of making it easier for us developers to retrieve and work with databases. Don’t worry, you don’t have to be a SQL-guru to use ADO; however, some basics would be nice.

SQL Basics

SQL (Structured Query Language) is a ‘programming’ language used for querying data within Relational Databases. The most basic command is SELECT. SELECT is used to retrieve data from one or more tables of the database. For this article, we will be using the NorthWind database. (It comes with both SQL Server, and MS-Access (For MS-Access, it is installed in the Samples-Folder)).

If we want to retrieve all rows within a certain table, we would issue the following statement:

SELECT * FROM employees

This would retrieve all data from the ’employees’ table. However, if we’d want to see which employee handled which order, we’d query two tables; employees, and orders. How would we know which employee handled which order though? This is where the relational part of the Relational Database-concept kicks in. Instead of listing the employee’s full data (employee-id, name, DOB, etc), the orders-table just shows a reference to a unique number assigned to the employee dealing with a particular order. If we match the number from employees with the number from orders, we would get the full information about the employee, as well as the full order detail. The statement to do this would be:

SELECT * FROM employees, orders WHERE employees.EmployeeID = orders.EmployeeID

Let’s look at that statement for a second. It’s not that hard, and I don’t want to spend too much time on the actual SQL-syntax (Mike handles the SQL Server articles). The first part is similar to our very first statement. SELECT * specifies we want to retrieve all rows from the table(s). FROM employees, orders specifies the tables to retrieve the data from are employees and orders. The comma acts to tell the database we want data from both tables. (If you’d specify more tables, you’d just seperate them with commas each time). WHERE employees.EmployeeID = orders.EmployeeID is what we use to specify the relationship between the tables. In this case the EmployeeID of the orders table is equal to an ID in the employeesID, and we want to see all the information. Does this sound complicated? Let’s look at a quick graphical representation of the tables.

Without going into too much detail, you might see from the diagram (which I whipped up using SQL Server), that between Orders and Employees, a one-to-many reationship exists. One order has one employee handling it. However, an employee can handle many orders. (The way to recognise this is the little symbol looking like two intertwining o’s. this is the mathematical symbol for infinity. (Don’t pin me down on this. Math never was my favourite subject) going to the orders-table).

That ought to be enough about SQL for now. Let’s get started with ADO; after all, that is why you are reading this article.

The ADO Object Model

ADO is a simple way of making a connection with a database, and issuing commands (such as stored procedures in SQL Server), or retrieving data into so-called RecordSets.

ADO resides on top of OLE-DB (from within Visual Basic, at least). OLE-DB enables you to access datasources of different types, without having to deal with issues like network-packets, security, etc.

The ADO Object model graphically looks like this.

Yes, only three objects. Let’s look at them individually, and discuss what they are used for.

Connection Object

The top level object; handles the actual connection to the database. In order for a Connection object to succesfully work, it needs to be told where to connect to, how to connect, and the database to access. This information is stored in it’s ConnectionString property.

Command Object

Using a Command object, you can issue a SQL statement that returns a single row, execute a stored procedure (A prec-compiled SQL statement, or batch of statements on SQL Server), or issue other commands against a database. Commands objects can also support parameters (for instance for useage with a stored procedure). You can use the Command object, without specifying an existing Connection (the Command object will create a Connection object by itself. I strongly recommend not using this approach though, and always creating a Connection object. That way, you can issue multiple commands on the same connection, and you prevent large numbers of connections being created (and hogging up valueable resources on the server)).

RecordSet Object

The RecordSet object defines rows returned from a database; more or less like a database table. A RecordSet is the object we will be using most, if we want to query a database. It is possible to not use an explicit connection when opening a database; however, if you use a connection, you can open multiple RecordSets on the same connection. (Also see above).

A RecordSet is built up out of multiple fields, that each represent a column of data. Graphically, a RecordSet could look like this.

(We created this ‘RecordSet’ by issuing the SQL query SELECT EmployeeID, LastName, FirstName FROM employees). The whole returned list is the RecordSet. The individual ‘boxes’ are all fields. So ‘Anne’ is a field, ‘3’ is a field, and ‘Buchanan’ is a field. (Notice the gray columns are just to provide some readability, and are NOT part of the RecordSet). The total number of fields in this particular RecordSet would be 27. (9 rows of three fields each).

Implementing ADO in an application

Let’s build a quick application to retrieve the RecordSet shown above, shall we?

Start up Visual Basic, and select ‘Standard Exe’ as the project-template.

To display the RecordSet in our program, we’re going to use a Hierarchical Flexgrid. Right-click within the ToolBox, and select Components. Scroll down untill you see ‘Microsoft Hierarchical Flexgrid Component’.

Check the box in front of it to add the component to your Toolbox. In the Toolbox, you’ll notice a new icon:

Draw a new Hflexgrid on your form. The size doesn’t really matter; we can make it fit our RecordSet later.

Now we’re going to add the ADO object-libraries to our project. Click on Project, and select ‘References…’. In the list, scroll down untill you come across ‘Microsoft ActiveX Data Objects 2.1 Library’. (You can also use other versions, instead of 2.1. Just make sure that if you will distribute the application to other users, they also have the same, or a later version).

What exactly did we do here? Well, basically we imported the classes (read: Objects) (The Connection, Command and RecordSet, that is) of ADO into our project, which means as much as that we can now start to use the objects within the ADO-library, along with their events, properties, and functions. (You can get an overview of the complete ADO-library by using the Object Browser. The Object Browser can be called either by using it’s icon in the Toolbar, or by pressing F2.

In the Object Browser, make ADODB the library you want to explore (Set the upper ComboBox’s value to ADODB).

Switch to the code-window (Use either F7, or the ‘View Code’ icon in the Properties Window).

If you followed our Installation Instructions, you ought to see ‘Option Explicit’ there. (If not, Don’t worry. Click on Tools, Options…, and check the ‘Require Variable Declaration’ box). This requires that you declare all variables with the Dim, Public, or Private statement.

In the General Declarations-area (Which is after ‘Option Explicit’ and before any subroutines), we’ll declare two object-variables; 1 for a Connection, and 1 for a RecordSet:

Dim CN As ADODB.Connection
Dim RS As ADODB.RecordSet

Notice that we just allocated memory-space for the variables. The objects themselves do not exist yet. (In other words, the object is set to Nothing). If you don’t like the names I picked, feel free to change them within your own application. CN and RS are just a habit of mine.

Go to the Form_Load() event. (Either double-click on the form itself in the Form Designer, or use the ComboBoxes above the Code-Window. Use the left one to set the current control to ‘Form1’. A form’s default event will be ‘Load’, so you don’t need the right ComboBox to select the event.

Alright. We want to retrieve the employee_id, last name, and first name from the employees table. Remember the SQL statement we used earlier? If not, it’s:

SELECT EmployeeID, LastName, FirstName FROM employees

Our RecordSet will be built with that query. However, we need to supply that query to the RecordSet somehow. In my opinion, the most elegant way to do it is by using a string to store the query in. (You could also supply it directly when opening the RecordSet, but I dislike having really long lines of code. It makes the code less clear to read. That’s a personal decision though. Since I am the one writing this article though, we’ll use my way *sticks out his tongue*).

Dim SQL as String

(Again, SQL is just a habit of mine; I normally prefer using the Hungarian notation (Which I’ll discuss in a beginner-article soon), but for this example, it will do.

Next, we assign the SQL-statement to the string:

SQL = “SELECT EmployeeID, LastName, FirstName FROM employees”

We’re all set to go now. First, we create a connection object:

Set CN = CreateObject(“Adodb.Connection”)

Set means we assign an object reference to a variable (The earlier created CN). CreateObject means we want to create a new instance of the object.

Now we provide the Connection with a ConnectionString, so the connection knows where and how to access the database.

For SQL Server:
CN.ConnectionString = “Provider=SQLOLEDB;Data Source=SQLServer;” & _
“User ID=sa;Password=””;Initial Catalog=NorthWind”

For MS-Access:
CN.ConnectionString=”Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;” & _
“User Id=admin;Password=;”

Note: The ampersand (&) and underscore (_) are used here to increase readability. They allow a string to be spread over multiple lines. You could also just type the full string out on a single line though.

Let’s analyse the connectionstring for a second. Provider supplies the OLE-DB Provider to use. Datasource specifies where ADO should look for the database. In the case of SQL Server, this is where you specify the servername. When using MS-Access, you supply the filepath to the database. User ID is which login to use when you’re accessing the server or database. Since both ‘sa’ in SQL Server and ‘admin’ in MS-Access have a default blank password, there’s no need to specify the Password (meaning you can just leave out the whole Password-string).. If, however, you changed the password, you should provide it. In SQL Server, we also have to supply which database to use (A single SQL Server can hold many databases, some of which are mandatory (master, msdb, tempdb and model). The Initial Catalog setting specifies to which database we will be connecting.

The next step is creating a RecordSet:

Set RS = CreateObject(“Adodb.RecordSet”)

This is similar to how we created the Connection-object. We’ll now open the connection to the data source, so we can retrieve data into our RecordSet:

CN.Open

Ok, now we got everything needed to fill our RecordSet with the required data:

RS.Open SQL, CN

For MS-Access databases, you have to add a little something:

RS.Open SQL, CN, adOpenDynamic

SQL is the parameter supplied as the Source for the RecordSet, while CN is used as the ActiveConnection. You’ll notice that the Intellisense offers other parameters as well. We’ll discuss those next week. For now, this will do just fine for our example.

So, we have the data in our RecordSet now, but we still need to display it in the Flexgrid. We can use a very nifty feature of the HflexGrid, namely the DataSource. This property can be used to display RecordSets. So what we do is we set our RecordSet to be used for the Datasource:

Set MSHFlexgrid.Datasource = RS

We have to use the ‘Set’ statement because we assign an object reference to a property.

Before we go on, I want to take a brief moment to explain a little something about objects. If you create an object, memory is allocated in which to store the object. As long as the object is active, this memoryspace will be in use by it. The same thing goes for the connection. We opened a connection, that is kept live both on the client (our computer) and on the server (The SQL Server, or, if you’re using a local instance of SQL Server or an MS-Access-Database, locally). To free up that memory, I recommend closing both RecordSets and Connections as soon as possible. Closing them does not mean the objects are destroyed; it just closes the connections. Since we don’t need them anymore anyway, we destroy them right after closing them. We do this by setting the object to Nothing:

RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing

That’s it! To see the results, run the code. If all goes well, and you didn’t make any typos, you should be seeing the RecordSet displayed.

Welcome to the third Article in our Visual Basic section. Last week we installed Visual Basic. This week we will be looking at the environment that you are presented with when you start Visual Basic, and set a few options that will prepare Visual Basic to meet our demands.

Let’s run Visual Basic for the very first time. The first thing you will see is this screen, where you are presented with a list of various project templates.

For now, select Standard EXE, and click on Open. The next Visual Basic will add a few things for you, to accomplish the standard setup it needs for creating a Standard Executable (Which eventually will be a runnable program with a .exe extension).

The Visual Basic Interface

The screen you see now will look more or less similar to this one.

Wow. That’s a lot of weird windows. Let’s go through them one by one. In the lower-right corner, you will see the Form Layout window.

The Form Layout Window

Using the Form Layout Window, you can choose where your program will essentially show up once it is completed. As you can see, it already contains a small version of a form, which can be moved around the screen.

Right-clicking the Form Layout Window will also give you the possibility to select some positions (In the ‘Startup Position’-sub menu). The Form Layout Window is not something we will be using often. (Well, I don’t use it often. It’s up to you to decide whether or not you leave it visible, or just hide it).

The window right above the Form Layout Window is the Properties Window.

The Properties Window

This is one of the windows you will probably use a lot. It shows the properties of the currently selected object. What is a property, you ask? The definition from MSDN is: “A property is a characteristic of an object, such as size, caption, or color”.

Let’s look at this a little more in-depth, shall we? Imagine for a second we’re a God. We created a world, threw in trees, bushes, and what not; added a few animals, and are now working on tossing in a few people. Every person has a few characteristics: Eye color, hair color, a name, a shoe-size, etc. With the Properties Window, you can quickly see all the various properties on objects, and set or change current properties.

The Properties Window is made up out of three elements (As seen in the picture):
# Object Box
# Sort Tabs
# Property List

The Object box provides a list of objects currently in use in the project, of which you can set properties.

The Sort Tabs provide a quick way of sorting the Property List. You can sort either by category, or alphabetically. This is really a matter of choice, or habit. Use whichever way you prefer.

The Property List provides a list of all the properties that can be set for the currently selected object. The left column provides the property name, the right column shows the currently assigned value.

Under the Property List, a short description of the currently selected property is displayed. (You can turn this off by right-clicking the Property Window, and unchecking the Description option).

Above the Properties Window, we’ll find our good friend the Project Explorer.

The Project Explorer

The Project Explorer shows the different files that make up your project. Notice that in the above screen shot, I added a few extra files, to show a more extensive window than the one you will currently see.

On the left side of the screen, you will find the toolbox.

The Toolbox

The toolbox holds various ActiveX controls that you can use in your projects. In addition to the standard ones you already see, you can also add other controls (that could be provided by Microsoft, third-parties, or controls that you made yourself).

More on controls next week!

In the middle of the screen, you will find the Form Designer.

The Form Designer

The Form Designer shows you the interface of your application. You can use the Form Designer to determine exactly what your project is going to look like, which controls will be available, which text will be displayed where, etc, etc.

This leaves us with two more things to discuss concerning the interface. The toolbar, and the menu, both of which have a lot in common with standard Windows applications, but offer a few new functions. Let’s tackle the toolbar first.

The Toolbar

The Toolbar provides a number of icons, some of which you will undoubtedly recognize from programs as Word, or any other Windows Application.

We’ll briefly describe the various icons here, but won’t go deeper into what they do for now, as we will get working with that gradually, as we advance more and more into the actual programming process. You can get little description-labels by hovering the cursor over the icon for a second.

The first three icons are specific to Visual Basic. The left-most one allows you to add other projects to the project you’re working on, the middle icon adds files to your project, and the right-most one allows you to create user-menus.

The next few ought to look rather familiar to anyone who worked with Windows before. The first icon is for Opening a file, the second one for saving a file. The third icon is for cutting, the fourth one for copying, the fifth one for pasting, and the sixth one is for searching. The two arrows on the far right are respectively for undoing and redoing operations.

Three VB-specific icons again. They might look familiar however. They highly resemble buttons as they are used on Stereo / Video equipment. They provide the same functionality as well. The first one runs a project, the second one pauses it, and the third one aborts (or stops) the project.

These icons are all short-cuts to the various windows we discussed earlier. The first one is the Project Explorer, the second one the Properties Window, the third one represents the Form Layout Window.

The fourth one is a new one. It points to the Object Browser which we will discuss in future articles.

The fifth icon is for the Toolbox, and the sixth is a new one again. It points to the Data View Window, which we will discuss when we get to the article about Connectivity to Databases. (Which will be a few weeks from now).

The right-most icon the Visual Component Manager; basically a repository database for storing reusable bits and pieces of code, files, components, etc in.

The last part of the toolbar is for keeping track of the position and size of controls. The leftmost part is for the position, while the right part lists the size of a control. To see what both do, double-click on one of the icons on the toolbar, which will make a control be added to your form. Rezise and move the control on the form, and the numbers will change.

The Menu

The Menu contains a few default Windows menu-items (like file, edit, view, help, etc.), and a few functions specific to programming (like Debug, Run, Project, etc.).

# File-menu; provides file-related functions. (Open / Save / New / Print)
# Edit-menu; provides edit-related functions (Cut / Paste) as well as some look-up functions.
# View-menu; provides functions to access various Windows.
# Project-menu; provides ways to add files, and edit properties of the current project.
# Format-menu; provides functions to format controls on the form.
# Debug-menu; provides functionality to test your code, as well as tracking down errors.
# Run-menu; provides different ways to start and end a program.
# Query-menu; provides database-functionality. Only available in Enterprise Edition.
# Diagram-menu; provides database-functionality. Only available in Enterprise Edition.
# Tools-menu; offers some nifty tools, like wizards.
# Add-ins-menu; offers ways to integrate various external programs into VB.
# Window-menu; provides functionality to rearrange various Windows in the environment.
# Help-menu; provides… *drum roll* help.

That concludes the description of the environment. I also promised to set a few quick options to configure Visual Basic, so bear with me for another 5 minutes or so. We’ve done the hardest part; the next few minutes are peanuts. I’ll just provide the settings here. Why I choose the settings will be explained in future articles.

Configuring Visual Basic

Click on Tools, and select Options. We’re going to make sure that we reduce the number of errors we’re going to have to deal with. On the tab ‘Editor’, check the ‘Require Variable Declaration’ box.

On the ‘General’-tab, I’d recommend selecting the ‘Break on Unhandled Errors’ option. This will make debugging easier.

That’s it! Thanks for sticking with me for so long. In next week’s article, we will discuss the standard controls, and include a few of them in a project. We’ll take a look at their common properties, events, and methods, as well as make our first actual program.