Home

Introduction to Procedures

 

Modules

 

Introduction

A module is a file that holds code or pieces of code in a Visual Basic application. Each form or report of a database has a (separate) module. To access the module of a form or report, you can open the object in Design View and click the View Code button View Code. If you initiate the coding of an event of a form (or report) or of a control that is positioned on a form (or report), this would open the Code Editor window and display the module that belongs to the form (or report). If no code has been written for the form or report, its module would be empty:

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.

Creating a 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
     
    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
       
      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.

Practical Learning: Creating a Module

  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:
     
    Geometric Figures
    Geometric Figures
    Geometric Figures
  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 View Code

Sub Procedures

 

Introduction

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.

Creating a Procedure Manually

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.

Practical Learning: Manually Creating a Procedure

  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

Inserting a Procedure

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

Insert Procedure

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: Inserting a Procedure

  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
     
    Insert Procedure
  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

Calling a Procedure

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

Practical Learning: Calling a Procedure

  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:
     
    Geometric Procedures
  9. Save the form and return to the Code Editor

Functions

 

Introduction

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.

Creating a Function

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:

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

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

Practical Learning: Creating a 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

Calling a 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

Practical Learning: Calling a Function

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

Accessories for Programming

 

Introduction

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 Compiler

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.

Writing Procedures With Arguments

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

Practical Learning: Writing Procedures With Arguments

  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

Calling Procedures That Have Arguments

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

Practical Learning: Calling Procedures With Arguments

  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
     
    Geometric Figures
  7. Save and close the form
  8. Close Microsoft Access

Techniques of Passing Arguments

 

Optional Arguments

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:

Price Calculation

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

Price Calculation

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

Price Calculation

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

Price Calculation

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

Price Calculation

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

Price Calculation

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

Price Calculation

Practical Learning: Using Default Arguments

  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

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$
    
    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.

Practical Learning: Randomly Passing Arguments

  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
     
    Employees Records
  12. Close the form
  13. When asked whether you want to save it, click Yes

Passing Arguments By Value

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

Practical Learning: Passing Arguments By Value

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

Passing Arguments By Reference

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.

Practical Learning: Passing Arguments By Reference

  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
     
    Triangle
  6. Close the form
  7. When asked whether you want to save it, click Yes

 

 

 
   

Previous Copyright © 2005-2011 FunctionX, Inc. Next