using ByVal in sub procedure

[greg.begin]

I heard if I use ByVal in my procedure, for example

Sub Test(byVal X as long)


code'


end sub


that my code will be executed faster. especially if I have a lot of code to go through. Is this true, if so, how can i use it on a userform button?


thanks

 

[Luke M]

From the help file:

by value

A way of passing the value of an argument to a procedure instead of passing the address. This allows the procedure to access a copy of the variable. As a result, the variable's actual value can't be changed by the procedure to which it is passed.


So, the speed gain is because you're not changing the variable (which takes memory) but just passing along the note. You might want to also read this:

http://www.cpearson.com/excel/byrefbyval.aspx

 

[greg.begin]

Luke,

that is the same source i previously used before coming here. thats funny. i guess my real question is, how to i declare a by value in my code? do i remove option explicit in my code?

suggestions?

 

[Luke M]

Copy of Chip's example:

[pre]

Sub CallingProcedure()
Dim A As Long
Dim B As Long
A = 123
B = 456
Debug.Print "BEFORE CALL = A: " & CStr(A), "B: " & CStr(B)
CalledProcedure X:=A, Y:=B
Debug.Print "AFTER CALL =  A: " & CStr(A), "B: " & CStr(B)
End Sub

Sub CalledProcedure(ByRef X As Long, ByVal Y As Long)
X = 321
Y = 654
End Sub

[/pre]
In the CalledProcedure, the variable Y is being defined as a Long. Thus, there's no need to remove "option explicit" as you can only have a ByVal is you're defining a variable. The ByVal modifier is simply telling the code that you want to treat the incoming variable as just a value. Does that help?

 

[SirJB7]

@greg.begin

Hi!

As my two cents for what Luke M explained very didactical and regarding your question about removing 'Option Explicit' statement, I'd like to add that always more that analyzing if remove it or not, it's a very good, safe and healthy practice to keep it. It's highly recommended to configure your VBA from Tools, Options, Editor, and set the second field to on for requiring variable declaration.

Regards!