Two Excel addins both handling the 'SheetChange' event - conflict

Situation

I am creating an Excel VBA addin that intercepts the worksheet 'SheetChange' events from a number of open data workbooks via a custom class declared WithEvents.

My custom addin is used in conjunction with a third party addin (which retrieves & transfers the value to a database via a  custom formula) which also intercepts the same event and is therefore indispensable

Issue

Previously my addin has been working without an issue but now whilst the code in my addin works the third party addin (TPA) only works 50% of the time.

My event has very little code and has had no real change - in fact I have recently reduced the processing in an effort to avoid code interference.

Question

It is my suspicion that the TPA is getting interrupted or not receiving the event that is raised when a worksheet change is made.

How can I force the order of event handling so that the third party addin handles first or is there someway to re-fire the event to ensure the TPA handles it ?

My understanding that in such a situation the order the events are called is random and cannot be predicted - can anyone confirm ?

Points to consider

The third party addin intercepts the event 100% of the time when it is the only addin. It also works 100% of the time if I comment out my entire event listener sub.

I have tried putting a DoEvents at the top of my code but this doesn't always appear to work.

Being a third party addin it is password protected and I can't look at the code.

I can not post either addin for review - so this is a theoretical exercise and I am looking for other exports approaches / views.

Thank you
LVL 3
AL_XResearchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

[ fanpages ]IT Services ConsultantCommented:
Are you able to explicitly call the third party's Add-in routine from your own add-in's Worksheet_Change() event code?

For example,

Application.Run "'C:\TPA-Folder\TPA-Addin.xla'!Worksheet_Change", Target

or

Application.Run "'C:\TPA-Folder\TPA-Addin.xla'!Workbook_SheetChange", ActiveSheet, Target

Open in new window


Replace ActiveSheet with an object referring to the worksheet being changed, where applicable.
AL_XResearchAuthor Commented:
Unfortunately I don't know what the event handling function in the TPA is called  otherwise- that would be my first thought
[ fanpages ]IT Services ConsultantCommented:
Have you contacted the supplier/vendor to ask for their advice, or to clarify the name of their event-handling function?

They may even work with you to resolve the issue.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AL_XResearchAuthor Commented:
I am looking into contacting the supplier on this instance but a) in the mean time I am looking for another opinion / additional information / alternate approach and b) I want to know generally about 2 addins that intercept the same event and precedence.

Is there a way to direct the even to a specific addin or re-raise the event for a specific workbook ? Obviously the 'RaiseEvent' command will not work in this instance but  does anyone know of an alternative idea / method ?
regmigrantCommented:
I accept that this is a bit of a reach but I can't find any way of 'handling' the sequence of events that doesn't require you being able to modify the TPA.

Is it possible to use an alternate event for your code such as worksheet.calculate and have your code 'detect' if the TPA is done?
AL_XResearchAuthor Commented:
I am not even suggesting modifying the TPA - that is not an option.

I have looking into this and I can't see what other event I can use.

For example: I need to update row & col markers when a cell changes. How else can I do this other than the 'SheetChange' event ? I could of course use 'OnTime' with an interval of 1 second but a) this would make the system unusable / really slow b) it may miss some changes. That is only an example.


Does anyone have any idea or experience about changing the order that addins as passed events ? What would be ideal if I could somehow say : process the application events via the TPA BEFORE passing the same events to my addin.
regmigrantCommented:
I assume you're aware of Chip Pearson's coverage of events at:
http://www.cpearson.com/excel/Events.aspx

He doesn't cover any 'precedence' or handling of multiple add-ins processing the same event so the only other suggestion I have is a change in the hierarchy-
Eg: use Workbook_SheetChange instead of Sheet_Change and ignore any sheets that aren't relevant to your add-in
AL_XResearchAuthor Commented:
Yes I am aware of Chip's very comprehensive article

I am not sure what you mean by "change the hierarchy" as my event handling is in a custom class and is already handling the 'SheetChange' event - the same as the TPA.

I did briefly consider using 'OnTime' to schedule some code but this introduced additional complications of storing the active cell in a global setting and then the user having moved selection by the time the code fires.

Looking at my issue in more details it appears that in each Excel session the TPA will either work or not. If the TPA event-watching-object  is active then for the remainder of the session the TPA will work but if not then the TPA formulas will not work for the remainder of the session.

Something my code is doing (that was not intentional and indeed is not obvious) is killing the TPA's even-watching-object (or causing it to die). I am not aware of any way you can do this (or trigger state-loss) in another workbook - either intentionally or accidentally. If anyone out there does then by all means post a reply.
AL_XResearchAuthor Commented:
I am thinking that in some way I am creating an error in the TPA - which is causing state-loss in that addin. How I have no idea. I have even less idea how I could tell when this state-loss occurs
regmigrantCommented:
Here's another article that explains some gotchas when managing events; for example: if you turn events off at the wrong level they can still have been triggered but processing is deferred and then they are restarted when events are turned back on - this sounds like the sort of asynchronous activity that could be hitting your add-ins because the state isn't valid when the processing restarts
http://pixcels.nl/events-in-workbooks/

