Introduction to Procedures and Functions

Sub Procedures

Introduction

A procedure is a section of code that performs an assignment so that other parts of the program can directly use the result of that assignment. For example, a procedure can be created to display a message to a user. Another procedure can be used to calculate something that the other parts of the database would use when and where necessary.

The Visual Basic language supports two categories of procedures: sub-procedures and functions.

Practical Learning: Introducing Procedures

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Geometry database
3. In the Navigation Pane, right-click Quadrilateral - Rectangle and click Design View
4. On the Ribbon, click Image and click the left side on the form
5. From the resources that accompany these lessons, select and open the Rectangle.bmp file:

   

6. In the Tools section of the Design tab of the Ribbon, click the View Code button

Creating a Procedure

A sub-procedure is a section of code that carries an assignment but doesn't give back a result. The formula to create a sub-procedure is:

Sub procedure-name()

End Sub

The Sub keyword and the End Sub expressions are required. The Sub keyword is followed by the name of the sub-procedure. The name is followed by parentheses. Normally, the Sub keyword and the name of the procedure (including its parentheses) are written on the same line. The name of a sub-procedure follows the same rules we reviewed for the names of variables, omitting the prefix:

• If the sub-procedure performs an action that can be represented with a verb, you can use that verb to name it
• To make the name of a-sub procedure stand, you should start it in uppercase. Examples are Show, Play, Dispose, Close
• If the name of a procedure is a combination of words, start each word in uppercase. Examples are: AfterUpdate, SayItLoud
• You should use explicit names that identify the purpose of the procedure

The section between the Sub procedure-name line and the End Sub line is referred to as the body of the procedure. In the body of a procedure, you define what the procedure is supposed to do. If you need to use a variable, you can declare it and specify what kind of variable you need. There is no restriction on the type of variables that can be declared in a procedure. Here is an example in which a string variable is declared in the body of a sub procedure:

Sub CreateName()
    Dim strFullName As String
End Sub

In the same way, you can declare as many variables as you want in the body of a procedure. A procedure can be used to perform a simple calculation such as adding two numbers. Here is an example:

Sub CalculateTotalStudents()
    Dim StudentsInClass1 As Integer
    Dim StudentsInClass2 As Integer
    Dim TotalNumberOfStudents As Integer
    
    StudentsInClass1 = 32
    StudentsInClass2 = 36
    TotalNumberOfStudents = StudentsInClass1 + StudentsInClass2
End Sub

Practical Learning: Creating a Procedure

1. In the Code Editor, type Sub SolveRectangle and press Enter
2. Notice that Visual Basic added the End Sub line and positioned the cursor inside the procedure
3. Complete the sub procedure as follows:
   

   Sub Sub SolveRectangle()
       ' Declare the necessary variables for the rectangle
       Dim dblWidth As Double, dblHeight As Double
       Dim dblPerimeter, dblArea As Double
       
       ' Retrieve the values of the sides
       dblWidth = txtWidth
       dblHeight = txtHeight
       
       ' Calculate the perimeter and the area of the rectangle
       dblPerimeter = (dblWidth + dblHeight) * 2
       dblArea = dblWidth * dblHeight
   
       ' Prepare to display the result in the appropriate text boxes
       txtPerimeter = dblPerimeter
       txtArea = dblArea
   End Sub

Calling a Procedure

After creating a procedure, you can use it in the section of code where you need it. Using a procedure is referred to as calling it. To call a simple procedure, just write its name. Here is an example:

Sub ShowMessage()
    lblMessage.Caption = "Helium is a chemical element found, or used, in solids, in liquids, and in gases."
End Sub

Private Sub cmdMessage_Click()
    ShowMessage
End Sub

Practical Learning: Calling a Procedure

1. In the Object combo box, select cmdCalculate
2. To call the RectangleSolution procedure, press Tab and type RectangleSolution:
   

   Private Sub cmdCalculate_Click()
       SolveRectangle
   End Sub

