[RHurlburt]

Old newbie back again!

Questions regarding Excel Queries, Macros that use the downloaded data for other processing and timing:

Set the Stage:
I have 5 web Queries (from 3 different sites) that are refreshed each time I select a particular cell (C6 on Sheet1), press a macro button which takes me to a detail sheet (Sheet3). This action places that information from (C6) to cell (B2) on Sheet3. This cell change event activates the queries, refreshes the tables, and, in turn, activate a couple of macros and links that go to other sheets. One of my macros massages a table to make it acceptable for a link to another sheet.

One of the Problems:
It appears that this macro does the "massage" before the query is completed and the information from the web is not cleaned prior to linking to a chart. (As a matter of fact, I can see the change to the table and then the web data fill).

The Questions:
1) Is there a good method to "time" each of the queries sequentially and then "time" the separate macros so that the tables are "set" before the "real" activity takes place? (Charts, hyperlinks, etc.).

2) Also, there are several modules involved (9). Should the RELATED queries/macros all be in one module, place them in the order I want them to run and then set some sort of delay for each sub?

3) Another real problem right now is that it does take a bit for these guys to run.

All the queries work really well and all the macros work (except for the timing on one). Have even used the call routine ("Call FormulaM") at the end of the queries but all that does is screw it up. And have used a worksheet change event to call that macro, but, as I said above, it seems to run too late and seems to interfere with a couple of the queries.

By the way, the "FormulaM" routine is the one Dreamboat taught me earlier. A really nice feature. And I couldn't have gotten this far without her and all the teachings from XLGuru.

 

[XL Guru]

Hi Dick (again). The name's Guru ... Excel (not VBA) Guru.

If I'm being consistent, I really should tick you off for starting the thread over.

That aside, I must say that you appear to be devouring this subject at an ASTOUNDING rate ; congrats.

I doubt I can help you very much at all ...

1) You can go into the properties of a query and turn off "enable background refresh". The query should then hold back other code until it's thru.

2) In broad terms I don't think it makes a lot of difference. By the same token, I don't think so many mods is necessary and could bloat the file. I try to use as few as poss.

#?) I don't know the reason (if any,) for "Call FormulaM". Plain "FormulaM" as a separate line has always worked for me.

#??) "it does take a bit for these guys to run". If you do formatting with code and you've taken a lead from the Macro Recorder, you can end up with a lot of junk. To add a bottom border, the MR'll give you

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

when you could probably get away with just

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous

Look at doing some tidying up.

HTH,
Andy

 

[RHurlburt]

Hey XL Guru!

You must have gotten that 'r-n-r', thanks for the responses.

I appreciate the compliments. There are times that I read and plagiarize almost 12 hrs. I really like what I'm doing and have a lot of fun doing it. Have had some really great challenges (to me), but with each challenge comes a new learning experience.

One of my sayings in the consulting business was "The Impossible Just Takes a Little Longer" ... the other was "Skillful Plagiarism Beats Inept Creativity".

But, I digress ... back to the problem:

1) Will click off the "background refresh" and see if that will hold off that "FormulaM" macro. Didn't know that.

2) So, would you cut and paste some of those macros in order to consolidate the mods? In some sort of orderly fashion, probably in the order that I expect them to run? Then delete the empty mods? I have found a program that claims to clean the code ... it is called "codecleaner" (seems logical). I am thinking that once I do the above I might run it just to be sure there are no stragglers. I haven't run it yet so not sure what it will do.

#?)I have used both the "Call FormatM" and just "FormatM", both react the same. I prefer as little code as possible so will stick with "FormatM". The problem may be resolved with (1) above.

#??)As for the tidying up, have looked in detail at what the MR does and it did seem a bit verbose ... I have just held back on what I might take out and replace with. I don't always use the MR, but for the "FormatM" and to "sheet hop", I do. I then attach code to that Macro sheet hop that will attempt to have charts and the like ready by the time it takes me to that page. And, I put the cursor at the cell I "think" I may need to make an entry.

Thought) I have some macros that are strictly for sheet hopping since all the queries and links are finished by the time I might use them. I figure to put these "plain Janes" all in one module and those that carry burdens in other individual mods. If nothing else, when I need to review a problem with a particular feature, it would all be consolidated in one mod.