Introduction to Procedures

If you have written events for a form (or report) or for the controls that belong to the form (or report), all these events would be part of the form's (or report's) module.

Besides the modules that belong to forms (or reports), you can create your own module(s) that is(are) not related to a particular form (or report). There are three main ways you can create an independent module:

• In Microsoft Access, on the Ribbon, click Create. In the Other section, click the arrow under the Macro button and click Module
   
  
• In Microsoft Visual Basic
  • On the main menu, click Insert -> Module
  • On the Standard toolbar, click the arrow of the Insert Module button and click Module
     
    

The names of modules are cumulative. This means that the first module would be called Module1; the second would be Module2, etc. It is a good idea to have names that are explicit especially if your application ends up with various modules. To use a custom name for a module, you must save it. This would prompt you to name the module. You can accept the suggested name or type your own and press Enter.

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Exercise2 application.
   (Make sure you click the Options... button and Enable the security)
3. In the Navigation Pane, double-click the frmProcedures form to open it in Form View. After viewing the form, switch it to Design View
4. The accompanying resources include pictures of geometric figures. To enhance the form, you can add other pictures. To do that, in Controls section of the Ribbon, click Image and click the left area of the labels. On the Insert Picture dialog box, locate the picture and add it:
    
5. To start a form module, with the form opened in Design View, on the Ribbon, in the Tools section of the Design tab, click the View Code button

A procedure is an assignment you ask Microsoft Visual Basic to perform besides, or to complete, the normal flow of the program. A procedure is created to work in conjunction with the controls' events of a database. Structurally, a procedure appears similar to an event. The main difference is that, while an event belongs to a control, a procedure does not. While an event is specific to the user's intervention or interaction with a control, a procedure can be general and applied anyway you like.

There are two kinds of procedures in Microsoft Visual Basic: A sub procedure and a function. The difference lies on their behaviors but their coding (programming) depends of your goal.

A sub procedure is a section of code that carries an assignment but does not give back a result. To create a sub procedure, start the section of code with the Sub keyword followed by a name for the sub procedure. To differentiate the name of the sub procedure with any other regular name, it must be followed by an opening and a closing parentheses. The Sub keyword and the name of the procedure (including its parentheses) are written on one line (by default). The section of the sub procedure code closes with End Sub as follows:

Sub ProcedureName()

End Sub

The name of a sub procedure should follow the same rules we learned to name the 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. Here are examples: show, display
• To make the name of a sub procedure stand, you should start it in uppercase. Examples are Show, Play, Dispose, Close
• You should use explicit names that identify the purpose of the sub procedure. If a procedure would be used as a result of another procedure or a control's event, reflect it on the name of the sub procedure. Examples would be: afterupdate, longbefore.
• If the name of a procedure is a combination of words, start each word in uppercase. Examples are: AfterUpdate, SayItLoud

The section between the Sub ProcedureName 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 need inside of a sub procedure. The actions you perform inside of a procedure depend on what you are trying to accomplish. For example, a procedure can simply be used to create a string. The above procedure can be changed as follows:

Sub CreateName()
    Dim strFullName As String
    strFullName = "Jacques Fame Ndongo"
End Sub

Similarly, 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

There are two main ways a procedure receives values. To start, a procedure that is written in the module of a form (or report) has direct access to the controls that belong to the form (or report). This means that the procedure can call them and manipulate any of their available properties. Here is an example of a procedure implemented in a form that has a text box called txtCountry:

Sub ChangeColor()
    txtCountry.BackColor = 16763293
End Sub

In the same way, you can declare variables and perform operations inside of a procedure and hand the result to a control that is part of a form or report.

1. Click the first empty line in the Code Editor, type Sub SquareSolution 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 SquareSolution() ' Declare the necessary variables for the square Dim dblSide As Double Dim dblPerimeter, dblArea As Double ' Retrieve the value of the side dblSide = txtSqSide ' Calculate the perimeter and the are of the square dblPerimeter = dblSide * 4 dblArea = dblSide * dblSide ' Prepare to display the result in the appropriate text boxes txtSqPerimeter = dblPerimeter txtSqArea = dblArea End Sub

