Animation delay/lag after running VBA script

Status
Not open for further replies.

nightmazino

New Member
Joined
Apr 8, 2020
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
I have a code wherein it automates manual processes to transform a 4k row dataset into 38k rows that can be fed into a system.

Main problem: After I finish running the code (takes 6 mins) I experience animation delay (which doesn't happen before running the script). 1 specific example is when I try to apply autofilter. I need to click on cells before the autofilter icon rows on column headers appear. Also when I try to "fit to columns" it takes a few seconds before it gets applied. Sometimes, even clicking on cells are delayed.

Observations:
  1. When I try to open another workbook right after I ran the script, the delay also happens to that workbook
  2. When I try to close the workbook right after I ran the script (without closing the whole Excel app) and then open the same workbook again, it still has the delay
  3. When I try to close the whole Excel app right after I ran the script and save the workbook then open the file again, the delay disappears and the workbook functions normally now
Steps I already took:

Based on one of the recommendations I got, I cleared all the objects declared in my VBA script. It helped a bit but the delay is still there. I tried to watch all the object variables I have and check all of them before ending the main sub to see if they are really cleared. Here's what I got:

I also add the
VBA Code:
Application.CutCopyMode = False
line since I copy a lot of ranges and it helped a bit in terms of the running time but the problem still persists.

Also, I already disabled the graphic acceleration setting in the options menu.

VBA Code:

My code consists of a Main sub calling more subs in different modules. I'll just try to post the main sub and subs from 1 module

MAIN SUB

VBA Code:
Public inputWB As Workbook
Public vbaWB As Workbook
Public laneWS As Worksheet
Public conversionWS As Worksheet
Public basePortWS As Worksheet
Public splitWS As Worksheet

Sub main()
Dim laneLR As Long, parsedLR As Long
Dim startTime As Double, minutesElapsed
Dim rngName As Name

startTime = Timer

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
.EnableAnimations = False
.Calculation = xlAutomatic
End With

FileOpenDialogBox
setWB

'copy base port grouping sheet to the vba file
'inputWB.Sheets("Base Port Grouping").Copy After:=vbaWB.Sheets("Conversion")
inputWB.Sheets("Lane Details").Copy After:=vbaWB.Sheets("Conversion")
setWS 'declare ws variables
inputWB.Close
Set inputWB = Nothing

'delete name ranges
On Error Resume Next
For Each rngName In Names
vbaWB.Names(rngName.Name).Delete
Next
Set rngName = Nothing
On Error GoTo 0

'/*** PHASE 1 - PARSING OF MULTIPLE PORT NAMES ***/'
identifyMultiplePortNames ("F") 'parse origin location rows with multiple port names
transferParsedToLane 'delete filtered rows in lane details with multiple port names
identifyMultiplePortNames ("I") 'parse destination location rows with multiple port names
transferParsedToLane

'/*** PHASE 2 - TRANSPOSING OF NOMINATIONS ***/'
deleteNominationSummary
getLatestBAF
transposeNomination

laneWS.Delete
Set laneWS = Nothing
ph2WS.Rows("4:1048576").ClearFormats

identifyCommodityType

Set ph2WS = Nothing
Set vbaWB = Nothing

'basePortWS.Delete

Application.CutCopyMode = False

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.EnableAnimations = True
End With

minutesElapsed = Format((Timer - startTime) / 86400, "hh:mm:ss")

MsgBox "Done! The script took " & minutesElapsed & " minute(s) to complete"

End Sub

LAST MODULE I CALL IN THE MAIN SUB
VBA Code:
Sub identifyCommodityType()
'/*** fill out commodity type ***/'

Dim delColStart As Variant, delColEnd As Variant, bafCol As Variant
Dim commCol As Long, ph2LR As Long

ph2LR = ph2WS.Cells(Rows.Count, "E").End(xlUp).row

'delete columns between dg class and baf
delColStart = ph2WS.Rows("3:3").Find(What:="Forecast Owner(s)", After:=ph2WS.Range("A3"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).column
delColEnd = ph2WS.Rows("3:3").Find(What:="Updated Forecast versus initial submitted in tender", After:=ph2WS.Range("A3"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).column
delColStart = columnNumberToLetter(delColStart)
delColEnd = columnNumberToLetter(delColEnd)
ph2WS.Columns(delColStart & ":" & delColEnd).Delete shift:=xlLeft

'insert commodity type column
bafCol = ph2WS.Rows("3:3").Find(What:="UNILEVER BAF PER 20FT", After:=ph2WS.Range("A3"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).column
bafCol = columnNumberToLetter(bafCol)
ph2WS.Columns(bafCol & ":" & bafCol).Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ph2WS.Range(bafCol & 3).Value = "commodity_type"

'generate formula
ph2WS.Select
commCol = ph2WS.Rows("3:3").Find(What:="commodity_type", After:=ph2WS.Range("A3"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).column
ph2WS.Cells(4, commCol).Formula = "=IFERROR(IF(AND(N4="""",I4=""REEFER""),""Food (Frozen)"",IF(N4="""",""Non-DG"",""DG"")),"""")"
ph2WS.Cells(4, commCol).AutoFill Destination:=ph2WS.Range(Cells(4, commCol), Cells(ph2LR, commCol))
ph2WS.Range(Cells(4, commCol), Cells(ph2LR, commCol)).Copy
ph2WS.Range(Cells(4, commCol), Cells(ph2LR, commCol)).PasteSpecial xlPasteValues
Application.CutCopyMode = False

transformNonDG (commCol)

'clear variables
Set delColStart = Nothing
Set delColEnd = Nothing
Set bafCol = Nothing
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Duplicate Animation delay/lag after running VBA script

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top