In Excel VBA 365, 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 must 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.
Operator | Meaning |
---|---|
= | Equal to |
> | More than |
< | Less than |
>= | More than and equal |
<= | Less than and equal |
<> | Not equal to |
Operator | Meaning |
---|---|
And | Both sides are equal |
Or | One side or other must be true |
Xor | One side or other must be true but not both |
Not | Negates Both |
We shall demonstrate the usage of If….Then…Else with the following example. In this program, we place the command button on the MS Excel spreadsheet and go into the Excel VBA 365 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
Copyright ® 2020 Dr.Liew Voon Kiong . All rights reserved Last update:11/20/2023 08:29:08 [Privacy Policy]
Contact: Facebook Page