[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
範例下載:陣列-填滿工作表.rar
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET