Copy/Paste doesn't trigger macro (again)
 

On september 3, 2006, I started a thread on the same
subject. Rather then returning to it, I prefer to start a
new one. That's because 1,300 threads were opened
afterwards, and also because I have something new. Please
excuse me if that's contrary to the rules.

I have constructed a small workbook (42 K) with the same
structure as my project, to try to isolate the problem. I'll
send it to those interested.

The problem in a nutshell
When data is entered manually in a specific cell, a macro is
triggered, which modifies some parameters of a scatter
chart. But when the data is entered in the cell using
Copy/Paste, the macro is indeed triggered, and the macro
instructions are indeed read, but the parameters of the
chart are not modified.

Here are some details on the mechanics of the program.
- The x-axis is comprised of 11 values.
- Cell A5 calls the udf PutInMem, which stores the squares
of the values of the x-axis in arrayyy(1 to 11), and returns
the value 1.
- The udf ValArrayyy(A5, i) returns the value of arrayyy(i)
when A5=1 (that is, when arrayyy() is in memory), otherwise
it returns an error message. The main reason for giving A5
as parameter to ValArrayyy is to force Excel to put A5 in a
good position in the Dependency Table.
- The values on the y-axis are either the values in
arrayyy() or these same values divided by 100, depending on
the content of A1.
- When there's an input in cell A1, the Worksheet_Change
module first calls the edf PutInMem. If the new value in A1
is different from the old, PutInMem sets the variable
execSetMinMax to 1, then returns the control to the
Worksheet_Change module. And there, if execSetMinMax=1, then
the macro SetMinMax is executed. The latter modifies the
scale of the y-axis to suit either the values of arrayyy(),
or those of arrayyy()/100.

I put some debug.print instructions at strategic points.
They allowed me to trace the execution of the code. I
indented the printouts for clarity.

***** MANUAL input in cell A1 of sheet "Def"
PutInMem begin
setting execSetMinMax=1
PutInMem end
DefChange begin
PutInMem begin
PutInMem end
SetMinMax begin
vertical scale: .01 1.3
SetMinMax end
DefChange end
***** Here, the y-scale of the chart IS modified

***** PASTE in cell A1 of sheet "Def"
DefChange begin
PutInMem begin
setting execSetMinMax=1
PutInMem end
SetMinMax begin
vertical scale: .01 1.3
SetMinMax end
DefChange end
PutInMem begin
PutInMem end
***** Here, the y-scale of the chart is NOT modified,
although the instructions are read, proof beeing the
printout "vertical scale: .01 1.3")

Notes
- With manual input, control goes first to PutInMem, then to
DefChange (short for Worksheet_Change). With paste, it's the
reverse. I don't care, since my code allows for that.
- There's another discrepancy (more important) arising when
I replace the call to PutInMem by Sheets("Def").Calculate in
the Worksheet_Change module. I won't go into that, since I
don't insist on writing Sheets("Def").Calculate .
- In my original program, when I copy/paste several cells at
a time (maybe 3 or 5), I get sometimes the message "Not
enough memory". This is inconvenient, but acceptable, since
I am alerted to the fact that I must press F9.
- "Copy / Paste Special / Values" doesn't solve the problem.
But there's no problem when a cell is edited (function key
F2), its content marked and then pasted in A1. No problem
either when copying from an external program.
- I use Excel 2000.

I would really appreciate some feedback on that. I've been
stuck there for some time.