asked on

XlCalculations and Macro Slow Problem Excel Vba

Dear Experts,

Please Guide me Over Below Code How can i Speed Up i Made it False Screenupdating and xlcalculations manual but on status bar while running code it shows calculating and code execute slowly Please Guide the best you understand out of my question.

Regards,

Sub GL1_Code()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If ThisWorkbook.Sheets("GL").FilterMode Then
ThisWorkbook.Sheets("GL").showalldata
End If

ThisWorkbook.Sheets("GL").Unprotect ' unprotect Protected area

Dim rRow As Range

Dim Lrr As Integer
Lrr = ThisWorkbook.Sheets("GL").Range("A" & Rows.Count).End(xlUp).Row

For Each rRow In ThisWorkbook.Sheets("Inv").Range("B9:B" & ThisWorkbook.Sheets("Inv").Range("B" & Rows.Count).End(xlUp).Row)

If rRow.Value > Empty Then

If ThisWorkbook.Sheets("GL").Range("A" & Lrr + 1).Value = Empty Then

'SERIAL NUMBER AT GL LEDGERS
ThisWorkbook.Sheets("GL").Range("A" & Lrr + 1).Value _
= ThisWorkbook.Sheets("GL").Range("A" & Lrr).Value + 1
'DATE AT INVOICE
ThisWorkbook.Sheets("GL").Range("B" & Lrr + 1).Value = CDate(ThisWorkbook.Sheets("Inv").Range("K5").Value)
'
ThisWorkbook.Sheets("GL").Range("C" & Lrr + 1).Value = ThisWorkbook.Sheets("Inv").Range("C4").Value

ThisWorkbook.Sheets("GL").Range("D" & Lrr + 1).Value = "BR"
ThisWorkbook.Sheets("GL").Range("E" & Lrr + 1).Value = ThisWorkbook.Sheets("Inv").Range("B4").Value
ThisWorkbook.Sheets("GL").Range("F" & Lrr + 1).Value _
= rRow.Value & " , " & rRow.Offset(0, 2).Value & " , " & rRow.Offset(0, 2).Value & " , " & rRow.Offset(0, 3).Value & " , Fare:" & rRow.Offset(0, 4).Value & " , Taxes:" & rRow.Offset(0, 5).Value
ThisWorkbook.Sheets("GL").Range("G" & Lrr + 1).Value = rRow.Offset(0, -1).Value

If rRow.Offset(0, 6).Value > 0 Then
ThisWorkbook.Sheets("GL").Range("H" & Lrr + 1).Value = _
"-" & 100 * (rRow.Offset(0, 6).Value / rRow.Offset(0, 4)) & "%"
ElseIf rRow.Offset(0, 6).Value < 0 Then
ThisWorkbook.Sheets("GL").Range("H" & Lrr + 1).Value = _
-100 * (rRow.Offset(0, 6).Value / rRow.Offset(0, 4)) & "%"
End If

ThisWorkbook.Sheets("GL").Range("I" & Lrr + 1).Value = rRow.Offset(0, 1)
ThisWorkbook.Sheets("GL").Range("J" & Lrr + 1).Value = rRow.Offset(0, 7)


Call Module2.CopyData_Line

Lrr = ThisWorkbook.Sheets("GL").Range("A" & Rows.Count).End(xlUp).Row

ThisWorkbook.Sheets("GL").Range("C" & Lrr).Value = ThisWorkbook.Sheets("Inv").Range("C5").Value
ThisWorkbook.Sheets("GL").Range("E" & Lrr).Value = ThisWorkbook.Sheets("Inv").Range("B5").Value

If rRow.Offset(0, 8).Value > 0 Then
ThisWorkbook.Sheets("GL").Range("H" & Lrr).Value = _
"-" & 100 * (rRow.Offset(0, 8).Value / rRow.Offset(0, 4)) & "%"
ElseIf rRow.Offset(0, 8).Value < 0 Then
ThisWorkbook.Sheets("GL").Range("H" & Lrr).Value = _
-100 * (rRow.Offset(0, 8).Value / rRow.Offset(0, 4)) & "%"
Else

