VB.NET跨工作簿单元格复制内存占用过高问题求助

Hey there, let's tackle that frustrating memory spike you're seeing after moving your VBA cross-workbook data copy code to VB.NET. I’ve helped folks work through similar COM object memory leaks before, so here are actionable fixes to bring that 1.5GB usage down:

1. Explicitly Release COM Objects (Critical!) #

VBA handles Excel COM object cleanup automatically, but VB.NET doesn’t—this is the most common culprit for memory bloat. Every Excel object you create (Range, Worksheet, Workbook) needs to be manually released:

• Use Marshal.ReleaseComObject() for each object, starting from the most specific (like your rij Range) up to the broadest (Workbook).
• Follow up with garbage collection to ensure objects are fully cleared.

Example code snippet:

Imports System.Runtime.InteropServices

' After you're done using the Range
Marshal.ReleaseComObject(rij)
rij = Nothing

' Release other Excel objects in order: Worksheet first, then Workbook
Marshal.ReleaseComObject(sourceWorksheet)
Marshal.ReleaseComObject(sourceWorkbook)
sourceWorksheet = Nothing
sourceWorkbook = Nothing

' Force garbage collection to clean up lingering references
GC.Collect()
GC.WaitForPendingFinalizers()

2. Avoid Loading Unnecessary Data #

If your rij Range includes empty cells or unused areas, VB.NET will still load all that into memory. Try:

• Target only used cells with worksheet.UsedRange instead of entire rows/columns.
• Calculate the actual data range dynamically to avoid empty space:

  Dim lastRow As Integer = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(Excel.XlDirection.xlUp).Row
  Dim lastCol As Integer = sourceWorksheet.Cells(1, sourceWorksheet.Columns.Count).End(Excel.XlDirection.xlToLeft).Column
  Dim rij As Excel.Range = sourceWorksheet.Range(sourceWorksheet.Cells(1,1), sourceWorksheet.Cells(lastRow, lastCol))
  

3. Use Value2 Instead of Value #

Range.Value returns a Variant type which is memory-heavy. Swap it out for Range.Value2, which returns raw data types (e.g., Doubles instead of Variants) and cuts down on memory usage significantly when dealing with thousands of cells.

4. Batch Process Large Datasets #

Copying thousands of cells in one go can overwhelm memory. Split the job into smaller batches (e.g., 1000 rows at a time):

• Process each batch, release the Range object for that batch, then move to the next. This prevents memory from piling up as you work through the dataset.

5. Minimize COM Object Interactions #

Every time you access an Excel object from VB.NET, it adds overhead. Instead of manipulating the Range directly, read the data into a .NET array first, then write it to the target workbook all at once:

' Read data into a .NET array (far more memory-efficient)
Dim dataArray As Object(,) = rij.Value2

' Process the data if needed here...

' Write the array to the target Range in one go
targetRange.Value2 = dataArray

These steps should help you slash that memory usage back to reasonable levels. Let me know if you need help refining any part of your code!

内容的提问来源于stack exchange，提问作者Alex de Jong