VBA command has stopped functioning

[Gizmo63]

Hi folks,

In some of my v.large workbooks I use a macro to recalculate data fields
from weekly data dumps. The size of the workbooks mean that these cannot be
'live' calculations. So I am using the code to put the formulas in, calculate
the answer and then convert to numbers before moving onto the next data block.

I have been using the very useful 'Selection.Calculate' code to isolate and
calculate the updated the cells in this process and thereby NOT have to
recalculate the entire workbook before converting the selection to hard
numbers.

Over the last couple of weeks I have started running into problems with the
'Selection.Calculate' code.
In previously trouble free workbooks I am now getting debug errors with the
message "Runtime error 1004. Calculate method of range class failed".

Sometimes it can be cured by closing Excel and starting anew but not always.

Does anyone have any ideas what may be happening and any potential solutions?

Cheers Giz

 

[Jim Rech]

Some possibilities:

http://support.microsoft.com/kb/825011/

http://support.microsoft.com/kb/292476/

--
Jim
| Hi folks,
|
| In some of my v.large workbooks I use a macro to recalculate data fields
| from weekly data dumps. The size of the workbooks mean that these cannot
be
| 'live' calculations. So I am using the code to put the formulas in,
calculate
| the answer and then convert to numbers before moving onto the next data
block.
|
| I have been using the very useful 'Selection.Calculate' code to isolate
and
| calculate the updated the cells in this process and thereby NOT have to
| recalculate the entire workbook before converting the selection to hard
| numbers.
|
| Over the last couple of weeks I have started running into problems with
the
| 'Selection.Calculate' code.
| In previously trouble free workbooks I am now getting debug errors with
the
| message "Runtime error 1004. Calculate method of range class failed".
|
| Sometimes it can be cured by closing Excel and starting anew but not
always.
|
| Does anyone have any ideas what may be happening and any potential
solutions?
|
| Cheers Giz

 

[JLGWhiz]

Sometimes in large workbooks when there is continuous calculations, the
memory gets stressed out. Try building a periodic "Save" into your program
to release some of the memory and see if that makes the problem go away.