Visual Basic Loops

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.

Decisions in Visual Basic

This week’s article will deal with decision making in Visual Basic.

As you all know, computers are great for calculations. We already saw the possibility to add, subtract, multiply, and divide numbers. That’s all very nice, but at times we need to compare numbers with another (is b bigger than c?), string with one another (did the user supply the proper username and password?) etc.

I’ll introduce you to the If… Then… Else… and Select Case statements today.

If… Then… Else…

If… Then… Else… is used when you need to make a decision. Computer are very bad at making decisions. Ask a computer “Where should I go, Left, or Right?”, and your puter will prolly just sit there. If you’re in bad luck, and your puter had a bad day, it might start smoking. Computers are very good though at evaluating things though. As far as a computer is concerned, the whole wide world is built up out of 1’s and 0’s. A computer can test whether something is True or False. A value of 0 would be False. Logically speaking, True would be a 0. However, in VB6, True is -1. Don’t worry about why, just remember that a value of False equals a 0. (VB.NET recognizes 1 as True).

To evaluate whether or not something is True or False, we can use the If… Then…. Statement.

The Syntax for this statement is:

If condition Then

Visual Basic will evaluate whether or not the condition you specified was met. An example here could be:

Dim x as Integer

x = 6

If x <> 5 Then
Msgbox “The value of x = ” & x
End If

What do we actually do here? First we declare a variable x, then we set the value of x to 6.

Next, we use an IF statement to check if X is more or less than 5. In other words, we check if x is NOT 5. (If a value is smaller or bigger than the value you’re looking for, it is not equal to it).

If the value of x is different, than we make Visual Basic give us a MessageBox with the value of x in it. If you would set the value of x to 6, instead of 5, you would not see a MessageBox at all.

All right, so where’s the ‘Else’ statement then? We’ve just seen an ‘If’ statement here. Let’s enhance the earlier code a little.

Dim x as Integer

x = 6

If x <> 5 Then
Msgbox “The value of x = ” & x
Else
MsgBox “x = 5”
End If

It’s not hard to see what’s happening here. The code we added will make a MessageBox appear if x equals 5. Change x to 5 in the code, and rerun the program to see the new MessageBox.

The If-statement can be enhanced by using the ‘ElseIf’ function. For demonstration purposes, get a new form, and create a TextBox and a CommandButton on it. This is where we will take an inputnumber from the user. Change the caption of the CommandButton to ‘Guess!’, the Form’s caption to ‘A number guessing game’, and remove the initial text from the TextBox. Double-click the CommandButton, and add the following code:

Dim intOurNumber as Integer

intOurNumber = 10

If intOurNumber > Text1 Then
MsgBox “Guess higher”
ElseIf intOurNumber < Text1 Then MsgBox "Guess lower" Else MsgBox "Congratulations, you guessed our number" End If What we did here should be quite obvious. If the user guessed too low, we'll tell him so. Likewise, when he guessed too high, he'll be told so. If he guessed correctly, we congratulate him on guessing the correct number. Our first If-statement evaluates whether the value was too low. The ElseIf-statement checks too see if the number was too high. Finally, the Else-statement determines all other possible options (If the number is not higher, and the number is not higher, it must be the correct number, right?). One word of warning. Please do NOT forget to 'close' the If-statement by using 'End If'. You will get an error when you try to run the program. If-statements can be nested. This basically means that within an If-statement, you could create another If-statement. This could be useful once you start writing some more advanced programs. For now, I'll just give you a hypothetical example: If x > 5 And x < 10 Then ' x is between 5 and ten If x > 7 and x < 9 Then ' x must be 8 MsgBox x End If End If So far, we've only seen comparisons with numbers, but you could just as well evaluate String Functions. Let's also try out something with that. Create a new form, and call it 'Login'. On the form, create two TextBoxes and a CommandButton. Use the following schema.

Basically, we’re creating a very, very basic version of a loginscreen. This is not a very secure one though!

Double-click on the button, and insert the next code:

Dim strUsername As String
Dim strPassword As String

strUsername = “peter”
strPassword = “secret”

If txtUsername.Text <> strUsername Then
‘ Wrong username provided
MsgBox “Wrong credentials”
Else
‘ Correct Username. Let’s check password
If txtPassword.Text <> strPassword Then
‘ Wrong password
MsgBox “Wrong credentials”
Else
‘ Username and Password are correct
MsgBox “Welcome!”
End If
End If

All right. We’ve seen how we can use the If-statement to evaluate a few possible options. Usually, we use If-statements if the possibilities are few (I.e. Either true, false, and maybe an ‘Else’ for everything else). What though if we want to evaluate multiple conditions? Like… Days of the week? Or friend names? Dates?

