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

7. After testing various quantities, close the form and return to Microsoft Visual Basic