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

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

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

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

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

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

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