greater than but less than operators for VBA

[jsr]

I am new to working with VBA and I need some help. I am trying to
create a commission structure based on sales ranges - here is what I
have got - not sure how to write if its less than one amount but
greater than another (or vice versa)
any help is greatly appreciated....


Function clientcomm(sales) As Single
'calculates sales commissions based on client mgmt fees

Const comm1 As Double = 0.04
Const comm2 As Double = 0.05
Const comm3 As Double = 0.06
Select Case sales
Case Is >= 5000000
clientcomm = sales * tier3
Case Is >= 300000 <= 4999999
clientcomm = sales * tier2
Case Is < 2999999
clientcomm = sales * tier1
End Select



End Function

[Dave Peterson]

If you're careful, you can arrange your cases in nice order:

Option Explicit
Function clientcomm(sales as double) As Double
'calculates sales commissions based on client mgmt fees

Const comm1 As Double = 0.04
Const comm2 As Double = 0.05
Const comm3 As Double = 0.06
Select Case sales
Case Is < 300000
clientcomm = sales * comm1
Case Is < 500000
clientcomm = sales * comm2
Case Else
clientcomm = sales * comm3
End Select
End Function

"Select case" will use the first one that fits and then drop through to the next
line.

And watch your variables (comm* vs tier*) and why not just make the function use
doubles.

jsr wrote:
>
> I am new to working with VBA and I need some help. I am trying to
> create a commission structure based on sales ranges - here is what I
> have got - not sure how to write if its less than one amount but
> greater than another (or vice versa)
> any help is greatly appreciated....
>
> Function clientcomm(sales) As Single
> 'calculates sales commissions based on client mgmt fees
>
> Const comm1 As Double = 0.04
> Const comm2 As Double = 0.05
> Const comm3 As Double = 0.06
> Select Case sales
> Case Is >= 5000000
> clientcomm = sales * tier3
> Case Is >= 300000 <= 4999999
> clientcomm = sales * tier2
> Case Is < 2999999
> clientcomm = sales * tier1
> End Select
>
> End Function

--

Dave Peterson

[[email protected]]

Make sure you want to apply the commission to the entire amount and not
just the marginal amount. For example, let's say your commission
structure is

< $1,000,000 = 1% commission
$1,000,000-$2,000,000 = 2% commission
> $3,000,000 = 3% commission

In the formula you're doing, if the salesman sold $999,999, his
commission would be $9,999.99.

But if he sold $1,000,001, his commission would be $20,000.00.

Two extra dollars in sales brought $10,000.01 in commissions!!!!!

The way this should be done is up to $1,000,000 is 1%, every dollar
AFTER $1,000,000 is 2%, etc.

That way $1,000,001 in sales would give $1,000,000 x 1% + $1 x 2%, for
a total of $10,000.02.

That said, I've seen this commission structure many, many times, where
one additional dollar in sales bumps up your commission percentage ALL
THE WAY BACK TO ZERO. I'm amazed companies do something so illogical,
but oh well.

Also, this can e done with a table & formulas instead of having to use
VBA. You're destroying an anthill with a rocket launcher here....

[bgeier]

Yeah, but rocket launchers are a helluva lot more fun!!!


Sorry for the offpost, but I could not resist!

[Dana DeLouis]

> ... not sure how to write if its less than one amount but
> greater than another...
> Case Is >= 300000 <= 4999999

Hi. You have excellent solutions. For future reference, I believe you
were looking for this:
Case 300000 To 5000000

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"jsr" <[email protected]> wrote in message
news:[email protected]...
>I am new to working with VBA and I need some help. I am trying to
> create a commission structure based on sales ranges - here is what I
> have got - not sure how to write if its less than one amount but
> greater than another (or vice versa)
> any help is greatly appreciated....
>
>
> Function clientcomm(sales) As Single
> 'calculates sales commissions based on client mgmt fees
>
> Const comm1 As Double = 0.04
> Const comm2 As Double = 0.05
> Const comm3 As Double = 0.06
> Select Case sales
> Case Is >= 5000000
> clientcomm = sales * tier3
> Case Is >= 300000 <= 4999999
> clientcomm = sales * tier2
> Case Is < 2999999
> clientcomm = sales * tier1
> End Select
>
>
>
> End Function
>

[jsr]

Thanks - it worked, I changed it a bit - per [email protected] to
deal with incremental dollars....

Thanks everyone!!!!! so glad I found this forum as I am teaching myself
VBA - good to have some real time guidance and help