By hierarchy I meant is your class handling Worksheet_Change and/or Workbook_SheetChange and/or App_SheetChange which are triggered one after another when a cell is changed. I'm hoping that the distinction offers a discrete hook to separate your code from the TPA
AL_XResearchAuthor Commented:
Thanks for the link, that will be a useful reference for the future.

I am still confused though: since my class contains an internal var of type 'Excel.Application' the only events it be monitor are application-events. To monitor book-level or sheet-level events you would need to have a class object instantiated for each book or sheet - which would need to be created by the application-level event watcher - wouldn't it ? So we would have the same issue.
regmigrantCommented:
I'm assuming you don't know where the TPA is hooking in?

Whichever level they are operating at then, in theory!, you could attach either immediately before or immediately after, your code is only triggered once their code is complete (you are senior in the hierarchy which is preferable). if they are triggering at the app level then you need to make sure your code is complete before they are triggered, so you attach at one of the subordinate levels.

I'm basing this on the section in Chip's document headed Multiple Events Being Called and thinking that you can a) wrap your code around their code with an APP_EVENT handler as shown in this example (which assumes TPA is subordinate and operating sheet or workbook level) or b) They are handling the APP_EVENT so your code needs to trap the Workbook/Sheet level ONLY, execute and handle the triggering of all subsequent events as laid out in the pixel.nl document - which will then be 'rolled up' for their APP_EVENT to start properly.

Without knowing your architecture its difficult to say but from what you've said I would hazard a guess that both you and TPA are trying to process the APP level event and as you can't change TPA the alternative is to move yours down stream, turning off application.events until you are done (so TPA don't intercept any changes in the meantime) then when it goes back on the state should be equivalent.
AL_XResearchAuthor Commented:
All that you say is true. As I have previously said, both my addin and the TPA are using the app-level event.

I should perhaps re-phrase my previous question: how can I attach to workbook-level events without creating an additional custom class object and instantiating it each time a target workbook is opened - which will have to be done from app-level events ?
regmigrantCommented:
There's no indication its possible to force a precedence for them processing the event and given the behavior you're seeing I would suggest that the two are probably running in parallel so unless there's a way of your code detecting the TPA exit you are stuck.

My suggestion would require a class object at the workbook level which handles the WorkBook_change event for each workbook BUT the APP level code that instantiates could execute from APP_WORKBOOK_OPEN or one of the other Senior events. Once it's in place you would be able to confirm the completion of your own changes before the APP level change is triggered

However - as said above - I don't know your architecture or what other problems this might introduce, its just a hypothesis
AL_XResearchAuthor Commented:
I beleive I may have found it. It is nothing to do with my event code (I had doubted it was since it has worked perfectly for the many months) but to do with the load of the TPA.

Basically I seems the TPA is not fully loading BEFORE my addin loads. If I open the TPA and then my addin the problem goes away - the formulas are not overwritten. if I load my Client on it's own (which loads the TPA as a reference) the problem comes back.

This could be due to poor network performance.

The question now is : how can I make sure the TPA loads fully BEFORE i start my addin ?
regmigrantCommented:
Would something like this be enough with appropriate fix for the names and an until loop?

On Error Resume Next    ' if its not there it will generate an error
Set TPA_Addin = Workbooks(Addins("TPA Addin").Name)
gotError = Err
On Error Goto 0        
If gotError <> 0 Then
    ' try force a load
    Set TPA_Addin = Workbooks.Open(Addins("TPA Addin").FullName)
End If


Or a variation on this link that I just turned up
http://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically

Open in new window

AL_XResearchAuthor Commented:
Well no that is no help I am afraid. I can easily open the addin manually but I need to check it had finished all its startup code before I continue

I tried to de-reference the TPA from my addin and as a first task open the TPA addin manually but unfortunately the code continues before the TPA is fully started,

I have tried to de-reference the TPA from my addin and instead start the two addins in sequence after a small delay, in the one Excel session, via an external VBScript but unfortunately this does not give sufficient time for the TPA to startup fully and hence the formula error is still present.
AL_XResearchAuthor Commented:
regmigrant : Thanks for your help but I have found the underlying cause of the apparent 'event interference' - it was a corrupt Windows user account / profile !

Needless to say that this took a great deal or testing and deduction to work out and I still have no idea why a corrupt account would affect the TPA in that way.

I am still interested if you know (or can provide a link) how VBA decides which addin gets priority when they both handle the same event at the same level (i.e. and application-level 'SheetChange').

As I said before my belief is that I read somewhere that the order is random and cannot be predicted,

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
regmigrantCommented:
Wow, would never have thought to go there, don't you just love intermittent problems? Glad to hear you found it, however bizarre the result.

This answer from the .net forum on here supports the assertion that there is no way to control which handler gets an event first, I'm trying to find something to back it up - and apply explicitly to VBA.

http://www.experts-exchange.com/Programming/Languages/.NET/Q_22798528.html
[ fanpages ]IT Services ConsultantCommented:
Further to comment ID: 40929471, I am still waiting on your findings following your contact with the supplier.
AL_XResearchAuthor Commented:
Although I eventually found the issue myself 'regmigrant' provided a helpful suggestion to move my handler to workbook-level which may be a very useful strategy in future development.
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 Excel

From novice to tech pro — start learning today.