Question 4. (This uses VBA commands InputBox, MsgBox, Dim, For Next, If Then Else, Cells, Option Explicit and a button)
The file PriceData.xlsx has a single sheet that lists all 128 of your products by product code. For each product, it lists the unit price and a discount percentage the customers receive if they purchase at least a minimum quantity of the product. For example, the discount for the first product is 7%, and that is obtained if the customer purchases at least 20 units of the product. Write a sub that asks for the number of products you have with an input box. It then ask for a product code. Finally, it should ask for the number of units purchased, which must be a positive number. Your sub should then use a For Next loop to search through your product list to find the product entered. If it finds a match, it should display in message something like the following: "You purchased ___ units of product ___. The total cost is ___. Because you purchased at least ____ units, you get a discount of ____% on each unit." (Replace the underscored areas with the correct values.) The last sentence should not be displayed if the user did not purchase enough units to get a discount. If you do not find a match, your sub should display a message such as "Product not found."
Open the attached Price Data file and press the Purchase product button.
Enter the correct item code and the number of items purchase and it'll calcuate the total cost, along with the discount if applicable.
If you enter wrong item code, the program will display a message and quit.
The VBA code behind this button is a macro containing a function Prod_Function. I paste the code here as well. It is a self explanatory code and contains inline comments.
Dim number As Integer
Dim code As String
Dim pFound As Boolean
Dim productCode As Variant
Dim productCost As Integer
Dim prodOrdered As Variant
Dim unitCost As Variant
Dim totalCost As Double
Dim minPurchase As Integer
Dim discount As Double
Dim discountAmount As Double
A very easy to understand code, with inline comments. The Excel file contains a macro function called Product_Function which asks the user for item code and number of units to purchase. It then matches the product code with the item requested and if found, calculates the total price. If the number of units purchased are more than the minimum amount required for discount, then a discounted price is calculated.