Yield function in VBA

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

 

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

 

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

 

[Jaky]

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

 

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

 

[Jaky]

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

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

 

[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

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

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

 

[Jaky]

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

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

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

 

[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

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

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

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?