Help with User defined function

[WillR]

I'm not too good with this sort of stuff, so any help would be greatly appreciated...

I have witten the follwing UDF

Function twoweekrate(balance)
If balance >= 0 And balance <= 5000 Then
twoweekrate = (balance * 0.04) / 365
Else
If balance > 5000 And balance <= 1000 Then
twoweekrate = (balance * 0.034) / 365
Else
If balance > 1000 And balance <= 25000 Then
twoweekrate = (balance * 0.032) / 365
Else
twoweekrate = (balance * 0.024) / 365
End If
End Function


However, when i try to use the formula in XL it gives Name? or Value! error msg...in VB, it says, "compile error: Block if without end if" i have an end if in the code, so what am i doing wrong??

Here's hoping.

Will.

 

[Legacy 98055]

Hi
If balance > 5000 And balance<= 1000 Then
Greater than 5000 and Less Than or Equal to 1000? Not possible!
This may be your problem...
Are you wanting 10000 Ten Thousand?
Next line is erroneous as well if that is the case...
Tom
This message was edited by TsTom on 2002-04-04 01:52

 

[WillR]

Tom, thanks....i spotted the thousands issue....but also my formula should have used "ElseIf" as opposed to "Else" that's why the VB compile error was happening...the other bit was just producing rubbish data but all fixed & working now...
the correct code (if anyone's interested) is

Function twoweekrate(balance)
If balance >= 0 And balance <= 5000 Then
twoweekrate = (balance * Range("TWrate1")) / 365
ElseIf balance > 5000 And balance <= 10000 Then
twoweekrate = (balance * Range("TWrate2")) / 365
ElseIf balance > 10000 And balance <= 25000 Then
twoweekrate = (balance * Range("TWrate3")) / 365
Else
twoweekrate = (balance * Range("TWrate4")) / 365
End If
End Function

 

[Dave Hawley]

Hi Will

Consider using the Select case for this type of Function,. It's far more flexible, easy to debug and read.

Function twoweekrate(balance)

Dim i As Integer
    Select Case balance
         Case 0 To 5000
          i = 1
         Case 5001 To 10000
          i = 2
         Case 10001 To 25000
          i = 3
         Case Else
          i = 4
    End Select

twoweekrate = (balance * Range("TWrate" & i)) / 365

End Function

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

This message was edited by Dave Hawley on 2002-04-04 03:53

 

[WillR]

even better!! thanks