VBA Week 3 Project Instructions Create a worksheet with headings and a command b

Question

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

Explanation / Answer

Hi,

I have modified the code. It is working fine now.

Sub cmdPurchases_Click()

Dim Counter As Integer

Dim Price As Currency
Dim total As Currency
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
total = PriceWithShipping(Price)
Cells(Counter, 4) = PriceWithShipping(Price)
MsgBox (PriceWithShipping(Price))

Next
MsgBox ("Purchase Complete.")

End Sub

Public Function PriceWithShipping(ByVal Price As Currency) As Currency

Const Shipping As Currency = 8.99
Price1 = Price + Shipping
PriceWithShipping = Price1
End Function

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.