Conditional Statements and Functions

Involving a Conditional Statement in a Function

Introduction

 When performing its assignment, a function can encounter different situations, some of which would need to be checked for a true value, a false value or a negation. Therefore, a conditional statement can assist a procedure or function to decide what action to take or what value to produce.

Conditional Returns

A function can return only one value but you can make it produce a result that depends on some condition.

Practical Learning: Conditionnally Returning a Value

1. Start Microsoft Access
2. In the list of files, click Business Starter from Lesson 9
3. In the Navigation Pane, right-click Inventory Review and click Design View
4. On the form, right-click the Cerate Sale Record button and click Build Event
5. In the Choose Builder dialog, double-click Code Builder and implement the event as follows:
```Function GetDiscountRate(ByVal price As Double, ByVal days As Integer) As Integer
Dim discountRate As Double

If days > 70 Then
discountRate = 70
ElseIf days > 50 Then
discountRate = 50
ElseIf days > 30 Then
discountRate = 35
ElseIf days > 15 Then
discountRate = 15
End If

GetDiscountRate = discountRate
End Function

Function CalculateMarketPrice(ByVal price As Double, Optional ByVal rate As Integer = 0#) As Integer
Dim markedPrice As Double

If rate = 0# Then
markedPrice = 0#
Else
markedPrice = price * rate / 100#
End If

CalculateMarketPrice = markedPrice
End Function

Private Sub cmdCreate_Click()
Dim unitPrice As Double
Dim markedPrice As Double
Dim discountRate As Double
Dim daysInStore As Integer
Dim discountedAmount As Double

markedPrice = 0#

daysInStore = CInt(txtDaysInStore)
unitPrice = CDbl(txtUnitPrice)
discountRate = GetDiscountRate(unitPrice, daysInStore)

If discountRate = 0# Then
discountedAmount = CalculateMarketPrice(unitPrice)
Else
discountedAmount = CalculateMarketPrice(unitPrice, discountRate)
End If

markedPrice = unitPrice - discountedAmount

txtItemNumberRecord = txtItemNumberIdentification
txtItemNameRecord = txtItemNameIdentification
txtDiscountAmount = FormatCurrency(discountedAmount)
txtMarkedPrice = FormatCurrency(markedPrice)
End Sub```
6. Return to Microsoft Access and switch the form to Form View

7. Fill out the top section of the form with a few values. Here is an example:

8. Click the Create Sale Record button

10. In our examples, we first declared a variable that was going to hold the value to return. This is not always necessary. If you already have the value or the expression to return, you can directly assign it to the name of the function. You can repeat this for each section where the value or expression can/must be returned. As examples, change the functions as follows:
```Function GetDiscountRate(ByVal price As Double, ByVal days As Integer) As Integer
If days > 70 Then
GetDiscountRate = 70
ElseIf days > 50 Then
GetDiscountRate = 50
ElseIf days > 30 Then
GetDiscountRate = 35
ElseIf days > 15 Then
GetDiscountRate = 15
End If
End Function

Function CalculateMarketPrice(ByVal price As Double, _
ByVal Optional ByVal rate As Integer = 0#) As Integer
If rate = 0# Then
CalculateMarketPrice = 0#
Else
CalculateMarketPrice = price * rate / 100#
End If
End Function```
12. Save and close the form
13. On the Ribbon, click Create and click Class Module
14. Type the following:
```Dim nbr As Long
Dim nm As String
Dim cost As Double

Public Property Get ItemNumber() As Long
ItemNumber = nbr
End Property

Public Property Let ItemNumber(ByVal value As Long)
ItemNumber = value
End Property

Public Property Get ItemName() As Long
ItemName = nm
End Property

Public Property Let ItemName(ByVal value As Long)
ItemName = value
End Property

Public Property Get UnitPrice() As Long
UnitPrice = cost
End Property

Public Property Let UnitPrice(ByVal value As Long)
UnitPrice = value
End Property```
15. In the Project window, click (Name) and type StoreItem
17. On the Ribbon, click Create and click Form Design
18. In the Controls section of the Ribbon, click the button and click the form.
If a wizard starts, click Cancel
19. On the form, right-click the button and click Build Event...
20. In the Choose Builder dialog box, double-click Code Builder

Introduction to Built-In Boolean Functions

Checking Whether a Variable Has Been Initialized

After declaring a variable, at any time, to let you check whether the variable has been initialized or holds a valid value, the Visual Basic language provides a function named IsEmpty. Its syntax is:

`Public Function IsEmpty(ByVal Expression As Variant) As Boolean`

When calling this function, pass the name of a variable to it. If the variable was already initialized, the function would return True. Otherwise, it would return False.

Checking Whether a Variable is an Object

After declaring a variable, at any time, to let you find out whether the variable in an Object type, the Visual Basic language provides a function named IsObject(). Its syntax is:

`Public Function IsObject(ByVal VariableName As String) As Boolean`

This function takes as argument the name of a variable. If the argument represents an object type, the function returns True. Otherwise, it returns False.

Practical Learning: Checking Whether a Variable is an Object

1. Implement the event as follows:
```Private Sub Command0_Click()
Dim item

If IsObject(item) Then
MsgBox "The item is an object."
Else
MsgBox "This item doesn't use a reference type."
End If

Set item = New StoreItem

If IsObject(item) Then
MsgBox "The item is an object."
Else
MsgBox "This item doesn't use a reference type."
End If

Set item = Nothing
End Sub```
2. On the form, right-click the Cerate Sale Record button and click Build Event

3. Close the form
4. When asked whether you want to save, click No

Checking Whether Something is Null

To help you find whether something, such as a control or a variable, has no valid value, that is, whether a control or a value is null, the Visual Basic language provides a function named IsNull. Its syntax is:

`Public Function IsNull(ByVal expression As Variant) As Boolean`

This function check the state of the. If the argument holds a valid value, the function returns True. If the argument holds no value or its value is not clear, the function returns False.

Practical Learning: Conditionally Stating an Else Statement

1. From the resources that accompany these lessons, open the Geometry1 database
2. In the Navigation Pane, right-click Geometric Shape - Pentagon and click Design View
3. In the Controls section of the Ribbon, click Image and click the form
4. From the resources that accompany these lessons, select and open Pentagon

5. On the form, right-click the Calculate button and click Code Builder
6. In the Choose Builder dialog, double-click Code Builder and implement the event as follows:
```Function CalculatePerimeter(ByVal value As Double) As Double
CalculatePerimeter = value * 5#
End Function

Function CalculateDiagonal(ByVal value As Double) As Double
CalculateDiagonal = value * (1# + Sqr(5#)) / 2#
End Function

Function CalculateArea(ByVal value As Double) As Double
CalculateArea = value * value * Sqr((5# * (5# + (2# * Sqr(5#))))) / 4#
End Function

Private Sub cmdCalculate_Click()
Dim side, perimeter, diagonal, area

If IsNull(txtSide) Then
side = 0#
Else
side = CDbl(txtSide)
End If

perimeter = CalculatePerimeter(side)
diagonal = CalculateDiagonal(side)
area = CalculateArea(side)

txtPerimeter = CStr(perimeter)
txtDiagonal = CStr(diagonal)
txtArea = CStr(area)
End Sub```
7. Return to Microsoft Access and switch the form to Form View

8. Click the Calculate button

9. Click Side and type a number such as 98.73 and click the Calculate button

10. Save and close the form

Checking for Non-Zero

To help you check the value of an expression or a control, the VBA language provides a function named Nz. Its syntax is:

`Nz(Value, ByVal Optional ValueIfNull IS NULL) As Variant`

The function checks the value of the (first) argument. If Value is null, the function returns 0 or an empty string. The second argument is optional. You can provide it as an alternative to 0 in case the Value argument is null. This means that, when the first argument is null, instead of returning 0 or an empty string, the Nz() function would return the value of the second argument.

Practical Learning: Checking for Non-Zero

1. On the Ribbon, click File and click Open
2. In the list of files, click Business Mathematics from the previous lesson
3. In the Navigation Pane, double-click Bill Preparation1 used in the previous lesson to open it in the Form View (or open the Bill Preparation2 form; they are the same at this time)
4. Click the button and notice the error:

