Excel VSTO工作簿System.OutOfMemoryException错误排查求助

Let's break down your issue and tackle it step by step. The fact that two identical modules work fine (with more data!) while others throw an out-of-memory error tells us this isn't just a system resource problem—it's likely tied to code inefficiencies, incorrect object handling, or worksheet-specific overhead in the failing modules.

First, Fix the Critical Code Errors #

Looking at your provided code, there are two obvious bugs that could be causing memory issues and data corruption:

1. Reusing the Same Object Reference in Loops #

You're creating a single ReservaExcursion object outside your loops and modifying its properties each iteration, then adding it to your list. This means every entry in listaExcResumen and listaExcGuia points to the same object—not separate instances. Over time, this creates unnecessary memory overhead and corrupts your data, which can trigger unexpected memory exceptions.

Fix: Move the object instantiation inside the loops, and create new instances for each entry:

' Inside the first loop (processing shResumen)
For Each cell As Range In rgRango
    If cell.Offset(0, iGuia).Value <> Nothing Or cell.Offset(0, iGuia2).Value <> Nothing Then
        ' Create a NEW instance for each row
        Dim resExcResumen As New ReservaExcursion
        With resExcResumen
            .observaciones = cell.Value
            .cliente = cell.Offset(0, iCliente).Value
            Try
                .dtFechaExc = cell.Offset(0, iFechaExc).Value
                .strFechaExc = Nothing
            Catch ex As Exception
                .strFechaExc = cell.Offset(0, iFechaExc).Value
                .dtFechaExc = Nothing
            End Try
            .pax = cell.Offset(0, iPax).Value
            .cant = cell.Offset(0, iCant).Value
            .excursion = cell.Offset(0, iExc).Value
            .excursionEspecial = cell.Offset(0, iExcEsp).Value
            .extras = cell.Offset(0, iExtras).Value
            .observacionesProveedores = cell.Offset(0, iObsProv).Value
        End With

        ' Add to list for Guia 1
        If cell.Offset(0, iGuia).Value <> Nothing Then
            resExcResumen.guia = cell.Offset(0, iGuia).Value
            listaExcResumen.Add(resExcResumen)
        End If

        ' Create a NEW instance for Guia 2 to avoid reference duplication
        If cell.Offset(0, iGuia2).Value <> Nothing Then
            Dim resExcResumen2 As New ReservaExcursion
            ' Copy properties from the first instance
            resExcResumen2.observaciones = resExcResumen.observaciones
            resExcResumen2.cliente = resExcResumen.cliente
            resExcResumen2.dtFechaExc = resExcResumen.dtFechaExc
            resExcResumen2.strFechaExc = resExcResumen.strFechaExc
            resExcResumen2.pax = resExcResumen.pax
            resExcResumen2.cant = resExcResumen.cant
            resExcResumen2.excursion = resExcResumen.excursion
            resExcResumen2.excursionEspecial = resExcResumen.excursionEspecial
            resExcResumen2.extras = resExcResumen.extras
            resExcResumen2.observacionesProveedores = resExcResumen.observacionesProveedores
            ' Set the second guide
            resExcResumen2.guia = cell.Offset(0, iGuia2).Value
            listaExcResumen.Add(resExcResumen2)
        End If
    End If
Next

' And for the shGuia loop:
If Not IsNothing(rgRango) Then
    For Each cell As Range In rgRango
        ' Create a NEW instance for each row
        Dim resExcGuia As New ReservaExcursion
        With resExcGuia
            .observaciones = cell.Value
            .guia = cell.Offset(0, iGuia).Value
            Try
                .dtFechaExc = cell.Offset(0, iFechaExc).Value
                .strFechaExc = Nothing
            Catch ex As Exception
                .strFechaExc = cell.Offset(0, iFechaExc).Value
                .dtFechaExc = Nothing
            End Try
            .cliente = cell.Offset(0, iCliente).Value
            .pax = cell.Offset(0, iPax).Value
            .cant = cell.Offset(0, iCant).Value
            .excursion = cell.Offset(0, iExc).Value
            .excursionEspecial = cell.Offset(0, iExcEsp).Value
            .extras = cell.Offset(0, 7).Value
            .observacionesProveedores = cell.Offset(0, iObsProv).Value
            .tarSinIva = cell.Offset(0, iTarSin).Value
            .tarConIva = cell.Offset(0, iTarCon).Value
            .debe = cell.Offset(0, iDebe).Value
            .haber = cell.Offset(0, iHaber).Value
            .factura = cell.Offset(0, iFactura).Value
            .fechaFactura = cell.Offset(0, iFechaFactura).Value
            .fechaPago = cell.Offset(0, iFechaPago).Value
            .estadoPago = cell.Offset(0, iEstadoPago).Value
            .observacionesPato = cell.Offset(0, iObsPato).Value
        End With
        listaExcGuia.Add(resExcGuia)
    Next