3. Return to Microsoft Access and switch the form to Form View
4. Click Width and type 68.59
5. Click Height and type 45.63
6. Click the Calculate button:

   

7. Save and close the form

Parameters and Arguments

Introduction

To carry out an assignment, a procedure may need a value. That is, in order to perform its assignment, a prodcedure may need the calling code to supply it with a value, such as value is external to the procedure. Such a value is called a parameter. The type of the parameter depends on your goal. If you are writing your own procedure, you decide based on your intentions.

To create a procedure that uses a parameter, inside of the parentheses of the procedure, write the name of the parameter followed by the As keyword followed by the type of value of the parameter. Here is an example:

Sub ShowMessage(Msg As String)

End Sub

In the body of the procedure, you can ignore and not use the parameter if you don't need it. Otherwise, in the body of the procedure, use the parameter as you would use a local variable. Here is an example:

Sub ShowMessage(Msg As String)
    lblMessage.Caption = Msg
End Sub

Of course, the parameter can be involved in an expression. Here is an example:

Sub Calculation Function CubeArea(Side As Double)
    Dim value As Double
    
    Let value = Side * Side * 6
End Sub

Calling a Procedure That Has a Parameter

If a sub-procedure is taking a parameter, to call it, you must provide a value for the parameter. Providing a value for the parameter is referred to as passing the argument. To pass an argument to a procedure, type the name of the procedure, followed by a space, and followed by the value of the parameter. Here is an example:

Sub Describe(Power As String)
    lblMessage.Caption = Power
End Sub

Private Sub cmdMessage_Click()
     Describe "Electricity is used to supply power to machines and devices."
End Sub

Alternatively, you can use the Call keyword to call a sub procedure. In this case, type the Call keyword on the left side of the calling procedure and include the argument between parentheses. Here is an example:

Sub ShowMessage(Power As String)
    lblMessage.Caption = Power
End Sub

Private Sub cmdMessage_Click()
     Call ShowMessage("Electricity is used to supply power to machines and devices.")
End Sub

The argument passed to a procedure can come from a variable. In this case, the name of the variable and that of the parameter don't have to be the same. Here is an example:

Sub Evaluate(Salary As Double)
    Dim OvertimeSalary As Double
    
    OvertimeSalary = Salary * 1.5
    
    txtOvertimeSalary = OvertimeSalary
End Sub

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    
    HourlyWages = txtHourlySalary
    
    Evaluate HourlyWages
End Sub

A Procedure With Many Parameters

A procedure can take more than one parameter. If you are creating such a procedure, In the parentheses of the procedure, each parameter must have a unique name and it must specify its type. The parameters are separated by commas. The parameters can use the same type. Here is an example:

Sub CalculatePerimeter(Length As Double, Height As Double)
  
End Sub

The parameters can also use different types. Here is an example:

Sub DisplayGreetings(strFullName As String, intAge As Integer, dblDistance As Double)
    
End Sub

In the body of the procedure, you can ignore all parameters, you can use one or some parameter, or you can use all of them. Here is an example:

Sub Evaluate(Salary As Double, Times As Double)
    Dim WeeklySalary As Double
    
    WeeklySalary = Salary * Times
    
    txtWeeklySalary = WeeklySalary
End Sub

Calling a Procedure With Many Parameters

If a sub-procedure is taking many parameters, when calling it, you must provide a value for each parameter. If a procedure takes more than one argument, you must provide a value for each parameter, in the exact order they appear in the parentheses of the procedure. In that case, the values must be separated by commas. Here is an example:

Sub Evaluate(Salary As Double, Times As Double)
    Dim WeeklySalary As Double
    
    WeeklySalary = Salary * Times
    
    txtWeeklySalary = WeeklySalary
End Sub

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    Dim TimeWorked As Double
    
    HourlyWages = txtHourlySalary
    TimeWorked = txtTimeWorked
    
    Evaluate HourlyWages, TimeWorked
End Sub

Remember that, as an alternative, you can use the Call keyword, in which case the arguments must be includes in parentheses. Here is an example:

