Home

Creating Procedures and Functions

 

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 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, checks its syntax, the words used in the program, 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. For this reason, you should know what you are doing.

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

We saw already how to call a procedure that does not take any argument. Actually, 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 space, 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 keyword Call 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 the Code Editor or 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
 

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

Instead of one, you can also create a procedure with more than one argument as we saw earlier. 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
 
 

Practical LearningPractical Learning: Using Default Arguments

  1. In the Forms section of the Database window, double-click the ItemPrice form to open it
  2. After viewing it, switch it to Design View
  3. On the form, click the Calculate button
  4. In the Properties window, click Events and double-click the On Click field
  5. 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 = 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(Nz(txtMarkedPrice))
        dblDiscountRate = CDbl(Nz(txtDiscountRate))
        dblTaxRate = CDbl(Nz((txtTaxRate))
        txtNetPrice = CalculateNetPrice(curMarkedPrice, dblTaxRate, dblDiscountRate)
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Test it
  8. Close the form
  9. 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. From the Forms section of the Database window, open the Employees Records1 form
  2. After view the form in Form View, on the main menu, click View -> Design View
  3. On the form, click the Resume button
  4. In the Properties window, click Events and double-click the On Click field
  5. Change the file as follows:
     
    Function ResumeEmployee$(salary As Currency, name As String, dHired As Date)
        Dim strResult$
        
        strResult = name & ", " & CStr(dHired) & ", " & 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, dHired:=dteHired, _
                                   salary:=curHourlySalary)
        txtResume = strResume
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Test it
     
  8. Close the form
  9. 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. Open the Triangle form in 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. Change the file as follows:
     
    Function CalculateTriangleArea#(ByVal Base As Double, ByVal Height As Double)
        CalculateTriangleArea = Base * Height / 2
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim dblBase#
        Dim dblHeight#
        
        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. Test it
     
  7. 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#
        Dim dblHeight#
        Dim dblArea#
        
        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. Test the form with different values than previously
     
  4. Close the form
  5. When asked whether you want to save it, click Yes
 

Programmer-Defined Data Types

 

Introduction

The built-in data types we have used so far allow you to declare a variable of a specific known type. Alternatively, you can create a new data type by using one of the above or by combining some them to get a new one. To do this, you must create a new module for the new type. You start the new type with the Type keyword followed by the name of the new type. The create of the type ends with the End Type expression:

Type SampleType
      
End Type

Between the Type line and the End Type line, you can declare one or more existing types as variables. That is, each declaration can be made of a name for a variable, followed by As, and followed by a known data type. Here is an example:

Type Sphere
	Radius As Double
	Diameter As Double
	Area As Double
End Type

 

Using a Programmer-Defined Data Type

After creating the type, in the procedure or event where you want to use it, declare a variable based on it. To access any of the member variables of the type, enter the name of its variable, followed by a period operator, and followed by the name of the member variable. After accessing a member variable of a type, you can initialize, change its value, or assign it to another variable.

 

Practical Learning: Using a Custom Type

  1. On the Database window of Microsoft Access, click the Modules button
  2. To create a new module, click the New button on the toolbar of the Database window
  3. Under the Option Explicit line, type the following:
     
    Type Employee
        DateHired As Date
        FullName As String
        IsMarried As Boolean
        HourlySalary As Double
    End Type
  4. To save the module, on the Standard toolbar, click the Save button
  5. Set the name to modRoutines and click OK
  6. Return to Microsoft Access and open the Employee form in Design View
  7. Right-click the Create button and click Build Event...
  8. Double-click Code Builder and change the event as follows:
     
    Private Sub cmdCreate_Click()
        Dim Contractor As Employee
        
        Contractor.DateHired = #12/4/2000#
        Contractor.FullName = "Leslie Abramson"
        Contractor.IsMarried = True
        Contractor.HourlySalary = 20.15
        
        txtDateHired = CStr(Contractor.DateHired)
        txtFullName = Contractor.FullName
        chkIsMarried.Value = Contractor.IsMarried
        txtHourlySalary = Contractor.HourlySalary
    End Sub
  9. Close Microsoft Visual Basic
  10. Switch the form to Form View and click the Create button
     
    Employee
  11. Close the form. When asked whether you want to save, click Yes
  12. Close Microsoft Access
 
 
 

Previous Copyright © 2005-2010 FunctionX, Inc. Next