VBA Chart

[FinnBarr]

Hi All

I have a spreadsheet that records market data at 1 second intervals,
for between 15 - 30 minutes. The sheet uses 100+ columns and upto 1800
rows (30*60), I want a dynamic chart to display the data as it comes
in. Using XL2002, I set a chart using a defined name offset formula I
found on John Peltiers website, amongst others. Problem is, it cant
complete the graph and crashes my code which stops the data updates. I
think the data is coming in too fast as its fine if I manually input
dummy data into the range.

Does anyone have a slick workaround or is this just out of XL's
capabilities? Is there a free/shareware graphing add-in that could
cope?

Thanks in advance.

 

[Jon Peltier]

You don't mention VBA in the message. Is there a VBA error? Or is the
error in the dynamic formulas?

- Jon

 

[finnbarr]

Hi Jon

Thanks for your post.

On re-reading my post, I realise that it might have been a bit oblique ,
sorry! Being a newbie, I'm not sure what to include/exclude in my posts.

Yes there is an error in VBA; and no it is not an error in your dynamic
formula.

Like I said in my earlier post, "I think the data is coming in too fast as
its fine if I manually input dummy data into the range."

FYI

Data arrives on Sheet A from a server with a page layout I have to adapt to
work with. Originally, I tried to just copy/paste every second on to Row 2 of
Sheet B but the screen flickered too much and when I tried to plot a chart I
got a VBA error, "late binding", I think it was, (I don't have the code here,
it's on a different PC).

So I rewrote the subs to set a link between Sheets A & B, taking the data
and setting it out in Row 2, underneath column headings . I then used an
array, linked to an OnTime call firing every second, to write the values to
the next empty row.

I read about the defined name in John Walkenbach's 2003 Programming book and
thought that this would allow me to limit the series to the penultimate row,
thus avoiding the "late binding" in case the array had the same effect as the
copy/paste method. He also has a link to your website in the appendix so I
checked that out, and some of the others that you link to as well.

I copied a number of the offset variations (and a couple of my own
creations) into my charts but they all fell over for one reason or another. I
dont recall the error numbers off the top of my head, I'm afraid. The errors
only happen when the update sub is running and capturing data in real time,
it's fine if I use the same code on "static" data and update the series by
manually inputting it.

The chart needs to plot a time series on the X axis in seconds. On the Y
axis, I need the volume of money traded in the last second, and also 2 moving
averages of N seconds, or more if possible for different time frames (N1, N2,
N3 etc). One for the volume of money available at the current buy price and
one for the volume of money available at the current sell price. On a
secondary Y axis I need to show the current last traded price and one or more
moving averages for that price. The time series is in Col A, the last traded
price in Col K, the volume of money traded in the last second is in Col W and
the moving averages are calculated by the chart but the underlying data is
Col X for the sell volume available and Col Y for the buy volume available.

In the appendix of his book, John described you as having an, "uncanny
ability to solve practically any chart-related problem." As a VBA newbie, I
would really appreciate your opinion on XL's capability. Is it possible to
create a chart as per the above and update every second with VBA? If so, how
do I go about it?

I look forward to hearing from you.

Kind regards

GG

 

[Jon Peltier]

Okay, not enough information became almost too much information, but at
least we can filter out what we don't need (yet).

There is no VBA error called "late binding". Late binding is a technique
used when linking to another library when you don't know what version is
on a user's computer, or even whether the library is there. Let us know
what the actual error is, not just the number but also the description.

Screen flickering is resolved using Application.ScreenUpdating=False
before and True after an operation.

Does the update sub (not your sub) run in VBA, or is it a DDE link? I
can see how the first would cause all manner of problems, but I've had
good luck with the latter.

- Jon