Excel VBA: Overflow error from too many undestroye

Question

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?

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

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.

Answer 1

In this case, an overflow results when you attempt an assignment that exceeds the limits of the assignment's target. Could your data set have something in it that exceeds the limit of a date or possibly a double? Those are the 2 types that I see getting passed in. Maybe there is a mismatch somewhere and a big double is getting passed in as a date. You can check for this kind of stuff with a subroutine that, for instance, checks the bounds of a date before trying to write it to the collection.

The other possibility is the size of the collection itself, since it is indexed by a long. That is a pretty big number though, you would have to exceed 2,147,483,647 records.

Answer 2

I don't think that this is necessarily the cause of your error but it is worth fixing anyway.

Compare and contrast these two parts of your code:

From AddDataPoint

data_points.Add new_data_point
Set 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

From Class_Terminate

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

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