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]

It's definitely a windows-processor relationship problem.
If you want I could review the macro and try to improve it.

 

[Crones8]

It's definitely a windows-processor relationship problem.
If you want I could review the macro and try to improve it.

Sure, any improvements are always welcome, thank you so much, however, If it's a windows-processor relationship, why does, on the same computer, work about 10 times faster, only "downgrading" from Excel 2016 to 2013?

I'm afraid I can't seem to find where to attach the .xlsm and .csv files. Are Google Drive links accepted here?

Code is as follows (there is a lot of commented parts, that at some point were useful to me, so I didn't want to delete them, so, just omit the commented parts please):

Sub LeerPato()

'ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
'ActiveSheet.Name = "Profundidad"
'ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
'ActiveSheet.Name = "Velocidad"
'ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)

'Limpiando hojas de contenido previo

'ActiveWorkbook.Sheets(1).UsedRange.ClearContents
'ActiveWorkbook.Sheets(2).UsedRange.ClearContents
'ActiveWorkbook.Sheets(3).UsedRange.ClearContents

'Inicio rutina
    ' Timer Start
    ' - Type Ctrl + G to see the Immediate Window, where the result of this
    '   will be output. (View > Immediate Window)
    Dim timerStart As Double
    timerStart = Timer
    
Application.ScreenUpdating = False


Dim FilePath As String
Dim strCSV As String
FilePath = ActiveWorkbook.Path
strCSV = "patop.csv"
Open (FilePath & "\" & strCSV) For Input As #1
Dim Period As Integer
Dim LastPeriod As Integer
Dim strTemp As String
row_number = 0
ActiveWorkbook.Sheets(1).Select
Range("A1").Select

i = 1
k = 0

Do Until EOF(1)

Line Input #1, LineFromFile

strTemp = LineFromFile

Do
    If InStr(1, strTemp, "  ") > 0 Then
        strTemp = Replace(strTemp, "  ", " ")
    Else
        Exit Do
    End If
Loop

LineItems = Split(strTemp, " ")

If i > 3 Then Period = LineItems(1)

If i > 3 And ((LastPeriod - Period) > 5) Then
k = k + 1
row_number = 2
End If

'If i > 3000 Then Exit Do

Fa = ActiveCell.Offset(row_number, k).Row
Ca = ActiveCell.Offset(row_number, k).Column

If i = 2 And k = 0 Then
ActiveCell.Offset(row_number, k).Value = LineItems(0)
ActiveCell.Offset(row_number, k + 1).Value = LineItems(1)
Sheets(2).Cells(Fa, Ca) = LineItems(0)
Sheets(2).Cells(Fa, Ca + 1) = LineItems(1)
Sheets(3).Cells(Fa, Ca) = LineItems(1)
Sheets(3).Cells(Fa, Ca + 1) = LineItems(1)

ElseIf i >= 3 And k = 0 Then
ActiveCell.Offset(row_number, k).Value = LineItems(1)
ActiveCell.Offset(row_number, k + 1).Value = LineItems(2)
Sheets(2).Cells(Fa, Ca) = LineItems(1)
Sheets(2).Cells(Fa, Ca + 1) = LineItems(3)
Sheets(3).Cells(Fa, Ca) = LineItems(1)
Sheets(3).Cells(Fa, Ca + 1) = LineItems(4)

ElseIf i >= 3 And k > 0 Then
ActiveCell.Offset(row_number, k + 1).Value = LineItems(2)
Sheets(2).Cells(Fa, Ca + 1) = LineItems(3)
Sheets(3).Cells(Fa, Ca + 1) = LineItems(4)
End If

i = i + 1

row_number = row_number + 1

If i > 3 Then LastPeriod = LineItems(1)

Loop

Close #1

'Fin de profundidad

'ActiveWorkbook.Sheets(2).Select
'Open (FilePath & "\" & strCSV) For Input As #1
'LastPeriod = 0
'Period = 0
'
'i = 1
'k = 0
'row_number = 0
'
'Do Until EOF(1)
'
'Line Input #1, LineFromFile
'
'strTemp = LineFromFile
'
'Do
'    If InStr(1, strTemp, "  ") > 0 Then
'        strTemp = Replace(strTemp, "  ", " ")
'    Else
'        Exit Do
'    End If
'Loop
'
'LineItems = Split(strTemp, " ")
'
'If i > 3 Then Period = LineItems(1)
'
'If i > 3 And ((LastPeriod - Period) > 5) Then
'k = k + 1
'row_number = 1
'End If
'
''If i > 3000 Then Exit Do
'
'If i < 2 And k = 0 Then
'ActiveCell.Offset(row_number, k * 2).Value = LineItems(0)
'ActiveCell.Offset(row_number, k * 2 + 1).Value = LineItems(2)
'ElseIf i >= 2 And k = 0 Then
'ActiveCell.Offset(row_number, k * 2).Value = LineItems(1)
'ActiveCell.Offset(row_number, k * 2 + 1).Value = LineItems(3)
'ElseIf i >= 2 And k > 0 Then
'ActiveCell.Offset(row_number, k + 1).Value = LineItems(3)
'End If
'
'i = i + 1
'
'row_number = row_number + 1
'
'If i > 3 Then LastPeriod = LineItems(1)
'
'Loop
'
'Close #1

Application.ScreenUpdating = True

    ' Timer End
    Debug.Print vbNewLine & String(40, "*") & vbNewLine & _
                "Timer: " & Timer - timerStart & _
                vbNewLine & String(40, "*") & vbNewLine

End Sub

 

[Alex Blakenburg]

I am sure that @DanteAmor will help you clean up the code (tomorrow) and yes Google Drive or any other sharing platform will work as long as you allow access to anyone with the link and post the link here.

I got bit hung up on this though

Do
    If InStr(1, strTemp, "  ") > 0 Then
        strTemp = Replace(strTemp, "  ", " ")
    Else
        Exit Do
    End If
Loop

Why not replace all of that with:-

strTemp = Application.Trim(strTemp)

 

[DanteAmor]

I'm afraid I can't seem to find where to attach the .xlsm and .csv files. Are Google Drive links accepted here?

Yes, on google drive it's perfect!
Don't forget to share the files to anyone who has the link.

 

[Crones8]

Yes, on google drive it's perfect!
Don't forget to share the files to anyone who has the link.

Thank you,

Here are the links:

Excel Files - Google Drive

drive.google.com

I am sure that @DanteAmor will help you clean up the code (tomorrow) and yes Google Drive or any other sharing platform will work as long as you allow access to anyone with the link and post the link here.

I got bit hung up on this though


Why not replace all of that with:-

strTemp = Application.Trim(strTemp)

I'm not aware of what the code you put does, I'm assuming it uses the Trim function of Excel that uses on worksheets. To be fair, I probably looked online how to use the code that I put, however, it's weird there is a Do Loop, I'm gonna have to make some memory to identify why I thought the Do was necessary, and, replace instead of Trim.

Cheers and thank you both.

 

[Alex Blakenburg]

I'm not aware of what the code you put does, I'm assuming it uses the Trim function of Excel that uses on worksheets. To be fair, I probably looked online how to use the code that I put, however, it's weird there is a Do Loop, I'm gonna have to make some memory to identify why I thought the Do was necessary, and, replace instead of Trim.

Yes it uses the Excel Trim function as opposed to the VBA Trim function. You appear to want to convert multiple spaces to a single space.
The Excel Trim function removed spaces at the front and spaces at the end AND converts multiple spaces to a single space.
The VBA Trim function only does the front and end but not the in between spaces.

The DO loop is required in the code you found because it is replacing 2 spaces with 1 but has to cater for an unknown number of spaces.
Say you have 5 spaces. The first replace 2 spaces for 1 would result in 3 spaces remaining. The 2nd replace would result in 2 spaces remaining and the 3rd replace would finally get it down to 1 space.

 

[rlv01]

Back to your original question, I do believe that macro execution speed under newer versions of Excel have slowed. My experience is with 2010 vs. 2016 or O365 versions. But it is clearly the case. The effect is most noticeable with large workbooks where large groups of row/cells are being processed. Sometimes the effect was a 2 or 3x slowdown. Macros that ran at say 20 sec under 2010 would take ~90 sec to perform the same function on the same data under 2016. For awhile I had 2010 and 2016 installed on the same PC to be sure I was doing an apples to apples comparison. Whatever the reason, the difference is real. This was a fairly common complaint when 2016 rolled out and you can use Google to read some of the threads on the Microsoft support sites. But I warn you it will be frustrating since most of the official responses deliberately refuse to understand the question being asked (i.e. "why is the newer version slower for the exact same macros") and go down the rabbit hole of "send us your code". Don't get me wrong, had you instead asked the question "how can I re-code to claw back some of the performance I lost when I transitioned to this more sluggish new version?" Those responses would have been appropriate. But that was not the question people were asking. Personally, I chalk it up to feature bloat. The slowest part of any macro is usually where the VBA code interacts with the worksheet to read/write/move cells and my subjective impression is that those interactions have gotten slower. Whatever the reason, it is what it is and you need to adapt. Re-coding (either major or minor), along with looking at your add-ins are part of that strategy. Another part is to manage user expectations, such as putting in a progress bar for long processing loops.

 

[Crones8]

Back to your original question, I do believe that macro execution speed under newer versions of Excel have slowed. My experience is with 2010 vs. 2016 or O365 versions. But it is clearly the case. The effect is most noticeable with large workbooks where large groups of row/cells are being processed. Sometimes the effect was a 2 or 3x slowdown. Macros that ran at say 20 sec under 2010 would take ~90 sec to perform the same function on the same data under 2016. For awhile I had 2010 and 2016 installed on the same PC to be sure I was doing an apples to apples comparison. Whatever the reason, the difference is real. This was a fairly common complaint when 2016 rolled out and you can use Google to read some of the threads on the Microsoft support sites. But I warn you it will be frustrating since most of the official responses deliberately refuse to understand the question being asked (i.e. "why is the newer version slower for the exact same macros") and go down the rabbit hole of "send us your code". Don't get me wrong, had you instead asked the question "how can I re-code to claw back some of the performance I lost when I transitioned to this more sluggish new version?" Those responses would have been appropriate. But that was not the question people were asking. Personally, I chalk it up to feature bloat. The slowest part of any macro is usually where the VBA code interacts with the worksheet to read/write/move cells and my subjective impression is that those interactions have gotten slower. Whatever the reason, it is what it is and you need to adapt. Re-coding (either major or minor), along with looking at your add-ins are part of that strategy. Another part is to manage user expectations, such as putting in a progress bar for long processing loops.

Thing is, I would be fine if it was maybe 2 times slower, even 3 than it usually was, I could attribute that to Macros not working that well due to versions of Excel, however, 2 things bother me:

- Macros is taking 6 to 10 times longer, that is certainly not passable, nor something that seems like it's a fault of the version of Excel.
- The same Macros, on another PC, with the same version of Excel, that has a comparable RAM SSD and CPU, takes less than a minute. that is 6 times faster than this PC, on the same version Excel.

So I can't help to assume that something else is at fault here.

 

[rlv01]

Thing is, I would be fine if it was maybe 2 times slower, even 3 than it usually was, I could attribute that to Macros not working that well due to versions of Excel, however, 2 things bother me:

- Macros is taking 6 to 10 times longer, that is certainly not passable, nor something that seems like it's a fault of the version of Excel.
- The same Macros, on another PC, with the same version of Excel, that has a comparable RAM SSD and CPU, takes less than a minute. that is 6 times faster than this PC, on the same version Excel.

So I can't help to assume that something else is at fault here.

If there is a wide difference in performance on two PCs running the same XL version and are roughly comparable in terms of CPU, memory and HD, then it is time to start looking at things like add-ins or check-on-the-fly AV programs like McAfee's on-access scanner service. After that start looking at other windows processes that might be consuming outsized amounts of CPU cycles. The free Microsoft SysInternals suite has a couple of good tools for this Process Monitor and Process Explorer.