MS Excel Examples: CD Publisher

	MS Excel Examples: CD Publisher

Introduction

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

Start Microsoft Excel and, to open Microsoft Visual Basic, on the main menu, click Tools -> Visual Basic Editor
To add a new form, on the Standard toolbar of Microsoft Visual Basic, click the Insert UserForm button

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

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
    Else
        UnitPrice = 5
    End If
        
    TotalPrice = Quantity * UnitPrice

    txtUnitPrice.Text = FormatCurrency(UnitPrice)
    txtTotalPrice.Text = FormatCurrency(TotalPrice)
End Sub

Press F5 to test the form
Perform the calculations with different quantities. For example, in the top text box, type 1250 and click Evaluate
After testing various quantities, close the form and return to Microsoft Visual Basic


Home	Copyright © 2004-2010 FunctionX, Inc.