Watch out for ByRef parameters not working!

[tbone]

I was stumped by this for a bit but finally figured it out. I'm
posting this so that others can benefit from the time I spent. (I'm
using Excel 2003.)

I had a SUBroutine to which I passed a boolean parameter ByRef, so
that it could return a value. However, in testing I found that the
value was NOT being returned as expected.

What I finally realized was that I was invoking the routine using what
sorta looks like a function call, i.e.

SubName (parameter)

Now, I know that's wrong, but here's the rub: VBA did not complain
about the syntax, and in fact the routine was called and the value set
in the routine as expected. The calling logic however did not receive
that updated value.

This kinda feels like a bug to me. Essentially VBA allows incorrect
code to be generated because of a syntax variation. No complaints,
just incorrect operation. Simply removing the parentheses makes the
code work.

So be forewarned!

tbone

 

[Peter T]

No reason for VBA to complain about that. Enclosing the variable in brackets
sends the result of the evaluated expression, not the variable.

You could send byRef like this

Call SubName(variable)

but this would also send the result of evaluating the variable
Call SubName((variable))

Regards,
Peter T

 

[tbone]

No reason for VBA to complain about that. Enclosing the variable in brackets
sends the result of the evaluated expression, not the variable.

I see. I've found that indeed, VBA allows me to use something that's
not an lvalue as a pass-by-reference parameter. Thus, I can do:

SendRef False ' if SendRef wants to return a boolean
SendRef 4 ' if SendRef wants to return an integer

I presume this is due to the growth of the language over time and the
choice to default parameter passing ByRef rather than ByVal.

I just hit this again using a Copy method:

srcrng.Copy (dstrng)

compiles but generates the not-too-helpful 1004 error. Removing the
parens takes care of the problem.

Thanks for the explanation. Hopefully this discussion will help those
who come across the same situation where ByRef doesn't seem to be
returning a value as expected.

tbone