Excel

1. Insert a module and create a subroutine named Receipt(). 2. Place a button with the name "Generate Receipt" that would link to this subroutine when clicked.

Do the following parts from 3 to 14 completely using VBA code. 3. Change the codename of the Sheet1 to wsReceipt and set this as the active sheet. 4. Change the tab name to "Billing".

5. Clear columns I to K. This must clear both contents and the formatting if any. 6. Ask the user for the name of their company with atleast two words and store this to a string variable. Dialog Box title must be "Query". Remove all leading and trailing spaces. Now find the number of characters in the string after removing the leading and trailing spaces and store to an integer variable.

7. Create a password using the first 3 characters of the reverse of the first word in the company name in lower case concatenated with the number of characters you just found, and display it in the cell B2. Feel free to create new variables if you would like. For example, if the user entered " FACTORY TRADING CORPORATION ", the password displayed would be "yro27"

8. Ask the user to enter the passcode displayed in cell B2 and store this to a string variable called strCheck. Dialog Box title must be "Query". If the password entered by the user exactly matched with the value show in cell B2, then do the following parts from 9 to 14, else show a dialog box that says "Wrong passkey entered. Program will now terminate." and the program should terminate.

9. In column I3, insert the title "Receipt". This text must be boldfaced, underlined and centeraligned. Make use of the With..End With construct to receive full credit. 10. Merge the cells I3 to K3. Apply a border to this cell with line style as xlDash. 11. In cell I5, insert the text "Item Name". In cell J5, insert the text "# of Units". In cell K5, insert the text "Total Cost". The text in these three cells must be boldfaced. 12. In cells below I5, starting at I6, one must copy over the names of all items listed in column B from the cell B6 onwards using the Copy method. Your line of code must be such that if the user enters any new names below B8, that would also get copied over. (you can make use of offsets if you would like, but it is not mandatory for this part of the problem)

13. Now do the following:

(i) ask the user how many units of Machine 1 they would like to purchase and store this value to an integer data type. title of this dialog box must be "Query" round the user input into an integer data type so that it would be okay even if the user entered something like 2.4. insert this value into cell J6.

(ii) ask the user how many units of Machine 2 they would like to purchase and store this value to an integer data type. title of this dialog box must be "Query" round the user input into an integer data type so that it would be okay even if the user entered something like 2.4. insert this value into cell J7.

(iii) ask the user how many units of Machine 3 they would like to purchase and store this value to an integer data type. title of this dialog box must be "Query" round the user input into an integer data type so that it would be okay even if the user entered something like 2.4. insert this value into cell J8.

The below example shows what your sheet would look like after the user entered a value of 15.3 or 15 in part(i), and the program is currently asking the user to enter the number of units of Machine 2 they would like to purchase when (ii) is executed.

14. Using I5 as your anchor cell (which means you must use offset from I5 for all the questions below and make use of the With..end With construct). Finding the number of rows of items you have might be helpful in this case. (i) insert a title "Subtotal" in a cell of column J that is two rows below the last entry in that column (i.e. there must be a gap of 2 rows).

(ii) insert a title "Discount" in the cell below the cell in which you wrote "Subtotal" (iii) insert a title "Total" in the cell below the cell in which you wrote "Discount" (iv) in column K, in the cells below "Total Cost", calculate the Cost of Units using the following formula in R1C1 format: Cost of Units = # of Units * Price per Unit The price per unit must be picked from the data in columns C,D and E based on the number of units of each type the customer wishes to buy. (Hint: Read the 1st tip given at the end of the problem.) (You can use a simple ifelse statement for this and no need to introduce loops. At this point, we are not bothered about any new type of machine the user enters and is concerned only about calculating for the 3 given machines, so do not worry about making the code very generic.)

