Decision Making in Excel VBA

In Excel VBA, we can write decision-making codes based on certain conditions. There are two methods to write decision-making code in Excel VBA, using the If…..Then…Else syntax or using the Select Case…End select syntax.

The syntaxes are exactly the same as VB6.  You need to use both the conditional operators and the logical operators together with If…The…Else statements and Select Case statements.

For example

If sale>1000 Then

bonus=50*sale

Else

Bonus=0

End If

For more information, read the articles below:

Excel 2010 VBA Lesson 10: Using If…Then…Else

Excel 2010 VBA Lesson 11: Using Select Case

Excel 2010 VBA Lesson 17: Excel VBA Methods

 [Lesson 16]<<[Table of Contents]>>[Lesson 18]

17.1: Methods

An Excel  2010 VBA object contains methods. A method usually performs certain operations. For example, ClearContents is a method that clears the contents of a cell or a range of cells.

Example 17.1

‘Clear contents from cells A1 to A6
Private Sub CommandButton1_Click()
 Range(“A1:A6”).ClearContents
End Sub

For example, you can let the user select his/her own range of cells and clear the contents by using the InputBox function, as shown in Example 17.2



Example 17.2

Private Sub CommandButton1_Click()
Dim, selectedRng As String
 selectedRng = InputBox(“Enter your range”)
 Range(selectedRng).ClearContents
End Sub

In order to clear the contents of the entire worksheet, you can use the following code:

Sheet1.Cells.ClearContents

However, if you only want to clear the formats of an entire worksheet, you can use the following syntax:

Sheet1.Cells.ClearFormats

In order to select a range of cells, you can use the Select method. This method selects a range of cells specified by the Range object. The syntax is as follows:

Range(“A1:A5”).Select

Example 17.3

Private Sub CommandButton1_Click()
 Range(“A1:A5″).Select
End Sub

Example 17.4The following example allows the user to specifies the range of cells to be selected.

Private Sub CommandButton1_Click()
Dim selectedRng As String
 selectedRng = InputBox(“Enter your range”)
 Range(selectedRng).Select
End Sub




To deselect the selected range, we can use the Clear method.

Range(“CiRj:CmRn”).Clear

Example 17.5

In this example, we insert two command buttons. The first button is used to select the range while the second button is to deselect the selected range.

Private Sub CommandButton1_Click()
 Range(“A1:A5″).Select
End Sub

Private Sub CommandButton2_Click()
 Range(“A1:A5″).Clear
End Sub

Instead of using the Clear method, you can also use the ClearContents method.
Another very useful method is the Autofill method. This method performs an autofill on the cells in the specified range with a series of items. The items may include numbers, days of the week, months of year and more. The syntax is

Expression.AutoFill(Destination, Type)

*Expression can be an object or a variable that returns an object. Destination means the required Range object of the cells to be filled. The Destination must include the source range. Type means the type of series, such as days of the week, the month of year and more. The AutoFill type constant is something like xlFillWeekdays, XlFillDays, XlFillMonths and more.


Example 17.6

Private Sub CommandButton1_Click()
 Range(“A1”)=1
 Range(“A2”)=2
 Range(“A1:A2″).AutoFill Destination:=Range(“A1:A10″)
End Sub

In this example, the source range is A1 to A2. When the user clicks on the command button, the program will first fill cell A1 with 1 and cell A2 will 2, and then automatically fills the Range A1 to A10 with a series of numbers from 1 to 10.

Example 17.7

Private Sub CommandButton1_Click()
 Cells(1, 1).Value = “monday”
 Cells(2, 1).Value = “Tuesday”
 Range(“A1:A2″).AutoFill Destination:=Range(“A1:A10″), Type:=xlFillDays
End Sub

Example 17.8

This example allows the user to select the range of cells to be automatically filled using the Autofill method. This can be achieved with the use of the InputBox. Since each time we want to autofill a new range, we need to clear the contents of the entire worksheet using the Sheet1.Cells.ClearContents statement.

Private Sub CommandButton1_Click()
Dim selectedRng As String
 Sheet1.Cells.ClearContents
 selectedRng = InputBox(“Enter your range”)
 Range(“A1″) = 1
 Range(“A2″) = 2
 Range(“A1:A2″).AutoFill Destination:=Range(selectedRng)
End Sub



 [Lesson 16]<<[Table of Contents]>>[Lesson 18]

Excel 2010 VBA Lesson 13: Do…Loop

 [Lesson 12]<<[Table of Contents]>>[Lesson 14]

