Creating custom formulas

[Sarah@LaborSource]

The answers provided to my previous post made it possible for me to obtain
the results I needed, but now I'd like to create a custom formula so I don't
have to type the whole thing out every time. In Microsoft Visual Basic I've
created a module that reads like this:

Function Commission(sales)
If sales < 150000 Then
Commission = sales * 0.0125
End If
If 150000 < sales < 200000 Then
Commission = sales * 0.0175
End If
If sales > 200000 Then
Commission = sales * 0.02
End If
End Function

But when I try to insert the formula into my worksheet, it says that it
takes no argument. Help?

Thanks,
Sarah

 

[Mike H]

Hi,

Try this
Alt + F11 to open VB editor. Right click 'This Workbook' and insert module
and put it in there.

Function Commission(sales) as double
If sales < 150000 Then
Commission = sales * 0.0125
ElseIf sales < 200000 Then
Commission = sales * 0.0175
Else
Commission = sales * 0.02
End If
End Function

Call it with
=Commission(a1)

Mike

 

[Sarah@LaborSource]

Hmm. When I try that, it says "Ambiguous name detected: commission". Is
that because I tried this several times already before posting and so have
several modules with the name "commission" in it?

 

[ryguy7272]

This is a little different that calling a function; this code auto calculates
numbers that you type into range A1:A10 (you will probably have to change
this range). To use this, right-click on your sheet and paste the code into
the window that opens.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False

If Target.Value < 150000 Then
Target.Value = (Target.Value * 0.0125)
End If

If Target.Value >= 200000 Then
Target.Value = (Target.Value * 0.02)
End If

If Target.Value < 200000 Then
If Target.Value >= 150000 Then
Target.Value = (Target.Value * 0.0175)
End If
End If

Application.EnableEvents = True
End If
End If
End Sub


Regards,
Ryan---