performance - 加速用字符串填充单元格的代码

Question

下面是一些非常简单的代码，它用字符串填充单元格，并在此过程中打印到状态栏给用户一些反馈。无论如何我可以更快地做到这一点吗？

屏幕更新之类的功能已经被禁用。

我的想法是将字符串发送到一个数组，然后从一个数组中填充单元格，但我不确定可以完成这样一个任务的代码。

让我知道我能做什么!

Sub SheetNames()

    DoEvents
    Application.StatusBar = "Populating array (0)"
    Range("O1") = "ASRS"
    Range("O2") = "Base Coat Line"
    Range("O3") = "Base Coat Line 2"
    Range("O4") = "Body Shop Feed"
    Range("O5") = "Cavity Wax Manual"
    Application.StatusBar = "Populating array (5)"
    Range("O6") = "Cavity Wax Masking"
    Range("O7") = "Cavity Wax Oven"
    Range("O8") = "Cavity Wax Robots"
    Range("O9") = "Clear Coat Line 1"
    Range("O10") = "Clear Coat Line 2"
    Application.StatusBar = "Populating array (10)"
    Range("O11") = "Control Room Robots"
    Range("O12") = "Crane 1"
    Range("O13") = "Crane 2"
    Range("O14") = "Crane 3"
    Range("O15") = "Crane 4"
    Application.StatusBar = "Populating array (15)"
    Range("O16") = "Crane 5"
    Range("O17") = "Crane 6"
    Range("O18") = "De-Mask"
    Range("O19") = "Delivery From Assembly"
    Range("O20") = "Delivery To Assembly"
    Application.StatusBar = "Populating array (20)"
    Range("O21") = "E-Coat"
    Range("O22") = "E-Coat Dip Process"
    Range("O23") = "E-Coat Oven"
    Range("O24") = "E-Coat Sand Strip Out"
    Range("O25") = "E-Coat Sand Strip Out Buffer"
    Application.StatusBar = "Populating array (25)"
    Range("O26") = "Final Inspection"
    Range("O27") = "Interior Sealer 2A"
    Range("O28") = "Interior Sealer 2B"
    Range("O29") = "Interior Sealer Manual"
    Range("O30") = "Interior Sealer Robots"
    Application.StatusBar = "Populating array (30)"
    Range("O31") = "Manual Work Decks"
    Range("O32") = "Mix Room"
    Range("O33") = "Phosphate"
    Range("O34") = "Phosphate Process"
    Range("O35") = "Polish Line"
    Application.StatusBar = "Populating array (35)"
    Range("O36") = "Pre-Trim"
    Range("O37") = "Prim Booth"
    Range("O38") = "Prim Color Sort Buffer"
    Range("O39") = "Prime Oven"
    Range("O40") = "Prime Oven & PSO"
    Application.StatusBar = "Populating array (40)"
    DoEvents
    Range("O41") = "Primer Automation"
    Range("O42") = "Primer Prep"
    Range("O43") = "Primer Tackoff"
    Range("O44") = "RTO 1"
    Range("O45") = "RTO 2"
    Application.StatusBar = "Populating array (45)"
    Range("O46") = "RTO 3"
    Range("O47") = "Sealer Oven"
    Range("O48") = "Sealer Prep"
    Range("O49") = "Sealer Strip Out"
    Range("O50") = "Skid Wash"
    Application.StatusBar = "Populating array (50)"
    Range("O51") = "Spot Repair Conveyor"
    Range("O52") = "Topcoat Blower/Feather"
    Range("O53") = "Topcoat Booth 1"
    Range("O54") = "Topcoat Booth 2"
    Range("O55") = "Topcoat Prep"
    Application.StatusBar = "Populating array (55)"
    Range("O56") = "Topcoat Strip Out"
    Range("O57") = "UBS"
    Range("O58") = "UBS Manual"
    Range("O59") = "UBS Robots"
    Range("O60") = "VIN Scribe Robot"
    Application.StatusBar = "Populating array (60)"
    Range("O61") = "Waste Water Process"
    Application.StatusBar = "Array populated."

End Sub

以下是几个答案的组合，它们有效，而且速度更快!

Sub FillRangeFromArray()
Dim S As Variant
Dim i As Long

S = Array("ASRS", "Base Coat Line", "Base Coat Line 2", "Body Shop Feed", "Cavity Wax Manual", _
"Cavity Wax Masking", "Cavity Wax Oven", "Cavity Wax Robots", "Clear Coat Line 1", "Clear Coat Line 2", _
"Control Room Robots", "Crane 1", "Crane 2", "Crane 3", "Crane 4", "Crane 5", "Crane 6", "De-Mask", _
"Delivery From Assembly", "Delivery To Assembly", "E-Coat", "E-Coat Dip Process", "E-Coat Oven", _
"E-Coat Sand Strip Out", "E-Coat Sand Strip Out Buffer", "Final Inspection", "Interior Sealer 2A", _
"Interior Sealer 2B", "Interior Sealer Manual", "Interior Sealer Robots", "Manual Work Decks", "Mix Room", _
"Phosphate", "Phosphate Process", "Polish Line", "Pre-Trim", "Prim Booth", "Prim Color Sort Buffer", "Prime Oven", _
"Prime Oven & PSO", "Primer Automation", "Primer Prep", "Primer Tackoff", "RTO 1", "RTO 2", "RTO 3", _
"Sealer Oven", "Sealer Prep", "Sealer Strip Out", "Skid Wash", "Spot Repair Conveyor", _
"Topcoat Blower/Feather", "Topcoat Booth 1", "Topcoat Booth 2", "Topcoat Prep", "Topcoat Strip Out", _
"UBS", "UBS Manual", "UBS Robots", "VIN Scribe Robot", "Waste Water Process")

Range("O1").Resize(UBound(S) + 1, 1).Value = Application.Transpose(S)    
End Sub

Answer 1

您可以一次性添加值:

Dim arr
arr = Array("one", "Two", "Three")
Range("a1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)