4. Return to Microsoft Access and switch the form to Form View. Enter a value in the Side text box of the square and click the Calculate button. Notice that nothing happens

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.

1. Switch the form back to Design View and return to Microsoft Visual Basic
2. On the main menu, click Insert -> Procedure...
3. On the Insert Procedure dialog box, click the Name text box and type SolveRectangle
4. In the Type section, click the Sub radio button if necessary.
   In the Scope section, click the Private radio button
    
   
5. Click OK
6. Implement the procedure as follows:
    

   Private Sub SolveRectangle() ' Declare the necessary variables for the rectangle Dim dblLength, dblHeight As Double Dim dblPerimeter, dblArea As Double ' Retrieve the values of the length and height dblLength = txtRLength dblHeight = txtRHeight ' Calculate the perimeter and the area of the rectangle dblPerimeter = (dblLength + dblHeight) * 2 dblArea = dblLength * dblHeight ' Prepare to display the result in the appropriate text boxes txtRPerimeter = dblPerimeter txtRArea = dblArea End Sub

After creating a procedure, you can call it from another procedure, function, or control's event. To call a simple procedure, you can just write its name. Here is an example where a sub procedure is called when a form is clicked.

Private Sub Detail_Click()
    ChangeColor
End Sub

1. In the Object combo box, select cmdSqCalculate
2. Call the SolveSquare procedure as follows:
    

   Private Sub cmdSqCalculate_Click() SquareSolution End Sub

3. In the Object combo box, select cmdRCalculate and implement its Click event follows:
    

   Private Sub cmdRCalculate_Click() SolveRectangle End Sub

4. Return to Microsoft Access and switch the form to Form View
5. In the Side text box of the square, type 14.55 and click the right Calculate button
6. Click the Length text box (of the rectangle) and type 18.25
7. Click the Height text box (of the rectangle) and type 15.75
8. Click its Calculate button:
    
   
9. Save the form and return to the Code Editor

A function is a procedure that takes care of an assignment and returns a result. A function resembles a sub procedure in all respects except that a function returns a value.

A function is created like a sub procedure with a few more rules. The creation of a function starts with the Function keyword and closes with End Function. Here is an example:

Function GetFullName()

End Function

The name of the function follows the same rules and suggestions we reviewed for the sub procedures. Because a function should return a value, after the parentheses, you can type the As keyword followed by the type of data the function must return. Here is an example:

Function GetFullName() As String

End Function

When we studied variables, we saw that, instead of using the As DataType expression, you could use a particular 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 then omit the As DataType expression. An example would be GetFullname$(). As with the variables, you must use the appropriate character for the function:

Here is an example:

Function GetFullName$()
        
End Function

The implementation of a function is done the same way that of a sub procedure is. Because a function is supposed to return a value, after performing whatever assignment you need in a function, you can assign the desired result to the name of the function before the closing of the function. Here is an example:

Function GetFullName() As String
    Dim strFirstName, strLastName As String
    strFirstName = txtFirstName
    strLastName = txtLastName
    GetFullName = strFirstName & " " & strLastName
End Function

1. In the Code Editor, scroll down, click the first empty line, type Function CircleCircumference As Double and press Enter
2. 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 CircleCircumference() As Double Dim dblRadius As Double dblRadius = txtCircleRadius CircleCircumference = dblRadius * 2 * 3.14159 End Function

3. On the main menu, click Insert -> Procedure...
4. Type CircleArea in the Name text box
5. In the Type section, click the Function radio
6. In the Scope section, click the Private radio button and click OK
7. Implement the function as follows:
    

   Private Function CircleArea() As Double Dim dblRadius As Double dblRadius = txtCircleRadius CircleArea = dblRadius * dblRadius * 3.14159 End Function

To call a function, you have two main alternatives. If the function was implemented as simple as a sub procedure, you can just write its name in the event or the function that is calling it. If you want to use the return value of a function in an event or another function, assign the name of the function to the appropriate local variable. Here is an example:

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

