Macros taking considerably longer on Microsoft Excel 2016 (vs 2013) in specific PC

[Crones8]

Hello Everyone,

Was wondering if anyone has ever had this problem, it looks like it's a problem with Excel 2016 in this PC specifically.

I've made a Macros for sorting data from a CSV File, and put it in a normal Worksheet.

I created the Macros in Excel 2013, on a PC at work. This PC has as CPU an Intel Xeon with 8 cores (Z620 HP Workstation, really old) and 1 TB HDD. On this Pc, Macros with current files takes about 2-3 minutes to complete on Excel 2013.

Last year, when using this Macros on this PC (can't remember if I had Excel 2016 or Excel 2013 at that time), which has a Ryzen 3800x with 8 cores and an NvME, Macros used to take about 1 and a half minutes, or 1 minute, depending on the file that was read, so, no problem there.

However, when using the same files as the ones used in the Work PC (which is significantly older and slower on all other tasks, and has Excel 2013), using Excel 2016, Macros now takes about 6 minutes, this is about 2 times slower, than the Work PC, using the exact same files, but, only changing the Excel version.

Assuming it was a problem maybe with the version of Excel, I tried the same Macros on my laptop, which has a Ryzen 4800h and Excel 2016. Macros took only 56 seconds on this notebook, so, it isn't Excel version which is at fault.

To test if it was a problem with my PC, uninstalled Excel 2016, and installed Excel 2013, then tried the Macros. Excel only took 28 seconds, even faster than notebook with Excel 2016.

At this point, I'm assuming there is something wrong on my specific PC when installing Excel 2016 and I have no idea what could be, since it clearly isn't a problem with the PC as such, given that Excel 2013 worked extremely fast.

Things I've tried so far:

- Uninstall Office 2016, run CCleaner, reboot, run CCleaner, reinstall Office 2016. Macros got worse, now it takes 10 minutes, instead of 6, which is about 3 times slower than a significantly older PC

- Uninstall All office related software (OneDrive included), run CCleaner, reboot, run CCleaner, reinstall Office 2016.

- Use Microsoft Office Official Tool for uninstalling, in this topic Uninstall Office from a PC , option 2, and uninstall all of the versions of Office that the software could find, by asking it to search my PC for any and all version of Office that might have been on my PC (this took a long time for the software, since it went checking Office 2007 till 2019).

- Deleting Microsoft Office Folders in Program Files, and, Folders in Users AppData.

RAM shouldn't be an issue, since Work PC has 8 GBs of RAM, laptop hast 16, and PC has 32 GBs, and, work PC, having less and older RAM works 3 times better.

None of this option have worked. To this moment, I believe it has to be fome leftover configuration of Office 2016 that is somewhere on my PC, since Macros works perfectly fast on another PC with the same version of Excel, however, Formatting my PC and reinstalling everything is not an option.

If anyone can help me find a solution that doesn't involve formatting my PC, or, changing to Office 2013, I would greatly apreciatte it.

Cheers and thank you.

 

[DanteAmor]

Hi @Crones8, I made a couple of macros to load the data into memory and then unload it into the sheets.

Why not replace all of that with:-

strTemp = Application.Trim(strTemp)

Hi @Alex Blakenburg, I tried with application.trim, but the process is slower.


The first macro takes 10 seconds:

Sub Extract_data_from_txt_file()
  Dim strCSV As String, strTemp As String
  Dim a As Variant, b As Variant, c As Variant, LineFromFile As Variant, LineItems As Variant
  Dim i As Long, j As Long, k As Long, nMax As Long
  Dim timerStart As Double
  '
  timerStart = Timer
  
  strCSV = ActiveWorkbook.Path & "\" & "patop.csv"
  Open strCSV For Input As #1
  ReDim a(1 To 500, 1 To 2000)
  ReDim b(1 To 500, 1 To 2000)
  ReDim c(1 To 500, 1 To 2000)
  
  Do Until EOF(1)
    Line Input #1, LineFromFile
    i = i + 1
    If i > 2 Then
      strTemp = Trim(LineFromFile)
      Do
        strTemp = Replace(strTemp, "  ", " ")
      Loop While InStr(1, strTemp, "  ") > 0
      
      LineItems = Split(strTemp, " ")
      If LineItems(0) = "1" Then
        nMax = j - 1
        j = 1
        k = k + 1
      End If
      a(j, k) = LineItems(1)
      b(j, k) = LineItems(2)
      c(j, k) = LineItems(3)
      j = j + 1
    End If
  Loop
  Close #1
  Sheets("Profundidad").Range("B3").Resize(nMax, k).Value = a
  Sheets("Velocidad").Range("B3").Resize(nMax, k).Value = b
  Sheets("Caudales").Range("B3").Resize(nMax, k).Value = c
  MsgBox Timer - timerStart
End Sub

--------------
The second macro takes 11 seconds, it should be faster because it has one less if, but it isn't.

Sub Extract_data_from_txt_file()
  Dim strCSV As String, strTemp As String, strWholeFile As String
  Dim a As Variant, b As Variant, c As Variant, LineItems As Variant, arrLines As Variant
  Dim i As Long, j As Long, k As Long, nMax As Long, lngFileLen As Long
  Dim timerStart As Double
  
  timerStart = Timer
  
  strCSV = ActiveWorkbook.Path & "\" & "patop.csv"
  lngFileLen = FileLen(strCSV)
  strWholeFile = Space(lngFileLen)
  Open strCSV For Binary Access Read As #1
    Get #1, , strWholeFile  'variable now holds all file data
  Close #1
  arrLines = Split(strWholeFile, vbCrLf)
  ReDim a(1 To 500, 1 To 2000)
  ReDim b(1 To 500, 1 To 2000)
  ReDim c(1 To 500, 1 To 2000)
  i = 2
  Do
    strTemp = Trim(arrLines(i))
    Do
      strTemp = Replace(strTemp, "  ", " ")
    Loop While InStr(1, strTemp, "  ") > 0
    
    LineItems = Split(strTemp, " ")
    If LineItems(0) = "1" Then
      nMax = j - 1
      j = 1
      k = k + 1
    End If
    a(j, k) = LineItems(1)
    b(j, k) = LineItems(2)
    c(j, k) = LineItems(3)
    j = j + 1
    i = i + 1
  Loop While UBound(arrLines) >= i
  Sheets("Profundidad").Range("B3").Resize(nMax, k).Value = a
  Sheets("Velocidad").Range("B3").Resize(nMax, k).Value = b
  Sheets("Caudales").Range("B3").Resize(nMax, k).Value = c
  MsgBox Timer - timerStart
End Sub

Test on your computers and versions to see which is faster.

 

[Crones8]

Hi @Crones8, I made a couple of macros to load the data into memory and then unload it into the sheets.

Hi @Alex Blakenburg, I tried with application.trim, but the process is slower.


The first macro takes 10 seconds:

Sub Extract_data_from_txt_file()
  Dim strCSV As String, strTemp As String
  Dim a As Variant, b As Variant, c As Variant, LineFromFile As Variant, LineItems As Variant
  Dim i As Long, j As Long, k As Long, nMax As Long
  Dim timerStart As Double
  '
  timerStart = Timer
 
  strCSV = ActiveWorkbook.Path & "\" & "patop.csv"
  Open strCSV For Input As #1
  ReDim a(1 To 500, 1 To 2000)
  ReDim b(1 To 500, 1 To 2000)
  ReDim c(1 To 500, 1 To 2000)
 
  Do Until EOF(1)
    Line Input #1, LineFromFile
    i = i + 1
    If i > 2 Then
      strTemp = Trim(LineFromFile)
      Do
        strTemp = Replace(strTemp, "  ", " ")
      Loop While InStr(1, strTemp, "  ") > 0
  
      LineItems = Split(strTemp, " ")
      If LineItems(0) = "1" Then
        nMax = j - 1
        j = 1
        k = k + 1
      End If
      a(j, k) = LineItems(1)
      b(j, k) = LineItems(2)
      c(j, k) = LineItems(3)
      j = j + 1
    End If
  Loop
  Close #1
  Sheets("Profundidad").Range("B3").Resize(nMax, k).Value = a
  Sheets("Velocidad").Range("B3").Resize(nMax, k).Value = b
  Sheets("Caudales").Range("B3").Resize(nMax, k).Value = c
  MsgBox Timer - timerStart
End Sub

--------------
The second macro takes 11 seconds, it should be faster because it has one less if, but it isn't.

Sub Extract_data_from_txt_file()
  Dim strCSV As String, strTemp As String, strWholeFile As String
  Dim a As Variant, b As Variant, c As Variant, LineItems As Variant, arrLines As Variant
  Dim i As Long, j As Long, k As Long, nMax As Long, lngFileLen As Long
  Dim timerStart As Double
 
  timerStart = Timer
 
  strCSV = ActiveWorkbook.Path & "\" & "patop.csv"
  lngFileLen = FileLen(strCSV)
  strWholeFile = Space(lngFileLen)
  Open strCSV For Binary Access Read As #1
    Get #1, , strWholeFile  'variable now holds all file data
  Close #1
  arrLines = Split(strWholeFile, vbCrLf)
  ReDim a(1 To 500, 1 To 2000)
  ReDim b(1 To 500, 1 To 2000)
  ReDim c(1 To 500, 1 To 2000)
  i = 2
  Do
    strTemp = Trim(arrLines(i))
    Do
      strTemp = Replace(strTemp, "  ", " ")
    Loop While InStr(1, strTemp, "  ") > 0
 
    LineItems = Split(strTemp, " ")
    If LineItems(0) = "1" Then
      nMax = j - 1
      j = 1
      k = k + 1
    End If
    a(j, k) = LineItems(1)
    b(j, k) = LineItems(2)
    c(j, k) = LineItems(3)
    j = j + 1
    i = i + 1
  Loop While UBound(arrLines) >= i
  Sheets("Profundidad").Range("B3").Resize(nMax, k).Value = a
  Sheets("Velocidad").Range("B3").Resize(nMax, k).Value = b
  Sheets("Caudales").Range("B3").Resize(nMax, k).Value = c
  MsgBox Timer - timerStart
End Sub

Test on your computers and versions to see which is faster.

Ok, so, this code is amazing, tests are:

- 3.89 seconds on Excel 2013
- 4.76 seconds on Excel 2016
- Haven't tried it on laptop yet.

So that is like 80%+ improvement on all macros. So this actually solves the issue and heavily improves my Macros. I will have to analyze it to understand what it does, and why it's so different to my Macros on Excel 2016. Thank you so much, this is much much better.

That being said, on another forum, someone suggested that it might have something to do with the Display Driver, and presented some code. His code also solved the problem (it just got rid of the awful 10 minutes and made it compute on the old times, which is less than 1 minute).

After his suggestion worked, I proceeded to uninstall my Display Drivers (nVidia), and install older ones and new ones. This didn't help. So I started trying every line that I suspected had something to do with fixing the lagging problem.

Eventually I discovered that the code Application.EnableEvents = False, fixes the Macros, and makes it work as usual (less than 1 minute).

Does anyone know why this line of command might make such an impactful difference, just changing Excel version, and leaving all other things equal?

Once again, thank you so much @DanteAmor , your code will make me improve greatly with my coding, and also thanks to @Alex Blakenburg for letting me know about Application usage in Macros.

If anyone could help understand why EnableEvents = False fixes the problems to help find the exact root of the problem, I would really appreciate it. Also, Why does @DanteAmor code work so fast without the EnableEvents = False? if mine is so sluggish (10 times slower than with EnableEvents = False) without it on Excel 2016? All of this could help me pinpoint the problem.

 

[DanteAmor]

Ok, so, this code is amazing, tests are:
- 3.89 seconds on Excel 2013
- 4.76 seconds on Excel 2016 ...
So that is like 80%+ improvement on all macros. So this actually solves the issue and heavily improves my Macros.

I'm glad to hear that good news.

If anyone could help understand why EnableEvents = False fixes the problems

EnableEvents = False, Turn off events that can be fired when an excel object has an event.
I assume that excel reserves memory to carry out the triggered processes.
As a consequence of reducing memory, the process itself is slower.
So when you turn off events, excel doesn't allocate memory and processes are faster.

These lines of your macro write to the cells in each cycle, that causes memory to be reserved for each "Change" event.

ActiveCell.Offset(row_number, k).Value = LineItems(0)
Sheets(2).Cells(Fa, Ca) = LineItems(0)
Sheets(3).Cells(Fa, Ca) = LineItems(1)

The difference with my code is that the values of each cycle are stored in memory (not in the cells); and until the end of the macro it is written only once in the cells.

 

[Crones8]

I'm glad to hear that good news.



EnableEvents = False, Turn off events that can be fired when an excel object has an event.
I assume that excel reserves memory to carry out the triggered processes.
As a consequence of reducing memory, the process itself is slower.
So when you turn off events, excel doesn't allocate memory and processes are faster.

These lines of your macro write to the cells in each cycle, that causes memory to be reserved for each "Change" event.

ActiveCell.Offset(row_number, k).Value = LineItems(0)
Sheets(2).Cells(Fa, Ca) = LineItems(0)
Sheets(3).Cells(Fa, Ca) = LineItems(1)

The difference with my code is that the values of each cycle are stored in memory (not in the cells); and until the end of the macro it is written only once in the cells.

So, with your code all cells are written at the same time all at once? that is incredible.

Also, following up on the root of the problem. With your comment on how my code writes on each cell everytime it does 1 loop, I assorted this as the "Events" the .EnableEvents = False is making Excel omit right?, that is to say, each time a cell is written, it triggers an event, which is then (with the help of this line of code) omitted for Excel purposes I guess.

Anyways, on this matter, and following up on @rlv01 comment (thank you so much, by the way, for your input), I started checking PlugIns, and found the root of the problem, which certainly isn't Display Drivers. Turns out, the main culprit of my specific problem is the EuroTOOLS official PlugIN for Excel, which isn't present on my Excel 2013 (so it was actually a config option). After disabling this PlugIN from Excel 2016, Macros speeds up signifcantly, going from 10 minutes runtime, to only 56 seconds (still not as fast as with the code .EnableEvents = False, which only takes 46 seconds, but, a major improvement none the less).

I'm guessing it has something to do with how the plugin interacts with each cell when it's activated, or written on, due maybe to the ActiveCell.Offset(row_number, k).Value = LineItems(0) command, specifically not using ActiveCell commands is always a recommendation in most pages I've researched, and this might be why. EuroTOOLS adds some functions to Excel that can be used as Formula on Cells, which I guess must be activated each time a cell is put on "Active" state (which looks like it's an "Event"), Since I'm writing 3 cells on each Loop, and the amount of cells written in total goes over 1.5 million, even something as little as 4 miliseconds of lag each time a cell is "Activated" and "DeActivated" produces an enormous amount of time difference in the runtime of the Macros. I'm guessing EuroTOOLS PlugIn is causing this 4 milisecond Lag each time a cell is activated, even though it's not being displayed, and, the cumulative lag in each cells slowed my Macros Significantly, while @DanteAmor code, only writes once it seems, and thus is many many times faster, thank you again for the code improvements, and the explanation on the "Events" in my specific Macros.