ThisWorkbook.Sheets("GL").Range("H" & Lrr).Value = 0

End If

ThisWorkbook.Sheets("GL").Range("J" & Lrr).Value = rRow.Offset(0, 9)

End If

Else

MsgBox "Last Row Has Already Data"

End If

Next rRow




'Protect GL Sheet.
Call Module5.GL_Protect

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Open in new window

Fabrice Lambert

First, couple of comments about your code:
- Indent your code, it is nearly unreadable in this state ...
- Set a reference to the workbooks, worksheets and ranges you are working with, always writing thisWorkbook.Workheets("name").Range("A5:K20") get tedious, plus it will slightly increase performances as Excel won't have to solve the full references on each instruction.

Concerning your issue, it looks like you are working with contigous cells, and you are not interrested in formulas, so I suggest to copy your cells in an array, process the array, and copy back the array to cells once you are done.
Processing arrays is about 20 times faster than processing ranges, and Excel is perfectly able to handle the copy from cells to array (and vice versa).
Little warnings
- The array obtained must be variant.
- The array obtained is 2D.
- When copying the array back to the workbook, you must ensure the targeted range is the same size as the array.
Sample code:

Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("A1:A100")

    '// copy the range to an array
Dim data() As Variant
data = rng.Value

    '// Data processing
Dim i As Long
For i = LBound(data, 1) to UBound(data, 1)
    Dim j As Long
    For j = LBound(data, 2), to UBound(Data, 2)
        '// random code
    Next
Next

    '// Copy the array back to the range
rng.Value = data

Open in new window

Finally, for a true speed gain, prefer formulas in your worksheets over VBA.

Norie

Are you sure the code is still running when you see the status bar indicating that calculation is happening?

Could it be that the code has finished running, calculation has been set back to automatic, the workbook is calculating and it's the calculation that's slowing things down?

Fabrice Lambert

Side note:
You should not jump on disabling Excel's screen update and calculation. It should be done only as last option for performances increase.

WiseOwl Excel

ASKER

It Show on Status Bar Calculating 8 Threads???? My Problem is to fasten Code.

Norie

Are you 100% sure the code is the problem?

What happens if you put a breakpoint (F8) on line 86 of the code, run it and then step over this line?

Application.Calculation = xlCalculationAutomatic

Open in new window

WiseOwl Excel

ASKER

Nothing Just Convert to Auto Calc.

Norie

When you added the breakpoint was the code still really slow?

WiseOwl Excel

ASKER

It Doesn't Feel Slow Just a Sec but Consolidated Will Slow.

Fabrice Lambert

So it appear that the direct culprit isn't the function itself, but whatever is called when calculation is turned back on.

You should investigate on what is slowing down:
How is the function triggered ?
Are there many VBA functions in your formulas ?
Can VBA functions be replaced by formulas ?
Any code is run on Worksheet events ? wich events ? wich code ?
ect ...

Give us as much détails about your workbook as possible.

PS: My first comment remain valid tho, think about it.).

Norie

If it's calculation that's actually slowing things down the first thing to check is your formulas.

What formulas are you using?

Are you using full column/row references in the formulas?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

Also it's not necessary to lock and unlock your data, and it will speed up your code, if you initially lock it this way.

Private Sub Workbook_Open()
Sheets(“GL”).Protect Password:="Secret", UserInterFaceOnly:=True
End Sub

Open in new window

See this Microsoft page for more protection options.

WiseOwl Excel

ASKER

Sir Martin Liss Code Work Better !!! Regards

Martin Liss

If you have the answer you need then please don't forget to select the post(s) that have helped you as the answer to this question.

WiseOwl Excel

ASKER

Sir I Said Code Work Better a bit Speed but yet sheet is slow i am Experimenting with Workbook and If Found Answer the i share my Experience and else your Input will be at Top of All Solutions.

Regards,

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

This is what I suggested in my first answer (even if I didn't provide a complete solution).

Martin Liss

I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Experts Exchange Top Expert VBA (current)