Solved

How to Restart VBA Class after Project is Reset

Posted on 2014-10-21
17
662 Views
Last Modified: 2014-11-07
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,
0
Comment
Question by:DrTribos
  • 10
  • 6
17 Comments
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
'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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 12

Expert Comment

by:thausla
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
any fresh ideas?
0
 
LVL 2

Expert Comment

by:Glen Richmond
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
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
 
LVL 2

Accepted Solution

by:
Glen Richmond earned 500 total points
Comment Utility
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
 
LVL 14

Author Comment

by:DrTribos
Comment Utility
Hi Glen - I'd be very interested to have a look at that, thanks :-)
0
 
LVL 14

Author Closing Comment

by:DrTribos
Comment Utility
I think it can't be done in Word... and external approach is required.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now