tags:

views:

122

answers:

2

In some VBA attached to an Excel 2003 spreadsheet I need to make use of some objects that take a while to instantiate - so I only want to do the 'set' thing once...

It's easier the show the code than to write an explanation!

' Declare the expensive object as global to this sheet
Dim myObj As SomeBigExpensiveObject

Private Sub CommandButtonDoIt_Click()

   ' Make sure we've got a ref to the object
   If IsEmpty(myObj) Then  ' this doesn't work!
      Set myObj = New SomeBigExpensiveObject
   End If

   ' ... etc

End Sub

How can I check if myObj has already been set?

I've tried IsNull(myObj) and IsEmpty(myObj) - both skip the 'set', regardless of the state of myObj. I can't do

if myObj = Nil then

or

if myObj = Empty then

or

if myObj = Nothing then

Any ideas?

SAL

+4  A: 

This should work:

    If myObj IS Nothing Then

(note the "IS") If that does not work, then there must be asynchronous intialization implemented specifically by that Class because COM init calls are synchronous by default. So, you need to check the doc for, or talk to the developer about, the Big class for some property or synch method for you to wait on.

RBarryYoung
<slap about the head>RBarryYoung is a genius. Stupid me.... I've spent the last hour trying to figure this out. As soon as I read your response I felt like an idiot...</slap about the head>
SAL
<blush> Heh, don't fret about it. I remembered mostly because the same thing happens all the time in SQL with NULL, in fact "WHERE column=NULL" is probably the most common error.
RBarryYoung
A: 

This page has good information on the differences between empty, null and nothing in VB/VBA

http://beta.blogs.msdn.com/ericlippert/archive/2003/09/30/53120.aspx

Lunatik