Build in Cushion between Modules being Called

[reberryjr]

I'm having an issue where data is getting "jumbled" on a worksheet. There are only 2 modules that update this particular worksheet and I cannot replicate the issue when I step through each module, nor when I run them manually. My thought is that the "parent" module that is calling these 2 modules is "overloaded". It currently calls 38 modules in total, with 34 of those importing data from other excel files. I have this snippet after every module that is called. Do I need to increase the increment, or is there another/better way to build in a cushion (DoEvents perhaps)?

    Application.Wait (Now + TimeValue("00:00:01"))

 

[bobsan42]

can you elaborate on "jumbled"? is it just the order of appearance or there is more to it?
VBA is a single threaded environment, so it would be safe to assume that two procedures cannot run at the same time.
However a procedure can start while another one is running and add/delete/change data. After the second one ends - the "data landscape" may have changed significantly, so the remainder of procedure 1 must take the changes into account. This is almost the only obvious (at first glance) reason for a "jumbled data" - but this is a design issue, i believe.
Only so much I can tell without debugging and inspecting the code itself. And no cushion size will help if this is the case.

A time gap could help if you are using background operations for which the code does not wait to complete e.g. background refresh of pivot tables or PowerQueries. But for these it is very hard to estimate the necessary delay, so I make sure to avoid them or make them "foreground".

 

[reberryjr]

can you elaborate on "jumbled"? is it just the order of appearance or there is more to it?
VBA is a single threaded environment, so it would be safe to assume that two procedures cannot run at the same time.
However a procedure can start while another one is running and add/delete/change data. After the second one ends - the "data landscape" may have changed significantly, so the remainder of procedure 1 must take the changes into account. This is almost the only obvious (at first glance) reason for a "jumbled data" - but this is a design issue, i believe.
Only so much I can tell without debugging and inspecting the code itself. And no cushion size will help if this is the case.

A time gap could help if you are using background operations for which the code does not wait to complete e.g. background refresh of pivot tables or PowerQueries. But for these it is very hard to estimate the necessary delay, so I make sure to avoid them or make them "foreground".

When I say "jumbled", the primary example is C1R2 has a value of "red" and C2R2 has a value of "apple". The code inserts a formula to update C3R2 to "red apple". C1R3 has a value of "green" and C2R3 has a value of "grape", the code updates C3R2 to "green grape".

Sometimes, the code updates C3R2 to "green grape" instead of "red apple", but C3R3 also says "green grape". I cannot replicate the issue when I step through the code, and it doesn't always happen.

 

[Tupe77]

You've probably checked this, but let's ask anyway because it's the most common reason for messed up data.

1. Is the workbook/worksheet to be processed definitely defined in all subprograms/functions?
and
2. Are there points (such as .select) where the active workbook/worksheet could be changed?

 

[reberryjr]

You've probably checked this, but let's ask anyway because it's the most common reason for messed up data.

1. Is the workbook/worksheet to be processed definitely defined in all subprograms/functions?
and
2. Are there points (such as .select) where the active workbook/worksheet could be changed?

@Tupe77 never I assume I've checked everything...lol.
1 - It is. I get the desired results most of the time, but not every time.
2 - No...I try never to use .Select.

 

[RoryA]

with 34 of those importing data from other excel files

How does it do that?

 

[reberryjr]

How does it do that?

@RoryA Each file has an individual module that looks at a SharedDrive folder. If the source file isn't there, the code moves on to the next file. If the source file is there, the code opens the source file and copies data from the source file and pastes it into the master workbook.

 

[RoryA]

OK, then I suspect it's just an error in the code logic. Can't really say without the code though, but I'd start by looking for anything that refreshes anything.

 

[reberryjr]

OK, then I suspect it's just an error in the code logic. Can't really say without the code though, but I'd start by looking for anything that refreshes anything.

I'm walking through every sort and filter again, to see if there's something I missed

 

[bobsan42]

Again, without the code it's only guesswork.
But if you are sorting and filtering the data itself and you are importing data in the sam place - I would say this is a bad decision.
At least IMHO.