Published on
14 December 2007 in
VBA.
I’ve talked about some of the Excel VBA I’ve done before, and here’s a new thing I learned today.
Problem
One part of my application selects a range from one worksheet in the spreadsheet and pastes it onto another. Originally, the code I used looked like this:
destinationWorksheet.Range("A2").Select
ActiveSheet.Paste
There is one problem I was unaware of. Every time the .Paste operation executed, it added a new “Named Range” to the ActiveSheet. I only realized this when snooping around in the Properties for the Excel file, where I found (to much dismay) 840 different “Named Ranges” referring to data long since thrown away! An older version of my application, which used this unspecified Paste operation much more frequently, had even more of these attached to numbers of other worksheets. Continue reading ‘Clean pastes with Excel VBA’
Published on
13 September 2007 in
VBA.
One project I constantly return to is an application I built in Excel VBA that does a lot of different things. One specific problem remains unsolved with this application, so I’ve decided to throw it out here and see if any gurus have an answer.
Basically, as a result of some Excel memory leaks beyond my control (I can’t install recommended hotfixes on the computers where this application is used, and need to be able to handle the issue with/without hotfixes anyway), when I shut down Excel it typically remains stuck in memory (and visible under “Processes” in “Task Manager”) even though it looks like it is gone to the user.
Paranoia
I realize that many would argue that since this bug is Microsoft’s problem and not mine, that I shouldn’t trouble with addressing it. However, since this application I’ve built is something that gets used frequently (but not constantly) throughout the day, I can imagine this becoming a problem (or at least wasting resources) at some point.
Another rejoinder to this charge is quite simple: I’ve actually ignored this issue for about a year now already, and am only revisiting it because I’ve learned some things about VBA that enable me to possibly defeat it. If I didn’t have any hope of fixing this, I wouldn’t try. Continue reading ‘Fixing memory leaks in Excel VBA… (we hope)’
Recent Comments