Yield VBA function missing in Excel 2007

  • Thread starter Thread starter Michel Petit
  • Start date Start date
M

Michel Petit

Price and Yield are now part of standard functions in Excel-2007.
Application.WorksheetFunctions.Yield(7 pars) returns error: "Object does not
support method", when called from VBA code. I believe it shouldn't.
It works fine in the cell (=YIELD(...) ).
All others functions used (Price, Duration, MDudation) works fine from VBA.

I'm using Office-Pro-2007 60 days Trial, with SP1 included, Activated; to
update a 2000 and 2003 project for Office 2007 use.

Is this a bug? Is beacuse of Trial version? Some SP missing?
Thanks for any help.
 
I think it is a bug

It is also not in the list in Help

Sub Example_Show_2007_Help()
If Val(Application.Version) = 12 Then
Application.Assistance.ShowHelp "HV10089628", ""
End If
End Sub

See if I can get info about this
 
I tested in xl2003 and Application.Price and Application.WorksheetFunction.Price
didn't work for me.

There are lots of excel functions that are not available through VBA's
worksheetfunction property.

(I wouldn't expect lots of changes between xl2003 and xl2007 for this kind of
thing.)

You do have a couple of other options...

You could use application.evaluate() or worksheets("somesheetname").evaluate()
or even plop the formula into an empty cell, calculate, retrieve the value, and
then clear that cell.
 
Hi Dave,
Thanks for your answer. It does work in Office 2000 and in 2003.
For this purpuse you need first to install the add-in "Analysis Tool Pack
VBA".
Once yo do this, you just call the function without the object:
var = Yield(par1, par2, par2, etc)

You may also need to check the VB Tool/reference atpvbex.xls

Onthe other hand, under "Office 2007 differences"or "what's new" it says
that some Anlysis Tool Pack are now standard and those new functions are
listed. And of couse YIELD is one of them.
Try out PRICE function and it will work.
I have a small test.xls that runs under any version but of course, no Yield
function.
Rgds

Michel
 
Price and Yield are now part of standard functions in Excel-2007.
Application.WorksheetFunctions.Yield(7 pars) returns error: "Object does not
support method", when called from VBA code. I believe it shouldn't.
It works fine in the cell (=YIELD(...) ).
All others functions used (Price, Duration, MDudation) works fine from VBA.

I'm using Office-Pro-2007 60 days Trial, with SP1 included, Activated; to
update a 2000 and 2003 project for Office 2007 use.

Is this a bug? Is beacuse of Trial version? Some SP missing?
Thanks for any help.
Click to expand...

I believe Bernard Liengme reported this as a bug three or four months ago.
--ron
 
But in xl2003, you're calling the yield function from the analysis tookpak (for
VBA) not from the native excel functions.

And yep, xl2007 moved the functions from the analysis toolpak into excel
proper. But that doesn't mean that they made all those functions available in
VBA through the .worksheetfunction property.

There are still lots of functions that are in xl2003 (proper) that are not
available that way.

So now there are more of that type of function. I don't know why MS did it that
way.

But you could still use .Evaluate or helper cell in a worksheet.
 
I'm not sure it would be classified as a bug. Maybe it was a design choice and
they actively chose to leave it out of the set of worksheet functions that could
be called from VBA.

(But I don't have any insight what Microsoft's plan was.)
 
I'm not sure it would be classified as a bug. Maybe it was a design choice and
they actively chose to leave it out of the set of worksheet functions that could
be called from VBA.
Click to expand...

Since there are so many similar functions (to YIELD) that are included, and
since the excluded functions usually have some native VBA equivalent, I would
think its exclusion was not intended.
--ron
 
If I were the developer, I'd call it a design choice. If I were a user, I may
call it a bug.

If it's in the next version, will that be a bug fix or a feature enhancement?
<vbg>
 
Back
Top