ByRef value not returned to the speadsheet

[Bruce A. Julseth]

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]

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]

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" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Friday, December 18, 2009 5:47 AM
Subject: Re: ByRef value not returned to the speadsheet

 

[Patrick Molloy]

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]

Thanks. I'll work on that approach.

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]

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

Thanks...

 

[Patrick Molloy]

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