Sub Evaluate(Salary As Double, Times As Double)
    Dim WeeklySalary As Double
    
    WeeklySalary = Salary * Times
    
    txtWeeklySalary = WeeklySalary
End Sub

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    Dim TimeWorked As Double
    
    HourlyWages = txtHourlySalary
    TimeWorked = txtTimeWorked
    
    Call Evaluate(HourlyWages, TimeWorked)
End Sub

Introduction to Functions

Introduction

A function is a procedure that performs an assignment and produces a result. Proceducing a result is also referred to as returning a value. A function resembles a sub-procedure in all except that a function returns a value.

Creating a Function

A function is started like a sub-procedure with a few more rules. To start, instead of the Sub keyword, a function uses the Function keyword. The name of the function follows the same rules and suggestions we reviewed for the sub-procedures. Because a function must return a value, after the parentheses, you must specify a data type. This is done by typing the As keyword followed by the type of value the function must return. Therefore, the primary formula to create a function is:

Function function-name() AS data-type

End Function

Here is an example:

Function GetFullName() As String

End Function

When we studied variables, we saw that, instead of using the As data-type expression, we could use a type character. This technique also applies to functions. To use it, on the right side of the name of the function, type the special character that represents the data type, followed by the parentheses of the function, and omit the As data-type expression. Here is an example:

Function GetFullName$()

End Function

As with the variables, you must use the appropriate character for the function:

Implementing a Function

The implementation of a function is primarily done the same way as a sub-procedure. Because a function must return a value, after performing whatever assignment you need in the body of the function, assign the final result to the name of the function before the End Function line. Here is an example:

Function GetFullName() As String
    Dim strFirstName, strLastName As String

    strFirstName = txtFirstName
    strLastName = txtLastName
    GetFullName = strFirstName & " " & strLastName
End Function

You can also start the assignment line with the Let keyword. Here is an example:

Function GetFullName() As String
    Dim strFirstName, strLastName As String

    strFirstName = txtFirstName
    strLastName = txtLastName
    Let GetFullName = strFirstName & " " & strLastName
End Function

Practical Learning: Creating a Function

1. In the Navigation Pane, right-click Circular Shape - Circle and click Design View
2. On the Ribbon, click Image and click the left side on the form
3. From the resources that accompany these lessons, select and open the Circle.bmp file:

   

4. In the Tools section of the Design tab of the Ribbon, click the View Code button
5. In the Code Editor, click Option Explicit, press End, and press Enter twice
6. Type Const PI = 3.14159265359 and press Enter twice
7. Type Function CalculateDiameter As Double and press Enter
8. Notice that Visual Basic completed the code with the End Function line and positioned the cursor in the body of the function. Implement the function as follows:
   

   Function CalculateDiameter() As Double
       Dim radius As Double
       
       radius = txtRadius
       
       CalculateDiameter = radius * 2#
   End Function

9. Create another function as follows:

   Function CalculateArea() As Double
       Dim radius As Double
       radius = txtRadius
       CalculateArea = radius * radius * PI
   End Function

Calling a Function

To call a function, you have two main options. If the function was implemented as simple as a sub-procedure, just write its name where its result is needed. If you want to use the return value of a function, assign the name of the function to a local variable. Here is an example:

Private Sub Detail_DblClick(Cancel As Integer)
    txtFullName = GetFullName
End Sub

Practical Learning: Calling a Function

1. In the Object combo box, select cmdCalculate
2. Implement its Click event as follows:
   

   Private Sub cmdCalculate_Click()
       txtDiameter = CalculateDiameter
       txtArea = CalculateArea
   End Sub

3. Return to Microsoft Access and switch the form to Form View
4. Click Radius and type 125.55
5. Click the Calculate button

   

