CODEWITHSUNDEEP
excelvbaobject
user3317305
user3317305

Reputation: 21

EXcel VBA objects destroyed

Hi I'm a newbie to Excel VBA so this may be obvious, if so I apologize.

I am instantiating a number of global objects (classes) in Workbook_Open() and trying to write wrapper functions for these classes to be called as UDFs in the various worksheets. If any of these functions fail with an un-trapped error all of these global objects are set to nothing.

Why is this happening, as I would have thought maybe only the global object I was manipulating would be affected not all of them, and is there any other solution other than closing the workbook and reopening to re-establish them for further debugging?

The instantiation is simply

Private Sub Workbook_Open()
   Dim i as Integer
   For i = 0 to nStreams
       Set gStream(i) = New CStream
   Next i
End Sub

Upvotes: 2

Views: 118

Answers (1)

Tim Williams
Tim Williams

Reputation: 166341

You can split your "set globals" code out from the Workbook_Open event:

Private Sub Workbook_Open()
   SetGlobals
End Sub

'in a regular module
Public Sub SetGlobals()
   Dim i as Integer
   For i = 0 to nStreams
       Set gStream(i) = New CStream
   Next i
End sub

Upvotes: 1

Related Questions