VBA: ByRef not returning changes to passed arguments

[Clif McIrvin]

Small question:

Excel macro:

public myfunction(byref data as variant) as boolean
do some stuff that modifies data
myfunction=true
end function

sub mycode()
dim xx as variant, x as variant
xx = some value
05 myfunction (xx)
10 stop
15 x = myfunction(xx)
20 stop
end sub

In the locals window at line 10, xx still contains some value but at
line 20 xx has been changed.

I've done some digging through the installed documentation (Office 2003)
and see nothing that explains why. Can someone point me towards
relevant documentation?

It seems that I first tried

05 myfunction xx

but the compiler didn't like that - it returned "argument not optional."

After changing to the line 15 syntax my macro is doing what I want, and
I'm a happy camper; just curious.

 

[Niek Otten]

Hi Clif,

Always copy and paste your code, don't retype it. Too many chances for
errors.

 

[Bob Phillips]

You are not modifying the parameter value in the function, just returning a
function value.

You probably need something like

Public Function myfunction(ByRef data As Variant) As Boolean
' do some stuff that modifies data
data = "some new value"
myfunction = True
End Function

Sub mycode()
Dim xx As Variant, x As Variant
xx = "some value"
5 myfunction xx
10 Stop
15 x = myfunction(xx)
20 Stop
End Sub

 

[Clif McIrvin]

Thanks Niek.

I intentionally cooked up sample code to simplify the presentation ...
but your point is well taken.

What I said about getting a compiler error on line 05 bothered me (I use
that syntax frequently to treat functions as subs) ... so I went back
and tried it again .... and this time not only did it compile
successfully, but it also executed as expected ....

So: my apopogies for posting a question *before* double checking
myself.

Case Closed.

Thanks again for responding.

--
Clif

Hi Clif,

Always copy and paste your code, don't retype it. Too many chances for
errors.

 

[Clif McIrvin]

You are not modifying the parameter value in the function, just
returning a function value.

Problem solved. (See my post of a few minutes ago.)

Thanks, Bob.

 

[Niek Otten]

No problem at all, Clif! Glad you solved it.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Thanks Niek.

I intentionally cooked up sample code to simplify the presentation ... but
your point is well taken.

What I said about getting a compiler error on line 05 bothered me (I use
that syntax frequently to treat functions as subs) ... so I went back and
tried it again .... and this time not only did it compile successfully,
but it also executed as expected ....

So: my apopogies for posting a question *before* double checking myself.

Case Closed.

Thanks again for responding.