Call function does not work

[marcvanderpeet12]

Dear all,

I created a new module in which in inserted the following code:

Function Discount(quantity, price)
If quantity >= 100 Then
Discount = quantity * price * 0.1
Else
Discount = 0
End If
Discount = Application.Round(Discount, 2)
End Function


But now when i switch to my excel en try to call the code (=Discount(a1,a2)) i get an error. Do I make a mistake somewhere?

Dear regards,

Marc

 

[Abdul Matheen]

Hi Marc,

I don't see any mistake in the code, I tired the same code in my system and it is working fine. Could send the error type you are getting, so that I try to understand what exact error you are getting.

Regards
Abdul Matheen

 

[SirJB7]

Hi, marcvanderpeet12!

You can use Round directly without the Application. qualification, since it's a VBA function. Despite of this, which values have cells A1 & A2 of the related worksheet? And how are they formatted?

Regards!

 

[Hui]

Marc

I wouldn't consider using a function to do this except as a learning exercise
I would use a worksheet formula like:
=ROUND((A1>=100)*A1*B1*0.1,2)
or
=ROUND(IF(A1>=100,A1*B1*0.1,0),2)
where A1 is Quantity and B1 is price
The first version is a lot faster than the second version


In regards to the Function
Functions must be placed in a Code Module not a Worksheet o Workbook Module in VBA


It is also good practice to define all variables
eg:

Function Discount(quantity As Double, price As Double) As Double
If quantity >= 100 Then
  Discount = quantity * price * 0.1
Else
  Discount = 0
End If
Discount = Round(Discount, 2)
End Function

 

[Marc L]

Hi Hui,

'cause function is declared as numeric, so value by default is zero, just need
​

Function Discount@(quantity@, price@)
    If quantity >= 100 Then Discount = Round(quantity * price * 0.1, 2)
End Function

Regards !​

 

[marcvanderpeet12]

Hi Abdul,

I inserted the code into a new module so that cant be the problem..

I defined the relevant cells (a1 and b1) as numbers. I tried it again and get the following error. Anysuggestion based on this?

 

[marcvanderpeet12]

Ahh already got it. Had to insert it with a semicolumn like: Discount(a1;b1)

 

[Hui]

That is a Language/Locale issue