If you need to compare multiple possible options, the Case-Statement is a better way than using the If-statement. The reason for this is that with If-statements, even if at one point you reached the ‘True condition’, VB will still look for other possibilities. This can have an impact on performance if you need to look for a particular value out of a lot of possible combinations.

Case

Suppose we’re writing a program that deals with a yearly calendar. On special holidays, we’ll want a notification to be sent (we’ll again use a MessageBox for this purpose).

The dates we want to use.
We have 5 dates there. Normally, that would require 5 different IF statements to evaluate whether or not one was today’s date. Even if it found the correct date, unless you’d make the function end at that point, it would require quite a bit of code.

The Case-statement offers a nice solution. It’s syntax is rather straightforward. You start with a Select Case statement, and then supply the string you want to evaluate, and then you list off the possible values using Case-statements.

Create a new project in VB, add a button, and double-click the button. There, add the following code:

Dim intDate

intDate = Day(Now()) & “-” & Month(Now())

Select Case intDate
Case “1-1”
MsgBox “January 01. Happy new year”
Case “4-7”
MsgBox “July 4th! Happy independence day!”
Case “11-9”
MsgBox “A tragic day in world’s history”
Case “31-10”
MsgBox “Trick or treat! Happy Halloween!”
Case “25-12”
MsgBox “Happy christmas!”
Case Else     MsgBox “Nothing special today” End Select

Note I used the European date/time settings here. However, this will run fun on any machine anywhere in the world. The reason for this is that both the Day() and Month() Functions take their part (they respectively retrieve the day- and month-part out of a given date. In this particular case, we use Now() which returns the current system date) from the Regional Settings of the computer.

The code takes the day-part of the current system date (which can be found using Now()), adds a hyphen (‘-‘), and then adds the month-part of the current system date. It then uses the select case statement to check whether that is the same as whatever we want to check.

You can check to see if the program works by changing your computer’s system time. Run the program, and then click the button. you’ll be greeted with the pop-up message applying to whatever date you picked. You can add your own dates to the list, and use it for whatever you’d like, really.

Visual Basic Functions and Sub-Procedures

Welcome back to our exploration of the wonderful world that’s called Visual Basic. Last time, we looked at arrays, and as promised, this week we’ll check out functions and sub procedures. First of all, let’s take a look at sub procedures.

Sub procedures

The definition of a sub procedures is a set of instructions that can be performed as a unit. (Those of you who remember DOS, think of it as a Batch-file. I.e. a list of commands to be executed after one another). This might surprise you, but you’ve already seen (and used) sub procedures.

Remember the article where we discussed Events? If we were to define what would happen if a user clicked our button, we’d code the button’s click event. The click-event is being handled by a sub procedure. Hence the fact your code will show this:

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

Notice how it says ‘sub’? That’s because the event is handled by the cmdPlus_Click sub procedure. The event itself is when the user clicks the button. The sub procedure stores the code that makes sure something happens.

You’re not limited to using the sub procedures used by the events of the controls you use. In fact, you can create your very own sub procedures like so:

[Private / Public] [Static] Sub subname [(Variables)]

Everything between the square brackets is optional. The only thing really required to declare a sub procedure is the ‘Sub’ keyword, and the name for the sub.

We’ve seen Public and Static before, but we haven’t seen Private. Private (in relation to sub procedures) usually means that the sub procedure is associated with an event. It’s actually a little more tricky than that, but for now, let’s leave it at that. We’ll mostly be using the Private way for declaring sub procedures for now.

Sub procedures do not return any values. Instead, they just run silently on the background, doing whatever you wanted them to do. An example could be writing a sub routine to loop through a text file and replace certain words with other words.

Creating this sub procedure would be done like this:

Private Sub ReplaceWords(strTextFile As String, strWordToReplace As String)

As you see, we demand two variables in this example. The first being the text file to use and the second being the string to search for. We declare the variable types as well, in order to save a few bytes of memory.

A way to run this sub procedure would be this:

Call ReplaceWords (“C:\test.txt”, “word”)

Functions

Functions are almost identical to Sub procedures, but differ in the fact that they do return a value.

Functions are declared like this:

[Private / Public] [Static] Sub subname [Variables] [As Type]

As an example, let’s write a function that accepts a value in Fahrenheit, and converts it to Celsius. The calculation for that would be Celsius = 5 * (Fahrenheit – 32) / 9:

Private Function Fah2Cel(ByVal Fahrenheit As Double) As Double
Fah2Cel = 5 * (Fahrenheit – 32) / 9
End Function

We would call this procedure the following way:

