DoEvents howto

[storm8]

Hello,

I am really struggling with DoEvents. I have very long loops on tables that should first of all be databases and therefore i need to use DoEvents...
however I cannot really understand how exactly it behaves and my research did not fully explained what I need.

1. As I understood, DoEvents tells computer to wait untill current task is performed. Why is this not automatic or why whouldn't I put doevents after "each" line?

2. So where should they be placed? and how often? or after what kind of commands? Id post my example, but its far too long for someone to go trough it...

thanks for any suggestions or useful links!

 

[xenou]

What do you mean by "the error". Does it actually crash?

 

[storm8]

not a crash but no cells found error although when the filter is applied there have to be some cells (and are when I either apply the filters manually or run the macro again)

 

[RoryA]

Is your listobject getting data from an external source? If so, is it set to refresh in the background? That could cause the problem you describe.

 

[storm8]

no external data. I added a check for count of visible rows (if 0 then clear filter and apply again) which did the trick, Im just curious how the error occurs...

 

[xenou]

no external data. I added a check for count of visible rows (if 0 then clear filter and apply again) which did the trick, Im just curious how the error occurs...

My habit is to always clear a filter before applying it so that I'm not inadvertently filtering data that's already partly filtered. If possible, don't just clear the filter if the visible cell count is zero but just clear it every time through. [Edit: I wouldn't consider that a problem, an error, or a mystery but just standard practice for my Excel vba programming ].

 

[storm8]

yes! that is what I had there! the filter was always cleared and then new was applied and yet I received 0 zows...

 

[xenou]

and yet clearing the filter still fixes the problem, you say, though the filter has already been cleared?

If I were you I would rebuild your workbook as it sometimes removes stray binary gunk. I've had, in the last five years, maybe one or two cases of strange things happening that I couldn't explain but just disappeared this way, so it's possible that you just need to clean the workbook up, though in my experience this isn't really something that's often a problem.

It may be worthwhile to refactor your code to make sure it's working as expected. You said it was a lot of code - so that increases the likelihood that there's a programming error. I personally would expect to have at least one logical error in any program of significant size (100 lines or more) - if not 2 or 3 errors - and I may not catch them for months, unless I am testing very thoroughly.

Another thing to do is to test very careful the behavior of filtering. I don't do that kind of thing often so I don't know offhand - what does it do if screenupdating is on or off, calculation is on or off.

In my opinion looping through 14000 rows to apply a filter (14000 times?) sounds like a strategy that could be improved - I doubt I'd every write any such code, in any case - so I think there might be opportunities to fix this by working with the code to improve it. You should also turn off any error handling if you are using that, so errors will show up at runtime (and if calc is off, screenupdating is off, etc. maybe try it without these involved).