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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)>
Newsgroups: microsoft.public.excel.programming
Sent: Friday, December 18, 2009 5:47 AM
Subject: Re: 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:5F5D5373-5CCA-41D7-85BA-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:OhA2GN#(E-Mail Removed)...
>
> 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" <(E-Mail Removed)>
> Newsgroups: microsoft.public.excel.programming
> Sent: Friday, December 18, 2009 5:47 AM
> Subject: Re: 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
> news:5F5D5373-5CCA-41D7-85BA-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>
>

Thanks. I'll work on that approach.

"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:%23QFDIQ%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:OhA2GN#(E-Mail Removed)...
>>
>> 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" <(E-Mail Removed)>
>> Newsgroups: microsoft.public.excel.programming
>> Sent: Friday, December 18, 2009 5:47 AM
>> Subject: Re: 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>> news:5F5D5373-5CCA-41D7-85BA-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>
>>

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

Thanks...

"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:%23QFDIQ%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:OhA2GN#(E-Mail Removed)...
>>
>> 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" <(E-Mail Removed)>
>> Newsgroups: microsoft.public.excel.programming
>> Sent: Friday, December 18, 2009 5:47 AM
>> Subject: Re: 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>> news:5F5D5373-5CCA-41D7-85BA-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>
>>

no. pretty much any VBA code (aka Macro) will allow you to write to a cell
or range. But you need a way to call the code, and raising an event can be
useful.
for example, in a standard module (this isn't pretty) :

Sub xxx()
With Range("A1")
.Value = "Hello World!"
.Font.Bold = True
End With

End Sub


"Bruce A. Julseth" <(E-Mail Removed)> wrote in message
news:OESqFp#(E-Mail Removed)...
> Does this mean that the only way I can change a cell, other then a
> referenced cell, is via an Event?
>
> Thanks...
>
> "Patrick Molloy" <(E-Mail Removed)> wrote in message
> news:%23QFDIQ%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:OhA2GN#(E-Mail Removed)...
>>>
>>> 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" <(E-Mail Removed)>
>>> Newsgroups: microsoft.public.excel.programming
>>> Sent: Friday, December 18, 2009 5:47 AM
>>> Subject: Re: 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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" <(E-Mail Removed)> wrote in message
>>> news:5F5D5373-5CCA-41D7-85BA-(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>
>>>
>
>