txtCelsius.Text = Fah2Cel(Val(txtFahrenheit))

As you can see, we assign the value of whatever is in our Fahrenheit TextBox as a parameter to the Fah2Cel function. The function then assigns the value of the converted Fahrenheit value to itself. finally, we assign that returned value to the TextBox txtCelsius.

Passing arguments

All right, so we saw we can pass parameters to functions and sub procedures. This can be done in two ways: By Value, or by Reference.

By Value means that a copy of the actual variable is passed. The variable itself will not change within the procedure or function.

By Reference means that the memory-address itself is passed. The variable will change if it is altered within the procedure or function it’s being passed to. By Reference is the VB standard.

Create a new standard Exe project. Now double click the form. This brings us into the Form_Load() sub procedure. We start by declaring two integer variables:

Dim intValue as Integer
Dim intReference as Integer

Next, we assign the two variables a value:

intValue = 10
intreference = 100

We’ll create a new procedure now:

Private sub AddTen (ByVal int1 As integer, ByRef int2 As Integer)
int1 = int1 + int1
int2 = int2 + int2

To show the values in between, we’re going to call in help from a debugging tool within VB (I’ll discuss debugging in a few weeks), the Immediate Window. We can use the Immediate window (among other things) to display values while an application is running. Type the following code within the procedure:

Debug.Print “Values of int1 in procedure: “; int1
Debug.Print “Values of int2 in procedure: “; int2

This will show us what the value of the two integers is after we multiplied the numbers within the procedure. Back to the Form_Load sub. Under the part where you set their values, insert the following code:

Call AddTen(intValue, intReference)

Debug.Print “Values of intValue after procedure: “; intValue
Debug.Print “Values of intReference after procedure: “; intReference

First we call the sub procedure, specifying the two parameters. intValue being the first one is passed by Value (See declaration of the function earlier. intValue is being passed as int1 to the procedure), while intReference is passed By Reference (int2)).

The second part is to show that the variables indeed changed. If you run the project now, the Immediate Window will show this:

Values of int1 in procedure: 20
Values of int2 in procedure: 200
Values of intValue after procedure: 10
Values of intReference after procedure: 200

Since intValue was passed by Value, the function altered a copy, and the actual variable itself remained the same. The intReference variable though changed not only within the sub procedure, but in the rest of the application as well.

Remember, if you want to pass variables to sub routines or functions, they become mandatory. Someone can’t call the function and not specify parameters. You can test this by substituting the code where we call the sub:

Call AddTen(intValue)

Run the program again, and you’ll get an error: “Compile Error; Argument not optional”. If you aren’t sure whether or not a user is going to give your function all the parameters it can take, you can make one or more of the arguments optional. To test this, add the Optional-keyword before the parameter you want to make optional (In our case, we just removed the second parameter to the function. Let’s make the second parameter optional):

Private sub AddTen (ByVal int1 As integer, Optional ByRef int2 As Integer)

Run the program again. The Immediate Window will show these values (above the old values. The Immediate Window keeps a ‘history’; it doesn’t empty itself every time):

Values of int1 in procedure: 20
Values of int2 in procedure: 0
Values of intValue after procedure: 10
Values of intReference after procedure: 100

As you can see, the value of int2 in the procedure is 0. That’s because we never specified a second parameter. One note about using the Optional keyword. If you have multiple parameters, first specify the mandatory parameters, and then the optional ones. As soon as you use the Optional keyword, VB demands all following parameters to also be Optional.

Lastly, we can also give an optional parameter a default value. This is a value that will be used if no parameter value is passed to the function. Let’s set one of those as well:

Private Sub AddTen(ByVal int1 As Integer, Optional ByRef int2 As Integer = 500)

Run the program again. The Immediate Window now shows:

Values of int1 in procedure: 20
Values of int2 in procedure: 1000
Values of intValue after procedure: 10
Values of intReference after procedure: 100

Visual Basic Arrays

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.

Visual Basic Variables and Constants

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.

Your First Visual Basic Program

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.

ActiveX Data Objects (ADO)

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.

Standard Controls in Visual Basic

Hello, and welcome back to our weekly series where we explore Visual Basic. Last week we looked at the environment in which we will be working. This week, we will discuss a few of the common controls that are installed automatically when you install Visual Basic. These include, but are not limited to the controls in the Toolbox.

We’ll discuss the controls in the Toolbox first. If we start at the upperleft, we see the pointer.

The pointer is not really a control. It basically is for moving or resizing objects on the form, dragging new objects from the toolbar to the form, and provides no other real functionality.

Right next to it is the PictureBox.

