MS Excel Examples: CD Publisher

CD Publisher


In this exercise, we will develop an application for a small CD publishing company. The owner wants the prices to be calculated so a customer would get a discount if he or she orders more. To attract customers and encourage them to order more, the owner has decided to fix the prices so that the customer would pay:

  • $20/CD if the order contains less than 20 units
  • $15/CD if the order contains less than 50 units
  • $12/CD if the order contains less than 100 units
  • $8/CD if the order contains less than 500 units
  • $5/CD if the order contains more than 500 units


Practical Learning: Creating the Application

  1. Start Microsoft Excel and, to open Microsoft Visual Basic, on the main menu, click Tools -> Visual Basic Editor
  2. To add a new form, on the Standard toolbar of Microsoft Visual Basic, click the Insert UserForm button
  3. Design it as follows:
    Control Name Caption Other Properties
    Label Number of CDs:  
    TextBox txtQuantity   TextAlign: 3 - frmTextAlignRight
    CommandButton cmdEvaluate Evaluate  
    Frame   Based on the Specified Quantity  
    Label   Each CD will cost:  
    TextBox txtUnitPrice    
    Label   And the total price is:  
    TextBox txtTotalPrice    
  4. Double-click the Evaluate button and implement its Click event as follows:
    Private Sub cmdEvaluate_Click()
        Dim Quantity As Integer
        Dim UnitPrice As Currency
        Dim TotalPrice As Currency
        Quantity = CInt(txtQuantity.Text)
        ' The price of one CD will depend on the number ordered
        ' The more the customer orders, the lower value each
        If Quantity < 20 Then
            UnitPrice = 20
        ElseIf Quantity < 50 Then
            UnitPrice = 15
        ElseIf Quantity < 100 Then
            UnitPrice = 12
        ElseIf Quantity < 500 Then
            UnitPrice = 8
            UnitPrice = 5
        End If
        TotalPrice = Quantity * UnitPrice
        txtUnitPrice.Text = FormatCurrency(UnitPrice)
        txtTotalPrice.Text = FormatCurrency(TotalPrice)
    End Sub
  5. Press F5 to test the form
  6. Perform the calculations with different quantities. For example, in the top text box, type 1250 and click Evaluate
    CD Publisher
  7. After testing various quantities, close the form and return to Microsoft Visual Basic


Home Copyright 2004 FunctionX, Inc.