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)
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 :)
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.
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
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!!
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.
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!!
Nice video thanks in advance I have one doubt how to compare one to many relations in matching amounts in reconciliation process if you can possible can create one video uploaded into TH-cam
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.
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.
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?
You can pass many values byRef if you have a sub that needs to make many changes to the variables in the calling sub, instead of returning the result via a function (which is often more clear but afaik you can only return a single item from a function). Even though byRef is the default it is worth adding to the sub to make it clear the sub intends to modify the parameters. A real application for byRef might be for example creating a list of all files in a all subfolders of a given directory by using a recursive function to call itself... speaking of which next video on recursion Paul now that you have covered byRef and byVal?
It depends. If you want to change multiple variables, ByRef is easier. If the naming indicate the action (eg: editSomething), it is not messy as you expect the sub to change it's parameter.
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.
For example, you pass a date to a function that returns it in some formatted string. It may manipulate the value, but you don't expect and usually don't want the origial value to be changed. Now assume you want an increaseDate sub which adds 10 days to the given date, and you want it to get modified in the caller. You could write a function though to return the increased date, but this is just another possibility.
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.
Enjoy the video.
Remember that it's always better to use ByVal where possible as it prevents unintended changes.
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)
Mr. Kelly's contributions to making Excel/VBA popular and understandable are unique and will never be equalled. Bless you, Sir!
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 :)
Glad you liked it Brett
At last, I get to know the difference between the 2 terms. Thanks a lot Paul.
You're welcome Sandeep
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 for the excellent video Paul ! Thumbs Up !!
Привет из России. Большое спасибо за видео. Всё очень хорошо объяснено и разделено по темам. Так держать!
Thanks Paul. This helps me a lot!
Thank you for your very clear explanation.
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.
Very informative and guided with simple examples. Thanks for clearing this out.
After so long got some valued explanation for interview purpose and for real life project too. Thank you !!
Glad you liked it
Seriously... U are VBA mann.... Simply FANTASSTICCCC.... Absolutely loved it...thanks for sharing ur exceptional knowledge paull...
You're welcome
really nice video,by far the most understandable explanation of difference between byval and by ref I’ve ever seen
Awesome, thank you!
Very well explained. I've been using VBA on and off for >20 years and didn't know the Shift+F9 trick! Cool
Glad it was helpful!
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
Thanks you are very very master
Best channel
Hello your videos and the homepage helped me a lot. Keep it up bro
Greetings from germany
Another gem of knowledge.
Thanks very much Paul.
You're welcome Joao
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!!
Thanks Wayne.
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.
My epiphany @ 2:30
Thank you!
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!!
Very useful summary. Thank you.
You're welcome Julio.
11:14 I learned a new trick, thank you! :)
Excellent lesson, Paul! I was not aware of some of the details related to passing arrays and connections. Thank you.
Thanks Celia. Glad you liked it.
ByRef is faster in terms of execution. Is that correct?
Thank you Paul for this explanation :) Once again high level ;)
Sir Paul, do you have a video dedicated to VBA keyboard shortcuts? Or would you create a new video for that?
Hi Houston.
There are shortcuts at the bottom of the description below the video.
Nice video thanks in advance I have one doubt how to compare one to many relations in matching amounts in reconciliation process if you can possible can create one video uploaded into TH-cam
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.
Yes, you can do it.
Awesome!!!
User Defined Types are like arrays in that they cannot be passed ByVal. UDTs can only be passed ByRef.
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.
Thanks Brian.
Does byVal and byRef make any difference if we use a function instead of sub?
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?
But one more thing... Can u please elaborate... When do we use this in the real world scenario
Use ByVal where possible. It prevents us accidentally changing a variable in a sub/function.
You can pass many values byRef if you have a sub that needs to make many changes to the variables in the calling sub, instead of returning the result via a function (which is often more clear but afaik you can only return a single item from a function). Even though byRef is the default it is worth adding to the sub to make it clear the sub intends to modify the parameters.
A real application for byRef might be for example creating a list of all files in a all subfolders of a given directory by using a recursive function to call itself... speaking of which next video on recursion Paul now that you have covered byRef and byVal?
Nice! ByRef can lead to very dirty code when subs are used to change data. Better use functions that pass your result back.
Exactly!
It depends. If you want to change multiple variables, ByRef is easier. If the naming indicate the action (eg: editSomething), it is not messy as you expect the sub to change it's parameter.
Can you actually change the behavior and have ByVal as the Default?
No. That would make the code more confusing though. it's better to use either ByVal or ByRef so it's obvious which one is being used.
❤️❤️
I am confused about the final condition. No data has passed to the second prosedure on your example. 🙃
It passes the collection variable. Then it sets the variable to reference a new collection. Is that what you mean?
@@Excelmacromastery yes 👍
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.
Like when you pass arguments to an excel function. The values of the arguments are not usually changed.
For example, you pass a date to a function that returns it in some formatted string. It may manipulate the value, but you don't expect and usually don't want the origial value to be changed.
Now assume you want an increaseDate sub which adds 10 days to the given date, and you want it to get modified in the caller.
You could write a function though to return the increased date, but this is just another possibility.
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.
not very clear for beginners
Vba is the worst
Why watch videos about it then?😀😀