The Picturebox can be used to display graphics within your application. Graphics can be of various formats, sizes, etc.

The next control is a Label.

Labels are used to display static text in an application. With static, I mean that while the application is running, the text cannot be altered. (Well, technically it could be, but I can’t really see why anyone would do that).

There’s also the Textbox.

The Textbox is an inputbox. It can be used to gather information from a user (such as a filepath, or a name), or to display this information (Although that’s usually what labels will be used for).

Right under the label is the Frame.

Frames are used to group certain controls together. This can be both for functionality , or to make sure that a user will recognise that controls are related.

Next to the frame, we will see a control everyone has seen before: The Command Button.

A command button is used to make a user acknowledge to the program that he wants something to happen. Examples include submitting information, cancelling a process, or exiting a program.

The next control is the CheckBox.

A CheckBox has two values. Checked, or unchecked. Quite simple, huh? If you add multiple checkboxes together (For instance grouped in a frame), every single CheckBox can be either checked or unchecked.

Right next to the CheckBox is the OptionButton.

An OptionButton is similar to a CheckBox in that it can be either ‘checked’ (With an OptionButton, this is called ‘True’) or ‘unchecked (False). However, if grouped together, only ONE of the OptionButtons can be true. The OptionButton that previously was true will be false as soon as a new OptionButton is set to True.

Under the CheckBox you will find the ComboBox.

A ComboBox holds a list of items. It basically shows up as a pull-down menu. A user could select a single value from within the list. An example is when you’re asked to fill out which country you’re from online. You click on the pull-down menu, and will be offered a list of countries to select from. Users can also enter data themselves. (This depends on the style you define for the ComboBox).

Lastly, there’s the listbox.

A listbox ALSO lists items, but it will show more than one. It can be scrollable, and users could select multiple items from it at the same time. It’s just a different way of presenting data than the ComboBox.

For the sake of getting on with the article, I’m going to discuss just ONE more control. The other controls are ones that you won’t be using as much, anyway, and I want to delve a little deeper into the whole controls.

The last control we’ll discuss this week is the Timer.

A timer can be used can be used to make the application deal with pre-set intervals. This might sound vague, but we will see how it works soon enough.

Alright. So now we know what a few of the most-used controls can be used for. A control consists of a lot of back-ground code though. Let’s take a look at what makes up a control then.

We’re going to use the CommandButton as an example. Double- click on the Commandbutton icon in the Toolbox, and you’ll notice a button being added to the form (In the Form Designer).

By default, a control you add to your project will have a name assigned to it by Visual Basic. For a CommandButton, this name is Command1. You will also see that on the Button itself, Command1 will be showing. Hmmm. Doesn’t quite look right, does it? Let’s play around with the CommandButton’s Properties a bit.

Click the button, go to the Property Window, and scroll down to ‘Caption’. Caption is the text that will be shown on the button. If you look at Caption, you will see it’s current Value is: “Command1”. Let’s change that to “&Click Me”. (The Ampersand (‘&’) makes sure that the letter after the ampersand will be underlined. Looks neat, but doesn’t do much, does it? That’s where the second part of the Ampersand comes in. If a letter is underlined, you can hold down the ALT-key, and if you press the underlined letter (In this case: ALT+C), the program will act as if you clicked the button itself).

You’ll notice that while you are typing the caption, the button on the form will change it’s text to reflect whatever the current value for Caption is.

Another property of the CommandButton is ‘Backcolor’. Currently, the button’s BackColor’s value is set to: ‘&H8000000F&’. This seems like a real obscure value, but it’s just an internal number Windows uses for colors. Luckily, you will see the color matching the value on the left of the number. On the right you will notice a little triangle that points down (A combo-box). If you click on it, you will see a few pre-set values. The values you are presented with, are dependant on your color-scheme in windows. If you use the values offered in the ComboBox, you can make sure your application will be consistent with the color-schemes as the user always sees them. If not, you can still use a different color to enforce your own color scheme. Mind though that people have different tastes, and not everyone shares the idea that pink background with bright yellow text looks neat.

For now, change it to Desktop. (Notice you can pick your own color by clicking on the ‘Palette’ tab after folding out the ComboBox). You’ll notice that nothing happens. Hmm… What went wrong here?

Actually, nothing went wrong. Buttons are just meant to stay the way they are (color-wise), except for when another property is changed. That propety is ‘Style’. So locate Style, and select the dropdown list. You will see two options:

# 0 – Standard

# 1 – Graphical

Pick Graphical, and the color of the button will change to the background color of your desktop. Quite neat, huh?

There’s another way to change the style. Double click on ‘Style’. You’ll notice the value for Style changes again.