1. In the Object combo box, select cmdCCalculate and implement its Click event as follows:
    

   Private Sub cmdCCalculate_Click() txtCircleCircumference = CircleCircumference txtCircleArea = CircleArea End Sub

2. Switch to Microsoft Access and switch the form to Form View
3. Click the Circular tab and, in the top Radius text box, type 25.55
4. Click the right calculate button (for the Circle)
    
   
5. Switch the form back to Design View

When using a database, you are in fact using two applications to create a final product. Microsoft Access is used to design the necessary objects for your product. This means that Microsoft Access is used for its visual display of objects. On the other hand, Microsoft Visual Basic is used to handle code that enhances the functionality of your application.

The code you write is made of small instructions written in Visual Basic. These instructions are written in plain English, a language that the computer, that is, the operating system, doesn't understand. Visual Basic, as its own language among other computer languages, is internally equipped with a low level program called a compiler. This program takes your English language instructions and translates them in a language the computer can understand. The language the computer speaks is known as the machine language. You usually don't need to know anything about this language.

After writing your code, at one time it is transmitted to the compiler. The compiler analyzes it first and checks its syntax. The words used in the program and the variables are checked for their declaration and use. The events and procedures are checked for their behavior. The expressions are checked for their accuracy. If something is wrong with the code, that is, if the compiler does not understand something in your code, it would display an error and stop. You must correct the mistake or else... As long as the compiler cannot figure out a piece of code in a module, it would not validate it. If the code is "admissible", the compiler would perform the assignments that are part of the code and give you a result based on its interpretation of the code. This means that the code can be accurate but produce an unreliable or false result. This is because the compiler is just another program: it does not think and does not correct mistakes although it can sometimes point them out.

To carry out an assignment, sometimes a procedure needs one or more values to work on. If a procedure needs a value, such a value is called an argument. While a certain procedure might need one argument, another procedure might need many arguments. The number and types of arguments of a procedure depend on your goal. If you are writing your own procedure, then you will decide how many arguments your procedure would need. You also decide on the type of the argument(s). For a procedure that is taking one argument, inside of the parentheses of the procedure, write the name of the argument followed by the As keyword followed by the type of data of the argument. Here is an example:

Sub CalculateArea(Radius As Double)
   
End Sub

A procedure can take more than one argument. If you are creating such a procedure, between the parentheses of the procedure, write the name of the first argument followed by As, followed by the data type, followed by a comma. Add the second argument and subsequent arguments and close the parentheses. There is no implied relationship between the arguments; for example, they can be of the same type:

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

The arguments of your procedure can also be as varied as you need them to be. Here is an example:

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

1. Switch to the Code Editor. Click an empty area at the end of the existing code and create the following procedure:
    

   Sub SolveEllipse(SmallRadius As Double, LargeRadius As Double) Dim dblCircum As Double Dim dblArea As Double dblCircum = (SmallRadius + LargeRadius) * 2 dblArea = SmallRadius * LargeRadius * 3.14159 txtEllipseCircumference = dblCircum txtEllipseArea = dblArea End Sub

2. To create an example of a function that takes an argument, add the following function at the end of the existing code:
    

   Function CubeArea(Side As Double) As Double CubeArea = Side * Side * 6 End Function

