Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing cel
Hello
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing cel
First port of call is Charles Williams' site, this page for starters -
http://www.decisionmodels.com/calcsecretsj.htm Whilst there are differences in the way Excel versions handle UDFs, from the information you have given I suspect the problems are not related to version but something else. I could be wrong but post code with sufficient information to allow others to reproduce the errors. Regards, Peter T "Ed Fulda" <Ed wrote in message ... Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing cel
I would gete all the errors resolved and see how long it takes for the macro
to run before I make a decision to change the UDF functions. The line below works in a new workbook in excel 2003 ActiveWorkbook.Names.Add Name:="This_Name", _ RefersToR1C1:="=Sheet1!R8C2" I had to add a line continuation character to get the above line to run without a compiler error. It also runs multiples times without an error which indicates that It Doesn't matter if the name already exists. My suspect that you are having problems with which object is the active object. For some reason the assumptions in excel 2000 and excel 2003 is using as the default object has changed. Try run the macro with diffferent worksheets as the activeworksheet and see if you can get the macro to run. fix the problems one att a time until you get the entire macro to run. "Ed Fulda" wrote: Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing
Joel - That line works fine when called as a macro, but not from a function
running in a Cell. My sample code looks like the following: Function TestFunc() As String On Error GoTo err ActiveWorkbook.Names.Add Name:="This_Name", _ RefersToR1C1:="=Sheet1!R8C2" ActiveWorkbook.Worksheets("Sheet1").Range("WriteCe ll").Value = "Written" ActiveWorkbook.Worksheets("Sheet1").Range("RecalcC ell").Calculate TestFunc = "Result" Exit Function err: MsgBox err.Description End Function Function test2() As String test2 = Rnd End Function Sub calculatesheet() ActiveWorkbook.Worksheets("Sheet1").Range("Calcula tingFunc").Calculate End Sub CalculatingFunc is a single cell containing =testfunc() WriteCell is a single empty cell RecalcCell contains =test2() There is then a button which runs calculatesheet Thanks, Ed "Joel" wrote: I would gete all the errors resolved and see how long it takes for the macro to run before I make a decision to change the UDF functions. The line below works in a new workbook in excel 2003 ActiveWorkbook.Names.Add Name:="This_Name", _ RefersToR1C1:="=Sheet1!R8C2" I had to add a line continuation character to get the above line to run without a compiler error. It also runs multiples times without an error which indicates that It Doesn't matter if the name already exists. My suspect that you are having problems with which object is the active object. For some reason the assumptions in excel 2000 and excel 2003 is using as the default object has changed. Try run the macro with diffferent worksheets as the activeworksheet and see if you can get the macro to run. fix the problems one att a time until you get the entire macro to run. "Ed Fulda" wrote: Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing cel
I missed what you said about expecting the UDF to do things like add Names
and the like. That would not have worked in any version (without some kludge type workarounds). A UDF called from the worksheet can only return a value or an array of values. Peter T "Peter T" <peter_t@discussions wrote in message ... First port of call is Charles Williams' site, this page for starters - http://www.decisionmodels.com/calcsecretsj.htm Whilst there are differences in the way Excel versions handle UDFs, from the information you have given I suspect the problems are not related to version but something else. I could be wrong but post code with sufficient information to allow others to reproduce the errors. Regards, Peter T "Ed Fulda" <Ed wrote in message ... Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing
Hi Peter,
It doesn't work in 2003 or XP, but it does work absolutely fine in 2000 - if you have it installed check with the code I posted in reply to Joel Ed "Peter T" wrote: I missed what you said about expecting the UDF to do things like add Names and the like. That would not have worked in any version (without some kludge type workarounds). A UDF called from the worksheet can only return a value or an array of values. Peter T "Peter T" <peter_t@discussions wrote in message ... First port of call is Charles Williams' site, this page for starters - http://www.decisionmodels.com/calcsecretsj.htm Whilst there are differences in the way Excel versions handle UDFs, from the information you have given I suspect the problems are not related to version but something else. I could be wrong but post code with sufficient information to allow others to reproduce the errors. Regards, Peter T "Ed Fulda" <Ed wrote in message ... Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing
A UDF can read data from any place. It can only return values to the cell
that called it. If you have code like this it should be turned in to Sub's (not functions). Then call the sub manually from the worksheet menu tools - Macros - testFunc. Another choice is to use a change event to trigger the macro to run rather than manually running the macro. I would start by converting the code to manual initiatated macros. the after yo uget them working then posibly adding a control button to make it easier to run the macro. "Ed Fulda" wrote: Joel - That line works fine when called as a macro, but not from a function running in a Cell. My sample code looks like the following: Function TestFunc() As String On Error GoTo err ActiveWorkbook.Names.Add Name:="This_Name", _ RefersToR1C1:="=Sheet1!R8C2" ActiveWorkbook.Worksheets("Sheet1").Range("WriteCe ll").Value = "Written" ActiveWorkbook.Worksheets("Sheet1").Range("RecalcC ell").Calculate TestFunc = "Result" Exit Function err: MsgBox err.Description End Function Function test2() As String test2 = Rnd End Function Sub calculatesheet() ActiveWorkbook.Worksheets("Sheet1").Range("Calcula tingFunc").Calculate End Sub CalculatingFunc is a single cell containing =testfunc() WriteCell is a single empty cell RecalcCell contains =test2() There is then a button which runs calculatesheet Thanks, Ed "Joel" wrote: I would gete all the errors resolved and see how long it takes for the macro to run before I make a decision to change the UDF functions. The line below works in a new workbook in excel 2003 ActiveWorkbook.Names.Add Name:="This_Name", _ RefersToR1C1:="=Sheet1!R8C2" I had to add a line continuation character to get the above line to run without a compiler error. It also runs multiples times without an error which indicates that It Doesn't matter if the name already exists. My suspect that you are having problems with which object is the active object. For some reason the assumptions in excel 2000 and excel 2003 is using as the default object has changed. Try run the macro with diffferent worksheets as the activeworksheet and see if you can get the macro to run. fix the problems one att a time until you get the entire macro to run. "Ed Fulda" wrote: Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing
I just tried TestFunc() in Excel 2000 and it throws an error when called from a worksheet cell. -- Jim Cone Portland, Oregon USA "Ed Fulda" wrote in message ... Hi Peter, It doesn't work in 2003 or XP, but it does work absolutely fine in 2000 - if you have it installed check with the code I posted in reply to Joel Ed |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workaround for Excel 2002/2003 UDFs recalculating and changing
I forgot, in Excel 2000 a UDF can add a Name, it's an oddity fixed in later
versions. However the next line that attempts to write to another cell will error. Adding the Name indirectly triggers one of the "kludges" I mentioned, namely it triggers a recalc (in 2000). Whilst you can take advantage of it, eg to write to another cell, the UDF also recalc's multiple times, a long loop. To escape that include something like the following in your function - Static bExit as Boolean on error goto errH if bExit then bExit = false exit sub end if bExit = True do stuff to trigger recalc, but don't write to other cells !!! done: bExit = False exit function errH: resume done end function ' do other stuff in the "kludged" calculate event This'll tidy up things for 2000, now forget it and don't use it! It won't work in later versions and should never have worked in earlier versions. If the overall objective is to write a Name, use a worksheet change event. Regards, Peter T "Ed Fulda" wrote in message ... Hi Peter, It doesn't work in 2003 or XP, but it does work absolutely fine in 2000 - if you have it installed check with the code I posted in reply to Joel Ed "Peter T" wrote: I missed what you said about expecting the UDF to do things like add Names and the like. That would not have worked in any version (without some kludge type workarounds). A UDF called from the worksheet can only return a value or an array of values. Peter T "Peter T" <peter_t@discussions wrote in message ... First port of call is Charles Williams' site, this page for starters - http://www.decisionmodels.com/calcsecretsj.htm Whilst there are differences in the way Excel versions handle UDFs, from the information you have given I suspect the problems are not related to version but something else. I could be wrong but post code with sufficient information to allow others to reproduce the errors. Regards, Peter T "Ed Fulda" <Ed wrote in message ... Hello I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround. The Sheet works in the following way: -User enters values, clicks a button to run a Macro, -Macro calculates all the ranges required to return a price -In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells -As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons. It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround? Macro security is set to low. The Errors I receive are the following: The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error" If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error". Any of these run from a UDF in Excel 2000 works. I have a sample workbook if this isn't clear |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workaround no COUNTIFS in 2003 | Excel Worksheet Functions | |||
Not understanding COUNTIFS workaround in 2003 | Excel Discussion (Misc queries) | |||
Workaround for file changing even after just viewing | Excel Discussion (Misc queries) | |||
Issue Recalculating VB Formulas in Excel 2003 | Excel Programming | |||
XL 2003 w/Windows XP Pro. Recalculating large linked files... | Excel Discussion (Misc queries) |