Yield function in VBA

J

Jaky

Hi,
I am wondering why I can not find the Yield function in VBA. I type in
"Application.worksheetfunctions." in the VBA workspace to find the yield
function, and YIELDDISC and YIElDMAT is there, but YIELD is not there? Did
they forget to put it in VBA?
 
T

Tom Ogilvy

Yield comes from the Analysis toolpak. It is not an Excel native worksheet
function

If you have Analysis Tookpak VBA installed, you can call it with

(demo'd from the immediate window using the help example from Excel Help on
the Yield Function):

? Application.Run("ATPVBAEN.XLA!Yield",Range("A2"),Range("A3"), _
Range("A4"),Range("A5"),Range("A6"),Range("A7"),Range("A8"))
6.50000068807315E-02

so it produces .065 as it should (according to the help example).
 
J

Jaky

I know in 2003 and earlier it was not a native function. I should have
clarified. For Excel 2007, it does not seem to show up. I am still having the
same problem.
 
T

Tom Ogilvy

Jakey
I don't have excel 2007 readily available, so there may be a better way, but:

If you have tried calling iit as a built in function and it doesn't work,
then try use evaluate or brackets

res = [Yield(A2,A3,A4,A5,A6,A7,A8)]

or

res = Evaluate("Yield(A2,A3,A4,A5,A6,A7,A8)")

this is essentially a virtual cell on the activesheet.
 
J

Jaky

Tom Ogilvy said:
Jakey
I don't have excel 2007 readily available, so there may be a better way, but:

If you have tried calling iit as a built in function and it doesn't work,
then try use evaluate or brackets

res = [Yield(A2,A3,A4,A5,A6,A7,A8)]

or

res = Evaluate("Yield(A2,A3,A4,A5,A6,A7,A8)")

this is essentially a virtual cell on the activesheet.
--
Regards,
Tom Ogilvy

Jaky said:
For Excel 2007, I believe it is native. It doesn't show up in VBA.
 
J

Jaky

Hi Tom,
I tried using that fix and it did not work. I looked in the object browser
for vba and the "yield" function is not in that area. I am not sure what else
to do.
-Jaky

Tom Ogilvy said:
Jakey
I don't have excel 2007 readily available, so there may be a better way, but:

If you have tried calling iit as a built in function and it doesn't work,
then try use evaluate or brackets

res = [Yield(A2,A3,A4,A5,A6,A7,A8)]

or

res = Evaluate("Yield(A2,A3,A4,A5,A6,A7,A8)")

this is essentially a virtual cell on the activesheet.
--
Regards,
Tom Ogilvy

Jaky said:
For Excel 2007, I believe it is native. It doesn't show up in VBA.
 
J

Jaky

I tried that fix, but it did not work. I looked in the object browser and the
yield function isn't there.

Tom Ogilvy said:
Jakey
I don't have excel 2007 readily available, so there may be a better way, but:

If you have tried calling iit as a built in function and it doesn't work,
then try use evaluate or brackets

res = [Yield(A2,A3,A4,A5,A6,A7,A8)]

or

res = Evaluate("Yield(A2,A3,A4,A5,A6,A7,A8)")

this is essentially a virtual cell on the activesheet.
--
Regards,
Tom Ogilvy

Jaky said:
For Excel 2007, I believe it is native. It doesn't show up in VBA.
 
S

sinclas

Same problem!

I have upgraded to Excel 2007 and the VBA is breaking on the YIELD function.
It is throwing a "not defined" error. Where is the YIELD function!?

I have ATP installed and active. What do I do now?

-Thanks

Steve


Jaky said:
I tried that fix, but it did not work. I looked in the object browser and the
yield function isn't there.

Tom Ogilvy said:
Jakey
I don't have excel 2007 readily available, so there may be a better way, but:

If you have tried calling iit as a built in function and it doesn't work,
then try use evaluate or brackets

res = [Yield(A2,A3,A4,A5,A6,A7,A8)]

or

res = Evaluate("Yield(A2,A3,A4,A5,A6,A7,A8)")

this is essentially a virtual cell on the activesheet.
--
Regards,
Tom Ogilvy

Jaky said:
For Excel 2007, I believe it is native. It doesn't show up in VBA.

:

Yield comes from the Analysis toolpak. It is not an Excel native worksheet
function

If you have Analysis Tookpak VBA installed, you can call it with

(demo'd from the immediate window using the help example from Excel Help on
the Yield Function):

? Application.Run("ATPVBAEN.XLA!Yield",Range("A2"),Range("A3"), _
Range("A4"),Range("A5"),Range("A6"),Range("A7"),Range("A8"))
6.50000068807315E-02

so it produces .065 as it should (according to the help example).

--
Regards,
Tom Ogilvy


:

Hi,
I am wondering why I can not find the Yield function in VBA. I type in
"Application.worksheetfunctions." in the VBA workspace to find the yield
function, and YIELDDISC and YIElDMAT is there, but YIELD is not there? Did
they forget to put it in VBA?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top