3. To use different examples of functions that take one or two arguments, type the following functions:
    

   Function CubeVolume(Side As Double) As Double CubeVolume = Side * Side * Side End Function

   Function BoxArea(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Area As Double Area = 2 * ((dblLength * dblHeight) + _ (dblHeight * dblWidth) + _ (dblLength * dblWidth) _ ) BoxArea = Area End Function

   Function BoxVolume(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Volume As Double Volume = dblLength * dblHeight * dblHeight BoxVolume = Volume End Function

There are various ways you can call a sub procedure. As we saw already, if a sub procedure does not take an argument, to call it, you can just write its name. If a sub procedure is taking an argument, to call it, type the name of the sub procedure followed by the name of the argument. If the sub procedure is taking more than one argument, to call it, type the name of the procedure followed by the name of the arguments, in the exact order they are passed to the sub procedure, separated by a comma. Here is an example:

Private Sub txtResult_GotFocus()
    Dim dblHours As Double
    Dim dblSalary As Double
    
    dblHours = txtHours
    dblSalary = txtSalary
    
    CalcAndShowSalary dblHours, dblSalary
End Sub

Sub CalcAndShowSalary(Hours As Double, Salary As Double)
    Dim dblResult As Double
    
    dblResult = Hours * Salary
    txtResult = dblResult
End Sub

Alternatively, you can use the Call keyword to call a sub procedure. In this case, when calling a procedure using Call, you must include the argument(s) between the parentheses. using Call, the above GotFocus event could call the CalcAndShowSalary as follows:

Private Sub txtResult_GotFocus()
    Dim dblHours As Double
    Dim dblSalary As Double
    
    dblHours = txtHours
    dblSalary = txtSalary
    
    Call CalcAndShowSalary(dblHours, dblSalary)
End Sub

1. To call the above procedures that take arguments, on the Object combo box, select cmdECalculate and implement its OnClick event as follows:
    

   Private Sub cmdECalculate_Click() Dim Radius1 As Double Dim Radius2 As Double Radius1 = txtEllipseRadius1 Radius2 = txtEllipseRadius2 SolveEllipse Radius1, Radius2 End Sub

2. On the Object combo box, select cmdCubeCalculate and implement its Click event as follows:
    

   Private Sub cmdCubeCalculate_Click() Dim dblSide As Double Dim dblArea As Double Dim dblVolume As Double dblSide = txtCubeSide dblArea = CubeArea(dblSide) dblVolume = CubeVolume(dblSide) txtCubeArea = dblArea txtCubeVolume = dblVolume End Sub

3. On the Object combo box, select cmdBoxCalculate and implement its Click event as follows:
    

   Private Sub cmdBoxCalculate_Click() Dim dLen As Double Dim dHgt As Double Dim dWdt As Double Dim Area, Vol As Double dLen = txtBoxLength dHgt = txtBoxHeight dWdt = txtBoxWidth Area = BoxArea(dLen, dHgt, dWdt) Vol = BoxVolume(dLen, dHgt, dWdt) txtBoxArea = Area txtBoxVolume = Vol End Sub

4. Close Microsoft Visual Basic and return to Microsoft Access
5. Switch the form to Form View and test the ellipse in the Circular tab
6. Also test the cube and the box in the 3-Dimensions tab
    
   
7. Save and close the form
8. Close Microsoft Access

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 provide a value for the argument, when the application runs, you would 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. Such a function would look like this:

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

Since this function expects an argument, if you don't supply it, the following program would not compile:

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()
    Dim dblDiscount#
    
    dblDiscount = CDbl(txtDiscountRate)
    txtPriceAfterDiscount = CalculateNetPrice(dblDiscount)
End Sub

If a procedure such as this CalculateNetPrice() function uses the same discount rate over and over again, instead of supplying an argument all the time, you can provide a default value for the argument. If you do this, you would not need to provide a value for the argument when you call the procedure. Such an argument is referred to as optional.

To make an argument optional, in the parentheses of its procedure, start it 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 that would be used for the argument if you fail to provide one or decide not to provide one. Based on this, the above CalculateNetPrice() function could be defined as:

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()
    txtPriceAfterDiscount = CalculateNetPrice()
End Sub

Notice that, this time, you don't have to provide a value for the argument when calling the function: 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 should make sure you provide the desired value. Consider the following call:

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)
    txtPriceAfterDiscount = CalculateNetPrice(dblDiscount)
End Sub

Instead of one, you can also create a procedure with more than one argument. You may want all, one, or more than one of these arguments to be optional. To do this, declare each optional argument with the Optional keyword and assign it the desired value.

Consider the following example where two arguments are optional:

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)
    dblTaxRate = CDbl(txtTaxRate)
    txtNetPrice = CalculateNetPrice(txtMarkedPrice, txtTaxRate, dblDiscountRate)
End Sub

If you create a procedure that takes more than one argument, 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 argument but only one argument is optional, you can provide a value for the required argument and omit the others. Here is an example:

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
    
    curMarkedPrice = CCur(txtMarkedPrice)
    txtNetPrice = CalculateNetPrice(txtMarkedPrice)
