请教:用VBA代码运行超大数据量的时间太久,该如何解决?
有一个文本文件,500万行记录,每行三个数据。
代码先将该文本文件的数据,按行分别读入到三个数组中。
然后需要遍历整个数组,做一个简单的计算。并把计算结果存储为字符串,每行字符串有500万个数据。
一次遍历完成后,将该行数据写到另一个文本文件中。
然后继续下一次遍历,共计80万次。
目前的硬件情况是:4G内存,core i5处理器。代码已经跑了快一个礼拜了,不知道何时会有个尽头。
请教各位高手,这样的VBA代码该如何优化能大幅度提升速度呢?附件是VBA代码,望指点!
Sub AP60First()
Const CON_RECORD_COUNT As Long = 5000000
Dim filePath As String
filePath = "C:\data set.txt"
Dim RowCount As Long, i As Long, j As Long, d As Single
Dim X() As Single, Y() As Single, G() As Long
ReDim X(1 To CON_RECORD_COUNT), Y(1 To CON_RECORD_COUNT), G(1 To CON_RECORD_COUNT)
'从txt加载到数组
Dim textSplit
Dim doCount As Long
doCount = 1
Dim rowText As String
Open filePath For Input As #1
Do While Not EOF(1)
Line Input #1, rowText
If doCount >= 2 Then
textSplit = VBA.Split(rowText, ",")
X(doCount - 1) = textSplit(0) '第一个数据导入
Y(doCount - 1) = textSplit(1) '第二个数据导入
G(doCount - 1) = textSplit(2) '第三个数据导入
End If
doCount = doCount + 1
Loop
Close #1
RowCount = doCount - 2
'对数组中的数据进行计算并写到另一个文本文件中
Dim StrLine As String
filePath = "C:\Data.txt"
Open filePath For Output As #1
For i = 1 To RowCount
If G(i) > 0 Then
StrLine = "" '初始化该行为空
For j = 1 To RowCount
d = (X(i) - X(j)) ^ 2 + (Y(i) - Y(j)) ^ 2
StrLine = StrLine & d & "," '将计算结果组合成字符串,以逗号分隔
Next
StrLine = Left(StrLine, Len(StrLine) - 1) '去除该行字符串的结尾的逗号
Print #1, StrLine '写入文本文件
End If
Next
Close #1
End Sub