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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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