| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditions when we can set the excel code calculations to manual andwhen we should not
Hi Friends,
I want to know is there some steps we need to know whether the code within should not have comparisons or any other thing Application.xlcalculation=xlmanual <code Applicatioon.xlCalculation=automatic My understanding is that if the cell used in the sheet should not be referenced by any other sheet. If those cells which are referenced by some other sheets for calculations setting the calculation off while performing a save in the active sheet which needs calculation to be set to manual for speed will hamper the functionality before i turn it to automatic again. I have put question mark where I have doubt. Please give your views so that i can understand when i can turn it off as it is hampering the performance. If [dt2.corep] 0 Then //Will this be an issue??? glngDate = CLng((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) like here i am comparing one cell if it is greater than zero so should calculation if set to manual will be problem Also ((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) will these functions be a problem If rInput.offset(8, 1) Like "[SR]" Or rInput.offset(8, 0) = "Y" // ****Will this be a problem as we are comparing ???????? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) < Kround(rInput.offset(8, 8)) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Fixed/RGS/Management contract " & "Rota " & (i - 3) / 16 & " core hours not equal to contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i ElseIf rInput.offset(8, 1) = "F" And rInput.offset(8, 0) = "N" //Wil tis be a problem ??? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) Kround(rInput.offset(8, 8) * 0.75) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Flexi contract " & "Rota " & (i - 3) / 16 & " Core hours greater than 75% of contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i End If Also we have sometimes Worksheet.Sum If IsEmpty(rInput.offset(12, 9)) = True And WorksheetFunction.Sum ([dt2.avt]) 0 Then //Will this be a problem????? rInput.offset(12, 9).Interior.ColorIndex = 3 fMessage.lbErrors.AddItem "Period Rules:= " & rInput.offset (11, 9) & " missing" ElseIf WorksheetFunction.Sum([dt2.avt]) = 0 Then rInput.offset(12, i).ClearContents End If Regards, Prince |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditions when we can set the excel code calculations to manual a
it may sound obvious, but did you test the esults?
Functions in VBA will calculate normally. You might find that switching off screen refress will make the calcs faster too Application.ScreenUpdating = False <CODE Application.ScreenUpdating=True "Yuvraj" wrote: Hi Friends, I want to know is there some steps we need to know whether the code within should not have comparisons or any other thing Application.xlcalculation=xlmanual <code Applicatioon.xlCalculation=automatic My understanding is that if the cell used in the sheet should not be referenced by any other sheet. If those cells which are referenced by some other sheets for calculations setting the calculation off while performing a save in the active sheet which needs calculation to be set to manual for speed will hamper the functionality before i turn it to automatic again. I have put question mark where I have doubt. Please give your views so that i can understand when i can turn it off as it is hampering the performance. If [dt2.corep] 0 Then //Will this be an issue??? glngDate = CLng((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) like here i am comparing one cell if it is greater than zero so should calculation if set to manual will be problem Also ((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) will these functions be a problem If rInput.offset(8, 1) Like "[SR]" Or rInput.offset(8, 0) = "Y" // ****Will this be a problem as we are comparing ???????? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) < Kround(rInput.offset(8, 8)) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Fixed/RGS/Management contract " & "Rota " & (i - 3) / 16 & " core hours not equal to contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i ElseIf rInput.offset(8, 1) = "F" And rInput.offset(8, 0) = "N" //Wil tis be a problem ??? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) Kround(rInput.offset(8, 8) * 0.75) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Flexi contract " & "Rota " & (i - 3) / 16 & " Core hours greater than 75% of contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i End If Also we have sometimes Worksheet.Sum If IsEmpty(rInput.offset(12, 9)) = True And WorksheetFunction.Sum ([dt2.avt]) 0 Then //Will this be a problem????? rInput.offset(12, 9).Interior.ColorIndex = 3 fMessage.lbErrors.AddItem "Period Rules:= " & rInput.offset (11, 9) & " missing" ElseIf WorksheetFunction.Sum([dt2.avt]) = 0 Then rInput.offset(12, i).ClearContents End If Regards, Prince |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditions when we can set the excel code calculations to manuala
On Feb 24, 10:52*am, Patrick Molloy
wrote: it may sound obvious, but did you test the esults? Functions in VBA will calculate normally. You might find that switching off screen refress will make the calcs faster too Application.ScreenUpdating = False <CODE Application.ScreenUpdating=True "Yuvraj" wrote: Hi Friends, I want to know is there some steps we need to know whether the code within should not have comparisons or any other thing Application.xlcalculation=xlmanual <code Applicatioon.xlCalculation=automatic My understanding is that if the cell used in the sheet should not be referenced by any other sheet. If those cells which are referenced by some other sheets for calculations setting the calculation off while performing a save in the active sheet which needs calculation to be set to manual for speed will hamper the functionality before i turn it to automatic again. I have put question mark where I have doubt. Please give your views so that i can understand when i can turn it off as it is hampering the performance. If [dt2.corep] 0 Then //Will this be an issue??? * * * * glngDate = CLng((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) like here i am comparing one cell if it is greater than zero so should calculation if set to manual will be problem Also ((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) *will these functions be a problem *If rInput.offset(8, 1) Like "[SR]" Or rInput.offset(8, 0) = "Y" // ****Will this be a problem as we are comparing ???????? Then * * * * For i = 19 To [dt2.corep] * 16 + 3 Step 16 * * * * * * If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) < Kround(rInput.offset(8, 8)) Then * * * * * * * * fMessage.lbErrors.AddItem ("Core Contract Details:= Fixed/RGS/Management contract " & "Rota " & (i - 3) / 16 & " *core hours not equal to contract hours") * * * * * * * * kaWks.Range("l12").Interior.ColorIndex = 3 * * * * * * End If * * * * Next i * ElseIf rInput.offset(8, 1) = "F" And rInput.offset(8, 0) = "N" //Wil tis be a problem ??? Then * * * * For i = 19 To [dt2.corep] * 16 + 3 Step 16 * * * * * * If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) Kround(rInput.offset(8, 8) * 0.75) Then * * * * * * * * fMessage.lbErrors.AddItem ("Core Contract Details:= Flexi contract " & "Rota " & (i - 3) / 16 & " *Core hours greater than 75% of contract hours") * * * * * * * * kaWks.Range("l12").Interior.ColorIndex = 3 * * * * * * End If * * * * Next i * * End If Also we have sometimes Worksheet.Sum * If IsEmpty(rInput.offset(12, 9)) = True And WorksheetFunction.Sum ([dt2.avt]) 0 Then //Will this be a problem????? * * * * rInput.offset(12, 9).Interior.ColorIndex = 3 * * * * fMessage.lbErrors.AddItem "Period Rules:= " & rInput.offset (11, 9) & " missing" * * ElseIf WorksheetFunction.Sum([dt2.avt]) = 0 Then * * * * rInput.offset(12, i).ClearContents * * End If Regards, Prince- Hide quoted text - - Show quoted text - Hi Patrick, Actually I wanted to do both but in order to avoid risks i am doing this setting calculatioon to manual in some part of the code which is enhancing the speed of calculations a bit as in my applications data is pulled from the sheets and while migrating from excel 2000 to excel 2003 the same codes are taking more time. I checked the options of handling errors and code rewriting but the calculation = manual is solving the problem. I know what you have suggested is alo helpful but friend i am literally not aware when to set it off and when to set it on. In the code sent above can you please guide what i should do. Please reply to al the question mark giving your views and also when to set these calculation to manual and when set the screen updating to false. The parts of code is when i am commiting the change in the sheets on click of button. All these are done to fasten the speed of calculations and performance in excel 2003 compared to excel 2000. Regards, Prince |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditions when we can set the excel code calculations to manu
It is better to identify an exact quantity
For i = 19 To [dt2.corep] * 16 + 3 Step 16 dim varNum varNum= ([dt2.corep] * 16) +3 For i = 19 To varNum Step 16 "Yuvraj" wrote: On Feb 24, 10:52 am, Patrick Molloy wrote: it may sound obvious, but did you test the esults? Functions in VBA will calculate normally. You might find that switching off screen refress will make the calcs faster too Application.ScreenUpdating = False <CODE Application.ScreenUpdating=True "Yuvraj" wrote: Hi Friends, I want to know is there some steps we need to know whether the code within should not have comparisons or any other thing Application.xlcalculation=xlmanual <code Applicatioon.xlCalculation=automatic My understanding is that if the cell used in the sheet should not be referenced by any other sheet. If those cells which are referenced by some other sheets for calculations setting the calculation off while performing a save in the active sheet which needs calculation to be set to manual for speed will hamper the functionality before i turn it to automatic again. I have put question mark where I have doubt. Please give your views so that i can understand when i can turn it off as it is hampering the performance. If [dt2.corep] 0 Then //Will this be an issue??? glngDate = CLng((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) like here i am comparing one cell if it is greater than zero so should calculation if set to manual will be problem Also ((WorksheetFunction.count(kaWks.Range ("f31,f47,f63,f79")) * (4 / [dt2.corep]))) will these functions be a problem If rInput.offset(8, 1) Like "[SR]" Or rInput.offset(8, 0) = "Y" // ****Will this be a problem as we are comparing ???????? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) < Kround(rInput.offset(8, 8)) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Fixed/RGS/Management contract " & "Rota " & (i - 3) / 16 & " core hours not equal to contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i ElseIf rInput.offset(8, 1) = "F" And rInput.offset(8, 0) = "N" //Wil tis be a problem ??? Then For i = 19 To [dt2.corep] * 16 + 3 Step 16 If Kround(rInput.offset(i, 12) + rInput.offset(i, 14)) Kround(rInput.offset(8, 8) * 0.75) Then fMessage.lbErrors.AddItem ("Core Contract Details:= Flexi contract " & "Rota " & (i - 3) / 16 & " Core hours greater than 75% of contract hours") kaWks.Range("l12").Interior.ColorIndex = 3 End If Next i End If Also we have sometimes Worksheet.Sum If IsEmpty(rInput.offset(12, 9)) = True And WorksheetFunction.Sum ([dt2.avt]) 0 Then //Will this be a problem????? rInput.offset(12, 9).Interior.ColorIndex = 3 fMessage.lbErrors.AddItem "Period Rules:= " & rInput.offset (11, 9) & " missing" ElseIf WorksheetFunction.Sum([dt2.avt]) = 0 Then rInput.offset(12, i).ClearContents End If Regards, Prince- Hide quoted text - - Show quoted text - Hi Patrick, Actually I wanted to do both but in order to avoid risks i am doing this setting calculatioon to manual in some part of the code which is enhancing the speed of calculations a bit as in my applications data is pulled from the sheets and while migrating from excel 2000 to excel 2003 the same codes are taking more time. I checked the options of handling errors and code rewriting but the calculation = manual is solving the problem. I know what you have suggested is alo helpful but friend i am literally not aware when to set it off and when to set it on. In the code sent above can you please guide what i should do. Please reply to al the question mark giving your views and also when to set these calculation to manual and when set the screen updating to false. The parts of code is when i am commiting the change in the sheets on click of button. All these are done to fasten the speed of calculations and performance in excel 2003 compared to excel 2000. Regards, Prince |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why doesn't Excel understand what 'Manual' for Calculations? | Excel Discussion (Misc queries) | |||
| how can i get MANUAL CALCULATIONS p e r m a n e n t l y | Setting up and Configuration of Excel | |||
| Automatic and manual calculations | Excel Discussion (Misc queries) | |||
| Automatic and manual calculations | Excel Discussion (Misc queries) | |||
| Excel Calculations Open in Manual Occasionally | Excel Discussion (Misc queries) | |||
Linear Mode
