Help with a UDF in VB

[WillR]

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.

[Ivan F Moala]

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.

[WillR]

Thanks Ivan - works a treat now.

Will

:grin