Unexpected "Code execution has been interrupted" 2

[jammastajc]

I am continually receiving the error "Code execution has been interrupted" when trying to execute a filter I wrote for Excel in VBA. Every time I debug, the code has stopped in a different location. VBA Help implies that this halt was caused by the developer manually entering into break mode, but I never hit ctrl+break! When the halt does come up, if I hit continue, it will continue filtering the information until the error happens again. Eventually, the code will complete and I will have the information I want. One Google search stated that this occurs because the code somehow got ahead of itself. I'll share any code fragments that might help figure this out, but due to the nature of the problem, I'm not sure which to post. Thanks for any help.

 

[DaleWatson123321]

jammastajc,

As one who has a keen interest in Excel's filtering capability, I'd be interested in viewing your code to see if I can help identify the cause of the error.

If you can also include the data (or some of it - and sensitive data replaced with ficitious data if necessary), that would help.

I can be reached at the following address.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JohnAcc]

I have encountered this problem b4 and i'm afraid it is a memory issue. You will need to increase the ram on your pc to solve this.

Out of interest - run the macro on a diff pc with more spec and i'm guessing that it will run fine.......

Let me know how you get on

Regards

John

 

[jammastajc]

The memory explanation would make some sence as the code needs time to catch up with itself and clear temporarily stored data. However, I don't see how there could be a memory constraint with 512 MB of ram. The code execution seems to be fast if the breaks didn't occur.

 

[JohnAcc]

possible solution:

Try putting DoEvents into your code:

i.e sub test
DoEvents
' do stuff
End Sub

Your operating system gives priority to the running macro. The DoEvents statement tells the o.s to stop the macro and complete any pending events. This can sometimes help especially when background tasks need to be completed b4 a macro can continue processing.

Let me know how you get on.

Cheers

John

 

[xlbo]

Seem to recall a discussion on this in the excel L group. I believe it is caused by saving a workbook with an active breakpoint in the VBE. When the workbook is re-opened, the breakpoint no longer shows but the code still breaks. The workaround suggested was to run the code. Where it breaks, put a breakpoint in and then get rid of it straight away (ie press F9 twice) then save the workbook. Repeat until the code has run through. Once this has been done, it should stop breaking

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[jammastajc]

I've actually never had any breakpoints in this macro and the DoEvents statement still allowed for the break. The problem is strange as somedays it will happen and somedays it won't. Dale provided extensive work on an alternate route which should avoid the problem.

 

[jammastajc]

I'd also like to add that the solution DaleWatson123321 offered makes use of the filter/database components of Excel. My previous solution used a looped call to the Find function, which is obviously much slower than using a powerful method like AdvancedFilter.

Thanks again Dale!

 

[pbbriggs]

DaleWatson,
Any chance you might share your "workaround" solution provided to jammastajc? I just started encountering this same problem (frequent "code execution has been interrupted" errors, which can be overruled by pressing "continue&quot when running a macro that I wrote several months ago and have never had problems with. Strangely, it also only occurs on some computers and not others. I don't think it's a RAM issue or a breakpoint issue, as suggested by others...

Thanks
Patricia

 

[DaleWatson123321]

Hi Patricia,

The solution I provided for jammastajc was based on a file he emailed me. While the file was made somewhat "generic", I don't want to breach confidentiality by sending the file.

I'm certainly prepared to share my "workaround", which involves use of Excel's relatively unused but POWERFUL "Advanced Filter" component. But because each application is usually unique, it's usually best if the actual file is emailed - or a scaled-down version (with any sensitive data replaced with fictitious data that still represents the type of data you're working with).

Unfortunately Microsoft has not provided much in the way of support for the Advanced Filter component. My "best guess" as to the reasoning is because they would much prefer that users opt for purchasing Access (more $$$). While Access is preferable for some applications, there are MANY other "database" type applications that can be handled with a spreadsheet-based database - i.e. within Excel.

I can't expect to "write a book" within Tek-Tips". Therefore, here's a little information on the use of Excel's "Advanced Filter" component. It includes the ability to extract data (from a spreadsheet-based database) to a separate worksheet, or alternatively filter-in-place, based on any type of "criteria" you specify. The criteria can be simple, or as complex as is required to identify the precise data you require.

The same criteria can be used for database formulas (i.e. =DSUM, =DCOUNTA, =DMAX, =DMIN, etc) - to create a summary of totals, for example a matrix of data: by EMPLOYEE by YEAR, by DEPARTMENT by MONTH, etc.

I highly recommend that a SEPARATE sheet be used for creating the criteria. The reason is because Excel "gets confused" and will cause problems if the criteria is placed on the same sheet as the database.

