How to Restart VBA Class after Project is Reset

Hi All,

I am delving into class modules in VBA.  I have code to initialize my class when documents open etc... when I debug or feel the urge to reset the project the class shuts down.  

So, in a nutshell, I would like to know how to restart the class after the project has been reset.  Conceptually, this is what I have:

Public pub_myObject As New clsDrTribos
' using 'As New' causes auto instantiate 

Sub ResetProject()
    End ' End resets the project (same as pressing the 'stop' button)
End Sub 'ResetProject

Open in new window


But, the class does not actually start working until I have some other code running to invoke it...
Sub SomeRandomSub()
...
' Since I am using an auto instantiate an object based on my class is kind of waiting, dormant, 
' ready to start working.  But it doesn't work.
' However, simply looking at pub_myObject causes it to become something & it starts working:
If pub_myObject is Nothing then 
end if
...
End Sub 'SomeRandomSub

Open in new window


My problem is that I absolutely need the class to be something as soon as my project is reset - is this possible?

I tried searching for code that runs after resetting a VBA project but, if the answer is out there I couldn't find it.

TIA,
LVL 15
DrTribosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glen RichmondCustomer Reporting Programmer.Commented:
look into using and calling Class_Initialize, Class_Terminate.
The terminate will be called if you just set your object var to nothing.
(I cant recall if you make the Initialize public if you can call when ever you want, but i think you can or make a public method in the class that calls the Initialize in scope.)
The Initialize occures when ever you set your object var to this class type.

'this inside your class...
Private Sub Class_Initialize()
    ' Set default states
End Sub

Private Sub Class_Terminate()
    'Kill/Reset what you want to
End Sub
0
DrTribosAuthor Commented:
Hi Glen -

Thanks for your comment.  It may have been remiss of me not to mention that I already have a Class_Initialize Sub.  

The problem is that when the project is reset the Class_Initialize Sub does not automatically get called...

As far as I can tell I would need to call it from another Sub, but I would like the class / object to restart automatically.  

Cheers,
0
Glen RichmondCustomer Reporting Programmer.Commented:
So when you call your ResetProject call a method of the class that calls the Initialize.


Sub ResetProject()
    MyObjClsRef.ResetClass()
    End ' End resets the project (same as pressing the 'stop' button)
End Sub 'ResetProject

Or am i missing somthing? I know this is still forcing the reset rather than the object causing its own terminate and Initialize by going out of scope.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DrTribosAuthor Commented:
When End is called (or the stop button is pressed) all code stops running, call stack is emptied, all variables are cleared and so on.  

So
Sub SomeSub() 
....
' The object will be destoryed and recreated
  Set pub_myObject = Nothing
  Set pub_myObject = New clsDrTribos
....
Call ThisIsTheEnd
' The following lines never get called
  Set pub_myObject = Nothing
  Set pub_myObject = New clsDrTribos
End 'SomeSub

Open in new window


Sub ThisIsTheEnd()
' Some Code...
...
End ' this is the last line that will be executed.
       ' this also destroys the object... 
MsgBox "I will never have my say"

End Sub 'ThisIsTheEnd

Open in new window

0
DrTribosAuthor Commented:
The point is, it is possible for a user to reset my project via the VBA IDE, for the most part this is not a problem BUT I need to have my Class Object running at all times so I want to restart it as quickly as possible.
0
Glen RichmondCustomer Reporting Programmer.Commented:
'Just an Idea?...

Call ThisIsTheEnd
' The following lines never get called
  Set pub_myObject = Nothing
  Set pub_myObject = New clsDrTribos
 
  'now force reset the new set class
  pub_myObject.ReIniClass() 'Public method to re ini the class or call the Class_Initialize() private method.

End 'SomeSub

oh and dont call 'End' in ThisIsTheEnd, you dont need to 'End' once all vars have been reset and any processing code stopped..
And of course calling end stops your set nothign, and set new class from being called
0
DrTribosAuthor Commented:
Hi Glen,  

