My Excel 2013 macro is way slow in Excel 2016...

[jmpatrick]

This Macro ran in a few seconds in Excel 2013 but takes 15-20 minutes in Excel 2016. The Macro scans every row and reformats the comments. My spreadsheet has around 4500 rows.

Is it bad coding? Any suggestions to improve it?:

Sub CommentFix()
Dim thisfile As Workbook
Set thisfile = ActiveWorkbook
Dim MyWorkbook As Workbook
Dim MySheet As Worksheet
Dim MyComment As Comment
Dim CommentCount As Long
Dim lArea As Long
Dim fixed As Boolean
fixed = False
On Error GoTo NeedToUnprotect
CommentCount = 0
For Each MyComment In ActiveSheet.Comments
With MyComment.Shape
.Placement = xlMoveAndSize
.Top = MyComment.Parent.Top + 5
.Left = MyComment.Parent.Offset(0, 1).Left + 5
.TextFrame.Characters.Font.Name = "Tahoma"
.TextFrame.Characters.Font.Size = 12
.TextFrame.Characters.Font.Bold = False
.TextFrame.AutoSize = True
CommentCount = CommentCount + 1
End With
If MyComment.Shape.Width > 300 Then
lArea = MyComment.Shape.Width * MyComment.Shape.Height
MyComment.Shape.Width = 200
MyComment.Shape.Height = (lArea / 200) * 1.1
End If
Next MyComment
If CommentCount > 0 Then
MsgBox ("A total of " & CommentCount & " comments in worksheet '" & ActiveSheet.Name & "' of workbook '" & ThisWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")
fixed = True
End If
If fixed = False Then
MsgBox ("No comments were detected.")
End If
On Error GoTo 0
Exit Sub
NeedToUnprotect:
MsgBox ("You must unprotect all worksheets before running the macro.")
thisfile.Activate
Exit Sub
End Sub