In this lesson, you will learn about another looping method know as the Do Loop in Excel 2010 VBA.

There are four ways you can use the Do…Loop, as shown below:

(i) Do...Loop While

(ii) Do until...Loop

(iii) Do while...Loop

(iv) Do...Loop until




We shall illustrate the four structures of Do…Loop in the following examples:

Example 13.1

Arranging numbers in ascending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = counter
Loop While counter < 10
End Sub




In this example, the program will keep on adding 1 to the preceding counter value as long as the counter value is less than 10. It displays 1 in cells(1,1), 2 in cells(2,1)….. until 10 in cells (10,1).

The Output

vba2010_fig13.1

 

Figure 13.1

Example 13.2

Arranging numbers in descending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do Until counter = 10
 counter = counter + 1
 Cells(counter, 1) = 11 – counter
Loop
End Sub

In this example, the program will keep on adding 1 to the preceding counter value until the counter value reaches 10. It displays 10 in cells(1,1), 9  in cells(2,1)….. until 1 in cells (10,1).

vba2010_fig13.2



Example 13.3

Private Sub CommandButton1_Click()
Dim counter , sum As Integer
‘To set the alignment to center
 Range(“A1:C11″).Select
With Selection
 .HorizontalAlignment = xlCenter
End With

Cells(1, 1) = “X”
Cells(1, 2) = “Y”
Cells(1, 3) = “X+Y”

Do While counter < 10
 counter = counter + 1
 Cells(counter + 1, 1) = counter
 Cells(counter + 1, 2) = counter * 2
 sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)
 Cells(counter + 1, 3) = sum
Loop
End Sub

In this example, the program will display the values of X in cells(1,1) to cells(11,1). The value of Y is X*2 and the values are displayed in column 2, i.e. from cells(2,1) to cells(2,11). Finally, it shows the values of X+Y in column 3, i.e. from cells(3,1) to cells(3,11)

The Output

vba2010_fig13.4



 [Lesson 12]<<[Table of Contents]>>[Lesson 14]

Excel 2010 VBA Lesson 12: Looping

 [Lesson 11]<<[Table of Contents]>>[Lesson 13]

Excel 2010  VBA  allows a procedure to repeat many times until a condition is fulfilled. This is called looping.  There are three kinds of loops,  the For…….Next loop, the While….Wend loop and the Do…Loop .

12.1 One level For…..Next Loop

The one level For….Next Loop event procedure is written as follows:

For counter=startNumber to endNumber (Step increment)
 One or more VB statements
Next

*We shall deal with the For………Next loop and the While….Wend loop first and the Do….Loop in the next lesson.



Example 12.1

In this Excel 2010 VBA  program, you place the command button on the spreadsheet then click on it to go into the Excel 2010 VBA editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3……until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents a row and j represent a column. We shall learn more about cells in future lessons.

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
 Cells(i, 1).Value = i
 Cells(i,2).Value=i^2
Next
End Sub

* i^2 means i2




The Output is shown in Figure 12.1

vba2010_fig12.1

Figure 12.1

Example 12.2

For……..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.

The code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10  Step 2
 Cells(i, 1).Value = i
Next
End Sub

The Output

vba2010_fig12.2

Figure 12.2




12.2 Nested For….Next Loops

Nested For…….Next loops mean there more than one For…Next Loops are nested within the first level For…..Next loop. The structure is as follows:

For counter=startNumber1 to endNumber1
For counter=startNumber2 to endNumber2
For counter=startNumber3 to endNumber3
 One or more VB statements
Next
Next
Next

 

Example 12.3

In this example , we use  nested loop to insert the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ……….until cells(10,5).

The code 

Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
 Cells(i, j).Value = i + j
Next
Next
End Sub

The Output

vba2010_fig12.3

Figure 12.3

12.3  While….Wend  Loop

The structure of a While….Wend Loop is very similar to the Do Loop. it takes the following form:

While condition
 Statements
Wend

The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let’s examine the program listed in example 12.4.

Example 12.4

In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
i = 1
While i <= 15
 Cells(i, 1) = i ^ 2
 i = i + 1
Wend
End Sub

The Output

vba2010_fig12.4

Figure 12.4



 [Lesson 11]<<[Table of Contents]>>[Lesson 13]

Excel 2010 VBA Lesson 11: Using Select Case

 [Lesson 10]<<[Table of Contents]>>[Lesson 12]

