Looks like my last posting didn't go through... Anyway, just wanted to say
THANKS A MILLION for your help on this. I will simply remove the Volatile
statement.

Thanks
Emma


"NickHK" wrote:

And XL2000 shows the same behaviour.

Does seem others have seen this.
http://www.pcreview.co.uk/forums/thread-1016922.php
Maybe the suggested .Calculation xlManual is better.

NickHK

"NickHK" wrote in message
...
Trying to recreate your situation with:
<Module
Public Function CallerTest() As String
Application.Volatile
CallerTest = Application.Caller.Offset(-1, 0).Address
'CallerTest = "Tested"
End Function
</Module

<WS
Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False
If Not Intersect(Target, Range("Input")) Is Nothing Then
Target.Offset(0, 1).Value = Asc(Target.Value)
End If
'Application.EnableEvents = True
End Sub
</WS

With the cell containing the data validation named "Input".
With this, the WS_Change events does not fire.
But comment out "Application.Volatile" and OK.
So seems .Caller/ThisCell is not the direct culprit.

Would that be a solution for you ?

NickHK

"Embirath" wrote in message
...
Hi again

The problem does go away when I don't use
Application.ThisCell.Offset()...
The problem is I need to use this function, so I need to make it work
with
it.

The reason I don't want to use arguments in the function is because in
the
work I do, I must constantly rearrange (cut and copy/paste) rows. When
there
are arguments in the functions, cutting and pasting messes up the
arguments.
Excel thinks that I want to keep the old arguments when doing cut/paste,
when
I in fact just want the numbers to be calulated using cells at some
relative
location from the one being calculated. Can you think of a different way
of
doing this, without using the ThisCell property?

It doesn't make any sense why the ThisCell property affects the
Worksheet_Change function. Does this look like some kind of bug to you,
or
is
there something about the ThisCell property that I don't understand?

Btw, I did try using Caller instead of ThisCell, but it does not fix the
problem.

Thanks
Emma

"NickHK" wrote:

ThisCell was added after XL2000. Not sure of any drawbacks/benefits to
the
change as I've not used it.
..Caller does not have to be cell; it depends on the reason the
routine
was
called.

To rule this routine out as the cause (or may be confirm it), you
could
change the routine to accept 2 dates as arguments and removed all the
..ThisCell.Offset code.

Quite possible this is barking up the wrong tree, but ..

NickHK

"Embirath" wrote in message
...
Hi Nick

For my curiosity, do you know what the difference is between
ThisCell
and
Caller? Is there a difference?

I'm wondering if there is a reason to update my programs to use
"Caller".

Thanks!
Emma

"NickHK" wrote:

Not sure if this applies, but Application.Caller (as was before
Application.ThisCell) could produce unexpected results, at least
for
me.
Could be because you are not passing the 2 ranges as arguments.
If you added a "debug.print .Thiscell.address", you may find it is
being
called from an unexpected cell.

NickHK

"Embirath" wrote in message
...
Hi there

I've had this problem with an Excel program of mine for quite
some
time
now.
Have posted this problem to many news groups but haven't been
able
to
find
a
solution yet. I finally was able to narrow down the problem, so
I
think it
is
now fairly well defined. I'm hoping this will help. Any help
would
be
greatly
appreciated!

I have one column in my spreadsheet which has "data validation"
set,
so
that
there is a drop-down list of allowed pre-set values. The user
can
go
in
and
change the contents of these cells by picking an item from the
drop-down
lists. When the user does this, the cell immediately to the
right
of
it is
filled out with some default parameters. I use the
"Worksheet_Change"
function to make this happen. Any time a cell changes in this
particular
column, a function is called which fills out the appropriate
information
in
the neighboring column. Fine, everything works great so far.

The problem comes in if I happen to call another function that I
wrote
in
this same spreadsheet. I call this my "haunted" function...
which
makes
the
above function break... The functions seem completely unrelated,
but
perhaps
I'm missing something. When I call this function from some other
cell
in
the
spreadsheet, nothing happens when I select an item from the
drop-down
lists
mentioned above (the information that is supposed to be entered
in
neighboring column doesn't appear). Here is my "haunted"
function:

Public Function diffr() As Date
Application.Volatile (True)
With Application
diffr = .ThisCell.Offset(0, -1).Value2 -
..ThisCell.Offset(-1,
-1).Value2
End With
End Function

This function simply calulates the difference between two
dates/times,
in
the two cells immediately to the left and "up and to the left"
of
whichever
cell calls the function. (There is a long story of why I wrote
this
function
rather than just simply typing in the formula in the cell - but
please
ignore
this for now.)

It would be easier to just send my spreadsheet, which clearly
shows
the
problem, but I don't see a way to do that here. If you need more
information,
please let me know.

If any of you have any ideas what might be going on, Please let
me
know!

Thank you!!
Emma