Conditions when we can set the excel code calculations to manual andwhen we should not

[Yuvraj]

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

 

[Patrick Molloy]

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

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

 

[Yuvraj]

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

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



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 greaterthan
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

 

[exceluserforeman]

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

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

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



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