(v) Format the values calculated in (iv) above such that it shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point. (vi) The cell to the right of "Subtotal" must show the sum of the values in the cells below "Total Cost". Use formula in R1C1 format. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point. Also, store this value to a double variable called dblSubtotal. (Hint: Read the 2nd tip given at the end of the problem.) (vii) The cell to the right of "Discount" must display the value in cell E2 (absolute reference) if thesubtotal value is greater than $65,000, else it must show zero. Store this value to a double variable called dblDiscount. Format this value such that it shows in percentage when displayed on your sheet with atleast one digit each on both sides of the decimal (but don't format when you store it to the double variable). (viii) The cell to the right of "Total" must calculate and display the value of the formula: Total = (1 Discount) * Subtotal You can make use of the double variables defined above in this formula. Format this value such that it has a $ sign and shows atleast one digit to the left of the decimal and always shows two digits to the right of the decimal point.

15. Display the time it took to execute this program in a MsgBox with the time formatted as in sample shown below. Show this MsgBox to the user ONLY if the program did not have to terminate in part 8 and actually proceeded with the calculations. (Also, irrespective of whether or not this message box would get displayed, make sure you start the timer from the very beginning of the program from before you set the active sheet.)

Discount Machine 1 299.99 Machine 2 559.59 Machine 3 349.99 5% Price per unit if ordering as many units as given below or more 1 >= 50 >=100 287.99 221.99 537.2 414.09 335.99 258.99 Query Enter company name. Must be atleast two words. OK Cancel X Query XWrong passkey entered. Program will now terminate. OK X Receipt I Item Name # of Units Cost of Units Machine 1 15 Machine 2 Machine 3 Query How many units of Machine 2 would you like to buy? OK Cancel X Microsoft Excel Time elapsed is: 12.67 seconds. OK X How your sheet must finally look like if all calculations are done correctly: Example 1 when discount is applicable 2 3 4 5 6 7 9 10 11 12 13 14 15 A yro27 Machine 1 Machine 2 Machine 3 Discount 299.99 559.39 349.99 E 287.99 537.2 335.99 5% Price per unit if ordering as many units as given below or more 1 -50 >-100 F 221.99 414.09 258.99 H 1 J Receipt Item Name # of Units Cost of Units Machine 1 Machine 2 Machine 3 4499.85 25181.55 39834.46 15 45 154 Subtotal Discount Total $69565.36 5.0% $66087.57 L M N 0 Generate Receipt P Example 2 when discount is not applicable 1 1 2 3 5 6 7 8 9 10 11 12 13 14 A yro27 Machine 1 Machine 2 Machine 3 C D 299.99 559.39 349.99 Discount E 287.99 537.2 335.99 5% Price per unit if ordering as many units as given below or more 1 >= 50 F >=100 221.99 414.09 258.99 H I Receipt Item Name # of Units Cost of Units Machine 1 15 Machine 2 Machine 3 45 100 4499.85 25181.35 25899.00 Subtotal Discount Total $55580.40 0.0% $55580.40 M N 0 Generate Receipt I Discount Machine 1 299.99 Machine 2 559.59 Machine 3 349.99 5% Price per unit if ordering as many units as given below or more 1 >= 50 >=100 287.99 221.99 537.2 414.09 335.99 258.99 Query Enter company name. Must be atleast two words. OK Cancel X Query XWrong passkey entered. Program will now terminate. OK X Receipt I Item Name # of Units Cost of Units Machine 1 15 Machine 2 Machine 3 Query How many units of Machine 2 would you like to buy? OK Cancel X Microsoft Excel Time elapsed is: 12.67 seconds. OK X How your sheet must finally look like if all calculations are done correctly: Example 1 when discount is applicable 2 3 4 5 6 7 9 10 11 12 13 14 15 A yro27 Machine 1 Machine 2 Machine 3 Discount 299.99 559.39 349.99 E 287.99 537.2 335.99 5% Price per unit if ordering as many units as given below or more 1 -50 >-100 F 221.99 414.09 258.99 H 1 J Receipt Item Name # of Units Cost of Units Machine 1 Machine 2 Machine 3 4499.85 25181.55 39834.46 15 45 154 Subtotal Discount Total $69565.36 5.0% $66087.57 L M N 0 Generate Receipt P Example 2 when discount is not applicable 1 1 2 3 5 6 7 8 9 10 11 12 13 14 A yro27 Machine 1 Machine 2 Machine 3 C D 299.99 559.39 349.99 Discount E 287.99 537.2 335.99 5% Price per unit if ordering as many units as given below or more 1 >= 50 F >=100 221.99 414.09 258.99 H I Receipt Item Name # of Units Cost of Units Machine 1 15 Machine 2 Machine 3 45 100 4499.85 25181.35 25899.00 Subtotal Discount Total $55580.40 0.0% $55580.40 M N 0 Generate Receipt