6. Return to Microsoft Visual Basic
7. To involve a call to a function in an expression, create a procedure as follows:

   Const PI = 3.14159265359
   
   Function CalculateDiameter() As Double
       Dim radius As Double
       
       radius = txtRadius
       
       CalculateDiameter = radius * 2#
   End Function
   
   Function CalculateArea() As Double
       Dim radius As Double
       
       radius = txtRadius
       CalculateArea = radius * radius * PI
   End Function
   
   Sub ProcessCircumference()
       Dim radius As Double
       Dim circumference
   
       radius = txtRadius
       circumference = CalculateDiameter() * PI
       
       txtCircumference = circumference
   End Sub
   
   Private Sub cmdCalculate_Click()
       txtDiameter = CalculateDiameter
       ProcessCircumference
       txtArea = CalculateArea
   End Sub

8. Return to Microsoft Access and click the button again:

   

9. Save and close the form

Functions and Arguments

A Function With a Parameter

Like a sub-procedure, a function may need one or more external values in order to carry its assignment. To create a function that takes an argument, use the same approach as for a sub-procedure. Here is an example:

Function CalculatOvertime(Salary As Double) As Double
    Dim OvertimeSalary
    
    OvertimeSalary = Salary * 1.5
    
    CalculatOvertime = OvertimeSalary
End Function

Practical Learning: Creating a Parameterized Function

1. In the Navigation Pane, right-click Geometric Volume - Cube and click Design View
2. On the Ribbon, click Image and click the left side on the form
3. From the resources that accompany these lessons, select and open the Cube.bmp file:

   

4. In the Tools section of the Design tab of the Ribbon, click the View Code button
5. In the Code Editor, type Function CalculateSingleArea(side As Double) As Double and press Enter
6. Implement the function as follows:
   

   Function CalculateSingleArea(side As Double) As Double
       CalculateSingleArea = side * side * 6
   End Function

Calling a Function With a Parameter

To call a function that takes an argument, if you are not planning to use its return value, proceed as done for a sub-procedure: Type the name of the function followed by a space and the argument. Here is an example:

Function CalculatOvertime(Salary As Double) As Double
    Dim OvertimeSalary
    
    OvertimeSalary = Salary * 1.5
    
    CalculatOvertime = OvertimeSalary
End Function

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    
    HourlyWages = txtHourlySalary
    
    CalculatOvertime HourlyWages
End Sub

Otherwise, if you want to use the value produced by the function, assign its call to a variable and put the argument in parentheses. Here is an example:

Function CalculatOvertime(Salary As Double) As Double
    Dim OvertimeSalary
    
    OvertimeSalary = Salary * 1.5
    
    CalculatOvertime = OvertimeSalary
End Function

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    Dim OvertimeSalary As Double
    
    Let HourlyWages = txtHourlySalary
    
    OvertimeSalary = CalculatOvertime(HourlyWages)
    Let txtOvertimeSalary = OvertimeSalary
End Sub

Practical Learning: Calling a Parameterized Function

1. In the Object combo box, select cmdCalculate
2. Implement its Click event as follows:
   

   rivate Sub cmdCalculate_Click()
       Dim s As Double
       Dim sa As Double
       
       s = txtSide
       sa = CalculateSingleArea(s)
   
       txtSingleArea = sa
   End Sub

3. Return to Microsoft Access and switch the form to Form View
4. Click Side and type 38.79
5. Click the Calculate button

   

6. Return to Microsoft Visual Basic
7. To involve a call to a function in an expression, create two additional functions and call them as follows:

   Function CalculateTotalArea(side As Double) As Double
       CalculateTotalArea = CalculateSingleArea(side) * 6
   End Function
   
   Function CalculateVolume(side As Double) As Double
       CalculateVolume = CalculateSingleArea(side) * side
   End Function
   
   Private Sub cmdCalculate_Click()
       Dim s As Double
       Dim sa As Double
       Dim ta As Double
       Dim vol As Double
       
       s = txtSide
       sa = CalculateSingleArea(s)
       ta = CalculateTotalArea(s)
       vol = CalculateVolume(s)
       
       txtSingleArea = sa
       txtTotalArea = ta
       txtVolume = vol
   End Sub
   

8. Return to Microsoft Access and click the button again:

   

