VBA Excel的大数据处理变得内存问题 [英] VBA Excel large data manipulation getting memory issue

问题描述

我有两个Excel文件。

首先excel文件包含人名和总天数present列
例

  PersonName的TotalDays
XYZ
A B C D
 

另一个Excel文件中包含人名，日期和状态（present /无）。

  PersonName的日期状态
XYZ 2011/1/1 present
XYZ 2011/1/1 present
 

我要组类似日期的地位为一体，尽数更新首Excel文件。

我在第一个文件大约100行，其中在第二档20000行从那里我需要检查。所以，使其更快，我装在一个阵列第二个文件中的所有行和阅读它们与正常工作的每个条目计算。

问题是，它需要大量的内存，以便在Windows这么多的应用程序会自动打开和系统几乎挂起。

是否有任何替代来实现这一点没有记忆问题，并快速处理。我碰到不过的Scripting.Dictionary不知道它是否会采取更少的内存。

修改
我试着用REDIM preserve和静态数组20,000大小，在这两种情况下，同样的问题发生。

修改

  lblStatus.Caption =加载到内存
 昏暗ArrAuditData（）由于ADATA
 昏暗TotalLookUpCount作为整数
 对于j = 1到50000 如果lookUpRange.Cells（J，cmbChoice.ListIndex）=失败然后
  使用ReDim preserve ArrAuditData（J）由于ADATA
    ArrAuditData（TotalLookUpCount）.AuditType = lookUpRange.Cells（J，cmdAudit2.ListIndex）
    ArrAuditData（TotalLookUpCount）.TransTime = lookUpRange.Cells（J，cmbChoice.ListIndex  -  1）
    ArrAuditData（TotalLookUpCount）.AuditValue = lookUpRange.Cells（J，cmbChoice.ListIndex）
    ArrAuditData（TotalLookUpCount）.Slno = lookUpRange.Cells（J，0）    TotalLookUpCount = TotalLookUpCount + 1
elseif的lookUpRange.Cells（J，cmbChoice.ListIndex）=和J＆GT; 4然后退出对于    万一
    的DoEvents
  下一个
 

解决方案

包含4个变种20000元素数组每次都会占用不到2 MB的RAM。我不认为内存有什么与你的问题 - 除非你碰巧使用一台旧电脑，2 MB的RAM或类似的东西。

一个更可能的原因，为什么你的code是如此沉重的是，你是通过细胞循环。有显著开销VBA和Excel工作表数据之间的每个通信，当你是指许多细胞一次这加起来。在你的情况，你的循环不会达到20万独立的单元格引用。

相反，你应该立刻通过数组加载所有的数据到一个变数组，然后循环，如下图所示。这显著快（即使使用的更多的内存，而不是更少;但同样，我不认为内存是您的问题）。

  lblStatus.Caption =加载到内存
昏暗ArrAuditData（）由于ADATA
昏暗varTemp为Variant
昏暗TotalLookUpCount作为整数加载一切都变成Variant数组。
varTemp = lookUpRange使用ReDim ArrAuditData（1至UBound函数（varTemp，1））由于ADATA对于j = 1至UBound函数（varTemp，1）    如果varTemp（J，cmbChoice.ListIndex）=失败然后        ArrAuditData（TotalLookUpCount）.AuditType = varTemp（J，cmdAudit2.ListIndex）
        ArrAuditData（TotalLookUpCount）.TransTime = varTemp（J，cmbChoice.ListIndex  -  1）
        ArrAuditData（TotalLookUpCount）.AuditValue = varTemp（J，cmbChoice.ListIndex）
        ArrAuditData（TotalLookUpCount）.Slno = varTemp（J，0）
        TotalLookUpCount = TotalLookUpCount + 1    elseif的varTemp（J，cmbChoice.ListIndex）=和J＆GT; 4然后
        对于出口    万一    的DoEvents
下一个使用ReDim preserve ArrAuditData（TotalLookUpCount）由于ADATA
 

有关进一步的阅读，看看这个老但仍相关文章： HTTP：// WWW .avdf.com / apr98 / art_ot003.html

如果你仍然认为RAM是问题，那么请向我们展示 ADATA 类型声明。

修改：另外，不要使用ReDim preserve 这样的循环中！ 使用ReDim preserve 是一个非常昂贵的操作，很少需要做一次以上任何给定的阵列上。这样做20000次会减慢你的code。在这里，我把它拿出来循环，并且只使用一次在最后剪掉未使用的元素。 （请注意如何我最初使用ReDim 编阵，以适应元素的最大数量可以想象的。）

