Running different macros in one shot

[Guinaba]

Hi guys,

I have 4 VBA macros running in order to get my final report result and format. I have tried to combined all of them in one sub just using call functionality but the results that I am getting are not the same, when I run them individually. So I tried to delayed each call 5 sec 'Application.Wait (Now + TimeValue("0:00:05")) but still didn’t worked. Not sure if I have to delay even more or if there is another better solution. Any suggestions?

1.Refresh Data
2.Run Coverage
3. Refresh Report
4. Format Report

 

[dougmarkham]

Hi Guinaba,

Please elaborate one what you mean by: "but the results that I am getting are not the same, when I run them individually"
For instance, do you get any Run-time errors, if so, what are they and when do they trigger exactly?

In my experience, if you have any code that deletes (e.g., delete row), that can sometimes stop a chain of called macros in it's tracks. In those instances, I normally split the chain of macros into two or more processes. The symptom of this phenomena is that the code just stops without erroring i.e., as if it's done its job but in fact it hasn't.

Kind regards,

Doug.

 

[Guinaba]

Hi @dougmarkham ,

I am not getting any run-time errors, I believe you hit the nail on the head because in one of the macros I am deleting all the repeated rows of some columns in the report. The problem is happening with these two subs:

3. Refresh Report - this one refreshs one power-query
4. Format Report - this one formats the report, deleting repeated rows, conditionaly colouring some rows, etc.

If I run them individually I am getting the expected results, but when I call the (4.Format Report) from (3.Refresh Report) the numbers are getting messed up, but no run-time errors.

I mentioned that if you have any code that deletes (e.g., delete row), that can sometimes stop a chain of called macros in it's tracks. In those instances, I normally split the chain of macros into two or more processes...

...so you split the code in smaller pieces and how do you link them? Using call procedure?

Kind Regads,

Gilly

 

[dougmarkham]

Hi @dougmarkham ,

I am not getting any run-time errors, I believe you hit the nail on the head because in one of the macros I am deleting all the repeated rows of some columns in the report. The problem is happening with these two subs:

3. Refresh Report - this one refreshs one power-query
4. Format Report - this one formats the report, deleting repeated rows, conditionaly colouring some rows, etc.

If I run them individually I am getting the expected results, but when I call the (4.Format Report) from (3.Refresh Report) the numbers are getting messed up, but no run-time errors.

I mentioned that if you have any code that deletes (e.g., delete row), that can sometimes stop a chain of called macros in it's tracks. In those instances, I normally split the chain of macros into two or more processes...

...so you split the code in smaller pieces and how do you link them? Using call procedure?

Kind Regads,

Gilly

Hi Gilly,

No, I don't link them.
Basically, my observation is this: if you have a macro with Delete sheet or Delete row (etc), when running via F8 to go through the code line-by-line, upon crossing through the delete line, the code terminates. It doesn't go through "End Sub", thus, if you have a string of macros that you're calling, the whole sequence terminates because the sub with the delete command terminates after the delete command without continuing through End Sub. As it can't continue through End Sub, it doesn't call the next macro in sequence.

So, I make my Delete code the last thing in a chain of subs which I run. The next chain of subs starts from that point. If I have multiple subs in a row which delete things, that's a problem.

Try creating a chain of 3 and 4 together (so chain1 = macro 1 and 2): what happens then?

Kind regards,

Doug.

 

[Oaktree]

Hard to tell without seeing it, but your issue could be that your Power Query refresh (step 3) isn't finishing before step 4 starts.

If step 4 relies on the data pulled in step 3, you could be applying code against values that aren't there yet.

If you don't already have this in your code, try something like:

 ...
Call Step 3
Application.CalculateUntilAsyncQueriesDone
DoEvents
Call Step 4
...

 

[Guinaba]

Thanks @dougmarkham and @Oaktree!! Awesome tips! Let me try both and see how it goes