Excel2003高级VBA编程---第11章 VBA编程示例和技巧

实例三：读写单元格

   这两天忙着结账，总结的东西比较少，也比较混乱，请大家多多包涵!

 Sub 读写单元格()
/P5zdo*S
[/o;aC0'经验教训：在给单元格赋值时，且末在循环引用单元格，而利用数组代替，最后再将数组赋值给单元格

'1、设定读写单元格个数
2_&Z _u5z%R/qu"m0'2、数组赋值
$s&e2DB7@K4B0'3、写单元格
Y g^+TWH:td[0'4、读单元格给数组精英博客%`5M w
Gs kf
Dim Elements As Long精英博客:yf5XcQ3z1J
Dim Marray()
+|)q+Q"rc3{0D kc%_0Dim Timer1 As Double精英博客;adc/e/A+i'X
Dim i As Long
.HogiZkP2~p0Dim WriteTime As String, ArrayWriteTime As String, ReadTime As String
_rC[1STy0Elements = Val(InputBox("请输入填充数组元素的个数：", "数组赋值"))精英博客V/JcRd6u$WNak`
If Elements = 0 Then Exit Sub
6t3z'A%@(Q+@(E&]6S0ReDim Marray(1 To Elements)精英博客kDB.rXc{
'数组赋值
6l"T;U(A)m6]0For i = 1 To Elements
^K6f{!BX`0   Marray(i) = i

B&tI_3}%J GTJz0Next i

'测试写单元格时间---在循环中引用对象单元格
4p*]
D {Q1~_d0Application.ScreenUpdating = False

vvo\^9n9v#_0Timer1 = Timer
$Nq!Tj1rZ{I iS0For i = 1 To Elements精英博客L,Wg^rX
   Cells(i, 1) = i
5fHbw5y-C0Next i
V*D*s0M!h(`7ex0WriteTime = Format(Timer - Timer1, "00:00") & "秒"

Cells.ClearContents

'测试利用数组写单元格时间

Timer1 = Timer
o3Xh*g i0'此处数组必须要转置，对于一维数组来说，永远都是列数组精英博客8S)O"? C7E0|)m:Z
Range("a1", Cells(Elements, 1)) = Application.WorksheetFunction.Transpose(Marray)精英博客w2|
KTR Pm
ArrayWriteTime = Format(Timer - Timer1, "00:00") & "秒"

'读单元格值给数组

Timer1 = Timer
s(l,|YuS)}0For i = 1 To Elements
tn y6K*BT(lR
^0  Marray(i) = Cells(i, 1)精英博客N+n0D%EQppe0mv
Next i

ReadTime = Format(Timer - Timer1, "00:00") & "秒"

'不利用循环，直接利用单元格区域赋值给数组

'下面取消的数组赋值法，元素不能超过256个（这里要将行转换为列赋值给数组）精英博客 l!K$FGIbC
'Marray = Application.WorksheetFunction.Transpose(Range("a1", Cells(Elements, 1)))精英博客/@&p6Y_%w#i
Application.ScreenUpdating = True

msg = "本次测试共有：" & Elements & "个元素，其中："精英博客7c+^ BK3lP6q'UG
msg = msg & vbCrLf & vbCrLf & "写单元格需要的时间为：" & WriteTime精英博客vW}:dev;h
msg = msg & vbCrLf & vbCrLf & "利用数组写单元格时间为：" & ArrayWriteTime
%h!HM&LF0msg = msg & vbCrLf & vbCrLf & "读单元格值给数组时间为：" & WriteTime

MsgBox msg & vbCrLf & vbCrLf & "第" & Elements & "个元素的值为：" & Marray(Elements), vbQuestion, "读写单元格时间测试"

End Sub
)wRJ2HD l\!s0