Yield VBA function missing in Excel 2007

[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.

 

[Ron de Bruin]

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

 

[Dave Peterson]

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.

 

[Ron de Bruin]

Hi Dave

I also test it in 2003 now and they are also not in VBA

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Michel Petit]

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

 

[Ron Rosenfeld]

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 believe Bernard Liengme reported this as a bug three or four months ago.
--ron

 

[Dave Peterson]

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.

 

[Michel Petit]

That make sense. Thanks Ron.

I believe Bernard Liengme reported this as a bug three or four months ago.
--ron

 

[Michel Petit]

Yes that's what I did. But it's very slow and ugly. Thks

 

[Dave Peterson]

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.)

 

[Ron Rosenfeld]

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.

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

 

[Dave Peterson]

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>