Explore BrainMass

Explore BrainMass

    VBA product price calculator

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    • VBA
    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."

    © BrainMass Inc. brainmass.com October 10, 2019, 8:23 am ad1c9bdddf


    Solution Preview


    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.

    Sub Prod_Function()

    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


    Solution Summary

    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.