Mickey
VBA/Excel interaction is often slow and its often best to avoid using the
calculate event for that reason.
presumably if you set calc to manual via the menus everything is fine?
You could try managing the calc state from other sheet events like activate.
or find a better way to move your array data into the cells - do you really
need to do that evey time the sheet is calculated?
its hard to give precise advice without seeing the full code.
I'm not totally clear where each bit of your code is and how often it gets
fired, if you post some more I'll try and help more
cheers
simon
Blog:
www.smurfonspreadsheets.net
Corporate:
www.codematic.net
Excel development, support and training


"mickey" wrote in message
...
I've written a UDF that transfers data from a VBA array to a worksheet (
the
data is ultimately used as source data for a "Bar" chart.

The data transfer is painfully slow, one can watch the 50 cells update one
at a time approximately every second.

The routine which performs the transfer is called from the "Calculate
Event". In an effort to improve the transfer rate, I have included the
following code:
Do
Loop Until Application.CalculationState = xlDone' Wait for any
calculations
to finish.
Application.EnableEvents = False ' To prevent re-calculation before the
entire transfer is complete. (Events are re-enabled at the completion of
the
transfer.

The code above has had no effect on the transfer rate.

I noted that the "Bar" graph tries to update as each item is entered.
Assuming that this could slow things down I tried to disable the chart
during
the transfer with the following code:

WorkSheets(1).ChartObjects(1).Enabled = False

Interesting this did not stop the chart from updating with each cell
transfer. I would have thought this would have suspended the chart from
changing until the transfer was completed, at which time the chart would
update when re-enabled. I did confirm that I was addressing the chart by
changing the "Enabled" property to "Visible" and indeed the chart became
invisible during the transfer and re-appeared when the transfer was
complete.
However, this did not effect the transfer rate either.

There definitely appears to be background processing continuing regardless
of the "EventEnabled" state. I would appreciate any insight to the
problem,
or suggestions.

Thanks.