Slow sheet data entry?
 

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.

Slow sheet data entry?
 

Hi Simon,

Thanks for your reply.

To answer your question: No, the data in question does not need to be
updated with each "Calcutation". I am convinced that even though I disable
events during the transfer, this does not stop Excel from performing a
calculation with each write to a cell - this is what is causing the slow
transfer.

I have created a work around, which works quite well. I split my UDF into
two parts: the first part calculates all the chart data, then instead of
transferring all the data to the sheet, I placed individual calls (50) to the
same function, with different arguments that individually return each data
point for the chart. As I suspected this runs fast, because Excel includes
each UDF call in one calculate event. What I would like is a way to suspend
the "Calculate" function until I can transfer all the data. However, you
have given me an idea: the next best thing to suspending the "Calculate"
function, maybe to set it to "Manual" as you suggested, and then set it back
to automatic after the transfer is complete. I'll let you know what happens.

Thanks again.


"Simon Murphy" wrote:

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.

Slow sheet data entry?
 

Bingo!

My speculation was correct - disabling events (calculate event not
withstanding) does not stop Excel from performing a calculate with each cell
that is altered by my UDF. I knew I needed to stop Excel from performing a
calculation. When you mentioned setting the calculation state via the
menu's, it gave me the idea that all I had to do was set the calculation
state to "Manual" (Application.Calculation = xlCalculationManual) just before
the transfer, and then set it back to "Automatic" after the tranfer was
complete - it worked! Apparently, for all practical purposes setting the
state to "Manual" is as good as turing the calculation off. This is going
to solve a similar problem I have in another Workbook.

This experience also sheads some light on disabling the events, apparently
only the user accessable events are disabled: in the case of the calculation
function the user calculate event is disabled, but Excel still performs sheet
calculations as warranted by cell changes. By the way calling the UDF from
the calculate event does not cause any problems.

Thanks again for your inspiration!

Cheers.

"Simon Murphy" wrote:

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.