EXcel VBA objects destroyed

Question

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

All global variables get reset if an untrapped error occurs or in other circumstances like calling Stop in your code. If you want to decouple your global object setup from the workbook open event then move that to a standalone Sub and call it from the open event. Then you can also call it from other code if you need to "restart" your project. — Tim Williams
– Tim Williams, Commented Feb 17, 2014 at 1:59

Tim Williams · Accepted Answer · 2014-02-17 06:47:07Z

1

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

answered Feb 17, 2014 at 6:47

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXcel VBA objects destroyed

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related