Help with a UDF in VB

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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. :confused:
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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. :confused:

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

<pre/>
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
</pre>

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

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top