What is wrong with my vba (excel)?

April 15, 2012 at 19:42:03
Specs: Windows Vista
For an assignment I'm using VBA for an else..if function in excel. I'm trying to revise the column (commission) to be based off the column (sales). I tried to typing the function, but its not coming out right. Please help.

My module:

Function rate(sales)
Dim commission_rate
If sales > 200000 Then
commission_rate = 0.1
ElseIf sales > 100000 Then
commission_rate = 0.05
ElseIf sales > 75000 Then
commission_rate = 0.04
ElseIf sales > 40000 Then
commission_rate = 0.03
ElseIf sales > 20000 Then
commission_rate = 0.01
ElseIf sales > 10000 Then
commission_rate = 0

rate = commision_rate
End Function



See More: What is wrong with my vba (excel)?

Report •


#1
April 16, 2012 at 04:29:40
re: "but it's not coming out right"

Since we can't see your spreadsheet from where we're sitting, we don't know what you mean by "but it's not coming out right".

That said, the one thing I can tell you right up front is that there is a built-in Excel function RATE (it returns the interest rate per period of an annuity) therefore you can not create a User Defined Function with that name.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 17, 2012 at 19:27:03
Thanks I rewrote by vba to:

Function commission (sales)
Dim commission_rate
If sales > 200000 Then
commission_rate = 0.1
ElseIf sales > 100000 Then
commission_rate = 0.05
ElseIf sales > 75000 Then
commission_rate = 0.04
ElseIf sales > 40000 Then
commission_rate = 0.03
ElseIf sales > 20000 Then
commission_rate = 0.01
ElseIf sales > 10000 Then
commission_rate = 0
End if

commission = commision_rate
End Function

--But when i type in the function "commission" referring to the "sales" column all i get is 0 even though i should be getting a percentage. Please help!


Report •

#3
April 18, 2012 at 04:00:48
Too bad VBA doesn't have a spell checker. ;-)

Look at your code very carefully.

In addition...what happens if "sales" equals one of your breakpoints?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 18, 2012 at 09:51:07
Well, VBA does have Option Explicit, and that'd catch the problem.

How To Ask Questions The Smart Way


Report •

Ask Question