Update Chart during iteration

[jonlemur]

Hello Everyone,

I am trying to write a macro that will iterate through a specified range of integers. This updates the value of a certain cell to be the value of the current integer, which in turn updates a table of data. A chart displays the data in the table in another worksheet. I need the chart to display the table for every iteration, but it currently does not update until the macro is done. I have tried to find a solution to this, but thanks to VBs help files, I have been unsuccessful so far. Here is the code for what I am trying to do, and some of my attempts to get it to work.

Please Login or Register  to view this content.

And I've tried many, many more ideas, but none have worked. Most fail without even running. I think I'm really close to the solution, I just don't know the correct syntax to get it to work. I renamed the chart that I want to update as "HPLC" through the properties tab under Layout. I don't know if that helps.

Thanks for any help you may be able to provide.

[shg]

Jon,

1. Do you get compile-time errors, run-time errors, or neither?

2. When you change the input range, does the chart data indeed recalculate?

3. Have to tried code to select the chart (meaning that you can find the chart in code)? That same method, with Refresh replacing Select, should work ...

[jonlemur]

shg,

On some of those I get compile-time errors, others I get run-time errors. The important thing is that I haven't found the right combination of objects, indeces and functions to access what I need.

The chart is based off of a table of data that looks up another table of data, so it doesn't really recalculate, it just changes where it looks.

I think I've tried to select the chart. I haven't done so with Select, but I think I have tried through other methods. Could you post an example of how I would do that? And could it include more or less a complete path (i.e. WorkBooks("Sheet1").Application.Chart("HPLC").Select or something like that). I don't know the proper syntax for something like that yet.

Thanks

[jonlemur]

I have verified that the table that the chart is referencing is being updated after each step of the loop. It is just the chart that is not being refreshed. Can anyone post how I can tell all charts in the workbook to refresh after each step of the loop, and how I could tell just one chart to refresh?

Thanks

[jonlemur]

I don't know if this will help, but I did find out that if I use

Application.Wait Now() + 0.5 / 60 / 60 / 24

instead of

Sleep Worksheets("HPLC Graph").Range("E2:E2") * 1000

the chart will update after each step. Unfortunately (and the reason I used the latter in the first place), I need the resolution of the pause to be in milliseconds. Using Wait, it either runs as fast as it can (if the first number is less than 1) or it runs an integer number of seconds, neither of which are optimal for me.

[protonLeah]

Can you please post a zipped copy of your workbook? Also, why do you need a timer to run through integer values rather than using spinners under your direct control?

[jonlemur]

I can't post the exact workbook (it contains confidential information), but I will make a mock up of it to demonstrate what I'm after. I do have a spinner in it for direct control, but I think that adding an automatic click-and-run feature would be nice. I don't have access to Excel right now, but I'll make one up and post it soon. Thanks!

[lecxe]

Hi jonlemur

If I understood correctly you just need to allow events to be processed. The chart will refresh automatically.

Try:

Please Login or Register  to view this content.

I did some tests with times less than a second as you want (for ex. I tried E2=0.1 to get 100ms) and it worked OK.