Same piece of code (with 20 Modules) produces different results

[VBAProIWish]

Hello all,

I have a large program that spans 20 modules. If I run all 20 modules using a call command without a breakpoint, the code results in providing incorrect data. but if I put in just 1 breakpoint at around module 18 and then resume the code to finish through module 20, the code produces the correct result.

Anyone have any idea how or why this happens and how I can prevent it?

I don't want to have to create a breakpoint just so the code produces the correct result.

Thanks

 

[RoryA]

Does your code refresh anything? That sounds like a timing issue to me.

 

[VBAProIWish]

Hello Rory, yes, I have at least one refresh command and it's in Mods 13, 26, 27 and several in Modules 28 and 29 (same refresh command)

That code is....

 ActiveWorkbook.RefreshAll

I guess I had more than 20 modules.

 

[RoryA]

That probably explains it then. Most likely something that you are refreshing is set to refresh in the background, so the code carries on before it has finished its refresh.

 

[VBAProIWish]

Oh, thanks for that. So what can I do about that? Can I create a 5 second pause after each refresh? If so, do you have the code for that?

 

[RoryA]

Turning off the background refresh is simpler and more effective.

 

[VBAProIWish]

Please forgive my ignorance but I am not sure what that means?

What is "background refresh"?

I don't think I can delete the "ActiveWorkbook.RefreshAll" code because I remember having issues not refreshing the workbook when it wasn't there. Of course, I am having different issues now. Not sure what my next step should be. Thanks

 

[RoryA]

In the queries pane, right-click each query and choose Properties, then make sure the highlighted option is not checked:

and do the same for any Connections you have.

 

[VBAProIWish]

I feel a bit silly to even say this but I have no queries or connections?
From the Excel workbook and on several different random worksheets, I clicked on "Data" and clicked on "Queries and Connections" and while the "Queries and Connections" pane did show up, it says "0 queries" and "0 connections".

 

[RoryA]

What are you refreshing then? Pivot tables? If they are based on the data in the workbook, then the refresh is not the cause of your original problem.