Introduction to VBScript and VBA

What is VBScript?

VBScript is a subset of the Visual Basic Programming language. The result of the slimming down process is a very small language that is easy to use. JavaScript can do all that VBScript can do and far more. So why use VBScript instead of JavaScript? Because VBScript is much easier to learn than JavaScript.

What is VBA?

Visual Basic for Applications (VBA) is another subset of the Visual Basic Programming language for use with Microsoft Word, Excel, Access etc. While it contains a good many freatures not supported by VBScript, the basic syntax, or construction of the language, is very similar.

Syntax

Variables

A variable is a virtual container in the computer's memory that's used to hold information. In concept, it is much the same as a notepad. You can jot down information on the page of a notepad and return to that specific page later to remember what you wrote or modify the information. A computer program can store information in a variable and then access that information later by referring to the variable's name.

How Do I Create a Variable?

When you create a variable, you have to give it a name. That way, when you need to find out what's contained in the variable, you use its name to let the computer know which variable you are referring to. You have two ways to create a variable. For example, you want to create a variable called Quantity, you would enter:

Dim Quantity

To assign a value to the variable quantity use:

Quantity = 5

Using Control Structures to Make Decisions

A control structure is a combination of keywords in code used to make a decision that alters the flow of code the computer executes.

If…Then

The first control structure you should know about is If…Then. The syntax for this control structure is given as:

If condition = True Then
...the code that executes if the condition is satisfied
End If

where condition is some test you want to apply to the conditional structure. If the condition is true, the code within the If and End If statements is executed. If the condition is not true, the code within these statements is skipped over and does not get executed.

If…Then…Else

What if you want to do one thing if the condition is true but another thing if the condition is false? You could use two statements:

If condition = True Then
Msgbox "The condition is true!"
End If
If condition = False Then
Msgbox "The condition is false!"
End If

but wouldn't it be nice if you didn't have to repeat the test Fortunately, you have another control structure available to you in VBScript that makes this process easier. The control structure, called If…Then…Else, is represented as

If condition = True Then
...this is the code that executes if the condition is satisfied
Else
...this is the code that executes if the condition is not satisfied
End If

ElseIf

What if you had a few other cases you wanted to test? You're in luck: You can do as many tests as you want by simply placing ElseIf statements between the first If statement and the End If statement. The syntax of such a structure looks like this:

If condition1 = True Then
...the code that executes for condition1
ElseIf condition2 = True Then
...the code that executes for condition2
ElseIf condition3 = True Then
...the code that executes for condition3
Else
...the code that executes if none of the above are true
End If

Select Case

What if you have to perform a large number of tests on the same expression? The Select statement often makes your code easier to read and interpret than would a long list of Else and Else If statements. The Select Case structure is defined as follows:

Select Case test_expression
Case expression-1
...this is the code that executes if expression-1 matches test_expression
Case expression-2
...this is the code that executes if expression-2 matches test_expression
Case expression-3
...this is the code that executes if expression-3 matches test_expression
Case Else
...this is the code that executes if expression-n matches test_expression
End Select

Consider an example in which you want to print a message to the user based on what state the user lives in:

Select Case State
Case "Michigan"
Message = "Michigan is a wonderful state to visit if you enjoy fresh-water lakes."
Case "Virginia"
Message = "Visit the Commonwealth for a wide variety of historical landmarks and beautiful mountain-scapes."
Case "Arizona"
Message = "Arizona is a wonderful getaway for those who love heat with low humidity" Case "Colorado"
Message = "Colorado is almost unsurpassed for its majestic mountains and rivers."
Case Else Message = "No specific information is available about this state."
End Select

Using Control Structures to Make Code Repeat

On occasion, you will need to write code that repeats some set of statements. Oftentimes, this will occur when you need to perform some calculation over and over or when you have to apply the same calculations or processing to more than one variable. This section shows you all the control structures you can use in VBScript to control code in your program that repeats.

For…Next

The first structure is often referred to as the For…Next loop. The syntax for this structure is

For counter = start to finish
...code that gets repeated
Next

For Example the following code will display a message box on the screen displaying the current value of x as it increments from 5 to 50.

For counter = 1 to 10
x = x + 5
msgbox x
Next

Do While…Loop

The basic syntax for this structure is

Do While condition
...code within the loop goes here
Loop

where the condition is either true or false. As long as the condition is true, the code within the loop gets executed. Once the condition becomes false, the loop stops and the code after the loop is executed. The only way for the program to break out of the loop is if the condition becomes false or if an Exit Do statement is encountered somewhere inside the loop. Consider the following example:

Again = True
DoubleIt = 1

' Keep doubling the number as long as the user desires

Do While Again = True
' Show current results and prompt to see if we should continue
If MsgBox("Current total is " & DoubleIt & ". Double it again ?", vbYesNo) = vbYes Then DoubleIt = DoubleIt * 2
Else Again = False
End If
Loop

In this example, the first line of code sets the variable that gets tested in the loop equal to true. The second line sets the initial value of the variable that gets doubled to one. Setting the loop variable ("Again") equal to true allows the loop to begin running because the third line says, "If Again is true, enter the loop." The first statement in the loop displays the result to the user.

Because nothing has been doubled yet, the result is one-the initial value of the DoubleIt variable. When the results are displayed, the user is asked if they want to continue. If they choose to go forward, the variable DoubleIt is multiplied by two. When the program hits the Loop instruction, it will return to the top of the loop and once again check to see if the condition is set to true. Because the condition has not changed, the loop executes again. It will continue to execute until the user chooses not to continue. Once that happens, the variable Again is set to False.

Now, when Loop is reached, the code swings back up to the top of the loop and evaluates the condition once more. This time, the condition is false, so the loop does not execute again. The code moves on beyond the loop.

Do Until…Loop

This is very similar to the Do While…Loop except that rather than perform the operations within the loop while the condition is true, it executes the operations inside the loop until the condition is true. The Do Until…Loop structure takes the form

Do Until condition
...code within the loop goes here
Loop

Visual Basic for Applications Features Not In VBScript

Related Links

Microsoft's scripting pages.