9. Save and close the form

A Function With Many Parameters

A function needs many parameters, create it as we saw for sub-procedures. Make sure the last line of the function specifies the value it returns. When calling the function, specify the arguments in its parentheses. Here is an example:

Function CalculatOvertime(Salary As Double, Times As Double) As Double
    CalculatOvertime = Salary * Times
End Function

Private Sub cmdCalculate_Click()
    Dim HourlyWages As Double
    Dim TimeWorked As Double
    Dim WeeklyWages As Double
    
    HourlyWages = txtHourlySalary
    TimeWorked = txtTimeWorked
    
    WeeklyWages = CalculatOvertime(HourlyWages, TimeWorked)
    txtWeeklySalary = WeeklyWages
End Sub

Practical Learning: Writing Procedures With Parameters

1. In the Navigation Pane, right-click Geometry - Box and click Design View
2. On the Ribbon, click Image and click the left side on the form
3. From the resources that accompany these lessons, select and open the box.bmp file:

   

4. In the Tools section of the Design tab of the Ribbon, click the View Code button
5. To create and call functions that use more than one parameter, type the following code:

   Function CalculateArea(dblLength As Double, _
                          dblHeight As Double, _
                          dblWidth As Double) As Double
       Dim area As Double
       
       area = 2 * ((dblLength * dblHeight) + _
                   (dblHeight * dblWidth) + _
                   (dblLength * dblWidth) _
                  )
       CalculateArea = area
   End Function
   
   Function CalculateVolume(dblLength As Double, _
                            dblHeight As Double, _
                            dblWidth As Double) As Double
       Dim volume As Double
       
       volume = dblLength * dblHeight * dblHeight
       CalculateVolume = volume
   End Function

6. In the Object combo box, select cmdCalculate and call the functions as follows:

   Private Sub cmdCalculate_Click()
       Dim area As Double, volume As Double
       Dim dWidth As Double, dHeight As Double, dDepth As Double
       
       Let dWidth = txtWidth
       Let dHeight = txtHeight
       Let dDepth = txtDepth
       
       Let area = CalculateArea(dWidth, dHeight, dDepth)
       Let volume = CalculateVolume(dWidth, dHeight, dDepth)
       
       Let txtArea = area
       Let txtVolume = volume
   End Sub

7. Return to Microsoft Access and switch the form to Form View

   

8. Enter some decimal numbers for the width, the height, and the depth

   

9. Click the Calculate button

   

10. Save and close the form

Techniques of Passing Arguments

Optional Arguments

Unless specified otherwise, from this section and the rest of our lessons, the word "procedure" represents both the sub-procedure and the function. The name "sub-procedure" will be used only if the issue particularly relates to that type. The name "function" will be used only if the procedure returns a value, that is, if the topic is particularly related to functions.

If you create a procedure that takes an argument, whenever you call that procedure, you must provide a value for that argument. If you fail to do that, you will receive an error. Imagine you create a function that will be used to calculate the final price of an item after discount. The function would need the discount rate in order to perform the calculation.

Practical Learning: Introducing Optional Arguments

1. In the Navigation Pane, right-click Price Processing and click Design View
2. In the Tools section of the Ribbon, click View Code button
3. Create a function as follows:

   Function CalculateNetPrice(DiscountRate As Double) As Currency
       Dim OrigPrice As Double
       
       OrigPrice = CCur(txtMarkedPrice)
       CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
   End Function

4. Since this function expects an argument, if you don't supply it, the form will produce an error. For an example, do this.
   In the Object combo box, select cmdCalculate
5. Implement the event as follows:

   Function CalculateNetPrice(DiscountRate As Double) As Currency
       Dim OrigPrice As Double
       
       OrigPrice = CCur(txtMarkedPrice)
       CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
   End Function
   
   Private Sub cmdCalculate_Click()
       txtPriceAfterDiscount = CalculateNetPrice()
   End Sub

6. Return to Microsoft Access
7. Click Marked Price and type a decimal number such as 125.50

   

