Running different macros in one shot

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
83
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 19, 2018
Messages
83
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

Board Regular
Joined
Jul 19, 2016
Messages
229
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top