Wrote all of this down, for anyone that might be looking to understand the problem on top of solving it, but, the TL DR version of the solution is:

Try disabling PlugINs (even Office official ones), and see how much of an improvement that makes.

Thank you all for your input and commentaries, my problem is solved, and, more than that, understood.

 

[Alex Blakenburg]

Thank you for your detailed feedback, I am sure it will help others.
Just curious though, do you have any event macros in any of the Sheet modules or the ThisWorkbook module ?

 

[Crones8]

Lastly, Benchmarking final results for anyone who might be interested.

- My PC, My Macros, Excel 2013 (without EuroTOOLS and with Events Activated), 27 seconds.
- My PC, My Macros, Excel 2016 (With EuroTOOLS and with Events Activated) 589 seconds.
- My PC, My Macros, Excel 2016 (Without EuroTOOLS and with Events Activated) 58 seconds
- My PC, My Macros, Excel 2016 (With EuroTOOLS and with Events DeActivated) 47 seconds.
- My Laptop, My Macros, Excel 2016 (Without EuroTOOLS, and with Events Activated) 43 seconds.
- My PC, @DanteAmor Macros, Excel 2013 (Without EuroTOOLS and Events Activated, even though they don't seem to matter much) 3.9 seconds
- My PC, @DanteAmor Macros, Excel 2016 (With EuroTOOLS and Events Activated, even though they don't seem to matter much) 4.96 seconds
- My PC, @DanteAmor Macros, Excel 2016 (Without EuroTOOLS and Events Activated, even though they don't seem to matter much) 5.1 seconds. This last result is weird, however, I did have some other software (Microsoft Teams and Adobe Reader) on the background, doing nothing though, so maybe it's that, negligible difference anyways.

Thank you again to everyone.

 

[Crones8]

Thank you for your detailed feedback, I am sure it will help others.
Just curious though, do you have any event macros in any of the Sheet modules or the ThisWorkbook module ?

Not sure what is an "Event" Macros, I'll upload a ScreenShot of my VBA for applications Window Tab, which shows everything that I have on this Excel File.

ThisWorkbook module is blank.

- "Módulo 1" Module has the Macros in the image uploaded, however it does not run at all.
- "Módulo 2" Module has my Macros in the image uploaded.
- "Módulo 3" Module has @DanteAmor Macros in the image uploaded.
- "Hoja 144" "Hoja 145" and "Hoja 146" are all also blank in this Window Tab.
- Note that "RibbonX_Code" Module is on the "FUNCRES.XLAM", I'm guessing this is a plugin? not sure what it is, but it's there in every Excel File I open.

Anyways, in the image, all Code that is not blank in any of the modules, sheets or ThisWorkbook is displayed, image also shows the tree of elements.

Lastly, does anyone know where Excel Configs (Or Office Configs) are stored in the PC?, Since, even though I uninstalled using the official tool from Office to uninstall the software, which should be a clean Uninstall, Developer Tab, Solver Tab Plugin, etc, they were all activated after I reinstalled Office 2016, however, they were not activated in my Excel 2013 when I Installed it previously, this proves to me, there is a configuration file somewhere in my PC, which is keeping record of the configuration of my Office Softwares, and, uses them, after I Uninstall and Reinstall the programs.

Cheers and thank you.

 

[Alex Blakenburg]

Not sure what is an "Event" Macros, I'll upload a ScreenShot of my VBA for applications Window Tab, which shows everything that I have on this Excel File.

Event procedures are triggered to run by a workbook or worksheet event.
Here is one link to more information How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change — TheSpreadsheetGuru

They are stored in the code module of a Sheet or Workbook.
They can get into a bit of a loop eg the macro runs when something is changed on the worksheet, the macro itself might make a change to worksheet and triggers itself to run again. EnableEvents = False stops this from happening by disabling event procedures from running. Typically when your macro finishes making changes it turns it back on again.

You don’t appear to have any, so I am a bit surprised by the performance improvement when you disable it. Like you indicated it must be related to the add-ins.

 

[DanteAmor]

Lastly, Benchmarking final results for anyone who might be interested.

- My PC, My Macros, Excel 2013 (without EuroTOOLS and with Events Activated), 27 seconds.
- My PC, My Macros, Excel 2016 (With EuroTOOLS and with Events Activated) 589 seconds.
- My PC, My Macros, Excel 2016 (Without EuroTOOLS and with Events Activated) 58 seconds
- My PC, My Macros, Excel 2016 (With EuroTOOLS and with Events DeActivated) 47 seconds.
- My Laptop, My Macros, Excel 2016 (Without EuroTOOLS, and with Events Activated) 43 seconds.
- My PC, @DanteAmor Macros, Excel 2013 (Without EuroTOOLS and Events Activated, even though they don't seem to matter much) 3.9 seconds
- My PC, @DanteAmor Macros, Excel 2016 (With EuroTOOLS and Events Activated, even though they don't seem to matter much) 4.96 seconds
- My PC, @DanteAmor Macros, Excel 2016 (Without EuroTOOLS and Events Activated, even though they don't seem to matter much) 5.1 seconds. This last result is weird, however, I did have some other software (Microsoft Teams and Adobe Reader) on the background, doing nothing though, so maybe it's that, negligible difference anyways.

Thank you again to everyone.

wow, that's a great piece of work. Thanks for sharing.

 

[Crones8]

Event procedures are triggered to run by a workbook or worksheet event.
Here is one link to more information How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change — TheSpreadsheetGuru

They are stored in the code module of a Sheet or Workbook.
They can get into a bit of a loop eg the macro runs when something is changed on the worksheet, the macro itself might make a change to worksheet and triggers itself to run again. EnableEvents = False stops this from happening by disabling event procedures from running. Typically when your macro finishes making changes it turns it back on again.

You don’t appear to have any, so I am a bit surprised by the performance improvement when you disable it. Like you indicated it must be related to the add-ins.

Thank you @Alex Blakenburg, will read further to know more. Is there any way to get visual prompts that tell every Event that's triggering on the WorkBook in the page while running the Macros? Such as "Change Event" or "BeforeSave Event"? If I could time how much time is every Event taking, then maybe I could pinpoint exactly what event is causing problems with the EuroTOOLS plugin.

wow, that's a great piece of work. Thanks for sharing.

Thank you for teaching me to improve my Macros.

Cheers and thank you 2 again.