excel 如何使这些VBA代码自动化，提高其运行时间？[副本]

vwkv1x7d 于 2023-05-19 发布在其他

关注(0)|答案(1)|浏览(105)

此问题已在此处有答案：

How To Speed Up VBA Code（5个答案）
3天前关闭。
我开发了一个VBA代码，可以在填充A列时填充B列，起初VBA代码运行得很快，但当填充的单元格数量很大时VBA代码变得非常慢，我如何才能使代码运行得更有效，使其不再缓慢？我还想让代码自动运行，而不必创建运行宏的快捷方式，这样每次在A列中输入新数据时，B列都会自动填充。这是我的代码

Sub CodeMaster_PipingTagSection()
    Dim lastrow As Long
    Dim i As Long
    
    lastrow = ActiveSheet.Range("I" & Rows.Count).End(xlUp).Row
    
    For i = 7 To lastrow
        If Not IsEmpty(Cells(i, 9)) Then
            Cells(i, 6) = "TEMPERATE"
            Cells(i, 11) = Split(Cells(i, 9), "-")(UBound(Split(Cells(i, 9), "-")))
            Cells(i, 34) = "Three_Layer_PE_or_PP"
            Cells(i, 38) = "N"
            Cells(i, 41) = "N"
            Cells(i, 45) = "Review by Process SME"
            Cells(i, 54) = "False"
            Cells(i, 55) = "1"
            Cells(i, 56) = "N"
            Cells(i, 70) = "Piping"
            Cells(i, 71) = "PIPE"
            Cells(i, 75) = "Criticality RBI Component - Piping"
            Cells(i, 76) = "Non Intrusive"
            Cells(i, 83) = "True"
            Cells(i, 84) = "True"
            Cells(i, 85) = "N"
            Cells(i, 87) = "N"
            Cells(i, 88) = "N"
            Cells(i, 89) = "Visual Detection"
            Cells(i, 90) = "Manual Shutdown"
            Cells(i, 91) = "Inventory blowdown"
            Cells(i, 96) = "100"
            Cells(i, 98) = "False"
            Cells(i, 102) = "False"
        ElseIf IsEmpty(Cells(i, 9)) Then
            Cells(i, 6).ClearContents
            Cells(i, 11).ClearContents
            Cells(i, 34).ClearContents
            Cells(i, 38).ClearContents
            Cells(i, 41).ClearContents
            Cells(i, 45).ClearContents
            Cells(i, 54).ClearContents
            Cells(i, 55).ClearContents
            Cells(i, 56).ClearContents
            Cells(i, 70).ClearContents
            Cells(i, 71).ClearContents
            Cells(i, 75).ClearContents
            Cells(i, 76).ClearContents
            Cells(i, 83).ClearContents
            Cells(i, 84).ClearContents
            Cells(i, 85).ClearContents
            Cells(i, 87).ClearContents
            Cells(i, 88).ClearContents
            Cells(i, 89).ClearContents
            Cells(i, 90).ClearContents
            Cells(i, 91).ClearContents
            Cells(i, 96).ClearContents
            Cells(i, 98).ClearContents
            Cells(i, 102).ClearContents
        End If
    Next i
End Sub

为了加快代码，我尝试通过输入此代码关闭屏幕更新

Sub Stop_Events()
    Application.EnableEvents = False
    '...Statemets
    Application.EnableEvents = True
End Sub

但它仍然运行缓慢，我需要另一种方法，使这个VBA运行快速和自动

excel

来源：https://stackoverflow.com/questions/76250391/how-to-make-this-vba-code-become-automized-and-improve-its-running-time

1条答案

按热度按时间

z31licg01#

尝试

Sub SpeedUpCode()
    Dim lastrow As Long
    Dim data As Variant
    Dim i As Long
    
    lastrow = ActiveSheet.Cells(Rows.Count, "I").End(xlUp).Row
    data = ActiveSheet.Range("A7:CX" & lastrow).Value 'column 102-CX
    
    For i = 1 To UBound(data)
        If Not IsEmpty(data(i, 9)) Then
            data(i, 6) = "TEMPERATE"
            data(i, 11) = Split(data(i, 9), "-")(UBound(Split(data(i, 9), "-")))
            data(i, 34) = "Three_Layer_PE_or_PP"
            data(i, 38) = "N"
            data(i, 41) = "N"
            data(i, 45) = "Review by Process SME"
            data(i, 54) = "False"
            data(i, 55) = "1"
            data(i, 56) = "N"
            data(i, 70) = "Piping"
            data(i, 71) = "PIPE"
            data(i, 75) = "Criticality RBI Component - Piping"
            data(i, 76) = "Non Intrusive"
            data(i, 83) = "True"
            data(i, 84) = "True"
            data(i, 85) = "N"
            data(i, 87) = "N"
            data(i, 88) = "N"
            data(i, 89) = "Visual Detection"
            data(i, 90) = "Manual Shutdown"
            data(i, 91) = "Inventory blowdown"
            data(i, 96) = "100"
            data(i, 98) = "False"
            data(i, 102) = "False"
        ElseIf IsEmpty(data(i, 9)) Then
            data(i, 6) = ""
            data(i, 11) = ""
            data(i, 34) = ""
            data(i, 38) = ""
            data(i, 41) = ""
            data(i, 45) = ""
            data(i, 54) = ""
            data(i, 55) = ""
            data(i, 56) = ""
            data(i, 70) = ""
            data(i, 71) = ""
            data(i, 75) = ""
            data(i, 76) = ""
            data(i, 83) = ""
            data(i, 84) = ""
            data(i, 85) = ""
            data(i, 87) = ""
            data(i, 88) = ""
            data(i, 89) = ""
            data(i, 90) = ""
            data(i, 91) = ""
            data(i, 96) = ""
            data(i, 98) = ""
            data(i, 102) = ""
        End If
    Next i
    
    ActiveSheet.Range("A7:CX" & lastrow).Value = data
End Sub

赞(0）回复(0）举报 2023-05-19

我来回答

excel 如何使这些VBA代码自动化，提高其运行时间？[副本]

1条答案

相关问题

热门标签

最新问答