The "smallest" criteria needs to consist of 2 cells - the top cell is for the field name, and the cell below is for the criteria. With text being used as the criteria, the field name is required. However, if a formula is used as the criteria, it's necessary that a field name NOT be used. But you must still reference BOTH cells as the criteria range - i.e. including the BLANK cell where the field name normally would be. Often a formula references a field name.

Here's an example where a formula is used in the criteria, and it's purpose is to isolate the records that fall within whatever MONTH is specified by the user. When the user specifies a month, the number of the month is placed in the cell named "mth" - see the CHOOSE formulas below.

=AND(Date>=INDIRECT(mo_s),Date<INDIRECT(mo_e))

&quot;mo_s&quot; is a range name for a cell containing the following formula...
=CHOOSE(mth,&quot;jan_&quot;,&quot;feb_&quot;,&quot;mar_&quot;,&quot;apr_&quot;,&quot;may_&quot;,&quot;jun_&quot;,&quot;jul_&quot;,&quot;aug_&quot;,&quot;sep_&quot;,&quot;oct_&quot;,&quot;nov_&quot;,&quot;dec_&quot

&quot;mo_e&quot; is a range name for a cell containing the following formula...
=CHOOSE(mth,&quot;feb_&quot;,&quot;mar_&quot;,&quot;apr_&quot;,&quot;may_&quot;,&quot;jun_&quot;,&quot;jul_&quot;,&quot;aug_&quot;,&quot;sep_&quot;,&quot;oct_&quot;,&quot;nov_&quot;,&quot;dec_&quot;,&quot;jan2_&quot

In these CHOOSE formulas, there is a reference to the different months - again by use of range names.

In the cell named &quot;jan_&quot;, is the formula...
=DATE(year_,1,1)

In the cell named &quot;feb_&quot;, is the formula...
=DATE(year_,2,1) ...etc.

The cell named &quot;jan2_&quot; has this formula...
=DATE(year_+1,1,1)

With this type of structure, changing from one year to another only involves changing the year in the cell named &quot;year_&quot;.

That's probably getting close to some sort of maximum text allowed in Tek-Tips, so I'll end this &quot;mini-course&quot;.

I M P O R T A N T P O I N T

There is one last point I'd like to make. I feel Excel users are REALLY being &quot;short-changed&quot; by Microsoft - by their continued refusal to &quot;modernize&quot; Excel to bring it up-to-date with the spreadsheet-based &quot;database functionality&quot; that existed over a decade ago.

Years ago when Microsoft created the filter/database component based on Lotus 123's pre-Windows version, they neglected to include all of the functionality of Lotus 123. The data manipulation functions Microsoft neglected to include are: Data-Find, Data-Modify, Data-Delete, and Data-Append.

Additionally, Lotus 123 had (spreadsheet-based) relational-database capability. So overall, Microsoft has a LONG way to go to make Excel as powerful as Lotus 123 was over 10 years ago. Once these missing functions are included, Excel users will be completely overwhelmed at &quot;RAW POWER&quot; that these additional functions provide will provide.

And finally, these &quot;custom functions&quot; are written in the same &quot;C&quot; language as Excel, and are therefore VERY fast - especially compared to the VBA &quot;looping&quot; routines that Excel users have had to resort to in place of the missing custom functions ...not to mention the &quot;code execution has been interrupted&quot; errors reported earlier in this thread.

I hope this has been useful reading, and that perhaps it will inspire some Excel users to take a serious look at Excel's &quot;Advanced Filter&quot;. While the Advanced Filter component only includes the ability to: a) filter-in-place, or b) extract data to a separate sheet, there is STILL sufficient capability here to warrant its use. And of course there are also the database functions (=DSUM, etc).

I'm hopeful that the more Excel users begin to utilize this Advanced Filter component, the more &quot;pressure&quot; can be placed on Microsoft to FINALLY come through with the long-awaited upgrade to bring this component of Excel up to the same level as that other &quot;ancient&quot; spreadsheet.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[pbbriggs]

Thanks, Dale, for this detailed explanation. You have obviously done extensive work with Advanced Filters and should probably write a FAQ or two, if you haven't already!

While I'm receiving the same error as jammastajc, mine is resulting not from a filter, but from a macro that has always worked in the past and now generates the error. I just found out that another macro on the same computer also generated this error, so it must be related to memory or something on that particular computer. Thanks for offering to help look at my code but I no longer think it's a problem with my code.

 

[DaleWatson123321]

Hi Patricia,

Thanks for the feedback

Just for the record... Where jammastajc referred to &quot;trying to execute a filter I wrote for Excel in VBA&quot;, he was actually NOT referring to Excel's &quot;Advanced Filter&quot;.

He had written a macro &quot;custom filter&quot; - which combined the use of User-Defined-Functions and looping through the records.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca