Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak
I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA
Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak
Works fine for me in 2007
-- Regards, Peo Sjoblom "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak
Ron West -
It's not clear to me from your post: Are you aware that functions that are part of the Analysis ToolPak in Excel 2003 (and earlier) are native worksheet functions in Excel 2007 (not requiring the Analysis ToolPak add-in)? - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis Too
But... are you aware that (unlike functions like EDate that are now been made
into WorksheetFunctions), the Yield() function is not one of them? Try this, and you will not find the Yield() function:- Sub xx() yy = WorksheetFunction.[press 'y' here] End Sub There are two other Yield-like functions (YieldDisc and YieldMat) in the WorksheetFunctions, but these cannot be replacements for Yield() as they do not have the same number of parameters. RW "Mike Middleton" wrote: Ron West - It's not clear to me from your post: Are you aware that functions that are part of the Analysis ToolPak in Excel 2003 (and earlier) are native worksheet functions in Excel 2007 (not requiring the Analysis ToolPak add-in)? - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak
This appears to be a bug. I have reported it to the Excel Development Team
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak
On Wed, 5 Nov 2008 08:14:38 -0800, "Peo Sjoblom" wrote:
Works fine for me in 2007 -- In VBA? --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis Too
RW -
Seems to be a bug. That is, in Excel 2007 SP1 the YIELD function works fine in a worksheet cell, but it's not available using Application.WorksheetFunction in VBA. - Mike "Ron West" wrote in message ... But... are you aware that (unlike functions like EDate that are now been made into WorksheetFunctions), the Yield() function is not one of them? Try this, and you will not find the Yield() function:- Sub xx() yy = WorksheetFunction.[press 'y' here] End Sub There are two other Yield-like functions (YieldDisc and YieldMat) in the WorksheetFunctions, but these cannot be replacements for Yield() as they do not have the same number of parameters. RW "Mike Middleton" wrote: Ron West - It's not clear to me from your post: Are you aware that functions that are part of the Analysis ToolPak in Excel 2003 (and earlier) are native worksheet functions in Excel 2007 (not requiring the Analysis ToolPak add-in)? - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis Too
I have written a workaround, where I created a new Worksheet and allocated 8
named ranges along the top row (7 for the input parameters and 1 for the result). The 8th cell has this formula in =YIELD(A1,B1,C1,D1,E1,F1,G1) Then, if you add the following function and replace all VBA calls to Yield() with Yield_Workaround() it seems to work OK now. _____________ Function Yield_Workaround(SettlementDate As Date, MaturityDate As Date, Rate, PR, Redemption, Frequency, Optional Basis = "") As Double 'Yield() function is not available in VBA for XL2007 SP1, only as a function in a Worksheet Range("Settlement").Value = SettlementDate Range("Maturity").Value = MaturityDate Range("Rate").Value = Rate Range("PR").Value = PR Range("Redemption").Value = Redemption Range("Frequency").Value = Frequency Range("Basis").Value = Basis Range("WsFn_Yield").Calculate Yield_Workaround = Range("WsFn_Yield").Value End Function "Bernard Liengme" wrote: This appears to be a bug. I have reported it to the Excel Development Team best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis Too
Bernard,
Thank you for reporting the missing Yield() VBA WorksheetFunction! Do you think you could also report the memory bug I found, as shown in the €śExcel 2007 SP1 - Worksheet.Close does not release memory!€ť thread in the microsoft.public.excel.crashesgpfs section? Thanks, Ron West "Bernard Liengme" wrote: This appears to be a bug. I have reported it to the Excel Development Team best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Ron West" wrote in message ... I have a spreadsheet that worked fine in XL2003 using the ATPVBAEN.XLA Analysis ToolPak, but now that our local IT department have forced us all to "upgrade" to XL2007 SP1 it has stopped working because it can't find the Yield() function from the new XL2007 ATPVBAEN.XLAM version of the ToolPak. Using the Object Browser confirms that the function is no longer there. However, if I deregister the new XLAM and register the old XLA file, the Yield() function appears back in the Object Browser and the spreadsheet compiles OK, BUT I now get unsolvable "Error 2015 - Type Mismatch" errors. (After some Googling, I have tried to ensure the date format is correct for the first 2 parameters but the Error 2015 will not go away from the XL2007 run whatever I do). Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
installed analysis toolpak in xl2007, not visible | Excel Discussion (Misc queries) | |||
analysis toolpak | Setting up and Configuration of Excel | |||
How to get Analysis ToolPak? | Setting up and Configuration of Excel | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) | |||
My XIRR function has dropped out, analysis toolpak doesn't fix. | Excel Worksheet Functions |