I have two excel files.

First excel file contains the Person Name and Total Days Present column Ex.

PersonName       TotalDays
xyz               
abcd             

Another excel file contains Person Name, Date and Status (present/absent).

PersonName      Date      Status
xyz           1/1/2011    Present
xyz           1/1/2011    Present

I need to group the similar dates status as one, and count them to update in first excel file.

I have around 100 rows in first file where as 20,000 rows in second file from where I need to check. So to make it faster, I loaded all the rows from second file in an Array and reading them to calculate with each entry which works correctly.

The issue is, it take large memory so in Windows so many application automatically opens up and system almost hangs.

Is there any alternate to implement this without memory issue and fast processing. I came across Scripting.Dictionary but not sure whether it will take less memory.

EDIT I tried using redim preserve and static array with 20,000 size, in both case same problem happens.

EDIT

lblStatus.Caption = "Loading to memory"
 Dim ArrAuditData() As AData
 Dim TotalLookUpCount As Integer
 For J = 1 To 50000

 If lookUpRange.Cells(J, cmbChoice.ListIndex) = "Fail" Then
  ReDim Preserve ArrAuditData(J) As AData
    ArrAuditData(TotalLookUpCount).AuditType = lookUpRange.Cells(J, cmdAudit2.ListIndex)
    ArrAuditData(TotalLookUpCount).TransTime = lookUpRange.Cells(J, cmbChoice.ListIndex - 1)
    ArrAuditData(TotalLookUpCount).AuditValue = lookUpRange.Cells(J, cmbChoice.ListIndex)
    ArrAuditData(TotalLookUpCount).Slno = lookUpRange.Cells(J, 0)

    TotalLookUpCount = TotalLookUpCount + 1
ElseIf lookUpRange.Cells(J, cmbChoice.ListIndex) = "" And J > 4 Then Exit For

    End If
    DoEvents
  Next

解决方案

An array of 20,000 elements containing 4 Variants each will take up less than 2 MB of RAM. I don't think memory has anything to do with your problem -- unless you happen to be using an old computer with 2 MB of RAM or something like that.

A more likely reason why your code is so heavy is that you are looping through cells. There is significant overhead to each communication between VBA and Excel sheet data, and this adds up when you refer to many cells one at a time. In your case, your loop does up to 200,000 separate cell references.

Instead, you should load all your data at once into a Variant array, and then loop through that array, as shown below. This is significantly faster (even though this uses more memory, not less; but again, I don't think memory is your issue).

lblStatus.Caption = "Loading to memory"
Dim ArrAuditData() As AData
Dim varTemp As Variant
Dim TotalLookUpCount As Integer

' Load everything into a Variant array. 
varTemp = lookUpRange

ReDim ArrAuditData(1 To UBound(varTemp, 1)) As AData

For J = 1 To UBound(varTemp, 1)

    If varTemp(J, cmbChoice.ListIndex) = "Fail" Then

        ArrAuditData(TotalLookUpCount).AuditType = varTemp(J, cmdAudit2.ListIndex)
        ArrAuditData(TotalLookUpCount).TransTime = varTemp(J, cmbChoice.ListIndex - 1)
        ArrAuditData(TotalLookUpCount).AuditValue = varTemp(J, cmbChoice.ListIndex)
        ArrAuditData(TotalLookUpCount).Slno = varTemp(J, 0)
        TotalLookUpCount = TotalLookUpCount + 1

    ElseIf varTemp(J, cmbChoice.ListIndex) = "" And J > 4 Then
        Exit For

    End If

    DoEvents
Next

ReDim Preserve ArrAuditData(TotalLookUpCount) As AData

For further reading, have a look at this old but still relevant article: http://www.avdf.com/apr98/art_ot003.html

If you still think RAM is the issue, then please show us the AData type declaration.

EDIT: Also, never ReDim Preserve inside a loop like that! ReDim Preserve is a very expensive operation and rarely needs to be done more than once on any given array. Doing it 20,000 times will slow down your code. Here I take it out of the loop, and just use it once at the end to trim off the unused elements. (Notice how I initially ReDim'ed the array to fit the largest conceivable number of elements.)

这篇关于VBA Excel的大数据处理变得内存问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！