Home

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

    Conditionnally Returning a Value

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

    Conditionnally Returning a Value

  8. Click the Create Sale Record button

    Conditionnally Returning a Value

  9. Return to Microsoft Visual Basic
  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
  11. Return to Microsoft Access
  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
  16. Return to Microsoft Access
  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

    Checking Whether a Variable is an Object

    Checking Whether a Variable is an Object

  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

    Geometric Figures - 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

    Geometric Figures - Square

  8. Click the Calculate button

    Checking Whether Something is Null

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

    Checking Whether Something is Null

  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:

    Checking for Non-Zero

  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
  9. Return to Microsoft Accecss and click the button again:

    Checking for Non-Zero

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

    Checking for Non-Zero

  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
  8. Return to Microsoft Accecss and click the button again:

    Checking Whether a Value Is Numeric

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

    Checking Whether a Value Is Numeric

  10. Click the Create Sale Record button

    Checking Whether a Value Is Numeric

  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
  7. Return to Microsoft Access and switch the to Form View

    Introducing the Immediate If Function

  8. Click the Decide button

    Introducing the Immediate If Function

  9. Click Customer Credit Store and type 722
  10. Click the Decide button

    Introducing the Immediate If Function

  11. Save and close the form
  12. Close Microsoft Access
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next