Controlling the refresh rate using Q2

[Frinck]

Hi Mark/Gary,

Regarding the cell Q2 (assumming output from BA starts at A1) that controls the refresh rate, does this value get overwritten with the BA value every time a new market is loaded?

I assummed (I suspect wrongly) that it did, but my testing indicates that the old value from the last market remains in place when the next market is loaded.

If you could let me know either way, that'd be great as I'm having a mare debugging it!

Cheers,
Frinck.

[Ian]

It doesn't get overwritten - I've got a formula in Q2 rather than a constant.

[Frinck]

Cheers for the quick reply Ian. Interesting that you've got a formula in Q2 - never thought of doing that (due to my original assumption). I'll re-work my code to accommodate this new knowledge now. Thanks again.

[Roger]

Now I'm gettng confused. In another thread, I am hoping that Gary will allow a formula in cell Q2 to contain the value -3 to initiate a Quick Pick List refresh - at the moment he says the cell is cleared as a response, so you can't use a formula. (This is a "I'll see what I can do" action.)

But if is IS possible to use a formula to change the refresh rate, that formula won't survive putting -3 in by VBA or manually. I'm getting the impression that Q2 is doing rather a lot of things...

[GaryRussell]

If the the value is 0.1 or above then it does not get cleared and you can use a formula.

If it is -1, etc. then it is cleared because the cell is checked on each refresh and if -1 remained there it would trigger the selection of the next market on the quick pick list on each refresh. In the case of the refresh rate it just checks if it is different to the current rate in BA.

[Roger]

Ah, thanks for the clarification Gary.

[Frinck]

Hi,

I've been trying (in vain) to control the refresh rate through VBA but am not getting the desired results...

What I'm trying to do is this:

When a market auto-loads, I want to take a 5 mins pre-off snapshot of the info as soon as all the data is completely loaded in Excel. I find that this often takes 2 full BA refreshes before all the data is available in Excel. In BA, my default refresh rate (as set in the Prefs) is 4 secs. As soon as I have my data stored safely in another sheet, I end by resetting Q2 to 999 (the max) as I no longer need any further data from this market. The next market then auto-loads, and so on and so forth...

What's happening for me is that after I set the refresh rate to 999 in VBA, this value is not overwritten by BA when the next market auto loads. I understand Excel needs 3 different refreshes per one BA refresh, and I check Target.Columns.Count = 16 before processing anything.

So what I'm getting at is this: is there a way I can reset the refresh rate back to 4 seconds whenever a new market is loaded in BA, given that my VBA will subseqently overwrite that with 999 (which rolls into the next new market)?

Hope I haven't confused anyone - me being confused is enough!

Cheers,
Frinck.

[Frinck]

Thinking about this some more, I'm now wondering if setting the refresh rate as high as 999 is ever going to work - whatever value (e.g. 4 secs) that I reset the refresh to in Excel, it will only kick on when BA refreshes (ie. after 999 seconds), whatever the market is.

I guess the only way my code was going to work was if BA automatically reset the refresh rate according to the Prefs instead of carrying over the refresh rate set in Excel from the last race.

Unless anyone knows otherwise?

Frustrated,
Frinck.