vba - Excel 2016 锁定了在早期版本的 Excel 中工作的 VBA 函数

Question

这是 VBA 代码的简化版本:

Option Explicit

Function LooBac(RawRan)

'   This VBA function locks up in Excel 2016.  It worked in previous
'   versions of Excel.

Dim oRawRanObj                  'The Raw Range
Dim iRawRanObjRow As Integer    'Number of Rows
Dim iRawRanObjCol As Integer    'Number of Columns
Dim Tem As Double               'Temp value

Dim i As Integer                'Counter
Dim j As Integer                'Counter

Application.Volatile

If TypeName(RawRan) <> "Range" Then

'       The Raw Range is not a range.

    LooBac = "*** An invalid RawRan range has been provided. ***"
    Exit Function

End If

'   Build an object using the structure of the Raw Range.

Set oRawRanObj = RawRan
iRawRanObjRow = oRawRanObj.Rows.Count
iRawRanObjCol = oRawRanObj.Columns.Count

'   This section seems to be the problem.
'   It appears to be related to the term oRawRanObj.Cells(j).Value

For i = 1 To 200
    For j = 1 To iRawRanObjRow
        Tem = oRawRanObj.Cells(j).Value
    Next j
Next i

LooBac = Tem

End Function

使用以下内容设置电子表格:

打开一个新的空白 Excel 2016 文件。

将计算模式设置为手动。

转到 VBA 并将 VBA 代码添加到 VBA 模块。

转到空白电子表格并在单元格 A1 中输入 1。

将公式 =A1+1 放入单元格 A2。

将单元格 A2 复制到从 A2 到 A2400

的所有单元格中

在单元格 B25 中输入公式 =LooBac(A1:A25)

将单元格 B25 复制到从 B25 到 B2400

的所有单元格中

点击 F9(重新计算)按钮。

我的机器将达到 61% 并挂起，或者将达到 85% 然后 Excel 崩溃。以下是我机器上的规范:

Excel 2016(32 位)

戴尔灵越 3847

Windows 10 专业版(64 位)

英特尔 i7 3.6 GHz

16 GB 内存

我读过一些用户认为 Excel 2016 存在内存泄漏的其他线程。其他线程认为可能发生了其他事情。有谁知道此类问题的解决方案？

编辑(12/29/2015)=========================================== ====================

我收到了几封来自有类似问题的人的电子邮件。他们问我是如何解决这个问题的。所以，就在这里。

在上面的代码(原始方案)中，我替换了行:

Dim oRawRanObj                  'The Raw Range
Dim iRawRanObjRow As Integer    'Number of Rows
Dim iRawRanObjCol As Integer    'Number of Columns

Set oRawRanObj = RawRan
iRawRanObjRow = Int(oRawRanObj.Rows.Count)
iRawRanObjCol = Int(oRawRanObj.Columns.Count)

Tem = oRawRanObj.Cells(j).Value

使用以下几行:

Dim RawRanVal As Variant        'This was added to replace the Raw Range Object
Dim iRawRanValRow As Integer    'This was added to store the number of rows that are in RawRanVal

RawRanVal = RawRan.Cells.Value2
iRawRanValRow = UBound(RawRanVal, 1)

Tem = RawRanVal(j, 1)

新方案与原方案的主要区别在于新的 RawRanVal比原来的 oRawRanObj 需要更少的内存.当 UDF 被复制到许多单元(示例中为 2375 个单元)中时，新方案浪费的内存量要少得多。因为这在以前版本的 Excel 中没有问题，所以我猜测 Excel 2016 的垃圾收集方案与以前版本的 Excel 不同。

此外，使用原始方案，Excel 的图形经常会搞砸。 Excel 的某些单元格将显示为灰色，并且不显示任何数字/字符。我仍然可以点击向下箭头并进入这些单元格(我可以知道我在哪里，因为公式栏会显示每个单元格的内容)，但无论我尝试了什么，单元格都不会正常显示。因此，我的猜测是 Excel 2016 存在内部内存泄漏。

最后一个问题是，尽管新方案允许我继续使用我的 UDF，但问题并没有真正解决。使用新方案，如果我只重新计算 (F9) 十五次，Excel 2016 将始终给我一条错误消息，提示我内存不足。

底线是，在我运行原始方案的大约一半时间里，Excel 向 Microsoft 发送了一条消息。我假设他们会把这个放在他们的错误修复列表中。

Answer 1

你用的太多了variant变量声明。

如果单元格是 #ERR 、空的或非数字的，会发生什么？

Option Explicit

Function LooBac(byval RawRan AS RANGE) AS DOUBLE 'declare return value type too

Dim oRawRanObj AS RANGE                 'The Raw Range
Dim iRawRanObjRow As LONG       'Number of Rows
Dim iRawRanObjCol As LONG       'Number of Columns
Dim Tem As Double               'Temp value

Dim i As LONG                'Counter
Dim j As LONG                'Counter

Application.Volatile

If RawRan is nothing  Then 'i deleted this part if not change the condition


    'LooBac = "*** An invalid RawRan range has been provided. ***" 'really? at the end of the function it is a double and here a string !
Exit Function

End If

'   Build an object using the structure of the Raw Range.

Set oRawRanObj = RawRan
iRawRanObjRow = oRawRanObj.Rows.Count
iRawRanObjCol = oRawRanObj.Columns.Count

'   This section seems to be the problem.
'   It appears to be related to the term oRawRanObj.Cells(j).Value
dim h$
For i = 1 To iRawRanObjCol '@=200 (makes no sense else)
    For j = 1 To iRawRanObjRow
       h= oRawRanObj.Cells(j,I).Value '(just j makes no sense)
       IF ISNUMERIC(H) THEN 
          Tem = CDBL(H) 'change value to double
          'obviously some big part of the code is missing here, just looping and doing nothing with 'tem' is useless
           IF some condition is met THEN
              LooBac = Tem 
              EXIT FUNCTION
           END IF
       END IF
    Next j
Next i

'LooBac = Tem

End Function