Lesson 05: Modules and Procedures

	Modules and 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 . 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 Macro & Code section, 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.

Practical Learning: Creating a Module

Start Microsoft Access
From the resources that accompany these lessons, open the Exercise2 application
In the Navigation Pane, double-click the Procedures form to open it in Form View
After viewing the form, switch it to Design View
The accompanying resources include pictures of geometric figures. To enhance the form, you can add them. To do that, on the Ribbon, click Image and click the left area of the labels. On the Insert Picture dialog box, locate the picture and add it.
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

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.

Manually Creating a Procedure

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

Click the first empty line in the Code Editor, type Sub SquareSolution and press Enter
Notice that Visual Basic added the End Sub line and positioned the cursor inside the procedure

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

Return to Microsoft Access and switch the form to Form View
In the Quadrilateral tab, click Side and type 35.55
Click the top 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:

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

Switch the form back to Design View and return to Microsoft Visual Basic
On the main menu, click Insert -> Procedure...
On the Insert Procedure dialog box, click the Name text box and type SolveRectangle
In the Type section, click the Sub radio button if necessary.
In the Scope section, click the Private radio button
Click OK

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

In the Object combo box, select cmdSqCalculate

Call the SolveSquare procedure as follows:

Private Sub cmdSqCalculate_Click()
    SquareSolution
End Sub

In the Object combo box, select cmdRCalculate and implement its Click event follows:
```
Private Sub cmdRCalculate_Click()
    SolveRectangle
End Sub
```
Return to Microsoft Access and switch the form to Form View
In the Side text box of the square, type 14.55
Click the top Calculate button
Click the Length text box (of the rectangle) and type 18.25
Click the Height text box (of the rectangle) and type 15.75
Click its Calculate button:
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

In the Code Editor, scroll down, click the first empty line, type Function CircleCircumference As Double and press Enter

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

On the main menu, click Insert -> Procedure...
Type CircleArea in the Name text box
In the Type section, click the Function radio
In the Scope section, click the Private radio button
Click OK

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

In the Object combo box, select cmdCCalculate

Implement its Click event as follows:

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

Switch to Microsoft Access
Click the Circular tab and, in the top Radius text box, type 25.55
Click the top Calculate button (for the Circle)
Switch the form back to Design View
Switch to the Code Editor

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

