The If and Select statements control Visual Basic logic flow. These statements are a part of any programming language, and they are used often to control output and input depending on a defined set of criteria. In this article, we'll show you the syntax for these statement using Visual Basic and what you can do with them to control logic flow throughout your programs.
The If, Else, Then Statement
The If statement is probably the most common control flow element. However, the If statement doesn't loop through any statements. It evaluates the statement within its logic condition. If the result is true, then the statements within the If structure are executed. If the result is false, then the next statements outside of the If statement are executed.
Let's first take a look at the basic structure of an If statement.
If <condition> Then
' Code execution here
End If
The above code is the basic If statement without any additional Else conditions. We'll discuss Else after we review a very basic If statement.
The If condition is evaluated, and if the condition evaluates to true, then the code within the statement executes. The End If statement tells the compiler where the If statement ends.
Let's look at a real-world example.
Dim mystring As String
mystring = "Test"
If mystring = "Test" Then
MsgBox "The condition evaluates to true."
End If
In the above example, the mystring variable is created and assigned the value of "Test." The If statement then evaluates if the string equals "Test." Since the string does indeed equate to "Test," the result of the condition is true. The If statement's code then executes, which means that a message box displays on the screen that says "The condition evaluates to true."
When working with strings, the values you evaluate are case sensitive. Let's take a look at another example.
Dim mystring As String
mystring = "test"
If mystring = "Test" Then
MsgBox "The condition evaluates to true."
End If
In the above code, we only changed the mystring value to "test" instead of "Test." Although the string is still the same, the binary value of a capital T is different than a lowercase t. Because string comparisons are case sensitive, the above If statement's condition evaluates to false. The result is that no message box displays, because the code within the If statement never executes.
The If statement also works with numeric values. Take a look at the following code that switches the string variable for an integer.
Dim mynumber As Integer
mynumber = 5
If mynumber > 0 Then
MsgBox "The condition evaluates to true."
End If
We used a comparison operating in this If statement. A comparison operator evaluates if a value is greater than or less than another value. In this example, we identify if the mynumber value is greater than 0. Since we've assigned the value of 5 to the mynumber variable, we know that the If statement condition evaluates to true.
In many cases, you have more than one condition to evaluate. You could write several If statements, or you could incorporate an ElseIf or Else statement. Let's first take a look at the Else statement.
Dim mynumber As Integer
mynumber = 5
If mynumber < 0 Then
MsgBox "The condition evaluates to true."
Else
MsgBox "The Else condition executed."
End If
In the above example, we changed the condition in the main If statement to less than rather than greater than. The condition evaluates if mynumber is less than 0. Since we assign mynumber a value of 5, we know that this condition will evaluate to false.
We added an Else statement to the If statement. Since the condition evaluates to false, the next option in the If statement is Else. This statement says "If the condition evaluates to false, code execution falls to the Else statement regardless of the value."
In the example above, a message box displays "The Else condition executed" to the user.
Let's go back to the original If condition we had previously.
Dim mynumber As Integer
mynumber = 5
If mynumber > 0 Then
MsgBox "The condition evaluates to true."
Else
MsgBox "The Else condition executed."
End If
We've changed the condition statement to evaluate to true. In the above code, since the original If statement is true, the Else statement no longer executes. In the above example, a message box displays "The condition evaluates to true" to the user.
With the If statement, you can add several more conditions to control logic. For instance, suppose you have three values in a dropdown box. You want to display a message to the user depending on what dropdown option is selected. Let's take a look at an If statement that includes an ElseIf clause.
If mynumber = 0 Then
MsgBox "You selected 0."
ElseIf mynumber = 1 Then
MsgBox "You selected 1."
Else
MsgBox "You selected nothing."
End If
In the above code, we removed the statically defined value of 5. We assume that the mynumber variable is filled when the user makes a selection from the dropdown menu. If the user selects 0, then the first If block executes. If the user selects 1, then a message displays the number selected. If any other value is selected, the system tells the user that nothing was selected. Remember that the Else statement is a catch-all. It covers any other value not covered in the Else statements.
You can add as many ElseIf statements as you need, but it's recommended that you should use a separate subroutine when it becomes too many. Two or three ElseIf statements are reasonable, but too many can become too difficult to maintain and harder to troubleshoot should your logic have a bug.
The Select Case Statement
The If statement is the most common workflow condition, but Visual Basic also lets you use the Select Case statement. This statement is used when you have several possibilities for one variable's value. Remember we said that too many ElseIf statements could make your code difficult to troubleshoot and maintain. If you have too many of them and you don't want to use a subroutine, a Select Case statement is an option.
Let's first take a look at the basic syntax for the statement.
Select Case <variable>
Case <condition>
' Code to execute here
End Select
In the above template, the Select Case statement first defines the variable you want to evaluate. You then see a Case statement. You can have as many Case statements as you need to identify a value. We only display one, but you can have 10 of them if needed. If the condition in the Case statement evaluates to true, then the code within the statement is executed.
Let's take the example of the dropdown menu we used in the previous section. Suppose you have 3 values in the dropdown. You want to display a message depending on what the user selects. You could use an If statement with several ElseIf statements, but a Case statement is more efficient and requires less code. Let's take a look at the same logic that we used in the previous section only with a Select Case statement instead.
Select Case mynumber
Case 0
MsgBox "You chose 0."
Case 1
MsgBox "You chose 1."
Case 2
MsgBox "You chose 2."
Case 3
MsgBox "You chose 3."
Case Else
MsgBox "You chose nothing."
End Select
As you can see, this code is much easier to read. The Case statements cover each value in the dropdown menu and the right message is sent to the user depending on the option selected. Notice that the Select Case statement also has an Else option. The Case Else option is also a catch-all just like the Else statement used in an If condition. If none of the Case statements match the value of mynumber, then the Case Else code is executed.
You can also use both the If statement and the Select Case statement to fill data within a spreadsheet. In most cases, you'll use these statements to change the data within a cell depending on the user's input. Let's take a look at the same Select Case statement only with cell data used.
Select Case mynumber
Case 0
Range("A1").Value = mynumber
Case 1
Range("B1").Value = mynumber
Case 2
Range("C1").Value = mynumber
Case 3
Range("D1").Value = mynumber
Case Else
MsgBox "You chose nothing."
End Select
In the above code, a named range is filled with the value of mynumber depending on its value. In our example, mynumber contains a value chosen by the user from a dropdown menu. When the user chooses the value, it's then assigned to a named range. The named range isn't the same or we would not need to evaluate the value. In this example, we only fill a named range for a given selection. This is the type of logic flow that changes the spreadsheet output depending on user input. You will need to fill data depending on input in most of your VB applications.
In this example, we showed you how to change data output using different logic control structures. These structures provide you with numerous ways to create dynamic spreadsheets that present data based on any user input.
A1 vs. R1C1 References
If you're new to Excel, you probably don't know the difference between A1 and R1C1 styles. The A1 style is the most common and the default in Excel. It's the absolute reference to cell in a formula. The R1C1 is the absolute reference. You can also use relative references using formulas. In this lesson, we'll discuss the two reference types, formulas and we'll take a look at Arrays.
Toggling Style References
By default, Excel uses the A1 reference style. With an absolute reference, you use static variables in your formulas. For instance, when you create a formula to add two values from cells A1 and A2, your formula would look like the following.
=A1+A2
Note that the equal sign is set to indicate to Excel that you want to use a formula not the literal text in the cell. You can escape this default by placing an apostrophe in front of the equal sign should you want to print the text instead of calculating from the formula.
In the above example, Excel adds the two values in cells A1 and A2 and displays the result in the current cell. This type of referencing is called absolute referencing.
Let's assume that the currently active cell is A3 and we want to add cells A1 and A2. Your relative reference formula would look like the following.
=RC[-2]+RC[-1]
In the above formula, we are adding two numbers relative to the currently active cell, which we've indicated is A3. We are in the A row, so we can't move up, so the formula references the cell 2 places left from A3 and the cell 1 place left of A3.
Before you can use relative referencing, you first need to turn it on in your Excel settings. Follow these steps to turn on relative referencing.
1. Click the File tab or the Microsoft Office button if you're using Excel 2007.
2. Click "Options" or "Excel Options" if you're using Excel 2007.
3. Click the "Formulas" option in the left panel.
4. Check the box labeled "R1C1 reference style."
5. Click "OK" to save your settings.
Once you have relative referencing turned on, you can use it within your Excel spreadsheets.
Using Formulas
Formulas are a big part of an Excel spreadsheet. Even simple sheets have some kind of formula within them. Formulas add, subtract, multiply, divide and do several other mathematical calculations for a user. Even if you're coding macros, you still must know how to work with formulas. In some cases, you need to add a formula dynamically to a spreadsheet using Visual Basic.
We saw a simple formula in the previous section that added two cells. You can also subtract, divide and multiply in a formula. Let's take a look at the four basic types of calculations in an Excel formula.
A1 Style
=A1+A2
=A1*A2
=A1/A2
=A1-A2
R1C1 Style
=RC[-2]+RC[-1]
=RC[-2]-RC[-1]
=RC[-2*RC[-1]
=RC[-2]/RC[-1]
Simple calculations are easy to do once you understand the characters. The asterisk is for multiplication, the forward slash is for division, the plus sign is addition, and then the hyphen is for subtraction.
In the previous examples, we used simple formulas with two cells. What if you want to add a range of cells? You can perform calculations on multiple cells using functions. Functions are common with formulas, because they reduce the amount of code needed to perform the calculation.
Let's assume that you want to add 100 cells and display the sum. You don't want to statically define all of those cells. You can use a range of cells or use the SUM function. Let's take a look at an example of the SUM function.
=SUM (A1:A100)
You can also specify cells within a row or column. Take a look at an example of this type of calculation.
=SUM (A1, A2, A3, A10)
In the first formula, cells A1 to A100 are added and the result is displayed in the cell where the formula is created. In the second example, four cells are summed together and the result displayed in the cell where the formula is created.
The COUNT function is another common one for Excel formulas. The COUNT function counts the number of cells you have in a range. It's different from the SUM function that adds a group of values. The COUNT function just counts the number of cells. It also ignores any blank cells.
Let's take a look at an example.
=COUNT (A1:A100)
In the above example, there are a 100 cells in the range.
Excel also provides an average function. The AVERAGE function adds up a list of cell values and then averages these values. It's no different than any other averaging function in other programming languages. Let's take a look at an example.
=AVERAGE (A1:A100)
We used the same range as the other examples. The above formula adds the values of A1 to A100 and then divides the number by the number of cells added, which is the average. When you change the values in any of these cells, the formula and function will automatically re-calculate the numbers and provide the new value in the cell where the formula is created.
When working with spreadsheets that have heavy analytics or financial focus, you often need to know the minimum and the maximum number in a range. Let's take a look at the MIN and MAX functions.
=MIN (A1:A100)
=MAX (A1:A100)
The first MIN function finds the lowest number in the range A1 to A100. The second MAX function finds the highest number in the A1 to A100 range. These functions are good for revenue or analytical spreadsheets where each value can indicate an increase or decrease in income trends.
There are several formula functions that you can use. You can find a list of them and use Excel's visual tools to create formulas without manually typing them in your spreadsheet. To see a list of functions, click the Fx button at the top of the spreadsheet. This will open a dialog window that gives you a list of the pre-made Excel functions.
Introduction to Arrays
In this section we'll give you a basic introduction to the way they work and how you use them in your Visual Basic applications. Arrays can be more complicated to learn, so they are usually saved until last with programming tutorials.
Arrays are variables that can contain more than one value. The values are stored in its own index. Arrays can store thousands, hundreds or even just one value. When an array has no value, none of its indexes can be references because they are all null. If you try to use an index that does not exist in an Array variable, your programs will throw an error.
Arrays work closely with loops. Know that loops are how a program can dynamically assign or retrieve an array with 1 or thousands of indexes.
When you create an array in Visual Basic, you must define a variable name and a scope. The scope is the number of values that will be stored in the array. The number of values stored is the number of indexes included in the array when the program allocates memory for the object.
Let's take a look at an example.
Dim mystring (0 to 2) As String
In the above code, an array variable named mystring is defined. Notice that we give the array a data type. All arrays have a data type, which defines the type of data that it can contain. In this example, we've defined a string array. A string array can only contain a list of string values. If you attempt to store other data types such as an integer or decimal, your Visual Basic application will give you an error.
The parenthesis contains the index numbers for the array. We define this array as a string of values with an index from 0 to 2. Therefore, the array can contain 3 values. The index number starts at 0, so the values are assigned to index numbers 0, 1, and 2.
Once you define the array, you can now assign values to it. Let's take a look at code that fills the mystring array with some values.
Dim mystring (0 to 2) As String
mystring (0) = "red"
mystring (1) = "blue"
mystring (2) = "black"
In this example, we've filled the array with some string color values. Notice that each time we assign a value to the array, we reference its index. Since we can fill the array with 3 values, we've referenced indexes 0, 1, and 2 for each color. Had we tried to reference value 3, the program would have given us an error. If we use the same index to assign a different value, this value overwrites the current value in the index.
You can re-dimension an array to dynamically add a new index to it. Let's say that you defined an array that could hold 3 color values, but you later realize that you need to store 4 values. You can use the ReDim statement to change the number of indexes an array can contain. Let's take a look at an example using the mystring array.
Dim mystring (0 to 2) As String
mystring (0) = "red"
mystring (1) = "blue"
mystring (2) = "black"
ReDim Preserve mystring (0 to 3) As String
mystring (3) = "white"
We used the same mystring array and then re-dimensioned it to contain a new value. The "Preserve" keyword tells the compiler to preserve the current values in the array, so you don't lose them during the process.
After the array has been re-dimensioned, you can add a new value to it. You can add several new indexes using a ReDim statement. This statement is usually executed when you need to dynamically define an array and you don't know the number of elements you need to store.
In this article, we discussed formulas, functions and an introduction to array variables. You can use this in the basic Excel IDE or in your Visual Basic programming. You can assign formulas dynamically using macro code or you can create a formula that is always present in a specific cell.
