Programmatically pasting a Function locks macro
 

I am trying to copy a 7 row block of code programmatically and insert it
immediately below, then perform some simple edits.
It is a simple block with column 1&2 showing days of the week and date and in
a further column a single cell showing the week number(from April, tax year).
I also have a cell using a function copied from Chip Pearson's website,
called SumByColor, which sums, as it says, numbers above of particular
colours.

I generate the code via a recorded macro, copying the existing block, then
inserting the cells below, followed by simple edits. This works manually but
halts without error following the "Selection.Insert" command generated in the
macro. I see by stepping into the code that it somehow calls the SumByColor
function and halts in this function, giving the Excel "ding" tone, but no
error.
The area originally copied is now surrounded by moving dotted lines, I
suppose as this was the last selection, and all the cells above using the
SumByColor function have lost their values(now containing #VALUE!...as an
aside, F2 Enter seems to refresh these individually).

I searched for solutions and tried, for example,
setting "Application.Volatile True" in the function, and commenting it out
setting Tools, Options, Calculations, Automatic on/off
setting Tools, Options, Calculations, Save external link values on/off

I suspect that it is caused by the function wanting to recalculate all cells
that use it...though they do not get calculated !
Any ideas on what is happening and ways around, or defeat it ?
Thanks in anticipation

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Programmatically pasting a Function locks macro
 

Post the code that is causing the problem.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"diglas1 via OfficeKB.com" <u22313@uwe wrote in message
news:61e07164f8831@uwe...
I am trying to copy a 7 row block of code programmatically and
insert it
immediately below, then perform some simple edits.
It is a simple block with column 1&2 showing days of the week
and date and in
a further column a single cell showing the week number(from
April, tax year).
I also have a cell using a function copied from Chip Pearson's
website,
called SumByColor, which sums, as it says, numbers above of
particular
colours.

I generate the code via a recorded macro, copying the existing
block, then
inserting the cells below, followed by simple edits. This
works manually but
halts without error following the "Selection.Insert" command
generated in the
macro. I see by stepping into the code that it somehow calls
the SumByColor
function and halts in this function, giving the Excel "ding"
tone, but no
error.
The area originally copied is now surrounded by moving dotted
lines, I
suppose as this was the last selection, and all the cells above
using the
SumByColor function have lost their values(now containing
#VALUE!...as an
aside, F2 Enter seems to refresh these individually).

I searched for solutions and tried, for example,
setting "Application.Volatile True" in the function, and
commenting it out
setting Tools, Options, Calculations, Automatic on/off
setting Tools, Options, Calculations, Save external link values
on/off

I suspect that it is caused by the function wanting to
recalculate all cells
that use it...though they do not get calculated !
Any ideas on what is happening and ways around, or defeat it ?
Thanks in anticipation

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1