我正在使用以下代碼在我的 excel 電子表格中自動調整注釋（注釋）的大小。但是我的作業表非常大，代碼可以作業但速度很慢。為了加快速度，我希望指定一個較小的范圍，因為我不需要它在整個作業表上作業。假設單元格 A1 到 B10。有誰知道我該怎么做？請參閱下面的代碼
提前致謝
Sub NotesResize()
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub
我已嘗試按如下方式設定范圍，但出現運行時錯誤“438”：物件不支持此屬性或方法。希望有人知道更好的方法嗎？
Sub NotesResizeSelection()
Dim MyComments As Comment
Dim lArea As Long
Dim rng2 As Range
Set rng2 = Range("A1:B10")
For Each MyComments In rng2.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub
uj5u.com熱心網友回復：
該range物件沒有集合，comments因此您的呼叫rng2.Comments無效，因此出現錯誤。
Comments是Worksheet物件的屬性。
您可以做的是驗證活動評論是否在所選范圍內？雖然這仍然會遍歷所有評論？
如下所示：
Sub NotesResizeSelection()
Dim MyComments As Comment
Dim lArea As Long
Dim rng2 As Range
Set rng2 = Range("A1:B10")
minRow = rng2.row
maxRow = minRow rng2.Rows.Count - 1
minColumn = rng2.Column
maxColumn = minColumn rng2.columns.Count - 1
For Each Comment In ActiveSheet.Comments
cRow = Comment.Parent.row
cCol = Comment.Parent.Column
If (cRow >= minRow And cRow <= maxRow) And (cCol >= minColumn And cCol <= maxColumn) Then
With Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
End If
Next ' comment
End Sub
或者，您可以遍歷所有Cells范圍并確定if cell.Comment Is Not Nothing然后設定相關注釋，如果它不是什么。
雖然可以說這是否比處理每條評論更快？
您可以考慮嘗試設定Application.Screenupdating = false，但這可能會很棘手。（另請參閱我的回答：螢屏更新）
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/526513.html
標籤：擅长vba范围
上一篇：慢跑宏
下一篇：VBA宏洗掉具有3個條件的行