YAHomeworkT - Excel Programming / VBA Programming

[QueHuong]

I posted this already in the Software forum, but people are more responsive here (mods, please let this thread stay here at least until after tomorrow). This is for my Excel Programming class, it's a pretty simple assignment, except I'm stuck on calling fucntions from a cell and I'm not sure how to use arguments in VBA functions. Assignment:

You enjoy shopping. To help you determine the final prices of items that you are interested in buying, you want to create a custom function that will tell you the final price of an item after sales tax, if the original price of the item and the sales tax rate are provided. The formula that you would use is:

FinalPrice = OriginalPrice + (OriginalPrice * SalesTaxRate)

To complete this task:

1. Start Excel, open a new, blank workbook, and then save it with the filename Prices-7.
2. Enter the column headings ?Item Name,? ?Original Price,? ?Sales Tax Rate,? and ?Final Price.?
3. Make up data for the Item Name, Original Price and Sales Tax Rate columns for approximately 10 different items.
4. Open the Visual Basic Editor, insert a new module, and add a comment with your name and the current date.
5. Enter a function statement that creates a new function named FinalPrice that uses two arguments: OriginalPrice and SalesTaxRate.
6. Use the formula provided above, which assigns the value of the calculation to the FinalPrice custom function.
7. In the worksheet, enter the formula using the FinalPrice custom function into the appropriate cells so that the results are displayed in the Final Price column.
8. Add your name to the left section of the header for the worksheet, and print the worksheet.
9. Save the Prices-7 workbook. Close the workbook and exit Excel.

Since I'm not sure how to do it the way my professor wanted, I just used for loops:

Option Explicit
Dim OriginalPrice As Currency
Dim SalesTaxRate As Double

Public Function FinalPrice() As Currency
FinalPrice = OriginalPrice + (OriginalPrice * SalesTaxRate)
End Function

Public Sub Main()

Dim Count As Integer

For Count = 2 To 12
OriginalPrice = Cells(Count, 2).Value
SalesTaxRate = Cells(Count, 3).Value
Cells(Count, 4).Value = FinalPrice
Next Count
End Sub

But I'm sure I'll get points marked off for not doing it his way. So can anyone help me on how to correctly use functions with arguments and how to call that function from within a cell like in the instructions?

 

[QueHuong]

Bump...due tomorrow

 

[marquee]

you probably want to declare your function something like

Public Function FinalPrice(ByRef OriginalPrice as Currency, ByRef Tax as long) as Long

then call your function by

final_price = FinalPrice(Price, Tax)

 

[QueHuong]

Originally posted by: marquee
you probably want to declare your function something like

Public Function FinalPrice(ByRef OriginalPrice as Currency, ByRef Tax as long) as Long

then call your function by

final_price = FinalPrice(Price, Tax)

Could you elaborate? I'm not sure how OriginalPrice and Tax was passed into the function (am I using the right vocab?). And I don't think the cell can be set to "=final_price" that it would call the function correctly because I tried calling the function directly without the middleman (final_price) and it wouldnt' work.

 

[jonmullen]

---Code Starts---
Public Function FinalPrice(ByRef OriginalPrice As Currency, ByRef Tax As Double) As Double
FinalPrice = OriginalPrice + (OriginalPrice * Tax)
End Function
---Code Ends---
put the above does in your module that you added

then for each cell that you want the final price for just put this in its cell "=FinalPrice(B2,C2)" where B2 and C2 are the corresponding cells with the OriginalPrice B2 formated as curency and the Tax in decimal form. The key here is data types marquess was declaring Tax and the returned value as Long which takes away your decimal very frustrating as it rounds you tag to zero making is use less

hopethis helps

 

[jonmullen]

tell me if this works for you or any questions i'm going to bed in a min or two

 

[jonmullen]

ok well night night