On Feb 24, 10:52*am, Patrick Molloy
<PatrickMol...@discussions.microsoft.com> 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 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
Similar Threads
