To assign a value to a variable, use an equal sign after the variablename, followed by the value, The following statement assigns the value SS to the variable i ntDaysExpi red:
intDaysExp;red – SS
The following statement assigns the value “Ferndal e” to the variable strLocation:
strLocat;on – ‘Ferndale’
VBA provides a full complement of operators-items used for comparing. combining, and otherwise working with values. The operators fall into four categories:
Arithmetic operators (such as + for addition and / for division) for mathematical operations
• Logical operators (such as Or) for building logical structures
• Comparison operators (such as – and >-) for comparing values
• String operators (such as & for joining two strings)
Table 24.2 lists the operators in their categories, with brief examples of each and comments on the operators that are not self-explanatory.
Structures Used in VB Code
In this section, we’ll briefly discuss some of the programming structures that you can use in Visual Basic code. These structures fall into three major categories:
• Sequential structures, in which one command follows another and all commands are executed.
• Conditional structures, in ‘which a decision is made to execute one set of commands based on the value of a variable.
• Looping structures, in which one or more commands are repeated.
Sequential structures are the simplest structures. The procedure executes each command, in order, until it hits the End Sub command. The sample statements that have appeared in this chapter and the macros we created in Chapter 22 used sequential structure
Using Conditional Structures
Conditional structures are used to direct program flow based on one or more conditions. There are two major types of conditional structures: If (in a variety of flavors) and Select Case
VBAsupports three types of If statements:
• If /Then/Else
• If/Then, Elself/Else
VBA also supports the IIF (immediate if) statement used in Microsoft Access
The If/Then Statement
The If/Then statement is the simplest If statement, and is the same statement used in Excel, Access, and a variety of programming languages.
Here’s the syntax of the If/Then statement:
If condition Then
(commands executed when condition – True)
The condition is a test constructed using operators and variables or constants. If the condition is true, the lines of code between the If and End If statements is executed once. If the condition is false, VB skips to the command line that follows the
End If statement
With the If /Then/Else statement, you can execute
one of two sections of code, depending on the condition. The syntax is as follows:
If condition Then
_ (commands to be executed when condition True)
(commands to be executed when condition 2 False)
With this version of If, VB executes the first group of commands if the first condition is true. If the first condition is false, it checks to see whether the second condition is true. If it is, it executes the second group of commands. You don’t have to stop here.
You can use Else If again and test a third condition, a fourth condition, and so on. If none of the conditions is true, VB executes the commands following the Else statement and prior to the End If. As soon VB tests a condition that is True, it executes the commands following the condition, then skips to the command following the End If statement. No other conditions are tested
Select Case Statements
Select Case is much like If /Then …Else If /Else, and is used to execute one or more commands based on one value. Here’s the syntax for Select Case:
Select Case expression
The Select Case structure begins with a Select Case statement and ends with an End Select statement. Expression is a value or phrase used to determine which case gets executed. VBevaluates each Case statement in turn. As soon as finds a matching case, it executes the commands for that case, then skips to the command line following the End Select statement.
VBA supports a bevy of looping structures used to repeat a set of commands. The two families of looping structures are For loops and Do loops. For loops execute a series of commands a set number of times based on the value of a counter that’s incremented by VB Do loops rely on a condition and check to see whether it’s true or still true before the commands in the loop are executed. We’ll discuss four different looping structures commonly used in VBA
• Do While
• Do/Loop Whi1e
• Do Until
A For/Next loop repeats for a specified number of times, controlled by a counter variable. The syntax for a For/Next loop is
‘For counter – start To end [optional Step step value
(comm, ands to be executed if counter <. end)
The counter is a numeric variable. When VBhits the For line, it checks the value of the counter and compares it to the End value:
• If the current value of counter is less than or equal to the end value, VBA executes the statements in the loop. When it hits the Next line, it increments the counter and loops back to the For command.
• If the current value of counter’ is more than the end value, VBA skips to the command line following the Next command. By default, the counter value is Incremented by 1. To-have ‘VB increment by a different positive or negative value, include the optional Step keyword, followed by the increment you want to use:
For int Task Count – 10 to 50 Step 5
If the Step increment is a negative number, VBsubtracts the increment from the -counter and continues executing the commands in the loop until the counter value is less than the end value:
For intTaskCount – 50 to 10 Step -5
There are four types of Doloops: Do While. .. Loop loops, Do. .. Loop While loops,
Do Until Loop loops, and Do. .. Loop Until loops. In the following sections,
we’ll look at each type in turn.
Do Until Loops
The Do Until structure is the opposite of the Do While structure.
With Do Until, commands in ,the loop are executed until the condition is met:
Do Until condition
(commands to be executed if condition is not true)