ByRef vs ByVal

When passing arguments to a procedure (sub or function) the default will be ‘byRef’ in Excel VBA, thus you don’t have to write ‘byRef’ before the variable. If you want to pass ByVal you have to explicitly write ‘ByVal’ before the variable. When using ByVal you are only passing a copy of the argument to the function or sub and the original value is not changed. When passing arguments you always want to declare the variable as this will help your code run faster. If you leave out the declaration. Excel will assume as variant which takes longer to process. About 2.5 times longer according to the tests below.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s