VBA Week 3 Project Instructions

Create a worksheet with headings and a command button as shown below. You should type your name where it says Student Name and save the workbook as a Macro-Enabled workbook named: VBALab3_StudentName.xlsm (change StudentName to your LastnameFirstname in the filename.)

When you add the Command button to the worksheet, change the Name property to cmdPurchases.

Summary: The purpose of the command button is to activate a VBA program to collect data from the user for Product and Price using Input Boxes. These values will be displayed on the worksheet. A Function procedure should be used to calculate the Price With Shipping value and that Function procedure should be called from within the Sub Procedure. (You will not earn full credit for this lab unless you use a Function procedure for the calculation.)

Details to help you with your coding:

For the Sub Procedure named cmdPurchases_Click():

Declare necessary variables.

Use a For-Next loop to fill in data in rows 5 and 6 of the worksheet.

Use input boxes to collect values for Product and Price.

Assign the values for Product and Price to cells in the worksheet.

Call a Function procedure named PriceWithShipping (that uses the value from the Price variable) and assign the value returned by the function to column D of the worksheet.

Add a message box to show the process is complete.

Create a Function procedure named PriceWithShipping:

Declare a constant named Shipping that has a value of 8.99

PriceWithShipping is calculated by adding the Shipping amount to the Price.

The completed worksheet with data should look like this (you can input different product names and prices)

The problem that I am having is making a function and calling the function to the command.

This is what I have:

Sub cmdPurchases_Click()

'delcare variables

Dim Counter As Integer Dim Price As Currency Dim PriceWithShipping As Currency

'loop For Counter = 5 To 6 Product = InputBox("Please enter the name of the product.") Cells(Counter, 2) = Product Next

For Counter = 5 To 6 Price = InputBox("Please enter the price of the product.") Cells(Counter, 3) = Price Next

MsgBox ("Purchase Complete.")

End Sub

I don't think my function looks right, but that's what I have. I have to call the function in the middle of the cmd sub procedure, but I don't know how.

Function PriceWithShipping(ByVal Price As Currency) As Currency Const Shipping As Currency = 8.99 PriceWithShipping(Price) = Price + Shipping

End Function