Excel VBA: The Little-known secrets of ByVal and ByRef

Enjoy the video.

Remember that it's always better to use ByVal where possible as it prevents unintended changes.

Thanks Paul. This helps me a lot!

Fantastic. But i have problems understanding the connection when passing the variable to one sub to another. In the first example you are passing the variable "total" and the new sub is declearing "a" as long. How does it know that "a" should be total? What is i have other long variable in my new sub "Calc" ?

Thanks, helped me building dictionary's with other dictionary's as value. If I call a function and pass a dictionary, i do it with ByVal now to create multiple dictionary's depending on each other values.

my god Thank you I finally was able to get to the bottom of the confusing pair of concept!! 😂 And I meant more comprehensively, in different contexts!!

Does byVal and byRef make any difference if we use a function instead of sub?

Hi. Thanks for this tutorial. I have a question to ask. Is it valid to pass objects e.g. Pivot Table, Range or Worksheet in an argument? I hope that you don't mind my asking. Would appreciate any insights.

Help me

Setting a new object loses all the content of the original. If ByVal worked on the object itself rather than just the pointer, then it’d be easy to copy the object as in other modern languages. Just another reason VBA is the worst programming language. It just lacks consistency in so many ways.

Mr. Kelly's contributions to making Excel/VBA popular and understandable are unique and will never be equalled. Bless you, Sir!

One point not mentioned, but worth addressing, is what happens when a sub has more than one parameter. If you ever redo this lesson, you might consider adding this point to it.
Sub proc (byval a, b, c) ——> when proc() returns, a won't have changed, but b and c could be modified since they get passed byRef.

If the programmer does not want b and c to change, then proc() has to be defined as
Sub proc (byval a, byval b, byval c)

Just checked it out, i know it are the parenthesis that makes the function parameters needed to be set as a ByRef. At least, that is how it works on my job.
When i do it at home, it is the other way around

At home:
Calc total -> gives 100 at the end
Calc (total) -> gives 1 at the end


At Work:
Calc total -> gives 1 at the end

Calc (total) -> gives 100 at the end

At home i use office 2013 and at work i use offce 365
Very annoying that MicroSoft changed that between versions.

It is good to check which one you need and stick to it.

ByRef is faster in terms of execution. Is that correct?

Best channel

Sir Paul, do you have a video dedicated to VBA keyboard shortcuts? Or would you create a new video for that?

Very informative and guided with simple examples. Thanks for clearing this out.

Thanks you are very very master

After so long got some valued explanation for interview purpose and for real life project too. Thank you !!

User Defined Types are like arrays in that they cannot be passed ByVal. UDTs can only be passed ByRef.

This is great, but I'm trying to see the point in passing something to another sub but NOT to change its value. I must be missing something fundamental. Perhaps I need to see a real world example of both types.

Awesome!!!

really nice video,by far the most understandable explanation of difference between byval and by ref I’ve ever seen

Very well explained. I've been using VBA on and off for >20 years and didn't know the Shift+F9 trick! Cool

So basically there is no point in passing collection if we are going to make new one anyway, right?
And how does it work that we can pass array ByVal as a Variant? Or should I ask why cannot we pass normal array ByVal?

Привет из России. Большое спасибо за видео. Всё очень хорошо объяснено и разделено по темам. Так держать!

I translated a python program into vba yesterday and it wasn't working correctly. I fretted over it for hours. And for some reason it dawned on me that it was a byval byref issue. Unfortunately I didn't find this video until after I resolved the issue. But great explanation.

Can you actually change the behavior and have ByVal as the Default?

I am confused about the final condition. No data has passed to the second prosedure on your example. 🙃

Hello your videos and the homepage helped me a lot. Keep it up bro
Greetings from germany

❤️❤️

Excellent lesson, Paul! I was not aware of some of the details related to passing arrays and connections. Thank you.

Another gem of knowledge.
Thanks very much Paul.

Nice! ByRef can lead to very dirty code when subs are used to change data. Better use functions that pass your result back.

Very useful summary. Thank you.

Thanks for the excellent video Paul ! Thumbs Up !!

Hi Paul.. thanks for the video and good new information (for me anyway). Also.. I like that SHIFT+F9 keyboard for Quick Watch and then Add for the Watch window.. didn't know about that sequence before viewing this video. Always something new and interesting at Excel Macro Mastery. Thanks for all the great tips that you generously share week after week! Thumbs up!!

But one more thing... Can u please elaborate... When do we use this in the real world scenario

Seriously... U are VBA mann.... Simply FANTASSTICCCC.... Absolutely loved it...thanks for sharing ur exceptional knowledge paull...

Thank you for your very clear explanation.

Thank you Paul for this explanation :) Once again high level ;)

I would like to thank you for your amazing good videos. You have it to be a teacher, shows a person that has searched enough to be able to pass knowledge in an easy way

At last, I get to know the difference between the 2 terms. Thanks a lot Paul.

Keep up these awesome VBA tutorials!!! I never know there was so many functions of ByRef! And thanks for the tip on how to look into an array with Watch :)