VB.Net - Excel Interop - Passing Excel.Range ByVal versus ByRef: Impact on memory usage for very large ranges
Posted on 2013-11-14
I'm confused about how much memory an Excel Range object actually takes up, especially if the range points to a large block of cells in a worksheet.
I'm working with some VB.Net code that automates Excel using Interop and have noticed that Excel ranges are always passed ByVal and I was wondering if I could conserve a significant amount of RAM at run-time if I were to pass them ByRef.
We're dealing with some pretty big blocks of data these days (200,000+ rows of 10 to 15 columns) and running into situations where users are reporting out of memory errors (although we're having trouble re-creating them)
So here's my question:
If an Excel range object points to a large block of cells (say 150,000 rows by 20 columns), would I save a lot of memory at run-time by passing ranges around in my code by reference instead of by value?
I understand the implications of ByRef in that the called routine can manipulate the original object directly. From what I've seen of the code, none of these routines need to change the object anyway (probably why they were using ByVal in the first place).
Any tips would be appreciated.