In the previous lesson, we have learned how to write Excel 2010 VBA  code using If….Then….Else statement. However, for multiple options, the If…Then…Else structure could become too bulky and difficult to debug. Fortunately, Excel  2010 VBA provides another way to handle complex multiple-choice cases, that is, the Select Case…End Select decision structure. The structure of Select Case…End Select is as follows:

Select Case variable
Case value 1
Statement
Case value 2
Statement
Case value 3
Statement
.
.
Case Else
End Select




We shall demonstrate the use of Select Case…End Select in the following examples:

Example 11.1

In this example, we want to automatically assign a remark in relation to a certain examination grade. For example, if the grade is A, we shall assign remark as High distinction, A- as distinction and so forth. To enter the code, start Excel 2010 then place a command button onto the spreadsheet and click on it to launch the Excel 2010 VBA  editor.

The code:

Private Sub CommandButton1_Click()

Dim grade As String
Dim grade As String
 grade = InputBox("Enter the grade(A, A-, B, C, E or F")
Select Case grade
 Case "A"
  MsgBox "High Distinction"Case "A-"
  MsgBox "Distinction"
 Case "B"
  MsgBox "Credit"
 Case "C"
  MsgBox "Pass"
 Case Else
  MsgBox "Fail"
End Select

End Sub



Example 11.2

In this example, we shall show you how to process the grades of students according to the marks given. For example, if a student’s mark is 85, the corresponding grade is A and if the mark is 20 the grade will be F and so forth. We can use the statement case value1 to value 2  to specify the range of values that fulfill the particular case.

You should also include the error case where the values entered are out of the range or invalid. For example, if the examination mark is from 0 to 100, then any value out of this range is invalid.
The code:

Private Sub CommandButton1_Click()

Dim mark As Single
Dim grade As String
 mark = InputBox("Enter the mark")
Select Case mark
 Case 0 To 29
  grade = "F"
 Case 30 To 49
  grade = "E"
 Case 50 To 59
  grade = "D"
 Case 60 To 69
  grade = "C"
 Case 70 To 79
  grade = "B"
 Case 80 To 100
  grade = "A"
 Case Else
  grade = "Error!"
End Select
 MsgBox grade

End Sub



 [Lesson 10]<<[Table of Contents]>>[Lesson 12]

Excel 2010 VBA Lesson 10: Using If…Then…Else

[Lesson 9]<<[Table of Contents]>>[Lesson 11]

In this lesson, you will learn how to create Excel 2010 VBA code that can make a decision . Decision-making process is an important part of Excel 2010 VBA  programming because it can help to solve practical problems intelligently so that it can provide useful feedback to the users.In Excel 2010 VBA, decision making involves the use of the If…then…Else syntax to process data and display the output based on the fulfillment of certain conditions. To effectively control the VB program flow, we need to use If…Then…Else statement together with the conditional operators and logical operators.



These operators are shown in Table 10.1 and Table 10.2 respectively.

Table 10.1: Conditional Operators

Table 10.2: Logical Operators

We shall demonstrate the usage of If….Then…Else with the following example.In this program, we place the command button1 on the MS Excel spreadsheet and go into the Excel 2010 VBA editor by clicking on the button. In the editor, enter the program code as shown below.

We use the RND function to generate random numbers. In order to generate random integers between 0 and 100, we combined the syntax Int(Rnd*100). For example, when Rnd=0.6543, then Rnd*100=65.43, and Int(65.43)=65. Using the statement cells(1,1).Value=mark will place the value of 65 into cell(1,1).

Now, based on the mark in cells(1,1), we use the If…….Then….Elseif statements to put the corresponding grade in cells(2,1). So, when you click on command button 1, it will insert a random number between 1 and 100 in cells(1,1) and the corresponding grade in cells(2,1).

The Code

Private Sub CommandButton1_Click()
Dim mark As Integer
Dim grade As String
 mark = Int(Rnd * 100)
 Cells(1, 1).Value = mark
If mark < 20 And mark >= 0 Then
 grade = “F”
 Cells(2, 1).Value = grade
ElseIf mark < 30 And mark >= 20 Then
 grade = “E”
 Cells(2, 1).Value = grade
ElseIf mark < 40 And mark >= 30 Then
 grade = “D”
 Cells(2, 1).Value = grade
ElseIf mark < 50 And mark >= 40 Then
 grade = “C-”
 Cells(2, 1).Value = grade
ElseIf mark < 60 And mark >= 50 Then
 grade = “C”
 Cells(2, 1).Value = grade
