Thanks:  0
Likes:  0

# Thread: Help with User defined function

1. 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.

2. Hi
If balance > 5000 And balance <= 1000 Then
Greater than 5000 and Less Than or Equal to 1000? Not possible!
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 ]

3. 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

4. Hi Will

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

Code:
```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
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-04-04 03:53 ]

5. even better!! thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•