Sorry mate I don't understand what you are suggesting...   but, with respect, I think you are missing the point I'm trying to make.  

Using the End statement is just a conceptual way to demonstrate what a user could do in the IDE - i.e. press the stop / reset button.

In other words, if a user presses the stop / reset button - is there a way for me to change my object from Nothing to Something?

The statement: Public pub_myObject As New clsDrTribos  automatically instantiates my object / class but in reality the object actually remains "Nothing".  It remains Nothing until I try to access it programatically.  Problem is, I don't want it to be Nothing - I want it to be automatically something BEFORE I try to access it programatically.

Cheers,
0
Glen RichmondCustomer Reporting Programmer.Commented:
I think i get you now..

So the user is using the VB Editer and not a 'user interface' you have built for them?
And you want a class to exist past the user hitting the Stop button in the Developers Interface?

Hmm not sure how you can make a class persist past that point as it dumps everything at that point..

Ill have a think, but it may be somthign like building a user control that is the class you want to persist!?
0
DrTribosAuthor Commented:
You got it.

Although I'm not actually expecting the user to use the VB IDE - but some users simply can't help themselves.  

Also, if I do manage to generate an unhandled error then I want to be able to recover from the user pressing End or Quit or whatever button is shown on the VBA Error Dialog.

Also I just want to be able to do this!

I'm stumpped on this.  

Cheers,
0
Dr. Thomas HenkelmannDirector Consulting ServicesCommented:
Hi,

I think you requested the REAL functionality in your last answer:

Also, if I do manage to generate an unhandled error then I want to be able to recover from the user pressing End or Quit or whatever button is shown on the VBA Error Dialog.

Urgent recommendation: build a central error handler, capture ALL errrors with "On Error Goto XYZ" and ALWAYS present a msgbox to your user listing the error number (Err.Number) and description (Err.Description). Then they won't be able to END you classes and ALL code that should run.

Regards

Thomas
0
DrTribosAuthor Commented:
Hi Thomas - yes I agree with what you are saying and I believe I have added error handling to the entire project.  

However, I still want to know that my pub_myObject is running in the background...  at this stage I guess it is more of an academic pursuit but I want to know.

I understand that it might not be possible using VBA - would it, for example, be possible using some sort of a helper DLL that monitors my project?

Right now the ONLY scenario where this could be a problem is if a User took deliberate steps and went into the IDE and pressed Stop / Reset, it's a pretty small problem - but none the less I want to understand what can be done to work around it :-)
0
DrTribosAuthor Commented:
any fresh ideas?
0
Glen RichmondCustomer Reporting Programmer.Commented:
Write the class states out to output/immediate window on a regular basis so when user or you terminate run you can view last know states/data in the immediate window.

Use debug.print to write the data out to immediate.
0
DrTribosAuthor Commented:
Hi Glen,

Not really what I'm after... I'm looking for something that will restart the class if it is stopped.  I won't be around to look at the immediate window and the user is generally unaware that such a window even exists...

I was wondering if a DLL or some other COM addin could provide a way forward.  I think there is no solution for this question :-(
0
Glen RichmondCustomer Reporting Programmer.Commented:
in order for that to happen you need something that is running in the background that will restart the class, and as the stop button terminates all running script you will have to think of something outside of excel to do that.
I will give it some more thought, but in the past i when i required services or exe to keep running in case of crash or user terminate, i made a standalone exe (ServiceWatch) that was a service listener, that would restart the services if not running.

ServiceWatch would be started every half hour by scheduled tasks just in case that too crashed..

I can let you have the source if you like?
ServiceWatch application is configurable in a sys file, and it can email out alerts too?

You need this same philosophy but for a internal class in a excel module.. tough ask i think?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrTribosAuthor Commented:
Hi Glen - I'd be very interested to have a look at that, thanks :-)
0
DrTribosAuthor Commented:
I think it can't be done in Word... and external approach is required.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.