Home

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 Image and click the left side on the form
  5. From the resources that accompany these lessons, select and open the Rectangle.bmp file:

    Geometric Figures - Rectangle

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

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:

    Geometric Procedures

  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:

Character The function must return
$ A String type
% An Integer
! a Single type
# a Double
@ a Long

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 Image and click the left side on the form
  3. From the resources that accompany these lessons, select and open the Circle.bmp file:

    Geometric Figures

  4. In the Tools section of the Design tab of the Ribbon, click the View Code button View Code
  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

    Geometric Figures

  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:

    Geometric Figures

  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 Image and click the left side on the form
  3. From the resources that accompany these lessons, select and open the Cube.bmp file:

    Geometric Volumes - Cube

  4. In the Tools section of the Design tab of the Ribbon, click the View Code button View Code
  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

    Geometric Volumes - Cube

  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:

    Geometric Volumes - Cube

  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 Image and click the left side on the form
  3. From the resources that accompany these lessons, select and open the box.bmp file:

    Geometric Figures

  4. In the Tools section of the Design tab of the Ribbon, click the View Code button View Code
  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

    Geometric Figures

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

    Geometric Figures

  9. Click the Calculate button

    Geometric Figures

  10. Save and close the form
 
 
 

Techniques of Passing Arguments

Optional Arguments

Author Note 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 View Code
  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

    Price Calculation

  8. Click the Calculate button and notice the error:

    Price Calculation

  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:

    Price Calculation

  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

    Price Calculation

  17. Click the Calculate button and notice the error:

    Price Calculation

  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 View Code
  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

    Price Calculation

  28. Click the Calculate button

    Price Calculation

  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

    Price Calculation

  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:

    Price Calculation

  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

    Randomly Passing Arguments

  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

    Randomly Passing Arguments

  10. Click Resume

    Randomly Passing Arguments

  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

    Passing Arguments By Value

  8. Click Calculate

    Passing Arguments By Value

  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

    Passing Arguments By Reference

  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

Insert 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
 
 
   
 

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