Calculating Yield in VBA

[JohnAndersen]

All,

I am importing raw trade data into excel where I then need to run yield calculations. This is where I am getting hung up. Although there is a yield function in the worksheet there doesn't appear to be anything equivalent when I try to write it up in VBA. The Analysis Toolpak for VBA is loaded and everything but it still won't work.

Does anyone know anything about this? Has anyone done tried to calculate anything similar where they've gotten around this?

 

[Richard Schollar]

Hi

In the VBE, do you have a reference set to atpvbaen.xls in Tools>References? You need this to access the ATP VBA Yield function. You don't call it as a Worksheetfunction:

MsgBox Yield(settlement,maturity,rate,pr,redemption,frequency,basis)

 

[Andrew Poulsom]

To use Analysis ToolPak functions in VBA, first install the Add-In 'Analysis ToolPak - VBA', then, with your workbook selected in the Visual Basic Editor, set a reference to atpvbaen.xls.

 

[JohnAndersen]

To use Analysis ToolPak functions in VBA, first install the Add-In 'Analysis ToolPak - VBA', then, with your workbook selected in the Visual Basic Editor, set a reference to atpvbaen.xls.

I understand how to use the analysis toolpak functions and how to set up a reference. The issue is that where there is a YIELD function in the worksheet (for an interest paying bond), there isn't an equivalent in VBA that I can find. YieldDisc is for a discounted security and YieldMat is for a bond that only pays interest at maturity.

 

[JohnAndersen]

Hi

In the VBE, do you have a reference set to atpvbaen.xls in Tools>References? You need this to access the ATP VBA Yield function. You don't call it as a Worksheetfunction:

MsgBox Yield(settlement,maturity,rate,pr,redemption,frequency,basis)

I am running Excel 2007 and don't see the yield function.

 

[Andrew Poulsom]

Yield appears as a member of the <Globals> class of atpvbaen.xls in the Object Browser (above the two functions you mentioned). It's syntax is as posted by Richard.

 

[Richard Schollar]

Do you definitely not have Yield as part of the WorksheetFunction Class in VBA in xl2007?

 

[Andrew Poulsom]

I am running Excel 2007 and don't see the yield function.

Isn't it a WorksheetFunction in Excel 2007?

 

[JohnAndersen]

Isn't it a WorksheetFunction in Excel 2007?

It's not a worksheet function as far as I can see. I've looked everywhere.

 

[Andrew Poulsom]

Seems it's missing:

http://www.pcreview.co.uk/forums/yield-vba-function-missing-excel-2007-a-t3763031.html