# Speeding up formatting of all comments in a worksheet

I have recently added a subroutine, CleanUpComments, to be called from multiple other subroutines to clean up comment positions and sizes. It works, but it is extremely slow taking about 10 seconds to run through ~200 comments. I have indicated the code section that accounts for the delay. (wsPlan is the code name of the sheet I'm working with and all of the UF_*** variables are global.) I have tried it with comments visible and hidden with no apparent difference.

Public Sub CleanUpComments()
'           (1) move and size with cells
'           (2) be physically positioned near the cell to which they correspond
'           (3) be optimally sized appropriate to the text within
'           This was created by combining code posted on the following website:

Dim lngArea As Long

'''vv This accounts for the 10 second delay *******************************************
.Placement = xlMoveAndSize
.Left = CellComments.Parent.Offset(0, 1).Left + 5
.TextFrame.Characters.Font.Name = "Tahoma"
.TextFrame.Characters.Font.Size = 8
.TextFrame.AutoSize = True
End With
CellComments.Shape.Height = (lngArea / 150) * 1.1
End If
'''^^ This accounts for the 10 second delay *******************************************

UF_Label1Caption = "Do you wish to leave comments showing?"
UF_Height = 105
UF_TopOffset = 70

UF_QuestionYN.Show

Application.DisplayCommentIndicator = xlCommentAndIndicator
Else
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End If

End Sub


Is there a better (faster) approach to reset the comment positions and sizes?

(I tried this approach, checking if the values needed to be changed before I changed them, but it added another second!)

'''vv
.Placement = xlMoveAndSize
If .Top - CellComments.Parent.Top <> 5 _
Then .Top = CellComments.Parent.Top + 5
If .Left - CellComments.Parent.Offset(0, 1).Left <> 5 _
Then .Left = CellComments.Parent.Offset(0, 1).Left + 5
If .TextFrame.Characters.Font.Name <> "Tahoma" _
Then .TextFrame.Characters.Font.Name = "Tahoma"
If .TextFrame.Characters.Font.Size <> 8 _
Then .TextFrame.Characters.Font.Size = 8
If .TextFrame.AutoSize <> True Then .TextFrame.AutoSize = True
If .Width > 200 Then
lngArea = .Width * .Height
.Width = 150
.Height = (lngArea / 150) * 1.1
End If
End With
'''^^


I also have included my subroutine, Updates_Off, that I use to turn off updates and it is invoked in the calling routine so they are already off when CleanUpComments is called. (OBTW, the variables in this are all globals declared somewhere else.) I have also included my Updates_On to answer any questions about that.

Am I missing anything that I should be turning off that would speed this up?

Thanks.

Public Sub Updates_Off()

' Turn off Screen updating
Application.ScreenUpdating = False

' Turn off Event tracking
Application.EnableEvents = False

' Check what calculation mode is in effect and set current to manual
CalcMode = Application.Calculation
If CalcMode = xlCalculationManual Then _
MsgBox "Starting mode is manual"

IterationMode = Application.Iteration
Iterations = Application.MaxIterations
Application.Calculation = xlCalculationManual

End Sub

' Turn on Screen updating
Application.ScreenUpdating = True

' Turn on Event tracking
Application.EnableEvents = True

' Reset Calculation mode
Application.Calculation = CalcMode
Application.Iteration = IterationMode
Application.MaxIterations = Iterations

If CalcMode = xlCalculationManual Then _
MsgBox "Reset to manual mode"

End Sub

• Since you need to iterate all comments, autosize them, re-adjust sizing, ...I'm not sure I'd call slow taking 50 milliseconds to do all that though.. Although if the comments are visible...do you get similar performance if Updates_Off is invoked before the loop? – Mathieu Guindon Jul 2 at 1:22
• @MathieuGuindon, I forgot to indicate that updates are already turned off before this is called. Clarified that in the OP. And comments are not visible, although I tried it both ways with no discernable difference. – Rey Juna Jul 2 at 20:38
• I'd be curious to see the code for Updates_On – Mathieu Guindon Jul 2 at 20:43
• @MathieuGuindon, it's just the inverse of the Updates_Off. However, I can add it to the OP. These two are called multiple times in the master program to keep updates off except when I need for things like user form display. – Rey Juna Jul 2 at 20:46
• That's exactly what I thought. Having them in the OP would make feedback on these two procedures less speculative. Also UF_*** variables are all globals as well? – Mathieu Guindon Jul 2 at 20:48