Well, that’s it for this article. Next week we will delve a little deeper into the properties, and use a few controls to build a small application. Untill then, feel free to fiddle around with the properties for various controls a little. That’ll get you known with their options. Be sure to check out the descriptions for the properties as well.

The Visual Basic Environment

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.

Installing Visual Basic

Hi again, and welcome to the second article in our Visual Basic section. Last week we looked at why you would want to use Visual Basic, what you could do with it, and we discovered a little about VB’s history.

This week, we will be comparing different versions of Visual Basic and then installing and configuring Visual Basic. Granted, this is not the most interesting part of VB, but a necessary one nevertheless. And in the end, it will definitely be worth it!

Visual Basic Editions
Like most Microsoft products, Visual Basic comes in a few different versions as well. This makes it easier (and usually cheaper) for developers of different backgrounds to choose an edition that is right for what they want to be doing with the software.

For Visual Basic, Microsoft offers three different versions:

  • Learning Edition: This edition is targeted at beginners. You get all the basic controls with this fully working version, so you can get easily started, and get used to VB.
  • Professional Edition: This version is meant for developers who have some experience working with Visual Basic. It includes the full Learning Edition, along with extra controls, wizards, and tools to access databases.
  • Enterprise Edition: The creme-de-la-creme of Visual Basic. This version targets advanced developers, offering even more controls, the option to create complex server-software, and provides a bunch of very useful tools.

Installing Visual Basic

I’ve used the Visual Studio 6.0 Enterprise Edition installation to take the included screenshots. It might be that you have some slightly different screens, but the basics ought to be more or less the same.

The very first screen (after you run the setup.exe program) is the introduction screen. As you can see, Microsoft doesn’t leave us a heluva lot of options there. you can read the readme, and that’s about it. So just click Next.

The next form is the end user agreement. If you want to read all of it, by all means, go ahead. It’s just some legal mumbo jumbo. When you’re ready to proceed, check ‘I accept the agreement’, and then on next.

The next screen is where you enter the registration number, and your own name, as well as the name of your company.

About now, you will be prompted for which form of installation you want. For this example, we’ll pick custom. This means we get to choose which parts of Visual Studio we want to install.

You’ll be prompted for the path where to install the common Visual Studio files now. Even if you DO pick a different path, parts will still be installed on your standard drive. (usually C:). You’ll also see how much space you will need. I’d recommend keeping the default installation path.

The next two screens aren’t really interesting. Just click on, and you will see the Installation guide will search your system for installed components.

If you picked ‘custom’ as the installation version earlier, you will now be asked to select which particular components to install. Feel free to install whatever you want, but for this guide, we’ll select the following components.

* Visual Basic
* Active X
* Data Access
* Enterprise Tools
* Graphics
* Tools

A gray checkbox means that not all components are installed. To select those anyway, click on ‘change option’ button, and select all options in there. (There are some sublevels). Just select everything within the above mentioned components for now. We’ll get to what they are for in future articles.

You can see brief descriptions of what the individual options are in the Description frame. After you click Next, the REAL installation process will commence, and Visual Basic and the selected components will be installed onto your system.

Next, you’ll be prompted to reboot. Close everything, and let the Installation process reboot your computer. (This is necessary to remove all references of old components, and make sure you load the new ones into memory. This is also the reason why most install-programs tell you to close down all other applications during the install).

Next, you’re prompted for an (optional) installation of the Microsoft Developer Network Library (MSDN) now. (Think of it as Technet for Developers). I highly recommend doing so, as the MSDN provides a great deal of information and help.

You can optionally install some other software as well. In my case, it only showed Installshield. Select it if you want. After completing this step, you will have finished the installation process.

Basically, you have a clean installation of Visual Basic installed now. But we’re not there just yet. Before you start patting yourself on the back, there’s two other things I’d recommend installing.

The first is the latest Service Pack (Basically some patches, updates, and fixes to various problems). You can find the latest service packs here. Be sure to download this, even if you don’t want to install Visual Basic just yet. I suspect Microsoft is discontinuing all support for Visual Basic in the near future, in favour of their new .NET strategy. (They have already started doing this).

The link above is part of the online MSDN, by the way. The online version of msdn is available at http://msdn.microsoft.com. You can always find the latest updates and information there. Another nifty site for developers is the Microsoft Support Knowledgebase. This can be found at http://support.microsoft.com. The knowledgebase holds information about patches, updates, bugs, known errors, and frequently asked questions.

The second thing I’d recommend installing is a patch that solves a few license issues with components. (Don’t worry about this yet. The standard installation prevents you from using some components. This fix will solve that). The fix can be found here.