Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Calculate Question
I have a Macro that runs on Caclulate that looks at a dynamic dataset as well
as a static dataset. If there is a variance between the two datasets, it will copy the values of the dynamic dataset and paste them into the static dataset so that there is no more variance. My problem is that when the workbook I am in is on Auto Calculate it will exit the subroutine that houses my macro once the data is pasted and go back to the Workbook_SheetCalculate subroutine and get stuck in a loop because the variance between the dynamic dataset and the static dataset is never reconciled. Is there a way I can temprarily bypass the autocalculate functionality to keep this from happening? Thanks, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Calculate Question
Try this....
Dim strOrigCalcStatus As String 'save current calculation setting Select Case Application.Calculation Case xlCalculationAutomatic strOrigCalcStatus = "Automatic" Case xlCalculationManual strOrigCalcStatus = "Manual" Case xlCalculationSemiautomatic strOrigCalcStatus = "SemiAutomatic" Case Else strOrigCalcStatus = "Automatic" End Select 'set workbook to manual Application.Calculation = xlCalculationManual 'PUT YOUR CODE HERE 're-set workbook to original calculation status Select Case strOrigCalcStatus Case "Automatic" Application.Calculation = xlCalculationAutomatic Case "Manual" Application.Calculation = xlCalculationManual Case "SemiAutomatic" Application.Calculation = xlCalculationSemiautomatic Case Else Application.Calculation = xlCalculationAutomatic End Select -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Chad" wrote: I have a Macro that runs on Caclulate that looks at a dynamic dataset as well as a static dataset. If there is a variance between the two datasets, it will copy the values of the dynamic dataset and paste them into the static dataset so that there is no more variance. My problem is that when the workbook I am in is on Auto Calculate it will exit the subroutine that houses my macro once the data is pasted and go back to the Workbook_SheetCalculate subroutine and get stuck in a loop because the variance between the dynamic dataset and the static dataset is never reconciled. Is there a way I can temprarily bypass the autocalculate functionality to keep this from happening? Thanks, Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Calculate Question
Gary,
Thanks for your quick response. I tried something like that earlier with my code but I am still running into a problem...After I reset the Applications Calculation status to its original status (which in this case is Automatic) the code goes right to the Workbook_SheetCalculate subroutine again resulting in an unending loop. If you can think of anything else that would help me out I would really appreciate it. Thanks, Chad "Gary Brown" wrote: Try this.... Dim strOrigCalcStatus As String 'save current calculation setting Select Case Application.Calculation Case xlCalculationAutomatic strOrigCalcStatus = "Automatic" Case xlCalculationManual strOrigCalcStatus = "Manual" Case xlCalculationSemiautomatic strOrigCalcStatus = "SemiAutomatic" Case Else strOrigCalcStatus = "Automatic" End Select 'set workbook to manual Application.Calculation = xlCalculationManual 'PUT YOUR CODE HERE 're-set workbook to original calculation status Select Case strOrigCalcStatus Case "Automatic" Application.Calculation = xlCalculationAutomatic Case "Manual" Application.Calculation = xlCalculationManual Case "SemiAutomatic" Application.Calculation = xlCalculationSemiautomatic Case Else Application.Calculation = xlCalculationAutomatic End Select -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. "Chad" wrote: I have a Macro that runs on Caclulate that looks at a dynamic dataset as well as a static dataset. If there is a variance between the two datasets, it will copy the values of the dynamic dataset and paste them into the static dataset so that there is no more variance. My problem is that when the workbook I am in is on Auto Calculate it will exit the subroutine that houses my macro once the data is pasted and go back to the Workbook_SheetCalculate subroutine and get stuck in a loop because the variance between the dynamic dataset and the static dataset is never reconciled. Is there a way I can temprarily bypass the autocalculate functionality to keep this from happening? Thanks, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto calculate | Excel Worksheet Functions | |||
Auto Calculate Question / HELP! | Excel Worksheet Functions | |||
Auto Calculate | Excel Discussion (Misc queries) | |||
Auto Calculate | Excel Discussion (Misc queries) | |||
Auto Calculate - Some... | Excel Discussion (Misc queries) |