Execution of Sub from another Sub

Question

I have the following VBA code to color the cells of a table, which is the result of a query that can be updated time after time.

Sub ColorMain()

Dim Data As Range
Dim cell As Range

Sheet1.Activate
Range("D7").Select
Range(Selection, Selection.End(xlDown)).Select

'clean the coloring
Set Data = Selection
Data.Interior.ColorIndex = 0

'apply the coloring
For Each cell In Data
If cell.Value = "X" Then
   cell.Interior.ColorIndex = 22
ElseIf cell.Value = "Y" Then
   cell.Interior.ColorIndex = 44
End If
Next

End Sub

I am calling this Sub from another main Sub which updates the SQL query. When running the main Sub coloring applies to some cells, but not all. When I run the main Sub one more time, then coloring applies to all cells appropriately.

When I run the coloring Sub line by line, it works perfectly. Why it is not working from the first attempt when calling main Sub? Do I have to pause the application somewhere?

EDITED: Main Sub refreshes the queries I have in the workbook and calls ColorMain:

Sub RefreshAll()

    ActiveWorkbook.RefreshAll

    Call ColorMain
End Sub

Not sure, but note that you should avoid `Activate` and `Select` - see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — BigBen, Oct 31 '19 at 20:01
The query is not updated fully by the time the color sub is completed running, so it results in the query still updating and color one is already done, THEN the query updates. The reason it works line by line is likely that it gives it enough time to fully update. Try using a method to wait until it's complete with the refresh before continuing. — Mark S., Oct 31 '19 at 20:06
Can we see the other sub as well though? It will better help us see what's going on. — Mark S., Oct 31 '19 at 20:07
Might also want/need to pass in the worksheet to this sub, to be explicit and make sure the same workbook/worksheet is being used and another doesn't somehow become the active sheet. Also, `Set Data = ...` can be `Set Data = Sheet1.Range(sheet1.Cells(1,4), sheet1.Cells(1,4).End(xlDown))`. ..and finally, you could instead just do Conditional Formatting, no? — BruceWayne, Oct 31 '19 at 20:34
@BruceWayne After setting Data as you showed, it does not color anything now. Do I need to modify For loop as well then? — anarz, Oct 31 '19 at 20:42
@anarz change to `Set Data = Sheet1.Range(sheet1.Cells(7,4), sheet1.Cells(7,4).End(xlDown))` — alowflyingpig, Nov 01 '19 at 01:18

anarz · Accepted Answer · 2019-11-01T08:49:58.353

0

The problem was in query not being fully updated when coloring sub is executed, as @Mark S. mentioned.

In the properties of the query (Edit Connection Properties), I ticked of "Enable background refresh" option. This allows the query to be fully updated, before doing any other executions, i.e. running coloring Sub.

edited Nov 01 '19 at 08:49

answered Nov 01 '19 at 08:34

anarz

209
4
11

Execution of Sub from another Sub

1 Answers1