Passing Variables to Functions by Reference Clarification

[arskiracer]

Hello, I've been stumbling my way through learning VBA and lately I've been having issues passing variables by reference to functions. In the past, I've always used individual modules as individual functions to my main sub. I recently learned that you can have multiple functions within a single module and so I started programming as such, but passing variables by reference does not seem to work the same.

Please Login or Register  to view this content.

I expected the above example to display three message boxes: 5, 15 and 15, but the three message boxes display 5, 15, 5. Most examples that I've seen about how to pass arguments by reference use the method above, but they get different results. One example I saw, which works is below:

Please Login or Register  to view this content.

So changing the "ByReferenceExample (MyLong)" to "ByReferenceExample ChangeValue:=MyLong" causes the variable to be passed by reference, but the majority of examples I've seen do it the the way of the first example, why does that not work for me?

My problem actually showed up when I tried to pass an array of type long in the same way as the first example and it wouldn't run because it said the data type that I was trying to pass into the function was not what the function was expecting, even though both of them were an array of type long.

I suspect the answer to the above simplified examples will give me the answer to the array problem. Thanks,

Adrian

[arskiracer]

I just realized I've been calling the ByReferenceExample a function, when it's actually a sub. It used to be a function, but I changed it to a sub to see if I could find any difference between the two and there doesn't seem to be any except that functions return something and subs do not. And functions don't seem to show up in my list of Macros in Excel either.

[Andy Pope]

The brackets are causing the problem.

Please Login or Register  to view this content.

The help says

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist. If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded.

And for more detail read,
http://www.cpearson.com/Excel/byrefbyval.aspx

[Norie]

Adrian

The problem is the brackets around MyLong in the first code.

They aren't there in the second code so you get the expected result.

[arskiracer]

Things look great now. I was thinking the difference was when I started using multiple functions in the same module, but I must have slipped back into my old C++ habits along the way somewhere and didn't even realize it.

Andy, thanks for the explanation of Call, I've been wondering why some people's code uses it and others don't, I should have looked it up. Things make a lot more sense now.

Thanks for the help