使用数组分配范围时的 VBA 内存问题

VBA memory issue while assigning Range with array

提问by Alexander

My purpose is to assign an Excel range of cells with the values of a two dimensional array of variants.

我的目的是使用二维变体数组的值分配 Excel 单元格范围。

The assignement works fine, but I face a memory problem (leak) : when I launch Excel, the process takes more or less 22 Mo of Ram. After running the following code (creating a new workbook, assigning the range, closing the workbook without saving), the process takes 33 Mo of RAM.

分配工作正常，但我面临内存问题（泄漏）：当我启动 Excel 时，该过程或多或少需要 22 Mo 的 Ram。运行以下代码（创建新工作簿、分配范围、关闭工作簿而不保存）后，该过程需要 33 Mo 的 RAM。

Does anyone have an idea what I am doing wrong ?

有谁知道我做错了什么？

Regards, Alexander

问候， 亚历山大

Here the code

这里的代码

Option Explicit

Sub test()
   Dim nLines As Long
   Dim xSize As Long
   Dim j As Long
   Dim i As Long

   'Creating New Empty Workbook
   Application.Workbooks.Add
   Range("A1").Select

   Application.ScreenUpdating = False

   nLines = 10000
   xSize = 200

   Dim myRange As Range
   Dim myArray() As Variant

   ReDim myArray(1 To nLines, 1 To xSize)

   'Assigning some values
   For j = 1 To nLines:
       For i = 1 To xSize:
        myArray(j, i) = i * 4 / 3#
       Next i
   Next j

   Set myRange = Range(ActiveCell, ActiveCell.Offset(nLines - 1, xSize - 1))
   myRange .Value = myArray

   'Cleaning up
    Erase myArray
    Set myRange = Nothing

    'Closing workbook without saving
    Application.ActiveWorkbook.Close (False)

    Application.ScreenUpdating = True

End Sub

回答by Alain

VBA isn't C, don't count on being able to control Excel's memory footprint in any way. Excel isn't great at memory management all on its own and often times the fact that you're running macros or what you're doing in those macros has little to no control on how much memory Excel decides to allocate, or whether it ever decides to free it up again.

VBA 不是 C，不要指望能够以任何方式控制 Excel 的内存占用。Excel 本身并不擅长内存管理，而且通常情况下，您正在运行宏或在这些宏中执行的操作几乎无法控制 Excel 决定分配多少内存，或者它是否曾经决定再次释放它。

That being said, there are a few things you can do in your code to try to reduce its mess.

话虽如此，您可以在代码中做一些事情来尝试减少它的混乱。