ElseIf mark < 70 And mark >= 60 Then
 grade = “C+” 
 Cells(2, 1).Value = grade
ElseIf mark < 80 And mark >= 70 Then
 grade = “B”
 Cells(2, 1).Value = grade
ElseIf mark <= 100 And mark >=80 Then
 grade = “A”
 Cells(2, 1).Value = grade
End If
End Sub



The output is shown in Figure 10.1

vba2010_fig10.1


[Lesson 9]<<[Table of Contents]>>[Lesson 11]

Excel VBA Lesson 13: Using Select Case

<<Lesson 12>> [Contents] <<Lesson 14>>

In lesson 4, we have learned how to handle codes in Excel VBA that involve decision-making process through the use the conditional statement If….Then….Else. However, for multiple options or selections programs, the If…Then…Else structure could become too bulky and difficult to debug if problems arise. Fortunately, Excel VBA provides another way to handle complex multiple-choice cases, that is, the Select Case…..End Select decision structure. The syntax of a Select Case…End Select structure is as follows:

Select Case variable
Case value 1
Statement
Case value 2
Statement
.
.
Case Else
End Select



We shall demonstrate the use of Select Case…End Select in the following examples:

Example 13.1

In this program, we want to automatically assign a remark in relation to a certain examination grade. For example, if the grade is A, we shall assign remark as High distinction, A- for distinction and so forth. To enter the code, start MS Excel then place a button and a label onto the spreadsheet. The label is for displaying the remark. Cells(1,1) is for the user to enter the grade.

The code:

Private Sub CommandButton1_Click()
Dim grade As String
grade = Cells(1, 1)
Select Case grade

Case “A”
label1.Caption = “High Distinction”

Case “A-“
label1.Caption = “Distinction”

Case “B”
label1.Caption = “Credit”

Case “C”
label1.Caption = “Pass”

Case Else
label1.Caption = “Fail”

End Select
End Sub

The Output

Excel VBA

Figure 13.1



Example 13.2

In this  example, we shall show you how to process the grades of students according to the marks given. For example, if a student’s mark is 85, the corresponding grade is A and if the mark is 20 the grade will be F and so forth.

The code

Private Sub CommandButton1_Click()

Dim mark As Single
Dim grade As String
mark = Cells(1, 1).Value

‘To set the alignment to center
Range(“A1:B1”).Select
With Selection
.HorizontalAlignment = xlCenter
End With

Select Case mark
Case 0 To 20
grade = “F”
Cells(1, 2) = grade
Case 20 To 29
grade = “E”
Cells(1, 2) = grade
Case 30 To 39
grade = “D”
Cells(1, 2) = grade
Case 40 To 59
grade = “C”
Cells(1, 2) = grade
Case 60 To 79
grade = “B”
Cells(1, 2) = grade
Case 80 To 100
grade = “A”
Cells(1, 2) = grade
Case Else
grade = “Error!”
Cells(1, 2) = grade
End Select

End Sub

Explanation:

To set the cell align alignment to center, we use the following procedure:

Range(“A1:B1”).Select
With Selection
.HorizontalAlignment = xlCenter
End With

We can use the statement case value1 to value 2  to specify the range of values that fulfil the particular case.

You should also include the error case where the values entered are out of the range or invalid. For example, if the examination mark is from 0 to 100, then any value out of this range is invalid. In this program, I use case else to handle the error entries.

Figure 13.2 illustrates the output of this example.

Excel VBA

Figure 13.2

<<Lesson 12>> [Contents] <<Lesson 14>>

Excel VBA Lesson 15: The Do Loop

<<Lesson 14>> [Contents] <<Lesson 16>>

In the previous lesson, you have learned how to use the   For……..Next loop and the While…..Wend loop to execute a repetitive process. In this lesson,  you will learn about another looping method known as the Do Loop. There are four ways you can use the Do Loop, as shown below:

(i) Do………..Loop While

(i) Do………..Loop While

(ii) Do until………….Loop

(iii) Do while…………Loop

(iv) Do…………Loop until




We shall illustrate the four structures of Do Loops in the following examples:

Example 15.1

Arranging numbers in ascending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do
counter = counter + 1
Cells(counter, 1) = counter
Loop While counter < 10

End Sub

In this example, the program will keep on adding 1 to the preceding counter value as long as the counter value is less than 10. It displays 1 in cells(1,1), 2 in cells(2,1)….. until 10 in cells (10,1).

The Output

Excel VBA

Figure 15.1

Example 15.2

Arranging numbers in descending order

