Example VBA Projects

Duwamish Books, Phase 3.5: VBA

Nate Woods and Chris Bova
Summit Software Company

December 1999

Summary: Provides a brief description of the example projects included with Duwamish Books, Phase 3.5: VBA. (3 printed pages)

Introduction

This article highlights two Microsoft® Visual Basic® for Applications (VBA) projects that demonstrate customization possibilities for Duwamish Books, Phase 3.5: VBA. The first example project, Order.vba, shows a customization that extends the order process business logic, in this case restricting a buyer's purchasing power. The second example project, Buyer.vba, implements a performance points scheme in VBA.

These example projects can be loaded into the Modifier by running the Modifier client, choosing either the Buyer or Order Modification option button, showing the VBA integrated development environment (IDE), and importing the project.

Order.vba: Restricting a Buyer's Purchasing Power

Order.vba is a VBA project used to customize the Business Logic Layer (BLL)'s InsertOrder method, limiting orders fitting the following criteria:

1. Orders valued more than $1,000 submitted by non-administrative purchasers.
   
   
2. Orders of more than 100 books submitted by non-administrative purchasers.
   
   
3. All orders consisting of more than 50 copies of any one title.
   • This is all done by sinking the application object's InsertOrderStart event. The VBA code handling the event then checks each of the properties to see if any of those criteria have been met. If they have been met, the MyApplication.RaiseError method is called. If not, the event handler simply exits, returning control to the BLL method.

Code listing

Private Sub MyApplication_InsertOrderStart(Cancel As Boolean)
    On Error GoTo ErrorHandler
    
    '   Non-administrators have limited ordering power
    '
    If (MyApplication.Order.EmployeeAlias <> "ADMIN") Then
        If (MyApplication.Order.TotalPrice > 1000) Then
            MyApplication.RaiseError -1, _
                "Order Process", _
                "Non-Administrators may not place orders of value " & _
                    "greater than $1,000"
        End If
        If (MyApplication.Order.TotalBooks > 100) Then
            MyApplication.RaiseError -1, _
                "Order Process", _
                "Non-Administrators may not place orders for more " & _
                    "than 100 books"
        End If
    End If

    '   Nobody may order more than 50 copies of the same title,
    '
    Dim TooManyBooks As Boolean
    
    '   Search the order for entries containing more than 50 copies
    '
    TooManyBooks = False
    Dim TheOrderItem As VbaOrderObjectModel.OrderItem

    For Each TheOrderItem In MyApplication.Order.OrderItems
        If (TheOrderItem.UnitQuantity > 50) Then TooManyBooks = True
    Next
    
    '   Raise and error if found
    '
    If (TooManyBooks) Then
        MyApplication.RaiseError -1, _
            "Order Process", _
            "Non-Administrators may not place orders for more " & _
                "than 100 books"
    End If
    Exit Sub

ErrorHandler:
    MyApplication.RaiseError -1, _
        "Order Process VBA error", _
        Err.Description
End Sub

Buyer.vba: Modifying the Buyer Performance Points Model

Buyer.vba is a VBA project that provides an implementation of CalculatePerformance in order to calculate the performance of a particular buyer.

• This customization is quite simple. It derives the performance of the buyer from the ratio between the order's value and the average order value for that period, and weights the ratio if the user is an administrator (user alias ADMIN).

Code Listing

Private Sub MyApplication_CalculatePerformance( _
            ByVal EmployeeId As Long, _
            ByVal EmployeeAlias As String, _
            ByVal Orders As Long, _
            ByVal OrderValue As Currency, _
            ByVal OrderVolume As Long, _
            ByVal OrderDelay As Long, _
            PerformancePoints As Long)

    '   Provide the Administrator with a different model
    '   from everyone else
    '
    If (EmployeeAlias = "ADMIN") Then
        PerformancePoints = OrderValue / _
            MyApplication.PeriodOrderValue * 105
    Else
        PerformancePoints = OrderValue / _
            MyApplication.PeriodOrderValue * 100
    End If
End Sub