当我们需要为Range写入大量数据时,一般将数据先读入一个Variant数组中(类型视具体情况而定),此处不一一细说。通过将数组赋值给Range.Value的方式,可以批量较快地写入数据,这个写入的速度总体是可以接受的,但如果我们的程序要求较高时,这个速度并不理想。 通过Excel的前台写入不同类型数据的效率问题(Ctrl+Enter),向区域(如A1:J20000)分两次写入不同类型(先数字后字符串)同一值的方式进行简单比较,差异明显。 VBA对比代码如下: Private Sub WriteNumberValue() ''''写入数值型的数据 Dim alngValue(1 To 20000, 1 To 10) As Long Dim L As Long Dim I As Integer Dim sngBegin As Single Dim sngEnd As Single For I = 1 To 10 For L = 1 To 20000 alngValue(L, I) = L * I Next Next sngBegin = Timer ActiveSheet.Range("A1:J20000").Value = alngValue sngEnd = Timer Debug.Print sngEnd - sngBegin '''' 0.625 S End Sub Private Sub WriteStringValue() ''''写入文本型的数据 Dim astrValue(1 To 20000, 1 To 10) As String Dim L As Long Dim I As Integer Dim sngBegin As Single Dim sngEnd As Single For I = 1 To 10 For L = 1 To 20000 astrValue(L, I) = "Excel" Next Next sngBegin = Timer ActiveSheet.Range("A1:J20000").Value = astrValue sngEnd = Timer Debug.Print sngEnd - sngBegin '''' 7.671875 S End Sub