Does this mean that the only way I can change a cell, other then a
referenced cell, is via an Event?

Thanks...

"Patrick Molloy" wrote in message
...
understood. Unfortunately functions can only affect the cell making the
call, not referenced cells.
Alternative perhaps would be to used the sheet's CHANGED event. You could
test if a specific cell changed and the change avalue of another.
so if U7 was changed to the value 10, then the value in R7 would be
incremented by the amount in U7

right click the sheet tab and select View Code then paste this

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "U7" Then
Range("R7") = Range("R7") + Range("U7")
End If
End Sub


"Bruce A. Julseth" wrote in message
...

Thanks for the suggestion but that is not what I want.

I want to change the value on the spreadsheet of the input argument,
CompletetionDate.

My call from the spreadsheet cell (U7) is "=SetCompletionDate(R7)". The
value of R7 is 25-Dec-09.

In my function, Function SetCompletionDate(CompletionDate as Date) as
Date, I
increment
ComplettionDate = CompletionDate + 10

The Cell, R7, is NOT changed by the above statement. It should be changed
to
04-Jan-10

I understand that the default parameter passing is ByRef, I would expect
R7
to be changed by
ComplettionDate = CompletionDate + 10

So, what am I doing wrong, Why isn't R7 being changed.

Thanks again for the help. I hope someone can answer my query.


----- Original Message -----
From: "Patrick Molloy"
Newsgroups: microsoft.public.excel.programming
Sent: Friday, December 18, 2009 5:47 AM
Subject: ByRef value not returned to the speadsheet


your last line doesn't return the adjusted date - change it to what I
have in the following code:

Function SetCompletionDate(CompletionDate As Date) As Date

' Correct value for CompletionDate is displayed via debug.print
Debug.Print "1:" & CompletionDate

' Correct value for CompletionDate is displayed via debug.print
' but the value on the spreadsheet is not changed

CompletionDate = CompletionDate + 10
Debug.Print "2:" & CompletionDate

' Correct value is returned to the speadsheet
SetCompletionDate = CompletionDate '' AMENEDED LINE

End Function

"Bruce A. Julseth" wrote in message
...
The argument, CompletionDate, is a reference to a cell (R7). I add 10
to CompletionDate.
Debug.Print, in both cases, displays the correct value but my cell (R7)
is not updated.

My function resides in cell U7 and it's value is returned correctly.

What am I doing wrong? Thanks for the the help.

Function SetCompletionDate(CompletionDate As Date) As Date

' Correct value for CompletionDate is displayed via debug.print
Debug.Print "1:" & CompletionDate

' Correct value for CompletionDate is displayed via debug.print
' but the value on the spreadsheet is not changed

CompletionDate = CompletionDate + 10
Debug.Print "2:" & CompletionDate

' Correct value is returned to the speadsheet
SetCompletionDate = Now() + 10

End Function





"Patrick Molloy" wrote in message
...
your last line doesn't return the adjusted date - change it to what I
have in the following code:

Function SetCompletionDate(CompletionDate As Date) As Date

' Correct value for CompletionDate is displayed via debug.print
Debug.Print "1:" & CompletionDate

' Correct value for CompletionDate is displayed via debug.print
' but the value on the spreadsheet is not changed

CompletionDate = CompletionDate + 10
Debug.Print "2:" & CompletionDate

' Correct value is returned to the speadsheet
SetCompletionDate = CompletionDate '' AMENEDED LINE

End Function

"Bruce A. Julseth" wrote in message
...
The argument, CompletionDate, is a reference to a cell (R7). I add 10
to CompletionDate.
Debug.Print, in both cases, displays the correct value but my cell (R7)
is not updated.

My function resides in cell U7 and it's value is returned correctly.

What am I doing wrong? Thanks for the the help.

Function SetCompletionDate(CompletionDate As Date) As Date

' Correct value for CompletionDate is displayed via debug.print
Debug.Print "1:" & CompletionDate

' Correct value for CompletionDate is displayed via debug.print
' but the value on the spreadsheet is not changed

CompletionDate = CompletionDate + 10
Debug.Print "2:" & CompletionDate

' Correct value is returned to the speadsheet
SetCompletionDate = Now() + 10

End Function