Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help with User defined function

  1. #1
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Soliohull
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    even better!! thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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