vba Excel 在尝试计算一个或多个公式时资源不足
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15329199/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel ran out of resources while attempting to calculate one or more formulas
提问by flacnut
I have a workbook to do 'smart'-graphs on my expenses. It's been running for a year and there are now a lot of graphs and expenses. Excel now throws an out-of-resources error whenever I change anything or open the workbook. Thing is, I have lotsof resources and its not using hardly any of them.
我有一本工作簿，可以为我的开支做“智能”图表。它已经运行了一年，现在有很多图表和费用。现在，每当我更改任何内容或打开工作簿时，Excel 都会引发资源不足错误。事情是，我有很多资源，而且几乎没有使用它们。
Win8 64bit w/ 8 core CPU and 32GB of ram
Office 2013 64bit
I have 2 sheets, the first sheet called Expenseshas 3 columns [Date,Description,Amount] and about 1500 rows of data. The second sheet has a LOT (500 or so) of formulas that are all the same and aim to do "Sum all expenses between date X and Y where description matches -some needle-". The formula I have is this:
我有 2 张工作表，第一张名为Expenses 的工作表有 3 列 [Date,Description,Amount] 和大约 1500 行数据。第二张表有很多（大约 500 个）公式，它们都是相同的，目的是“总结日期 X 和 Y 之间描述匹配的所有费用 - 一些针 -”。我的公式是这样的：
=
ABS(
SUMPRODUCT(
--(Expenses!A:A >= DATE(2011,12,1)),
--(Expenses!A:A < DATE(2012,1,1)),
--(ISNUMBER(FIND(C50,Expenses!B:B))),
Expenses!C:C
)
)
Can I give Excel more resources? (I'm happy for it to use all my ram, and chug my CPU for a few minutes).
我可以给 Excel 更多资源吗？（我很高兴它使用了我所有的内存，并让我的 CPU 运行了几分钟）。
Is there a more efficient way I can do this formula?
有没有更有效的方法可以做这个公式？
I understand that this formula is creating a large grid and masking my expenses list with it, and that for each formula this grid has to get created. Should I create a macro to do this more efficiently instead? If I had a macro, I would want to call it from a cell somehow like
我知道这个公式正在创建一个大网格并用它掩盖我的费用清单，并且必须为每个公式创建这个网格。我应该创建一个宏来更有效地执行此操作吗？如果我有一个宏，我想以某种方式从一个单元格中调用它
=sumExpenses(<startDate>, <endDate>, <needle>)
Is that possible?
那可能吗？
Thanks.
谢谢。
采纳答案by NickSlash
I had a go at creating a function that hopefully replicates what your current equation does in VBA with a few differences. Since I don't know the specifics of your second sheet the caching might not help at all.
我尝试创建一个函数，希望可以复制您当前在 VBA 中所做的等式，但有一些不同之处。因为我不知道你的第二张纸的细节，所以缓存可能根本没有帮助。
If your second sheet uses the same date range for all calls to sumExpensesthen it should be a bit quicker as it pre-sums everything on the first pass, If your date range changes throughout then its just doing a lot of work for nothing.
如果您的第二张工作表对所有调用使用相同的日期范围，sumExpenses那么它应该会更快一些，因为它会在第一次通过时预先计算所有内容，如果您的日期范围在整个过程中发生变化，那么它只是白做了很多工作。
Public Cache As Object
Public CacheKey As String
Public Function sumExpenses(ByVal dS As Date, ByVal dE As Date, ByVal sN As String) As Variant
Dim Key As String
Key = Day(dS) & "-" & Month(dS) & "-" & Year(dS) & "_" & Day(dE) & "-" & Month(dE) & "-" & Year(dE)
If CacheKey = Key Then
If Not Cache Is Nothing Then
If Cache.Exists(sN) Then
sumExpenses = Cache(sN)
Exit Function
End If
Set Cache = Nothing
End If
End If
CacheKey = Key
Set Cache = CreateObject("Scripting.Dictionary")
Dim Expenses As Worksheet
Dim Row As Integer
Dim Item As String
Set Expenses = ThisWorkbook.Worksheets("Expenses")
Row = 1
While (Not Expenses.Cells(Row, 1) = "")
If Expenses.Cells(Row, 1).Value > dS And Expenses.Cells(Row, 1).Value < dE Then
Item = Expenses.Cells(Row, 2).Value
If Cache.Exists(Item) Then
Cache(Item) = Cache(Item) + Expenses.Cells(Row, 3).Value
Else
Cache.Add Item, Expenses.Cells(Row, 3).Value
End If
End If
Row = Row + 1
Wend
If Cache.Exists(sN) Then
sumExpenses = Cache(sN)
Else
sumExpenses = CVErr(xlErrNA)
End If
End Function
Public Sub resetCache()
Set Cache = Nothing
CacheKey = ""
End Sub
回答by Mackles24
I had a similar problem where there were a few array formulas down about 150 rows and I got this error, which really baffled me because there really aren't that many formulas to calculate. I contacted our IT guy and he explained the following, some of which I understand, most of which I don't:
我有一个类似的问题，在大约 150 行有几个数组公式，我得到了这个错误，这真的让我感到困惑，因为实际上没有那么多公式可以计算。我联系了我们的 IT 人员，他解释了以下内容，其中一些我理解，其中大部分我不理解：
Generally when the computer tries to process large amounts of data, it uses multi-threaded calculation, where it uses all 8 processors that the computer tricks itself into thinking it has. When multi-threaded calculation is turned off, the computer doesn't throw the 'Excel ran out of resources...' error.
通常，当计算机尝试处理大量数据时，它会使用多线程计算，即使用计算机欺骗自己认为它拥有的所有 8 个处理器。关闭多线程计算后，计算机不会抛出“Excel 资源不足...”错误。
To turn off multi-threaded calculation, got to the 'File' tab in your Excel workbook and select 'Options'. On the right side of the box that appears select 'Advanced' and scroll down to the heading 'Formulas'. Under that heading is a check box that says 'Enable multi-threaded calculation'. Untick it, then select 'OK' and recalculate your formulas.
要关闭多线程计算，请转到 Excel 工作簿中的“文件”选项卡并选择“选项”。在出现的框的右侧选择“高级”并向下滚动到“公式”标题。在该标题下是一个复选框，上面写着“启用多线程计算”。取消勾选它，然后选择“确定”并重新计算您的公式。
回答by Mike Benstead
There could be many causes of this. I just wish Excel would tell us which one (or more) of the 'usual suspects' is committing the offence of RAM hogging at this time.
造成这种情况的原因可能有很多。我只是希望 Excel 能告诉我们此时哪个（或多个）“常见嫌疑人”正在犯下占用 RAM 的罪行。
Also look for
还要找
Circular references
Fragmented Conditional formatting (caused by cutting, pasting, sorting, deleting and adding cells or rows.
Errors resulting in #N/A, #REF, #DIV/0! etc,
Over-use of the volatile functions TODAY(), NOW(), etc.
Too many different formats used
循环引用
碎片化条件格式（由剪切、粘贴、排序、删除和添加单元格或行引起。
导致 #N/A、#REF、#DIV/0 的错误！等等，
过度使用易变函数 TODAY()、NOW() 等。
使用了太多不同的格式
... in that order
... 以该顺序
While you're there, check for
当你在那里时，检查一下
Broken links. A formula relying on a fresh value from external data could return an error.
Any formulas containing #REF!. If your formulas are that messed these may well be present also. They will not cause an error flag but may cause some unreported errors. If your formulas are satisfied by an earlier condition the part of the formula containing #REF! will not be evaluated until other conditions prevail.
断开的链接。依赖外部数据新值的公式可能会返回错误。
任何包含#REF! 的公式。如果你的公式很乱，这些也很可能存在。它们不会导致错误标志，但可能会导致一些未报告的错误。如果您的公式满足较早的条件，则公式中包含 #REF 的部分！除非其他条件适用，否则不会进行评估。