1. Range("A1").Select

   It's generally bad practice to select cells before operating on them in Excel. The only time you need to select a cell is if you are illustrating something to the viewer of the macro. Furthermore, this code can actually cause a problem in certain versions of excel since you just added a workbook and it there's nothing to tell excel what workbook, and what worksheet from that workbook it should be selecting. You should remove this code altogether (it doesn't impact your logic anyway).

2. Set myRange = Range( ...

   This code isn't necessary since you're only performing one action with the range. You don't need to create the myRange variable at all, so you also don't have to worry about setting it to nothing, or more importantly - the fact that setting it to nothing doesn't actually accomplish anything memory-wise.

3. ActiveCelland Application.ActiveWorkbook

   Again, these unqualified references based on calls to 'Select' and the 'ActiveWorkbook' aren't explicit, and can cause unreliable behavior, especially if the user has other spreadsheets open in the same instance of excel. You're better off storing a reference to the workbook you added in the beginning.

1. Range("A1").Select

   在 Excel 中操作之前先选择单元格通常是不好的做法。您需要选择单元格的唯一时间是向宏的查看者说明某些内容。此外，这段代码实际上可能会在某些版本的 excel 中导致问题，因为您刚刚添加了一个工作簿，并且没有什么可以告诉 excel 什么工作簿，以及它应该选择该工作簿中的哪个工作表。您应该完全删除此代码（无论如何它不会影响您的逻辑）。

2. Set myRange = Range( ...

   此代码不是必需的，因为您只对范围执行一项操作。您根本不需要创建 myRange 变量，因此您也不必担心将其设置为空，或者更重要的是 - 将其设置为空实际上并没有在内存方面完成任何事情。

3. ActiveCell和 Application.ActiveWorkbook

   同样，这些基于对“Select”和“ActiveWorkbook”的调用的非限定引用不是明确的，并且可能导致不可靠的行为，尤其是当用户在同一 excel 实例中打开其他电子表格时。您最好存储对您在开始时添加的工作簿的引用。

All in all, I would change your code to this:

总而言之，我会将您的代码更改为：

Sub test()
    Const nLines As Long = 10000, xSize As Long = 200
    Dim i As Long, j As Long
    Dim newWorkbook As Workbook

    Application.ScreenUpdating = False

    'Creating New Empty Workbook
    Set newWorkbook = Application.Workbooks.Add

    'Assigning some values
    Dim myArray(1 To nLines, 1 To xSize) As Double
    For j = 1 To nLines:
       For i = 1 To xSize:
        myArray(j, i) = i * 4 / 3#
       Next i
    Next j

    With newWorkbook.Sheets(1)
        .Range(.Cells(1, 1), .Cells(nLines - 1, xSize - 1)).Value = myArray
    End With

    'Closing workbook without saving
    newWorkbook.Close (False)

    'Cleaning up
    Erase myArray
    Set newWorkbook = Nothing

    Application.ScreenUpdating = True
End Sub

Closing remarks:

闭幕致辞：

VBA coding lessons aside - Note that the act of opening a new workbook will ask Excel to allocate a lot of new memory - way more than creating your temporary array. This is memory that is up to excel to free or keep around, even after you close the new workbook. There will be no getting around these kinds of increases. Note that the memory footprint of Excel isn't associated with just your current workbooks, it's the entire lifetime of the instance (which supports multiple workbooks), and their backups, optimized calculation trees, undo histories, etc. It really makes no sense to compare the footprint of a newly launched instance of excel to that of one that's just done 'a bunch of stuff'.

除了 VBA 编码课程 - 请注意，打开新工作簿的行为将要求 Excel 分配大量新内存 - 比创建临时数组更多。即使在您关闭新工作簿之后，这也是可以释放或保留的内存。将无法绕过这些类型的增长。请注意，Excel 的内存占用不仅与您当前的工作簿相关联，还与实例的整个生命周期（支持多个工作簿）及其备份、优化计算树、撤消历史记录等相关。这真的没有任何意义将新启动的 excel 实例的足迹与刚刚完成“一堆东西”的实例的足迹进行比较。

Also note that just because Excel doesn't decide to free that memory, it doesn't make it a leak. Excel might have allocated the memory in recyclable / reusable objects that will be repopulated with whatever next workbook you decide to open. Repeatedly rerunning the code may result in a small increase in memory each time, but Excel could just as well be tracking a certain amount of instance action history.

另请注意，仅仅因为 Excel 不决定释放该内存，它不会使其成为泄漏。Excel 可能已经在可回收/可重用对象中分配了内存，这些对象将被您决定打开的下一个工作簿重新填充。反复重新运行代码可能会导致每次内存的小幅增加，但 Excel 也可以跟踪一定数量的实例操作历史记录。

To (anecdotally) illustrate my point, here's the memory footprint of Excel (2007) upon opening a fresh instance, and running your code once, 9 more times, and then 40 more times after that.

为了（轶事地）说明我的观点，以下是 Excel (2007) 在打开一个新实例并运行您的代码一次、9 次，然后再运行 40 次时的内存占用。

It's not your code's fault, the onus is not on you to worry about it, and if your business users are worried about it, they shouldn't be choosing old versions of Excel as their target development platform.

这不是您的代码的错，您不必为此担心，如果您的业务用户担心，他们不应该选择旧版本的 Excel 作为他们的目标开发平台。