function call not returning changed value
I'm sure this must be blindingly obvious to everyone but me, but ....
I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Matilda,
Check the help for the difference between "ByVal" and "ByRef" Hint: You're using the wrong one if you are changing the value of dStDt in the function. Or are you returning the date from the function ? If so, you are not assigning a value to the return. There is no startDateIs=SomeDate NickHK "Matilda" wrote in message ... I'm sure this must be blindingly obvious to everyone but me, but .... I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Hi Nick,
Thick as a brick but I simply don't get it... what's the difference between assigning a value and changing a value in a function? In both cases there is one value before, and a different one after. I thought the difference between function and sub is that former returns a value, latter simply performs a task. What I am trying to do in the function is put the value of the date selected in the listbox into the date variabale dStDt, and then use that value in the code. Have tried changing argument byVal to byRef with same results, ie expected result within function, empty variable on return to code. Hopeless case me :-(( "NickHK" wrote: Matilda, Check the help for the difference between "ByVal" and "ByRef" Hint: You're using the wrong one if you are changing the value of dStDt in the function. Or are you returning the date from the function ? If so, you are not assigning a value to the return. There is no startDateIs=SomeDate NickHK "Matilda" wrote in message ... I'm sure this must be blindingly obvious to everyone but me, but .... I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Matilda,
See the output in the immediate window of these routine To make the point more clear, I changed the variable dStDt from Public to local to mySub. Using Public/Global variable means that you do not have to pass it to a sub/function. Only use these if truely needed. as they make debugging more difficult because they can be chnaged in many (possibly incorrect) place. Sub mySub() Dim RetVal As Variant Dim dStDt As Date Debug.Print dStDt RetVal = ByRef_StartDateIs(dStDt) Debug.Print dStDt RetVal = ByVal_StartDateIs(dStDt) Debug.Print dStDt Debug.Print RetVal End Sub Function ByRef_StartDateIs(ByRef InputDate) As Long 'This has an effect outside the routine InputDate = DateAdd("d", 10, InputDate) ByRef_StartDateIs = -1 'True, success End Function Function ByVal_StartDateIs(ByVal InputDate) As Date 'This will have no effect outside of this routine InputDate = DateAdd("d", 10, InputDate) ByVal_StartDateIs = InputDate 'Return the calculated value End Function NickHK "Matilda" wrote in message ... Hi Nick, Thick as a brick but I simply don't get it... what's the difference between assigning a value and changing a value in a function? In both cases there is one value before, and a different one after. I thought the difference between function and sub is that former returns a value, latter simply performs a task. What I am trying to do in the function is put the value of the date selected in the listbox into the date variabale dStDt, and then use that value in the code. Have tried changing argument byVal to byRef with same results, ie expected result within function, empty variable on return to code. Hopeless case me :-(( "NickHK" wrote: Matilda, Check the help for the difference between "ByVal" and "ByRef" Hint: You're using the wrong one if you are changing the value of dStDt in the function. Or are you returning the date from the function ? If so, you are not assigning a value to the return. There is no startDateIs=SomeDate NickHK "Matilda" wrote in message ... I'm sure this must be blindingly obvious to everyone but me, but .... I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Hi Nick,
Yeessss!!! Thankyou for the crystal clear demonstration of the effect of passing an argument to a function, and using its return. I can see I was miles off in my comprehension of the way functions are called, arguments passed, and returned values retrieved. In essence, all I needed to do was declare a variant for the return value, (as I understand it, to hold the boolean -1 you assigned it in function) and then the date variable - used after the function call - reflects the change by reference. It works! And I think I now understand why it wouldn't before... Something simple but I was miles off. Thankyou again Matilda "NickHK" wrote: Matilda, See the output in the immediate window of these routine To make the point more clear, I changed the variable dStDt from Public to local to mySub. Using Public/Global variable means that you do not have to pass it to a sub/function. Only use these if truely needed. as they make debugging more difficult because they can be chnaged in many (possibly incorrect) place. Sub mySub() Dim RetVal As Variant Dim dStDt As Date Debug.Print dStDt RetVal = ByRef_StartDateIs(dStDt) Debug.Print dStDt RetVal = ByVal_StartDateIs(dStDt) Debug.Print dStDt Debug.Print RetVal End Sub Function ByRef_StartDateIs(ByRef InputDate) As Long 'This has an effect outside the routine InputDate = DateAdd("d", 10, InputDate) ByRef_StartDateIs = -1 'True, success End Function Function ByVal_StartDateIs(ByVal InputDate) As Date 'This will have no effect outside of this routine InputDate = DateAdd("d", 10, InputDate) ByVal_StartDateIs = InputDate 'Return the calculated value End Function NickHK "Matilda" wrote in message ... Hi Nick, Thick as a brick but I simply don't get it... what's the difference between assigning a value and changing a value in a function? In both cases there is one value before, and a different one after. I thought the difference between function and sub is that former returns a value, latter simply performs a task. What I am trying to do in the function is put the value of the date selected in the listbox into the date variabale dStDt, and then use that value in the code. Have tried changing argument byVal to byRef with same results, ie expected result within function, empty variable on return to code. Hopeless case me :-(( "NickHK" wrote: Matilda, Check the help for the difference between "ByVal" and "ByRef" Hint: You're using the wrong one if you are changing the value of dStDt in the function. Or are you returning the date from the function ? If so, you are not assigning a value to the return. There is no startDateIs=SomeDate NickHK "Matilda" wrote in message ... I'm sure this must be blindingly obvious to everyone but me, but .... I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Matilda,
It does take a little practice. And there are some finer points to the situation that will confuse you if/when you come across them. <Ignore until later if desired Like passing an argument ByVal when it is declared ByRef in the function. And using brackets or not when passing arguments. </End Ignore When passing an variable to a sub or function, decide if that variable needs to be updated inside the called routine. If Yes, pass ByRef. If No (you only need to use its value), pass ByVal. ByRef is the default if specify neither. NickHK "Matilda" ... Hi Nick, Yeessss!!! Thankyou for the crystal clear demonstration of the effect of passing an argument to a function, and using its return. I can see I was miles off in my comprehension of the way functions are called, arguments passed, and returned values retrieved. In essence, all I needed to do was declare a variant for the return value, (as I understand it, to hold the boolean -1 you assigned it in function) and then the date variable - used after the function call - reflects the change by reference. It works! And I think I now understand why it wouldn't before... Something simple but I was miles off. Thankyou again Matilda "NickHK" wrote: Matilda, See the output in the immediate window of these routine To make the point more clear, I changed the variable dStDt from Public to local to mySub. Using Public/Global variable means that you do not have to pass it to a sub/function. Only use these if truely needed. as they make debugging more difficult because they can be chnaged in many (possibly incorrect) place. Sub mySub() Dim RetVal As Variant Dim dStDt As Date Debug.Print dStDt RetVal = ByRef_StartDateIs(dStDt) Debug.Print dStDt RetVal = ByVal_StartDateIs(dStDt) Debug.Print dStDt Debug.Print RetVal End Sub Function ByRef_StartDateIs(ByRef InputDate) As Long 'This has an effect outside the routine InputDate = DateAdd("d", 10, InputDate) ByRef_StartDateIs = -1 'True, success End Function Function ByVal_StartDateIs(ByVal InputDate) As Date 'This will have no effect outside of this routine InputDate = DateAdd("d", 10, InputDate) ByVal_StartDateIs = InputDate 'Return the calculated value End Function NickHK "Matilda" wrote in message ... Hi Nick, Thick as a brick but I simply don't get it... what's the difference between assigning a value and changing a value in a function? In both cases there is one value before, and a different one after. I thought the difference between function and sub is that former returns a value, latter simply performs a task. What I am trying to do in the function is put the value of the date selected in the listbox into the date variabale dStDt, and then use that value in the code. Have tried changing argument byVal to byRef with same results, ie expected result within function, empty variable on return to code. Hopeless case me :-(( "NickHK" wrote: Matilda, Check the help for the difference between "ByVal" and "ByRef" Hint: You're using the wrong one if you are changing the value of dStDt in the function. Or are you returning the date from the function ? If so, you are not assigning a value to the return. There is no startDateIs=SomeDate NickHK "Matilda" wrote in message ... I'm sure this must be blindingly obvious to everyone but me, but .... I have a userform with a list box for user to choose from a list of dates which determines the start date of a range to be analysed. My code is in a general module, so I have declared the two variables to hold the date and the list index globally, in the code general module. The following is my code. I can't for the life of me work out why the function correctly assigns the date value to the list index, as displayed within the function, but on return to the code the variable is empty!! aaarrrgghhh!!! Many thanks for any help - Matilda public dStDt as date, iLstIndx as integer sub mySub() dStDt = startDateIs(dStDt) msgbox dStDt ' display results of function on return end sub function startDateIs(byVal dStDt) as date select case iLstIndx case 0 ' set value to dStDt case 1 'set value to dStDt etc... end select msgbox dStDt 'display results of function end function |
function call not returning changed value
Indeed! Thanks Nick. I have to say that I'm a lot closer to understanding the whole picture now, you have managed to simplify and clarify in a way that the textbooks have not so far! Especially when to use byVal and byRef. Am definitley not ready for the finer points, but alert to them and shall put your advice in my stash. Hope this is reaching a wider audience as well... Thankyou again Matilda "NickHK" wrote: Matilda, It does take a little practice. And there are some finer points to the situation that will confuse you if/when you come across them. <Ignore until later if desired Like passing an argument ByVal when it is declared ByRef in the function. And using brackets or not when passing arguments. </End Ignore When passing an variable to a sub or function, decide if that variable needs to be updated inside the called routine. If Yes, pass ByRef. If No (you only need to use its value), pass ByVal. ByRef is the default if specify neither. NickHK |
All times are GMT +1. The time now is 03:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com