Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
Thanks. I'll work on that approach.
"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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByRef value not returned to the speadsheet
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ByRef vs ByVal | Excel Programming | |||
ByRef strangeness | Excel Programming | |||
byref errors | Excel Programming | |||
ByRef question | Excel Programming | |||
Is ByVal always better if ByRef isn't necessary | Excel Programming |