Disabling End statement during onChange event

AntLam

New Member
Joined
Mar 25, 2009
Messages
5
Hi,

I am writing an XLA in XL2002 which will act on a number of legacy and new spreadsheets. During the execution of the XLA I set cell values.

i.e. m_rRange.Cells(j, i).Value = m_sInVal(j, matchIndex)

This then triggers the onChange event of the worksheet. Unfortunately, for one of the spreadsheets that this needs to work with, this causes an error. The error is trapped within the spreadsheet itself but the sub End is called which halts all VBA code execution.

My problem is that the XLA has not finished working and gets terminated early. I cannot capture this using an onError statement as it is never triggered (the spreadsheet code stops the execution and control never returns to the XLA).

I can disable the events using Application.EnableEvents but this means that none of the onChange events are called which is also not desirable.

So my question is: is there any way of disabling the termination of code using the End() statement, or of detecting when it happens to stop it?

Thanks for the help,

Ant
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Not that I know of. Can you not simply slap whichever muppet put an End statement in their code, and get them to fix it?!
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!
This may not solve your problem, but it might help some
Code:
Private Sub Workbook_Open()
Sheet1.Protect "password", UserInterFaceOnly:=True
End Sub

lenze
 

AntLam

New Member
Joined
Mar 25, 2009
Messages
5
Rory,

Unfortunately this code was written in 2002 so it's unlikely that I'll be able to track down the offender. There's also the possibility that someone may do this again in the future, you never know.

Lenze,

I'm not sure what effect you're recommendation would have. Could you explain how this would assist.

Thanks,

Ant
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Lenze,

I'm not sure what effect you're recommendation would have. Could you explain how this would assist.

Like I said, I don't know if that's your problem. Not enough info. But, this code will protect a sheet from manual entry, but still allow changes via VBA, which in turn will trigger a Change Event. Note, it has to be reset each time the WB is opened!

lenze
 

AntLam

New Member
Joined
Mar 25, 2009
Messages
5
Ah, I see. The onChange event is triggered by my VBA code so other than disabling all events I can't see a way around the fact that the event will be called.

Thanks,

Ant
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I think your best option is to disable events. That way you stay in control and you're not relying on the competence of other users. What issues will you have to address if the worksheet change event handlers don't run? What does your add-in actually do?
 

AntLam

New Member
Joined
Mar 25, 2009
Messages
5
The add-in allows the storage and restoring of a subset of information within a sheet. The sheets themselves run additional DLLs and act as a front end to other systems. This particular issue was caused by the restoring of a name.

This name should refer to an object in memory, however the sheet had not been calculated and the named object did not exist. The onChange item tried to interrogate the item in order to fill a cell validation list but failed.

There are a number of scenarios in which a name/ value is restored and the user interface would need to update to reflect the new value. Either through updating a validation list or getting the relevant values into another area of the sheet for that item. This is a valid use case but unfortunately the design of the legacy sheet is causing me issues.

Sorry to be a bit vague about the implementation but explaining the DLLs and memory objects is not possible (and would probably not help :) )

Ideally I'd like to be able to at least trap the error and warn the user that the sheet is badly designed since the current warning of "Application defined or Object defined error" is not that clear.

Thanks,

Ant
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Can you convert your add-in to a dll? If not, I think you are out of luck. (much as you would be if the user has their VBE set to break on all errors and you use On Error Resume Next anywhere!)
 

AntLam

New Member
Joined
Mar 25, 2009
Messages
5
In theory there would be no issue with converting the add-in to a DLL. However I have no experience of doing this so would have to learn.

I'm guessing this would then interface to the excel application through a COM object and the End statement would no longer propogate allowing the DLL code to continue executing.

Could well be a way around this. Now I just have to work out how to convert the XLA to a DLL.

Thanks,

Ant
 

Watch MrExcel Video

Forum statistics

Threads
1,122,547
Messages
5,596,782
Members
414,101
Latest member
ExcelBasicBro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top