VBA product price calculator
Not what you're looking for?
• 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."
Purchase this Solution
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.
Solution Preview
Hi,
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
...
Purchase this Solution
Free BrainMass Quizzes
Basic Networking Questions
This quiz consists of some basic networking questions.
Basic UNIX commands
Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.
Java loops
This quiz checks your knowledge of for and while loops in Java. For and while loops are essential building blocks for all Java programs. Having a solid understanding of these constructs is critical for success in programming Java.
Basic Computer Terms
We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.
C# variables and classes
This quiz contains questions about C# classes and variables.