VBA Takes Over 1 Hour To Run

[Nanogirl21]

I have 1 macro that calls several other macros. I am calling instead of running each code separately because I’d like to run everything with the push of 1 button. I have the call set up like this:

  Sub RUN_ALL()

   

      OptimizeCode_Begin

      01_IMPORT

      02_FIND_DATE

      03_ROLE

      04_NEW

      05_ADD

      06_STATUS_FIX

      07_LEVEL

      08_ FORMATTING

      09_E2

      10_E2_UPDATES

      11_ FORMATTING2

      12_LAST

      13_HIDE

      Save_EXCEL_Files

      OptimizeCode_End

      Complete_Message


  End Sub

When running this macro the time to complete is almost 1 hour + 30 minutes. During this time my computer freezes and becomes very slow. However, if I run each macro 1 by 1 OR run this single call macro by using F8 (step into) the entire process takes no longer than 10 minutes (still a long time, but a whole lot better than 90 minutes).

Any idea on why pne way or running the macros is faster than the other?

Any code that I can add to make things faster?

I have to run this process 3 times a day so having this move faster is very important.


I am using an OptimizeCode_Begin and OptimizeCode_End, but it doesn’t seem to help at all.

  Sub OptimizeCode_Begin()
      

      Application.ScreenUpdating = False

      Application.ErrorCheckingOptions.BackgroundChecking = False

      
      EventState = Application.EnableEvents
      Application.EnableEvents = False
      
      PageBreakState = ActiveSheet.DisplayPageBreaks
      ActiveSheet.DisplayPageBreaks = False
      
  End Sub

  Sub OptimizeCode_End()
      
      ActiveSheet.DisplayPageBreaks = PageBreakState
      Application.EnableEvents = EventState
      Application.ScreenUpdating = True
      Range("A1").Select
  End Sub

 

[AlanY]

see if this improve

Sub OptimizeCode_Begin()
      

      Application.ScreenUpdating = False
      
      [COLOR="#FF0000"]Application.Calculation = xlManual[/COLOR]

      Application.ErrorCheckingOptions.BackgroundChecking = False

      
      EventState = Application.EnableEvents
      Application.EnableEvents = False
      
      PageBreakState = ActiveSheet.DisplayPageBreaks
      ActiveSheet.DisplayPageBreaks = False
      
  End Sub

Sub OptimizeCode_End()
      
      ActiveSheet.DisplayPageBreaks = PageBreakState
      Application.EnableEvents = EventState
      Application.ScreenUpdating = True
      [COLOR="#FF0000"]Application.Calculation = xlAutomatic[/COLOR]
      Range("A1").Select
  End Sub

 

[Nanogirl21]

see if this improve

Sub OptimizeCode_Begin()
      

      Application.ScreenUpdating = False
      
      [COLOR=#FF0000]Application.Calculation = xlManual[/COLOR]

      Application.ErrorCheckingOptions.BackgroundChecking = False

      
      EventState = Application.EnableEvents
      Application.EnableEvents = False
      
      PageBreakState = ActiveSheet.DisplayPageBreaks
      ActiveSheet.DisplayPageBreaks = False
      
  End Sub

Sub OptimizeCode_End()
      
      ActiveSheet.DisplayPageBreaks = PageBreakState
      Application.EnableEvents = EventState
      Application.ScreenUpdating = True
      [COLOR=#FF0000]Application.Calculation = xlAutomatic[/COLOR]
      Range("A1").Select
  End Sub

Would this have any impact on codes that have calulations dependant on others?

For example 07_LEVEL uses a math calulation that happens in 03_ROLE.

 

[hotabae]

Personally, I don't see anything inherently wrong with how you are calling these macros.

My first thought to diagnosing this would be to use something like MicroTimer() to log how long different chunks of code are taking.

- Can use this link: MicroTimer() or google "Microtimer vba excel"

If you put a microtimer check at the end of each "Sub-macro" (01, 02, 03 ... ) you could Debug.Print the time and see if anything sticks out.


As far as what could be taking up time it's hard to say without seeing more of your code. Some classic offenders are Selecting/Activating/Copying/Pasting Cell Data or Looping chunks of code.

 

[AlanY]

can add

Application.Calculate

at the end of 03_ROLE

to force a recalculation

 

[offthelip]

You haven't said what you are doing in your macros , I am guessing you might be able to speed them up by a factor of 500 or so by using variant arrays depending on what you are doing:
One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.

 

[Nanogirl21]

You haven't said what you are doing in your macros , I am guessing you might be able to speed them up by a factor of 500 or so by using variant arrays depending on what you are doing:
One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.

One of the codes that I think is taking a long time is sorting the data based on multiple criteria and then removing durplicates. I need to sort before removing the duplicates becuase I only want the first occurance after sorting. When removing duplicates second, third, fourth ect occurancance is removed. After duplicates are removed I need to filter on a sincgle column. Here is my code.

'Sort Data
Cells.Select
    
Selection.AutoFilter
ActiveWorkbook.Worksheets("Metrics").Sort.SortFields.Clear
    
ActiveWorkbook.Worksheets("Metrics").Sort.SortFields.Add Key:=Range( _
"O:O"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
    
ActiveWorkbook.Worksheets("Metrics").Sort.SortFields.Add Key:=Range( _
"M:M"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
    
ActiveWorkbook.Worksheets("Metrics").Sort.SortFields.Add Key:=Range( _
"N:N"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
    
ActiveWorkbook.Worksheets("Metrics").Sort.SortFields.Add Key:=Range( _
"A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
    
With ActiveWorkbook.Worksheets("Metrics").Sort
.SetRange Columns("A:AG")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
    
'Remove Duplicates
Cells.Select
    
ActiveSheet.Range("$A:$AG").RemoveDuplicates Columns:=1, Header:=xlYes
    
With ActiveSheet
.AutoFilterMode = False
.UsedRange.AutoFilter
.UsedRange.AutoFilter Field:=12, Criteria1:="Yes"
.UsedRange.AutoFilter Field:=22, Criteria1:="New"
End With

 

[Johnny C]

I can't see why it would make a difference running each separately, but check where the last cell is in the Metrics sheet. If it's down at the end row 1,000,000 then the sort might take a long time, you'd need to trim the empty rows.

 

[Nanogirl21]

I can't see why it would make a difference running each separately, but check where the last cell is in the Metrics sheet. If it's down at the end row 1,000,000 then the sort might take a long time, you'd need to trim the empty rows.

Trim empty rows? Never heard of that. How would one do this?

 

[offthelip]

How are you removing duplicates? the fastest way to do that is mark each duplicated row in a spare column at end, then sort on this column so they are all at the bottom then delete the entire range in one go. If you are deleting line by line that will be sloooow.