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

Thread: Help with a UDF in VB

  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 have the following UDF code in VB

    Function SpecialInterest(account, balance)

    'this looks up the relevant special rate from the extract
    'and works out the daily interest P&L contribution

    Rate = WorksheetFunction.VLookup(account, Special_Table, 18, False)
    SpecialInterest = (balance * (Rate / -100)) / 365

    End Function

    I getting the following error msg

    'Function call on LHS of assignment must
    return variant or object'

    The data i'm lookin up is in col 18 of special_table so what's the problem...?

    Can anyone shed any light on this for me please...

    Will.

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 03:26, WillR wrote:
    I have the following UDF code in VB

    Function SpecialInterest(account, balance)

    'this looks up the relevant special rate from the extract
    'and works out the daily interest P&L contribution

    Rate = WorksheetFunction.VLookup(account, Special_Table, 18, False)
    SpecialInterest = (balance * (Rate / -100)) / 365

    End Function

    I getting the following error msg

    'Function call on LHS of assignment must
    return variant or object'

    The data i'm lookin up is in col 18 of special_table so what's the problem...?

    Can anyone shed any light on this for me please...

    Will.
    Rate is an excel function, hence the error
    as it is expecting the proper function call
    You will need to explicitly define rate eg

    Dim Rate

    Good practice NOT to use excel function names
    as your Definitions eg MyRate instead of
    Rate, MyCell instead of Cell etc.

    So your code should be


    Option Explicit

    Function SpecialInterest(account, balance)
    Dim Rate
    'this looks up the relevant special rate from the extract
    'and works out the daily interest P&L contribution

    Rate = WorksheetFunction.VLookup(account, Range("Special_Table"), 18, False)
    SpecialInterest = (balance * (Rate / -100)) / 365

    End Function


    Note: Option explicit
    Look up online help for this...it will help
    you in capturing errors in syntax decalrations.


    Kind Regards,
    Ivan F Moala From the City of Sails

  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

    Thanks Ivan - works a treat now.

    Will

    :grin

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
  •