[VBA] 陣列 一次 填滿 工作表 Sheet

[VBA] 陣列 一次 填滿 工作表 Sheet

以Office 2003為例,一個工作表有256*65536格,如果要填滿一張工作表,有下列兩種方法。

法一:土法鍊鋼,迴圈填入法,很簡單的用法,但效能很差。

Sub LoopFillRange()
    Dim myRows As Long, myCols As Integer
    Dim i As Long, r As Long
    Dim c As Integer
    Dim StartTime As Date, EndTime As Date
    Application.ScreenUpdating = False
    myRows = 65536
    myCols = 256
 
    ReDim myArray(1 To myRows, 1 To myCols)
    Cells.Clear
    
    StartTime = Timer
    i = 1
    For r = 1 To myRows
        For c = 1 To myCols
            Range("A1").Offset(r - 1, c - 1).Value = i '利用巢狀迴圈將值填入儲存格
            i = i + 1
        Next c
        DoEvents
    Next r
    
    EndTime = Timer
    MsgBox Format(EndTime - StartTime, "00.00") & "秒"
    Application.ScreenUpdating = True
End Sub

 

法二:陣列填入法,先將值填入陣列後,再將陣列填入工作表

    '利用陣列填滿一張工作表
    Dim myRows As Long, myCols As Integer
    Dim myArray() As Long
    Dim i As Long, r As Long
    Dim c As Integer
    Dim StartTime As Date, EndTime As Date
    myRows = 65536
    myCols = 256

    ReDim myArray(1 To myRows, 1 To myCols)
    Cells.Clear
    Application.ScreenUpdating = False
    StartTime = Timer
    i = 1
    For r = 1 To myRows
        For c = 1 To myCols
            myArray(r, c) = i '利用巢狀迴圈將值填入陣列
            i = i + 1
        Next c
    Next r
    
    '將陣列的值傳回儲存格
    Range(Cells(1, 1), Cells(myRows, myCols)) = myArray
    
    EndTime = Timer
    MsgBox Format(EndTime - StartTime, "00.00") & "秒"
    Application.ScreenUpdating = True
End Sub

 

1

範例下載:陣列-填滿工作表.rar

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

Image result for microsoft+mvp+logo