Private Sub CommandButton1_Click()
Dim counter As Integer
Do Until counter = 10
counter = counter + 1
Cells(counter, 1) = 11 – counter
Loop
End Sub

In this example, the program will keep on adding 1 to the preceding counter value until the counter value reaches 10. It displays 10 in cells(1,1), 9  in cells(2,1)….. until 1 in cells (10,1).

The Output

Excel VBA

Figure 15.2

Example 15.3

Private Sub CommandButton1_Click()
Dim counter , sum As Integer

‘To set the alignment to center
Range(“A1:C11”).Select
With Selection
.HorizontalAlignment = xlCenter
End With

Cells(1, 1) = “X”
Cells(1, 2) = “Y”
Cells(1, 3) = “X+Y”

Do While counter < 10
counter = counter + 1
Cells(counter + 1, 1) = counter
Cells(counter + 1, 2) = counter * 2
sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)
Cells(counter + 1, 3) = sum
Loop
End Sub

In this example, the program will display the values of X in cells(1,1) to cells(11,1). The value of Y is X2and the values are display in column 2, i.e. from cells(2,1) to cells(2,11). Finally, it shows the values of X+Y in column 3, i.e. from cells(3,1) to cells(3,11)

The Output

Excel VBA
Figure 15.3

<<Lesson 14>> [Contents] <<Lesson 16>>

Excel VBA Lesson 14: Looping

<<Lesson 13>> [Contents] <<Lesson 15>>

Another procedure that involves decision making is looping. Excel VBA allows a procedure to be repeated many times until a condition or a set of conditions is fulfilled. This is generally called looping .  Looping is a very useful feature of  Excel VBA because it makes repetitive works easier. There are  two kinds of loops in Visual Basic,  the For…….Next loop, the While….Wend loop and the the Do…Loop . We shall deal with the For………Next loop  and the While….Wend loop first and we will deal with the Do….Loop in the next lesson.

14.1 One level For…..Next Loop

The one level For….Next Loop event procedure is written as follows:

For counter=startNumber to endNumber (Step increment)

One or more VB statements

Next

Example 14.1

In this Excel VBA program, you place the command button 1 on the spreadsheet then click on it to go into the Visual Basic editor. When you click on the button, the Excel VBA program will fill cells(1,1) with the value of 1, cells(2,1) with the value of 2, cells(3,1) with the value of 3……until cells (10,1) with the value of 10. The position of each cell in the Excel spreadsheet is referenced with cells(i,j), where i represents row and j represent the column.

The Code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next
End Sub

The Output is shown in Figure 5.1

Excel VBA




Figure 14.1

Example 14.2

For……..Next loop with step increments. In this example, the number increases by 2 at a time. The resulting series is 1,3,5,7,9 displayed in Cells(1,1), Cells(3,1),Cells(5,1), Cells(7,1) and Cells(9,1) respectively.

The code

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10  Step 2
Cells(i, 1).Value = i
Next
End Sub

The Output
Excel VBA

 Figure 14.2

 14.2 Nested For….Next Loops

Nested For…….Next loops mean there are more than one For…Next Loops are nested within the first level For…..Next loop. The structure is as follows:

For counter=startNumber1 to endNumber1
For counter=startNumber2 to endNumber2
For counter=startNumber3 to endNumber3
One or more VB statements
Next
Next
Next

 

Example 14.3

In this example , we use  nested loop to put the values of i+j from cells(1,1),cells(1,2),cells(1,3),cells(1,4),cells(1,5) ……….until cells(10,5). The code and output are shown below.

Private Sub CommandButton1_Click()
Dim i, j As Integer
For i = 1 To 10
For j = 1 To 5
Cells(i, j).Value = i + j
Next
Next
End Sub

The Output

Excel VBA

Figure 14.3

14.3  While….Wend  Loop

The structure of a While….Wend Loop is very similar to the Do Loop. it takes the following form:

While condition
Statements
Wend

The above loop means that while the condition is not met, the loop will go on. The loop will end when the condition is met. Let’s examine the program listed in example 14.4.

Example 14.4

In this example, the Excel VBA program will compute the square of numbers from 1 to 15 and displays them from Cells(1,1) to Cells(15,1)

The Code

Private Sub CommandButton1_Click()
Dim i As Integer

i = 1
While i <= 15

Cells(i, 1) = i ^ 2
i = i + 1
Wend

End Sub

The Output

vba_example5.4

Figure 14.4

<<Lesson 13>> [Contents] <<Lesson 15>>