5. On the message box, click the End button
6. On the Ribbon, click Create and click Visual Basic
7. In the Project window, double-click Form_Bill Preparation1 (or Form_Bill Preparation2 if that's the form you are using)
8. Change the code as follows:
```Private Sub cmdCalculate_Click()
Dim pricePerCCF As Double
Dim monthlyCharges As Double
Dim consumption As Double

pricePerCCF = 50#
monthlyCharges = 0#
consumption = CDbl(Nz(txtConsumption))

If consumption >= 0.5 Then pricePerCCF = 35#

txtPricePerCCF = pricePerCCF

pricePerCCF = CDbl(txtPricePerCCF)

monthlyCharges = consumption * pricePerCCF

txtMonthlyCharges = Format(monthlyCharges, "Fixed")
End Sub```

10. Click Consumption and type 2.16 and click the button:

11. Save and close the form
12. In the Navigation Pane, right-click Straight-Line Method1 accessed in Lesson 4 and continued in Lesson 9. Click Design View (or use the Straight-Line Method2 form)
13. On the form, right-click the Calculate button and click Build Event... If you are using the Straight-Line Method1 form, in the Choose Builder dialog box, click Code Builder and click OK
14. Change the event as follows:
```Private Sub cmdCalculate_Click()
Dim cost
Dim salvageValue
Dim estimatedLife
Dim depreciation

cost = CDbl(Nz(txtCost))
salvageValue = CDbl(Nz(txtSalvageValue))
estimatedLife = CDbl(Nz(txtEstimatedLife))

depreciation = SLN(cost, salvageValue, estimatedLife)

txtDepreciation = FormatCurrency(depreciation)
End Sub```
15. Save and close the form

If a Value is Numeric

Remember that any value that a user types in a text box is primarily considered as text. Before using or converting a value that is supposed to be numeric, to let you check whether it is a number, the Visual Basic language provides a function named IsNumeric. This function takes one argument as the value to check. If the argument is an integer or a floating-point number, the function returns True. If not, it returns False. Its syntax is:

`Public Function IsNumeric(ByVal Expression As Variant) As Boolean`

This function takes as argument the value or expression to be evaluated. If the argument holds or can produce a valid integer or a decimal value, the function returns True. Here is an example:

```Private Sub cmdFunction_Click()
Dim Value As Variant

Value = 258.08 * 9920.3479

msgbox "Is Numeric? " & IsNumeric(Value)
End Sub ```

If the argument is holding a value that cannot be identified as a number, the function produces False. Here is an example:

```Private Sub cmdFunction_Click()
Dim Value As Variant

Value = #12/4/1770#

MsgBox "Is Numeric? " & IsNumeric(Value)
End Sub```

Practical Learning: Checking Whether a Value Is Numeric

1. On the Ribbon, click File and click Open
2. In the list of files, click Business Starter used earlier in this lesson
3. In the Navigation Pane, double-click Inventory Review to open it in the Form View
4. Click the button and notice the error
5. On the message box, click the Debug button
6. Change the code as follows:
```Function GetDiscountRate(ByVal price As Double, ByVal days As Integer) As Integer
If days > 70 Then
GetDiscountRate = 70
ElseIf days > 50 Then
GetDiscountRate = 50
ElseIf days > 30 Then
GetDiscountRate = 35
ElseIf days > 15 Then
GetDiscountRate = 15
End If
End Function

Function CalculateMarketPrice(ByVal price As Double, _
ByVal Optional ByVal rate As Integer = 0#) As Integer
If rate = 0# Then
CalculateMarketPrice = 0#
Else
CalculateMarketPrice = price * rate / 100#
End If
End Function

Private Sub cmdCreate_Click()
Dim unitPrice As Double
Dim markedPrice As Double
Dim discountRate As Double
Dim daysInStore As Integer
Dim discountedAmount As Double

If IsNumeric(txtDaysInStore) Then daysInStore = CInt(txtDaysInStore)
If IsNumeric(txtUnitPrice) Then unitPrice = CDbl(txtUnitPrice)

discountRate = GetDiscountRate(unitPrice, daysInStore)

If discountRate = 0# Then
discountedAmount = CalculateMarketPrice(unitPrice)
Else
discountedAmount = CalculateMarketPrice(unitPrice, discountRate)
End If

markedPrice = unitPrice - discountedAmount

txtItemNumberRecord = txtItemNumberIdentification
txtItemNameRecord = txtItemNameIdentification
txtDiscountAmount = FormatCurrency(discountedAmount)
txtMarkedPrice = FormatCurrency(markedPrice)
End Sub```
7. On the Standard toolbar, click the Reset button

9. Fill out the top section of the form with a few values. Here is an example:

10. Click the Create Sale Record button

11. Save and close the form

The Condition-Based Function

To assist you with checking a condition and its alternative, the Visual Basic language provides a function named IIf. This function operates like an If...Then...Else conditional statement. It takes three required arguments and returns a result of a type of your choice (actually an Object). It can be presented as follows:

```Public Function IIf(ByVal Expression As Boolean,
ByVal TruePart As Object,
ByVal FalsePart As Object) As Object```

The condition to check, a Boolean expression, is passed as the first argument:

• If that Expression is true, the function returns the value of the second argument, the TruePart. The third argument or last argument is ignored
• If the Expression is false, the first argument is ignored and the function returns the value of the second argument

Practical Learning: Introducing the Immediate If Function

1. On the Ribbon, click File and click Open
2. In the list of files, click Business Mathematics database
3. In the Navigation Pane, right-click Loan Decision1 and click Design View
4. On the form, right-click the Decide button and click Build Event...
5. In the Choose Builder dialog box, click Code Builder and click OK
6. Implement the event as follows:
```Private Sub cmdDecide_Click()
Dim decision As String
Dim creditScore As Integer
Dim hasGoodCredit As Boolean

creditScore = CInt(Nz(txtCreditScore))
hasGoodCredit = (creditScore >= 680)

decision = IIf(hasGoodCredit = True, "Approved", "Denied")

txtDecision = decision
End Sub```