Excel VBA：来自太多未破坏对象的溢出错误？ [英] Excel VBA: Overflow error from too many undestroyed objects?

问题描述

在大型数据集上执行优化任务时，我会不时收到溢出运行时错误6（通常在1小时或2小时后）。当我从停止的地方重新启动宏时，错误消失了，即从发生错误的地方再次启动宏。溢出错误是否与使用后创建过多的未正确销毁的对象有关？

when performing optimization tasks on a large dataset I receive an overflow runtime error 6 from time to time (generally after 1 hour or 2). The error goes away when I restart my macro from where it stopped, i.e. launch the macro again from the point where the error occured. Could an overflow error be related to some issue of having created too many objects that are not destroyed properly after use?

这是我的容器类的一个（简化版本），用于销毁（通过Set ... =否）和重建（通过Set ... = New）

Here's a (simplified version) of my container class, which is used destroyed (via Set ... = nothing) and reconstructed (via Set ... = New) thousands of times.

'CG_data_point custom collection class
    Public data_points As Collection
Private Sub Class_Initialize()
    Set data_points = New Collection
End Sub

Public Sub AddDataPoint(mydate as date, price as double)
    Dim new_data_point As CG_data_point
    Set new_data_point = New CG_data_point
    new_data_point.EnterData mydate, price
    data_points.Add new_data_point
    Set new_data_point = Nothing 'I assume this one could also be skipped
End Sub

Public Sub RMSE(X as double) as double
...
End Sub

Private Sub Class_Terminate()
    Dim data_point As CG_data_point
    For Each data_point In data_points  'destruct each data point individually
        Set data_point = Nothing
    Next data_point
    Set data_points = Nothing
End Sub

'Main module
dim global_container as CG_data_container

sub do_optimizations()
    Do
          set global_container= new CG_data_container
           .... do something with the data, have in call to global function RMSE_UDF as a cell formula for Solver
          set global_container= nothing
    While (...)
end sub

'worksheet function
function RMSE_UDF(X as double)
     global_container.RMSE(X)
end function

容器变量global_container必须是全局变量，因为它必须可从工作表UDF（RMSE_UDF）调用；据我所知，工作表公式不能将对象作为参数，例如 = RMSE（MyContainer，...）。均方根误差（RMSE）的最小化是使用Excel Solver进行的。

The container variable global_container has to be global because it must be callable from a worksheet UDF (RMSE_UDF); a worksheet formula cannot have an object as argument, as far as I know, like "=RMSE(MyContainer,...)". The minimization of Root Mean Squared Errors (RMSE) is carried out with Excel Solver.

推荐答案

我不认为这是

比较并对比代码的这两部分：

Compare and contrast these two parts of your code:

从 AddDataPoint

data_points.Add new_data_point
Set new_data_point = Nothing

这里我们要添加一个由临时变量 new_data_point 到集合中。然后，将 new_data_point 设置为 Nothing ，以删除对该对象的引用。显然，集合仍将引用该对象

Here we are adding an object referred to by the temporary variable new_data_point to the collection. We then set new_data_point to Nothing to remove the reference to the object that it used to refer to. Obviously the collection will still have a reference to this object

来自 Class_Terminate

For Each data_point In data_points  'destruct each data point individually
    Set data_point = Nothing
Next data_point

在这里，我们将从集合中依次读取每个项目，并将其读取到一个名为 data_point 的临时变量中。然后，将 data_point 设置为 Nothing ，以删除对该对象的引用。 （也许不是这样）显然，集合中仍然会引用该对象。

Here we are reading each item in turn from the collection into a temporary variable called data_point. We then set data_point to Nothing to remove the reference to the object that it used to refer to. (Maybe not quite so) obviously, the collection will still have a reference to this object.

要从集合中删除每个对象，请尝试重复删除集合中的第一个对象直到集合为空：

To remove every object from the collection try repeatedly removing the first object in the collection until the collection is empty:

Do Until (data_points.Count < 1)
    data_points.Remove 1
Loop

这篇关于Excel VBA：来自太多未破坏对象的溢出错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！