Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
5 Comments
When you pass in an object, the object is always passed in by reference. In the case of an object parameter, the difference between ByVAl and ByREf, determines how the reference is passed in.
So...
if you pass in an object reference as ByVal, then you are sending a copy of the reference, and your reference can be "repointed" to another instance of that type of object, and your original reference will remain unchanged.
If you pass in an object reference as ByRef, then when you change what object the reference points to, that change will also be reflected in the calling routine.
In Summary whether you use byRef or ByVal, you are not going to see much of a performance impact either way.
you can see the following article for more details on the topic of how ByRef vs ByVal are treated.
http://www.cpearson.com/excel/byrefbyval.aspx
So...
if you pass in an object reference as ByVal, then you are sending a copy of the reference, and your reference can be "repointed" to another instance of that type of object, and your original reference will remain unchanged.
If you pass in an object reference as ByRef, then when you change what object the reference points to, that change will also be reflected in the calling routine.
In Summary whether you use byRef or ByVal, you are not going to see much of a performance impact either way.
you can see the following article for more details on the topic of how ByRef vs ByVal are treated.
http://www.cpearson.com/excel/byrefbyval.aspx
this might help in what you are wanting to do in excel and vb.net.
I worked on a project a few years ago that was giving me problems with working in excel spreadsheets and vb.net.
Problem 1. Excel Interop does not work in 64 bit and in 64 bit servers.
Problem 2. Need a software that does not need microsoft office software.
So here is the answer that solved all my problems and more.
http://exceldatareader.codeplex.com/
This will read any excel sheet and place into a dataset for you to work with in vb.net.
I worked on a project a few years ago that was giving me problems with working in excel spreadsheets and vb.net.
Problem 1. Excel Interop does not work in 64 bit and in 64 bit servers.
Problem 2. Need a software that does not need microsoft office software.
So here is the answer that solved all my problems and more.
http://exceldatareader.codeplex.com/
This will read any excel sheet and place into a dataset for you to work with in vb.net.
Active 6 days ago
.NET doesn't know anything about referencing counting, so the System.Runtime.InteropServ
Marshal.ReleaseComObject Method
http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject(v=vs.110).aspx
Marshal.ReleaseComObject Method
http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject(v=vs.110).aspx
Active 6 days ago
Just tripped over interesting reading:
Marshal.ReleaseComObject Considered Dangerous
http://blogs.msdn.com/b/visualstudio/archive/2010/03/01/marshal-releasecomobject-considered-dangerous.aspx
Marshal.ReleaseComObject Considered Dangerous
http://blogs.msdn.com/b/visualstudio/archive/2010/03/01/marshal-releasecomobject-considered-dangerous.aspx
Thanks guys. I did not realize that all objects are passed by reference. That makes sense, as does the difference between passing them byref and byval.
Interesting reading on ReleaseCOMObject. I have run into those types of problems implementing the Excel application event handler for a COM Add-in. I had to implement my own handler as the one built in to VB.net was not releasing its COM objects properly.
Interesting reading on ReleaseCOMObject. I have run into those types of problems implementing the Excel application event handler for a COM Add-in. I had to implement my own handler as the one built in to VB.net was not releasing its COM objects properly.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 14 hours left to enroll
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.