| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalc Time
Hi All...........
Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recalc Time
Possibly set calculation to manual(application.Calculation = xlManual)
then use the Activesheet.Calculate command in your macro after you "send a formula to a cell". at the end, set application.Calculation = xlAutomatic might work. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........... Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recalc Time
Thanks Tom..........seemed like a really good suggestion, but I couldn't seem
to realize any improvemet. I tried shuffeling things around but nothing helped. The "application.calculation = xlmanual" didn't seem to shut anything off, or rather if it did, then one of the outside macros I called in to place the formulas before I wanted the "Activesheet.calculate" must have reset it somehow. At any rate, by the time it got to "activesheet.calculate" it had already done the recalc and so just did it again.......the whole process actually took longer........ Odd request, thanks for trying.........I reckon I'm stuck with the long processing time. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Possibly set calculation to manual(application.Calculation = xlManual) then use the Activesheet.Calculate command in your macro after you "send a formula to a cell". at the end, set application.Calculation = xlAutomatic might work. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........... Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recalc Time
You could put in your formulas as String s
activecell.value = "ZZXXSum(A1:B1000)" then after they are all entered, use the replace command columns(3).Replace "ZZXX", "=" in your code. -- Regards, Tom Ogilvy "CLR" wrote in message ... Thanks Tom..........seemed like a really good suggestion, but I couldn't seem to realize any improvemet. I tried shuffeling things around but nothing helped. The "application.calculation = xlmanual" didn't seem to shut anything off, or rather if it did, then one of the outside macros I called in to place the formulas before I wanted the "Activesheet.calculate" must have reset it somehow. At any rate, by the time it got to "activesheet.calculate" it had already done the recalc and so just did it again.......the whole process actually took longer........ Odd request, thanks for trying.........I reckon I'm stuck with the long processing time. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Possibly set calculation to manual(application.Calculation = xlManual) then use the Activesheet.Calculate command in your macro after you "send a formula to a cell". at the end, set application.Calculation = xlAutomatic might work. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........... Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recalc Time
Hmmmm.......interesting.........what I am doing is filling a 1200 cell
NamedRange with VLOOKUP formulas, which when using the TEXT method all fill in with the same text, .....in other words do not "step" over to be the correct cell addresses for the next cell........the find and replace worked fine, but i wound up with the same formula in all 1200 cells........and the recalc seemed to take the normal long time anyway. Thanks for the tip tho, I might can use it somewhere else.......... Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: You could put in your formulas as String s activecell.value = "ZZXXSum(A1:B1000)" then after they are all entered, use the replace command columns(3).Replace "ZZXX", "=" in your code. -- Regards, Tom Ogilvy "CLR" wrote in message ... Thanks Tom..........seemed like a really good suggestion, but I couldn't seem to realize any improvemet. I tried shuffeling things around but nothing helped. The "application.calculation = xlmanual" didn't seem to shut anything off, or rather if it did, then one of the outside macros I called in to place the formulas before I wanted the "Activesheet.calculate" must have reset it somehow. At any rate, by the time it got to "activesheet.calculate" it had already done the recalc and so just did it again.......the whole process actually took longer........ Odd request, thanks for trying.........I reckon I'm stuck with the long processing time. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Possibly set calculation to manual(application.Calculation = xlManual) then use the Activesheet.Calculate command in your macro after you "send a formula to a cell". at the end, set application.Calculation = xlAutomatic might work. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........... Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Recalc Time
As Clint Eastwood/Dirty Harry said, "A man's got to know his limitations!"
<g -- Regards, Tom Ogilvy "CLR" wrote in message ... Hmmmm.......interesting.........what I am doing is filling a 1200 cell NamedRange with VLOOKUP formulas, which when using the TEXT method all fill in with the same text, .....in other words do not "step" over to be the correct cell addresses for the next cell........the find and replace worked fine, but i wound up with the same formula in all 1200 cells........and the recalc seemed to take the normal long time anyway. Thanks for the tip tho, I might can use it somewhere else.......... Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: You could put in your formulas as String s activecell.value = "ZZXXSum(A1:B1000)" then after they are all entered, use the replace command columns(3).Replace "ZZXX", "=" in your code. -- Regards, Tom Ogilvy "CLR" wrote in message ... Thanks Tom..........seemed like a really good suggestion, but I couldn't seem to realize any improvemet. I tried shuffeling things around but nothing helped. The "application.calculation = xlmanual" didn't seem to shut anything off, or rather if it did, then one of the outside macros I called in to place the formulas before I wanted the "Activesheet.calculate" must have reset it somehow. At any rate, by the time it got to "activesheet.calculate" it had already done the recalc and so just did it again.......the whole process actually took longer........ Odd request, thanks for trying.........I reckon I'm stuck with the long processing time. Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Possibly set calculation to manual(application.Calculation = xlManual) then use the Activesheet.Calculate command in your macro after you "send a formula to a cell". at the end, set application.Calculation = xlAutomatic might work. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All........... Could anyone please tell me that when I send a formula to a cell of an ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that one sheet or every sheet in the workbook?......If it does them all, how might I be able to turn off the Recalc at the beginning of my macro for "all sheets" and then only turn it on for the ONE SHEET I want to recalculate with each segment of the macro and then turn the whole thing back on when the macro finishes? TIA Vaya con Dios, Chuck, CABGx3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't stop recalc | Excel Worksheet Functions | |||
| XL97 vs XL? Recalc-time | Excel Discussion (Misc queries) | |||
| Automatic Recalc | Excel Worksheet Functions | |||
| Auto ReCalc | Excel Programming | |||
| recalc question | Excel Programming | |||
Linear Mode
