Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Memory problem: Need to set object variables for userforms? For ranges?

17 views
Skip to first unread message

WHA

unread,
Nov 29, 2007, 10:47:20 PM11/29/07
to
Background: I am building a VBA-based tool in Excel 2003 on Windows
XP. To make a long story short, I'm having some kind of memory leak
problem. This is the first of four posts trying to sort out that
problem.

This post is about using object variables in certain situations. I
found individual sites on handling object variables but nothing
definitive; if there is an especially good resource, please point me
to it! Thanks in advance, WHA

QUESTION 1
Based on a thread in an MS Word VBA forum (http://www.tutorials-
win.com/WordVBA/memory/ ), I am wondering whether I ought to set
object variables for Userforms. Specifically, instead of writing
MyUserForm.Show
it would be better to do:
Dim u as MyUserForm
...
Set u = new MyUserForm
u.Show
...
Set u = Nothing

What do you folks think of this idea? Does use of "MyUserForm.Show"
lead to a memory leak, or does it not?

QUESTION 2 (kind of similar to Question 1)
My workbook has several named ranges. In order to save myself some VBA
keystrokes, I wrote a little function to refer to those named ranges:

Function myRange(s As String) As Range
Set myRange = ThisWorkbook.Names(s).RefersToRange
End Function

In lots of places in other modules, I have been writing lines like:
myRange("foo").value = "bar"
I am wondering whether this little helper function might be
contributing to my memory leak problems. For that matter, I am
wondering whether usage of a statement like
ThisWorkbook.Names("foo").RefersToRange.value = "bar"
leads to a memory leak by itself, with no helper function necessary.
Could that line cause the system to create a Range object, which then
fails to be released? Would I see an improvement from using the
following code instead, or would it make no difference?

Sub mySub()
Dim r as Range
...
Set r = myRange("foo")
r.value = "bar"
...
Set r = Nothing
End Sub

In any case, I would like to avoid writing the extra code if I can.

0 new messages