8. Click the Calculate button and notice the error:

   

9. Read the message box and click OK
10. Return to Microsoft Visual Basic and, on the Standard toolbar, click the Reset button
11. If a procedure uses the same value over and over again, instead of supplying an argument every time you call the procedure, you can provide a default value for the argument. Such an argument is referred to as optional.
    

    To make an argument optional, in the parentheses of its procedure, start the parameter with the Optional keyword. On the right side of the data type of the argument, type the assignment operator, followed by the desired default value. To apply an example, change the code as follows:

    Function CalculateNetPrice(Optional DiscountRate As Double = 0.20) As Currency
        Dim OrigPrice As Double
        
        OrigPrice = CCur(txtMarkedPrice)
        CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
    End Function
    
    Private Sub cmdCalculate_Click()
        txtPriceAfterDiscount = CalculateNetPrice(dblDiscount#)
    End Sub

12. Return to Microsoft Access and click the Calculate button:

    

13. Return to Microsoft Visual Basic
14. With an optional argument, if you omit the value of the argument, the default value would be used. At another time, when calling the function, if you want to use a value that is different from the default value, you can provide the desired value.
    Chande the code as follows:

    Function CalculateNetPrice(Optional DiscountRate As Double = 0.2) As Currency
        Dim OrigPrice As Double
        
        OrigPrice = CCur(txtMarkedPrice)
        CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim dblDiscount#
        
        dblDiscount = CDbl(txtDiscountRate)
        Let txtPriceAfterDiscount = CalculateNetPrice(dblDiscount)
    End Sub

15. Return to Microsoft Acccess
16. Click DiscountRate and type a number such as 35

    

17. Click the Calculate button and notice the error:

    

18. Return to Microsoft Access
19. Save and close the form
20. In the Navigation Pane, right-click Store Item Sale and click Design View
21. In the Tools section of the Ribbon, click the View Code button
22. You can create a procedure that uses more than one parameter. You may want all, one, or more than one of the arguments to be optional. To do this, declare each optional argument with the Optional keyword and assign it the desired value.
    For an example, create a function as follows:

    Function CalculateNetPrice(OrigPrice As Currency, _
                               Optional TaxRate As Double = 0.0575, _
                               Optional DiscountRate As Double = 0.25) As Currency
        Dim curDiscountValue As Currency
        Dim curPriceAfterDiscount As Currency
        Dim curTaxValue As Currency
        Dim curNetPrice As Currency
        
        curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
        curPriceAfterDiscount = OrigPrice - curDiscountValue
        curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
        
        txtDiscountValue = CStr(curDiscountValue)
        txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
        txtTaxValue = CStr(curTaxValue)
        CalculateNetPrice = curPriceAfterDiscount + curTaxValue
    End Function

23. In the Object combo box, select cmdCalculate
24. If you create a procedure that uses more than one parameter, when calling the procedure, make sure you know what argument is optional and which one is required. When calling a procedure that has more than one parameter but only one of them is optional, you can provide a value for the required argument and omit the others. To see an example, call the function as follows:

    Private Sub cmdCalculate_Click()
        Dim curMarkedPrice As Currency
        Dim dblTaxRate#
        
        Let curMarkedPrice = CCur(txtMarkedPrice)
        Let dblTaxRate = CDbl(txtTaxRate)
        Let txtNetPrice = CalculateNetPrice(txtMarkedPrice)
    End Sub

25. Return to Microsoft Access and switch the form to Form View
26. Click Marked Price and type 125.55
27. Click Tax Rate and type 7.75

    

28. Click the Calculate button

    

29. Return to Microsoft Visual Basic
30. To call the function without using the default values, change the code as follows:

    Private Sub cmdCalculate_Click()
        Dim curMarkedPrice As Currency
        Dim dblDiscountRate#
        Dim dblTaxRate#
        
        curMarkedPrice = CCur(txtMarkedPrice)
        dblDiscountRate = CDbl(txtDiscountRate)
        dblTaxRate = CDbl(txtTaxRate)
        txtNetPrice = CalculateNetPrice(txtMarkedPrice, txtTaxRate, dblDiscountRate)
    End Sub

31. Return to Microsoft Access
32. Click Discount Rate and type 25
33. Click the Calculate button

    

34. Return to Microsoft Visual Basic
35. If you create a procedure that has more than one parameter and at least one of them has a default value, if the optional argument is positioned to the left of a required argument, when calling the procedure, if you don't want to provide a value for the optional argument, enter a comma in its placeholder to indicate that there would have been a value for the argument but you prefer to use the default value. Remember that you must provide a value for any required argument. For an example, change the code as follows:

    Function CalculateNetPrice(OrigPrice As Currency, _
                               Optional TaxRate As Double = 0.0575, _
                               Optional DiscountRate As Double = 0.25) As Currency
        Dim curDiscountValue As Currency
        Dim curPriceAfterDiscount As Currency
        Dim curTaxValue As Currency
        Dim curNetPrice As Currency
        
        curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
        curPriceAfterDiscount = OrigPrice - curDiscountValue
        curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
        
        txtDiscountValue = CStr(curDiscountValue)
        txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
        txtTaxValue = CStr(curTaxValue)
        CalculateNetPrice = curPriceAfterDiscount + curTaxValue
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim curMarkedPrice As Currency
        Dim dblDiscountRate#
        Dim dblTaxRate#
        
        curMarkedPrice = CCur(txtMarkedPrice)
        dblDiscountRate = CDbl(txtDiscountRate)
        txtNetPrice = CalculateNetPrice(curMarkedPrice, , dblDiscountRate)
    End Sub

36. Return to Microsoft Access and click the Calculate button:

    

37. Close the form
38. When asked whether you want to save it, click Yes

Random Call of Arguments

When you call a procedure that takes more than one argument, you must pass the arguments in the right order. Consider the following function:

Function ResumeEmployee$(salary As Currency, name As String, dHired As Date)
    Dim strResult$
    
    Let strResult = name & ", " & CStr(dHired) & ", " & CStr(salary)
    Let ResumeEmployee = strResult
End Function

When calling this function, you must pass the first argument as a currency value, the second as a string, and the third as a date value. If you pass a value in the wrong position, you would receive an error. This is what would happen if you call it as follows:

Private Sub cmdResume_Click()
    Dim strFullName As String
    Dim dteHired As Date
    Dim curHourlySalary As Currency
    Dim strResume$
    
    Let strFullName = [txtFullName]
    Let dteHired = CDate([txtDateHired])
    Let curHourlySalary = CCur(txtHourlySalary)
    Let strResume = ResumeEmployee(strFullName, dteHired, curHourlySalary)
    Let txtResume = strResume
End Sub

While you must respect this rule, Microsoft Visual Basic provides an alternative. You don't have to pass the arguments in their strict order. Instead, you can assign the desired value to each argument using its name. To do this, when calling the procedure, in the parentheses of the function, type the name of the parameter, followed by the := operator, followed by the (appropriate) value.

Practical Learning: Randomly Passing Arguments

1. In the Navigation Pane, double-click the Employees Records form

   

2. After viewing the form in Form View, right-click its title bar and click Design View
3. On the form, right-click the Resume button and click Build Event...
4. Double-click Code Builder
5. Change the file as follows:
   

   Function ResumeEmployee(Salary As Currency, _
                           Name As String, _
                           DateHired As Date) As String
       Dim strResult As String
       
       strResult = Name & ", " & CStr(DateHired) & ", " & CStr(Salary)
       ResumeEmployee = strResult
   End Function
   
   Private Sub cmdResume_Click()
       Dim strFullName As String
       Dim dteHired As Date
       Dim curHourlySalary As Currency
       Dim strResume$
       
       strFullName = [txtFullName]
       dteHired = CDate([txtDateHired])
       curHourlySalary = CCur(txtHourlySalary)
       strResume = ResumeEmployee(Name:=strFullName, DateHired:=dteHired, _
                                  Salary:=curHourlySalary)
       txtResume = strResume
   End Sub

6. Return to Microsoft Access and switch the form to Form View
7. Click Date Hired and type 02/12/2018
8. Click Hourly Salary and type 32.05
9. Click Full Name and type Douglas Jacobs

   

10. Click Resume

    

11. Close the form
12. When asked whether you want to save it, click Yes

Passing Arguments By Value

Normally, when you call a procedure that uses one or more parameters, the argument itself is not changed. This technique is referred to as passing an argument by value. To reinforce this, you can type the ByVal keyword on the left side of the argument. Here is an example:

Function CalculateTriangleArea#(ByVal Base As Double, ByVal Height As Double)
    CalculateTriangleArea = Base * Height / 2
End Function

Practical Learning: Passing Arguments By Value

1. In the Navigation Pane, right-click the Triangle form and click Design View
2. On the form, right-click the Calculate button and click Build Event...
3. Click Code Builder and click OK
4. Change the file as follows:
   

   Private Function CalculateTriangleArea(ByVal Base As Double, _
                                  	       ByVal Height As Double) As Double
       CalculateTriangleArea = Base * Height / 2
   End Function
   
   Private Sub cmdCalculate_Click()
       Dim dblBase As Double
       Dim dblHeight As Double
       
       dblBase = CDbl([txtBase])
       dblHeight = CDbl([txtHeight])
       
       txtArea = CalculateTriangleArea(dblBase, dblHeight)
   End Sub

5. Return to Microsoft Access and switch the form to Form View
6. Click Base and type 24.95
7. Click Height and type 16.82

   

8. Click Calculate

   

9. Return to Microsoft Visual Basic

Passing Arguments By Reference

The Visual Basic language includes a feature that allows a procedure to change the value of a parameter. This is referred to as passing an argument by reference. To do this, type the ByRef keyword on the left side of the name of the parameter.

If you create a procedure that uses more than one parameter, you can decide which one(s) would be passed by value and which one(s) would be passed by reference.

Practical Learning: Passing Arguments By Reference

1. Change the file as follows:
   

   Private Sub CalculateTriangleArea(ByRef Area As Double, _
                             	  ByVal Base As Double, _
                             	  ByVal Height As Double)
       Area = Base * Height / 2
   End Sub
   
   Private Sub cmdCalculate_Click()
       Dim dblBase As Double
       Dim dblHeight As Double
       Dim dblArea As Double
       
       Let dblBase = txtBase
       Let dblHeight = txtHeight
       
       CalculateTriangleArea dblArea, dblBase, dblHeight
       Let txtArea = dblArea
   End Sub

2. Return to Microsoft Access
3. Click Base and type 34.72
4. Click Height and type 20.84
5. Click Calculate

   

6. Close the form
7. When asked whether you want to save it, click Yes

Options on Variables and Procedures

Introduction to the Scope and Lifetime of a Procedure

Like a variable, a procedure has scope. A procedure is global if it can be accessed from any code in the database. Normally, any procedure you create like those we have used so far is global.

Private Procedures

A procedure is private if it can be accessed only by events and procedures of the same module. To create a private procedure, precede its introductory Sub or Function with the Private keyword.

Public Procedures

A public procedure is one that can be called from any event or any procedure of the same application. As mentioned above, this is the default for the procedures. To re-inforce the fact that a procedure is public, precede its creation with the Public keyword.

Inserting a Procedure

Microsoft Visual Basic simplifies the creation of a procedure through the use of the Insert Procedure dialog box. To display the Insert Procedure:

• On the main menu, click Insert -> Procedure
• On the Standard toolbar, click the arrow of the Insert button and click Procedure

If you are creating a sub-procedure, click the Sub radio button. If you want the procedure to be accessed only by the objects, events and procedure of the same module, click the Private radio button. If you want to access the procedure from outside of the current module, click the Public radio button.

Practical Learning: Ending the Lesson

• Close Microsoft Access