excel programming

EET 1003

Name: _______________Section:___

Assignment # 10:

Refer to -

Book: Microsoft Excel VBA Programming for the Absolute Beginner

Chapter: 2, 3

Recorded Lecture: Lecture 11, 12 (eet100311.html, eet100312.html)

Powr Point Presentation:

Lecture11_Excel_VBA_02.pptx, Lecture12_Excel_VBA_03.pptx

Objective:

To understand Data types, variables, variable declaration, and scope of Procedures and

Functions.

1. Compete the following table by identifying the relevant data type for each value

Value Data Type

$1450

New York

12

05/06/2015

2. Open the file Herb Sales details.xlsx 3. Open VBE

4. Open the Sheet2 Code and declare the variables TotalPrice, Price, and SalesTax as Currency data type.

5. Write a Public Sub procedure named DisplaySalesTax that accepts the total price from the user (use the inputbox function), calculates 8% sales tax, and displays the

tax in a message box (use msgbox function). (You need not add the sales tax amount

with the price)

Option Explicit

Dim TotalPrice As Currency, Price As Currency, SalesTax As Currency

Public Sub DisplaySalesTax()

TotalPrice = InputBox("enter total price")

SalesTax = 0.08 * TotalPrice

MsgBox ("sales tax is " & SalesTax)

End Sub

6. Update the code and run the procedure. Provide the total price of Cinnamon as the input value.

7. Write a Public Sub procedure named CalculateSalesAmt to calculate the total price amount including sales tax. You need to take the total price from the cell D4 in the

worksheet. (Use the variable Price to store the price amount from D4, the variable

SalesTax to store the sales tax amount, and the variable TotalPrice to store the sum of

price and sales tax. Do not run any procedure until you are instructed to do so. You

will run this procedure in a later step).

8. In the same procedure, write code to assign the value in the variable TotalPrice to the cell E4.

9. Change the scope of the variables to Public. (delete Dim and replace it with Public

10. Insert a procedure named CallSub and add a statement that calls the procedure CalculateSalesAmt.

11. Update the code and run the CallSub procedure. (You can check the worksheet to verify that the price, including sales tax, is entered in cell E4.)

12. Insert a Function procedure named CalcualteSalesTax. Specify the return data type of the Function procedure as Currency.

13. Within the CalculateSalesTax procedure, write code for taking the price amount from the cell D5 and calculating the sales tax. The Function procedure should return the

sales tax amount to the calling procedure. (You need not add price to the calculated

sales tax amount.)

Public Sub CalculateSalesAmt()

Price = Cells(4, 4).Value

SalesTax = 0.08 * Price

TotalPrice = Price + SalesTax

Cells(4, 5).Value = TotalPrice

End Sub

Public TotalPrice As Currency, Price As Currency, SalesTax As Currency

Sub CallSub()

Call CalculateSalesAmt

End Sub

Function CalcualteSalesTax() As Currency

CalcualteSalesTax = Cells(5, 4).Value * 0.08

End Function

14. Insert a Sub procedure named CallFunc. Declare a variable ST of Currency data type within the procedure. Write a statement to call the procedure.

15. Write code to add the total price amount, in cell D5, with the value in ST and assign the result to the cell E5.

16. Update the code and run the CallFunc procedure.

17. Compare your work with Exhibit Assignment 10.

18. Close VBE

19. Update, and save the work book as Macro Enabled Excel Workbook

20. Close the workbook

Exhibit Assignment 10

Sub CallFunc()

Dim ST As Currency

ST= CalcualteSalesTax

Cells(5, 5).Value = ST + Cells(5, 4).Value

End Sub