End Sub

In reality, the Microsoft Visual Basic language allows you to create the procedure with the list of arguments as you see fit, as long as you make sure you clearly specify which argument is optional and which one is required. If you create a procedure that has more than one argument and at least one argument with 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. Consider the following example:

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

1. In the Navigation Pane, double-click the ItemPrice form to open it
2. After viewing it, switch it to Design View
3. On the form, double-click the border of the Calculate button
4. In the Properties window, click Event and double-click the On Click field
5. Click the ellipsis button to open Microsoft Visual Basic
6. Change the file 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 = (curPriceAfterDiscount * TaxRate * 100) / 100 txtDiscountValue = curDiscountValue txtPriceAfterDiscount = curPriceAfterDiscount txtTaxValue = curTaxValue CalculateNetPrice = curPriceAfterDiscount + curTaxValue End Function Private Sub cmdCalculate_Click() Dim curMarkedPrice As Currency Dim dblDiscountRate# Dim dblTaxRate# curMarkedPrice = txtMarkedPrice dblDiscountRate = txtDiscountRate dblTaxRate = txtTaxRate txtNetPrice = CalculateNetPrice(curMarkedPrice, dblTaxRate, dblDiscountRate) End Sub

7. Return to Microsoft Access and switch the form to Form View
8. Click the Marked Price text box if necessary and type 298.95
9. Click the Calculate button
10. Close the form
11. When asked whether you want to save it, click Yes

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$
    
    strResult = name & ", " & CStr(dHired) & ", " & CStr(salary)
    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, the compiler would throw an error and the program would not work. 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$
    
    strFullName = [txtFullName]
    dteHired = CDate([txtDateHired])
    curHourlySalary = CCur(txtHourlySalary)
    strResume = ResumeEmployee(strFullName, dteHired, curHourlySalary)
    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 as long as you know their names. To do this, when calling the function, to assign the desired value to an argument, on the right side of the sub procedure or in the parentheses of the function, type the name of the argument, followed by the := operator, followed by the (appropriate) value.

1. In the Navigation Pane, double-click the Employees Records1 form
2. After viewing the form in Form View, right-click its title bar and click Design View
3. On the form, double-click the border of the Resume button
4. In the Properties window, click Event and double-click the On Click field
5. Click its ellipsis button to open Microsoft Visual Basic
6. 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

7. Return to Microsoft Access and switch the form to Form View
8. Click Date Hired and type 02/12/2008
9. Click Hourly Salary and type 32.05
10. Click Full Name and type Douglas Jacobs
11. Click Resume
     
    
12. Close the form
13. When asked whether you want to save it, click Yes

So far, when creating a procedure with one or more arguments, we simply assumed that, when calling the procedure, we would provide the desired value(s) for the argument(s). With this technique, the procedure  receives the value of the argument and does what it wants with it. 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

1. In the Navigation Pane, right-click the Triangle form and click Design View
2. On the form, click the Calculate button
3. In the Properties window, click Events and double-click the On Click field
4. Click its ellipsis button and change the file as follows:
    

   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. Switch the form back to Design View

We also saw that the main difference between a sub procedure and a function is that a function can return a value but a sub procedure cannot. Microsoft Visual Basic, like many other languages, provides an alternative to this. Not only can a sub procedure return a value but also it makes it possible for a procedure (whether a sub or a function) to return more than one value, a feature that even a regular function doesn't have.

When creating a procedure with an argument, we saw that, by default, the procedure could not modify the value of the argument. If you want to procedure to be able to alter the argument, you can pass the argument by reference. To do this, type the ByRef keyword on the left side of the name of the argument.

If you create a procedure that takes more than one argument, you can decide which one(s) would be passed by value and which one(s) would be passed by reference. There is no order that the arguments must follow.

1. Return to Microsoft Visual Basic and change the file as follows:
    

   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 dblBase = CDbl([txtBase]) dblHeight = CDbl([txtHeight]) CalculateTriangleArea dblArea, dblBase, dblHeight txtArea = dblArea End Sub

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