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

jmpatrick

Active Member
Joined
Aug 17, 2016
Messages
482
Office Version
  1. 365
Platform
  1. Windows
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?:
Code:
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
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top