Help with User defined function

WillR · Apr 4th, 2002, 02:48 AM

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.

Tom Schreiner · Apr 4th, 2002, 02:51 AM

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 · Apr 4th, 2002, 04:44 AM

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 · Apr 4th, 2002, 04:52 AM

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
OzGrid Business Applications
Microsoft Excel/VBA Training

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

WillR · Apr 4th, 2002, 05:29 AM

even better!! thanks

Apr 4th, 2002, 04:52 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-04-04 03:53 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 02:48 AM	#1
WillR Board Regular Join Date: Feb 2002 Location: Sutton Coldfield Posts: 1,143	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.

Apr 4th, 2002, 02:51 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

Apr 4th, 2002, 04:44 AM	#3
WillR Board Regular Join Date: Feb 2002 Location: Sutton Coldfield Posts: 1,143	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

Apr 4th, 2002, 05:29 AM	#5
WillR Board Regular Join Date: Feb 2002 Location: Sutton Coldfield Posts: 1,143	even better!! thanks