excel 加速VBA中用于单元格格式设置的With循环

q7solyqu 于 2022-11-18 发布在其他

关注(0)|答案(2)|浏览(161)

我设置了一个小的vba宏，它在选定的用户区域中循环并更改单元格的字体。但是，当我不小心按下Ctrl+A并用代码更改字体时，整个工作表崩溃了（我相信是因为循环的单元格太多了）。
有没有办法加快循环速度或使其更有效？

Public Sub Font_OnAction( _
   ByRef Control As Office.IRibbonControl, _
   ByRef galleryID As String, _
   ByRef selectedIndex As Integer)
   
Dim myRange As Range
Set myRange = Selection
Dim myCell As Range
   
For Each myCell In myRange

Select Case selectedIndex

    Case 0
    With myCell.Font
        .Color = RGB(3, 3, 3)
     End With

    Case 1
    With myCell.Font
        .Color = RGB(5, 5, 5)
    End With
    
    Case 2
    With myCell.Font
        .Color = RGB(10, 10, 10)
    End With
    
    Case 3
    With myCell.Font
        .Color = RGB(50, 254, 225)
    End With
    
    Case 4
    With myCell.Font
        .Color = RGB(57, 69, 251)
    End With
    
    Case 5
    With myCell.Font
        .Color = RGB(154, 154, 154)
    End With
    
    Case 6
    With myCell.Font
        .Color = RGB(228, 228, 228)
    End With
    
    Case 7
    With myCell.Font
        .Color = RGB(62, 0, 175)
    End With
    
    Case 8
    With myCell.Font
        .Color = RGB(73, 252, 156)
    End With
    

End Select

Next myCell
   
End Sub

excel

来源：https://stackoverflow.com/questions/74351551/speed-up-with-loop-in-vba-for-cell-formatting

2条答案

按热度按时间

hc2pp10m1#

您不需要循环：

If TypeOf Selection Is Range Then
    Select Case selectedIndex
        Case 0
            Selection.Font.Color = RGB(3, 3, 3)
        Case 1
            Selection.Font.Color = RGB(5, 5, 5) 
        Case 2
            Selection.Font.Color = RGB(10, 10, 10)
        Case 3
            Selection.Font.Color = RGB(50, 254, 225) 
        Case 4
            Selection.Font.Color = RGB(57, 69, 251)
        Case 5
            Selection.Font.Color = RGB(154, 154, 154)
        Case 6
            Selection.Font.Color = RGB(228, 228, 228)
        Case 7
            Selection.Font.Color = RGB(62, 0, 175)
        Case 8
            Selection.Font.Color = RGB(73, 252, 156)
    End Select
End If

赞(0）回复(0）举报 2022-11-18

rm5edbpk2#

如果理解正确，selectedIndex将定义所选范围的颜色，因此您可以先在变量中设置颜色，然后将其应用于一行中的范围，而不需要循环。

Public Sub Font_OnAction( _
   ByRef Control As Office.IRibbonControl, _
   ByRef galleryID As String, _
   ByRef selectedIndex As Integer)

Dim myRange As Range
Dim vColor As Variant

    Application.ScreenUpdating = False
    Set myRange = Selection
    Select Case selectedIndex
        Case 0: vColor = RGB(3, 3, 3)
        Case 1: vColor = RGB(5, 5, 5)
        Case 2: vColor = RGB(10, 10, 10)
        Case 3: vColor = RGB(50, 254, 225)
        ' OTHER CASES...
    End Select
    myRange.Font.Color = vColor
    Application.ScreenUpdating = True
End Sub

赞(0）回复(0）举报 2022-11-18

我来回答

excel 加速VBA中用于单元格格式设置的With循环

2条答案

相关问题

热门标签

最新问答