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.