Can't eliminate flicking of chart on every calculate

[x77777x]

I am using Excel 2002.

I have real time data being charted , and I manually calculate
ranges and sheets as needed. This is not a problem.

I have recently added charts to plot this data. Every time there is
a recalculation anywhere on the sheet (even areas not being charted)
the charts flicker. Since there is new data multiple times per
second , this is a problem.

Yes, I know about Application.DisplayAlerts = False, and tried
inserting that before EVERY calculation call. I even went so far as
to use the LockWindowUpdate api call, which freezes the entire app.
Unfortunately , as soon as you unfreeze, you get the redraw flash.

Is there no way to avoid this visible screen redraw?

Interestingly, I also have a chart in this same workbook, that is
created with
Set ChartSpreadSheet = CreateObject("OWC11.Spreadsheet"), that is
displayed in a userform. This chart does not exhibit the flashing on
update problem. I was trying to avoid using that method again for
this particular chart.

 

[Jon Peltier]

Wow, four identical messages in the course of four minutes. We are
volunteers reading your questions, none of them actually from Microsoft.
Give us a chance and we might get to it.

In any case,

Application.DisplayAlerts = False

is not what you're looking for. Perhaps you want

Application.ScreenUpdating = False

but if your issue occurs on automatic calculations in the worksheet, I'm not
sure it will help. If you're calculating in code, then it might actually
help.

- Jon

 

[JoeG]

sorry about that duplication ;-). I posted using Microsofts web site, and
it threw an error on send. I thought it did not work , so I reentered it.

I mistyped , of course I meant
Application.ScreenUpdating = False

I think I'll have to use the ActiveX charting on a form, as this does not
appear to have the unfortunate flashing issue.

 

[Jon Peltier]

Oh yeah, that nice web interface. There are other ways to access the forums.
I use Outlook Express, and I've created a news account linked to the news
server msnews.microsoft.com.

Is there a lot of data in each chart? What version of Excel are you using?

- Jon

 

[JoeG]

As I said, Excel 2002. I have a lot of code written and working in that
version, and I haven't had time to install newer version to see what will
break.


There's quite a bit of data involved, its real time, and I have to do some
manipulation to get the data to display properly.

Working with the Active X module is more of a hassle than the built in
charting for obvious reasons. But, the upside is you get a userform that
you can actually put and size anywhere on the screen, as opposed to a chart
locked into the Excel app. I've done this before and I'm happy with the
results.

 

[Jon Peltier]

Yep, you did say, right up there. I suspected you had 2007, which does not
deal very well with charts that have too much data. And for 2007, too much
means not very much.

If the userform/OWC approach suits you, then keep with it.

- Jon

 

[JoeG]

This is not a chart specific issue, but I'm hear and it's related to the
project.

I spend considerable time working out a scheme using INDIRECT Relative
addressing.

Works fine in Excel. In the component spreadsheet I'm having problems
with R1C1 addressing. It's simply not working as advertised, and the same
code that works in an Excel sheet fails in the Active X component.

I tested R1C1 and it does not seem to function, though documentation
indicates it should. I'm using version 11 of Web Components .

It seems to use the A1 cells as its base no matter where the cell is
located. Not nice.

=INDIRECT("R1C1",FALSE) sees cell A1 even when located in cell E10 for
example.

Could be a known bug, but I could not find a reference to it.

 

[Jon Peltier]

I'm not really fluent in R1C1 notation, but I thought R1C1 means cell A1,
and R[1]C[1] means the cell one down and one left from the reference cell.
And putting it inside INDIRECT would make me run extra tests to be sure it
worked as I expected.

- Jon