Click an empty area at the end of the existing code
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
```

To create an example of a procedure that takes more than one argument, add the following function at the end of the existing code:

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

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

To call the above procedures that take arguments, in the Object combo box, select cmdECalculate

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

In the Object combo box, select cmdCubeCalculate

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

On the Object combo box, select cmdBoxCalculate

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

Close Microsoft Visual Basic and return to Microsoft Access
Switch the form to Form View
Click Circular
Click the top Radius text box and type 15.72
Click the top Calculate button
Click radius and type 22.86
Click the bottom Radius text box and type 55.45
Click the second Calculate button
Click 3-Dimensions tab
Click the Side text box and type 22.26
Click the top Calculate button
Click Length and type 52.82
Click Width and type 8.48
Click Height and type 36.64
Click the second Calculate button
Save and close the form

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

In the Navigation Pane, double-click the ItemPrice form to open it
After viewing it, switch it to Design View
On the form, double-click the border of the Calculate button
In the Properties window, click Event and double-click the On Click field
Click the ellipsis button to open Microsoft Visual Basic

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

Return to Microsoft Access and switch the form to Form View
Click the Marked Price text box if necessary and type 298.95
Click the Calculate button
Close the form
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

In the Navigation Pane, double-click the Employees Records form
After viewing the form in Form View, right-click its title bar and click Design View
On the form, right-click the Resume button and click Build Event...
Double-click Code Builder

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

Return to Microsoft Access and switch the form to Form View
Click Date Hired and type 02/12/2008
Click Hourly Salary and type 32.05
Click Full Name and type Douglas Jacobs
Click Resume
Close the form
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

In the Navigation Pane, right-click the Triangle form and click Design View
On the form, right-click the Calculate button and click Build Event...
Click Code Builder and click OK

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

Return to Microsoft Access and switch the form to Form View
Click Base and type 24.95
Click Height and type 16.82
Click Calculate
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

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

Return to Microsoft Access and switch the form to Form View
Click Base and type 34.72
Click Height and type 20.84
Click Calculate
Close the form
When asked whether you want to save it, click Yes

Fundamentals of Built-In Functions

Introduction

Microsoft Access and Microsoft Visual Basic ship with various functions and procedures you can use in your database. Before creating your own procedures, you should know what is already available so you don't have to re-invent and waste a great deal of your time. The functions already created are very efficient and were tested in various scenarios so you can use them with complete reliability. The available functions range in various types. There are so many built-in functions and procedures that we can only introduce some of them here. You can find out about the others in the Help files because they are fairly documented.

Conversion Functions

When studying variables, we introduced and also reviewed the conversion functions. We saw that each had a corresponding function used to convert a string value or an expression to that type. As a reminder, the general syntax of the conversion functions is:

ReturnType = FunctionName(Expression)

The Expression could be of any kind. For example, it could be a string or expression that would produce a value such as the result of a calculation. The conversion function would take such a value, string, or expression and attempt to convert it. If the conversion is successful, the function would return a new value that is of the type specified by the ReturnType in our syntax.

The conversion functions are as follows:

Function
Name	Return Type	Description
CBool	Boolean	Converts an expression to a Boolean value
CByte	Byte	Converts an expression to Byte number
CDate	Date	Converts an expression to a date, a time, or a combination of date and time
CDbl	Double	Converts an expression to a floating-point number with double precision
CDec	Decimal	Converts an expression to a decimal number
CInt	Integer	Converts an expression to an integer (natural) number
CLng	Long	Converts an expression to a long integer (a large natural) number
CObj	Object	Converts an expression to an Object type
CSng	Single	Converts an expression to a floating-point number with single precision
CStr	String	Converts an expression to a string

These functions allow you to convert a known value to a another type.

The Memory Used by a Data Type

In Lesson 3, we saw that different data types are used to store different values. To do that, each data type requires a different amount of space in the computer memory. To know the amount of space that a data type or a variable needs, you can call the Len() function. Its syntax is:

Public Function Len( _
   ByVal Expression As { Boolean | Byte | Double |
   Integer | Long | Object | Single | String | Date | Variant } _
) As Integer

To call this function, you can declare a variable with a data type of your choice and optionally initialize with the appropriate value, then pass that variable to the function. Here is an example:

Public Sub Exercise()
    Dim Value As Integer

    Value = 774554

    Len(Value)
End Sub

The Beeping Message

If you want, you can make the computer produce a beeping a sound in response to something, anything. To support this, the Visual Basic language provides a function called Beep. Its syntax is:

Public Sub Beep()

Here is an example of calling it:

Private Sub cmdBeep_Click()
    Beep
End Sub

If this function is called when a program is running, the computer emits a brief sound.

The Win32 API

Introduction

We will use many built-in functions in our lessons. Most of those functions will come from the Visual Basic language. Besides the libraries used in Microsoft Access, the Microsoft Windows operating system provides its own library of functions and objects. This library is called the Win32 Application Programming Interface or Win32 API, or simply Win32. The Win32 library is somehow available to applications but its functions are not directly available for a database.

The Win32 library is made of procedures, functions, and classes (mostly structures) that you can use to complement a project. There are so many of these functions and objects that it is hard to know most or all of them. The best way to get acquainted with them is to check its documentation. To do this, you can visit the MSDN web site. The functions are stored in various sub-libraries called dynamic link libraries (DLLs).

Using Win32

Before using a Win32 function in your code, you must first have two pieces of information: the DLL in which the function was created and the actual name of the desired function in that library. Examples of DLLs are shfolder or Kernel32. Once you know the name of the library and the name of the function you want to use, you must import it in your Visual Basic code. The basic formula to follow is:

Private Declare Function Win32FunctionName Lib "LibraryName"
	Alias "CustomName" (Arguments) As DataType

The Win32FunctionName factor is the name of the function in the Win32 library. The LibraryName is the name of the library. You can create a custom name for the function as the CustomName factor. In the parentheses, you can enter the names and types of the arguments. If the procedure returns a value, you can specify its type after the As keyword.

Here is an example:

Option Compare Database
Option Explicit

Private Const MAX_PATH = 260
Private Const CSIDL_PERSONAL = &H5&
Private Const SHGFP_TYPE_CURRENT = 0

' We will use the Windows API to get the path to My Documents
Private Declare Function SHGetFolderPath Lib "shfolder" _
    Alias "SHGetFolderPathA" _
    (ByVal hwndOwner As Long, ByVal nFolder As Long, _
    ByVal hToken As Long, ByVal dwFlags As Long, _
    ByVal pszPath As String) As Long

Private Sub cmdCreateDatabase_Click()
    Dim strMyDocuments As String
    Dim strDbName As String
    Dim valReturned As Long
    Dim dbMVD As DAO.Database
    
    ' Initialize the string
    strMyDocuments = String(MAX_PATH, 0)
    
    ' Call the Shell API function to get the path to My Documents
    ' and store it in the strMyDocuments folder
    valReturned = SHGetFolderPath(0, CSIDL_PERSONAL, _
                                  0, SHGFP_TYPE_CURRENT, strMyDocuments)
    ' "Trim" the string
    strMyDocuments = Left(strMyDocuments, InStr(1, strMyDocuments, Chr(0)) - 1)
    ' Include the name of the database in the path
    strDbName = strMyDocuments & "\Motor Vehicle Division.mdb"
    
    ' Create the database
    Set dbMVD = CreateDatabase(strDbName, dbLangGeneral)
End Sub

Practical Learning: Ending the Lesson

To close Microsoft Access, click File -> Exit