PDA

View Full Version : VBA Code Error



Jess Rulz
04-04-2008, 01:48 AM
Hi

My name is Rebecca and i am new to the forum and VBA. i have been learning it at uni for a couple of weeks and habe my first project due next week. We have been give the task to create a user defined function that can:

"Calculate the cost of buying Easter Eggs, where a larger discount is applied as you purchase more eggs… up to a maximum discount rate. Boxes to carry the eggs are charged extra, but a discount will be applied for purchases of a certain size"

I have a code as shown below but when i try and use it in excel it says that " this code takes no arguments"

i have tried to edit the function but i have had no luck getting it to work. i would appriciate it very much if someone could look at it and tell me how i can correct it. Many thanks in advance.

Rebecca

My code


Option Explicit

Public Function EasterEggs(intNumberEggs As Integer) As Integer

EasterEggs = intNumberEggs * 4


'The above calculates the cost of the easter eggs
End Function

Public Function DiscountEggs(intNumberEggs As Integer) As String

Dim strDiscount As String

If intNumberEggs < 10 Then
strDiscount = "No Discount"
Else
If intNumberEggs < 12 Then
strDiscount = "E"
Else
If intNumberEggs < 14 Then
strDiscount = "D"
Else
If intNumberEggs < 16 Then
strDiscount = "C"
Else
If intNumberEggs < 18 Then
strDiscount = "B"
Else
If intNumberEggs < 20 Then
strDiscount = "A"
End If
End If
End If
End If
End If
End If

'above is an If calcualtion which calculates the discount awarded to
'customers depending on the size of their purchase

DiscountEggs = strDiscount
End Function

Public Function BoxesCost(intNumberBoxes As Integer) As Integer

BoxesCost = intNumberBoxes * 2

'The above calculates the cost of the boxes for the easter eggs
End Function

Public Function BoxesDiscount(intNumberBoxes As Integer) As String

Dim strBoxDiscount As String

If intNumberBoxes < 2 Then
strBoxDiscount = "No Discount"
Else
If intNumberBoxes < 4 Then
strBoxDiscount = "E"
Else
If intNumberBoxes < 6 Then
strBoxDiscount = "D"
Else
If intNumberBoxes < 8 Then
strBoxDiscount = "C"
Else
If intNumberBoxes < 10 Then
strBoxDiscount = "B"
Else
If intNumberBoxes < 12 Then
strBoxDiscount = "A"
End If
End If
End If
End If
End If
End If

'above is an If calcualtion which calculates the discount awarded
'to customers depending on the size of their purchase of Boxes for Easter Eggs.
BoxesDiscount = strBoxDiscount
End Function



Edited by Aussiebear to include the last three lines within the vba tags
Edited By Simon Lloyd to make code window smaller easier to read

herzberg
04-04-2008, 02:14 AM
As far as I can see and test, the code is perfectly OK. I guess what you are missing is a procedure to call the function. Try adding this procedure and running it and you will see that all your functions are working fine.

Public Sub testme()

Dim OrderNum(2) As Integer, DisplayStr(6) As String

On Error GoTo ErrHandler

OrderNum(1) = InputBox("Enter the number of eggs you want")
OrderNum(2) = InputBox("Enter the number of boxes you want")

DisplayStr(1) = "You ordered " & OrderNum(1) & " eggs."
DisplayStr(2) = "It cost $" & EasterEggs(OrderNum(1)) & " for " & OrderNum(1) & " eggs."
DisplayStr(3) = "Your discount based on number of eggs ordered is " & DiscountEggs(OrderNum(1))
DisplayStr(4) = "You ordered " & OrderNum(2) & " boxes."
DisplayStr(5) = "It cost $" & BoxesCost(OrderNum(2)) & " for " & OrderNum(2) & " boxes."
DisplayStr(6) = "Your discount based on number of boxes ordered is " & BoxesDiscount(OrderNum(2))

MsgBox DisplayStr(1) & vbCrLf & DisplayStr(2) & vbCrLf & DisplayStr(3) _
& vbCrLf & DisplayStr(4) & vbCrLf & DisplayStr(5) & vbCrLf & DisplayStr(6)

Exit Sub

ErrHandler:
MsgBox "Error. Please try again.", vbCritical, ""