End If

2. Duplicate Column Lookup for Tariff Values #

You're assigning both iTarSin and iTarCon to the same column lookup:

iTarSin = rgRangoHeader.Find("TARIFA SIN IVA", LookAt:=XlLookAt.xlWhole).Column - 2
iTarCon = rgRangoHeader.Find("TARIFA SIN IVA", LookAt:=XlLookAt.xlWhole).Column - 2

This is a typo—iTarCon should look for "TARIFA CON IVA" instead. This mistake causes duplicate data in your array and mismatched column indexing, which can lead to Excel using extra memory to handle invalid data writes.

Fix: Correct the lookup:

iTarCon = rgRangoHeader.Find("TARIFA CON IVA", LookAt:=XlLookAt.xlWhole).Column - 2

Optimize Memory Usage & COM Object Handling #

VSTO relies on COM interop with Excel, and failing to clean up COM objects can lead to memory leaks over time. Here's how to fix that:

1. Release COM Objects Manually #

After using Excel objects like Range or ListObject, release them to free up memory:

' At the end of your sub, or after using each object:
System.Runtime.InteropServices.Marshal.ReleaseComObject(rgRango)
System.Runtime.InteropServices.Marshal.ReleaseComObject(rgRangoHeader)
rgRango = Nothing
rgRangoHeader = Nothing

' Replace your range write code with this safer version, then release the range:
Dim targetRange As Range = Globals.shGuia.Range("B2").Resize(listaExcResumen.Count, 19) ' B to T is 19 columns
targetRange.Value = arrGuia
System.Runtime.InteropServices.Marshal.ReleaseComObject(targetRange)
targetRange = Nothing

2. Disable Excel Features During Data Writes #

Turn off screen updating, automatic calculation, and events while your code runs to reduce Excel's memory footprint:

Sub CargarDatosGuia()
    ' Disable Excel features at the start
    Application.ScreenUpdating = False
    Application.Calculation = XlCalculation.xlCalculationManual
    Application.EnableEvents = False

    Try
        ' ... your entire code here ...
    Catch ex As Exception
        ' Handle errors if needed
        MsgBox("Error: " & ex.Message)
    Finally
        ' Restore Excel settings no matter what
        Application.ScreenUpdating = True
        Application.Calculation = XlCalculation.xlCalculationAutomatic
        Application.EnableEvents = True

        ' Release all COM objects here
        ' ... (add release code for all Range/ListObject instances)
    End Try
End Sub

Check Worksheet-Specific Overhead #

The failing modules' worksheets might have hidden overhead that's consuming memory:

• Conditional Formatting/Data Validation: If the shGuia sheets for the failing modules have excessive conditional rules or data validation, writing data forces Excel to recalculate all these rules, using extra memory. Try clearing conditional formatting temporarily to test.
• External Links/Formulas: Links to other workbooks or complex formulas can cause Excel to use more memory when updating data. Check if the failing sheets have more external dependencies.
• Hidden Objects: Look for hidden shapes, charts, or pivot tables that might be running in the background and consuming resources.

Final Testing Steps #

1. Apply the code fixes above first—these are the most likely causes of your issue.
2. Run the failing modules again to see if the error persists.
3. If it still happens, create a clean copy of the shGuia sheet (only keep the table structure, remove all formatting/formulas) and test with that.

内容的提问来源于stack exchange，提问作者Ricman1029