End Sub
Alternatively, you can try the functions out in the worksheet by keying the functions directly in a cell, e.g. ""=BoxesCost(4)" and you see that they are OK.

Jess Rulz
04-04-2008, 02:21 AM
Hi i have tried to add the Sub that you provided but the computer still says "this function takes no arguments" is there anything else i can try?

i was wonderinf it i had got the code right in the begining for example the

EasterEggs = intNumberEggs * 4

Thank you in advance x

Rebecca x x

herzberg
04-04-2008, 02:28 AM
Oh? It didn't work? Here, take the workbook I used to test. Open Module1 in VBE and run the "testme" procedure.

Anyway, your code is really fine. A bit untidy but correct.

Jess Rulz
04-04-2008, 02:35 AM
I know this is going to sound so stupid but it wount work the in module you gave me either, di you think it could be the computer?

i entered some data in to excel and tried to enter the function under user defined and is still came up with " the function takes not argumets"

i am so sorry trouble you again

Many thanks in advance

Rebecca x x

mdmackillop
04-04-2008, 03:29 AM
Hi Rebecca,
Welcome to VBAX
Please read this (http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item) re homework problems. We will try to point you in the right direction, but we won't do the tasks.
BTW, when you enter code, select it and click the VBA button to format it as shown
Regards
MD

Jess Rulz
04-04-2008, 03:30 AM
Ok Thanks i will try this

Many Thanks Rebecca x

mdmackillop
04-04-2008, 03:40 AM
Hi Rebecca,
Can you post your workbook. Use manage attachments in the Go Advanced section.
BTW What happens if someone orders 50 eggs or boxes?

Jess Rulz
04-04-2008, 04:20 PM
Hi i have attached my workbook, thank you for looking at it!

well when you enter a number for the easter eggs its fine but as soon as you try to use the function it wont let you

Many Thanks in Advance

Rebecca x

mdmackillop
04-04-2008, 04:25 PM
How are you using it? It works for me.

Jess Rulz
04-04-2008, 04:30 PM
Oh ok, well i open excel and VBA from the devloper tab. then i try to insert the User defined function into the cost collum for easter eggs and it just says "this function takes no arguments"

i thought it might be the computer at uni but i have tried it at home and it will not work?

its so strange!

Many thanks in advance x

Jess Rulz
04-04-2008, 04:31 PM
May i ask what version of Excel you have?

mdmackillop
04-04-2008, 04:33 PM
This is not a version problem.
In B2 type =eastereggs(A2)

Simon Lloyd
04-04-2008, 04:34 PM
I don't mean to point you to another forum but what UDF's are very well explained here (http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html)

Jess Rulz
04-04-2008, 04:37 PM
i have just opened excel and it has asked me to turn on Macro's in Excel 2003, how do i do that? sorry to trouble you

Rebecca x

mdmackillop
04-04-2008, 04:38 PM
Tools/Macros/Security
Set to Medium

Jess Rulz
04-04-2008, 04:43 PM
When i type =eastereggs(A2) into B" is comes up with "#Name?"

Jess Rulz
04-04-2008, 04:43 PM
Thank You i will have a look x

mdmackillop
04-04-2008, 04:47 PM
I've entered the functions in this copy

Jess Rulz
04-04-2008, 04:54 PM
I was wondering if the function name that appears under user defined functions is important as when i select user defined functions it cames up as below:

"VBA Assignemt on wages.xlsmoduleBoxesCost"
"VBA Assignemt on wages.xlsmoduleBoxesDiscount"
"VBA Assignemt on wages.xlsmoduleDiscountEggs"
"VBA Assignemt on wages.xlsmoduleEasterEggs"

I would like to thank you all for your help so far i know it must be boring for you :rotlaugh:

Mant thanks

Rebecca

mdmackillop
04-04-2008, 05:04 PM
What do you see in my workbook?

Jess Rulz
04-05-2008, 12:39 PM
Hi i have downloaded the workbook that you uploaded for me and the function works. its so strange how it would not work for me. Thank You so so much i really appriciate it #

Rebecca x x x

Jess Rulz
04-05-2008, 12:45 PM
I would like to thank all of the people that have helped me with my question i really appriciate it and i know that you all do it in your free time

Many thanks again

Rebecca x

mdmackillop
04-05-2008, 12:48 PM
glad to help