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

[@Excelmacromastery]

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

[@Gougligou]

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)

[@faanmuller4569]

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

[@brettnelson1592]

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 :)

[@Excelmacromastery]

Glad you liked it Brett

[@sandeepkothari5000]

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

[@Excelmacromastery]

You're welcome Sandeep

[@Victor-ol1lo]

Thanks for the excellent video Paul ! Thumbs Up !!

[@iincitr]

Thank you for your very clear explanation.

[@jerkorulez]

Thanks Paul. This helps me a lot!

[@shubhambangad1011]

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

[@MurphyMittens]

My epiphany @ 2:30 Thank you!

[@christianb8052]

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.

[@neetshil1434]

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

[@Excelmacromastery]

Glad you liked it

[@panayiotisyannopoulos2668]

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

[@rrrprogram8667]

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

[@Excelmacromastery]

You're welcome

[@iamjojo999]

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

[@Excelmacromastery]

Awesome, thank you!

[@kdjamal9525]

Thanks you are very very master

[@alterchannel2501]

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" ?

[@blauerbaer8742]

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

[@walerij]

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

[@vivvpprof]

11:14 I learned a new trick, thank you! :)

[@joaocustodio2094]

Another gem of knowledge. Thanks very much Paul.

[@Excelmacromastery]

You're welcome Joao

[@michaelmaguire6709]

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

[@Excelmacromastery]

Glad it was helpful!

[@wayneedmondson1065]

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!!

[@Excelmacromastery]

Thanks Wayne.

[@CeliaAlvesSolveExcel]

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

[@Excelmacromastery]

Thanks Celia. Glad you liked it.

[@JulioGarciaLx]

Very useful summary. Thank you.

[@Excelmacromastery]

You're welcome Julio.

[@Anomander1]

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.

[@micomc]

Best channel

[@houstonvanhoy7767]

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

[@Excelmacromastery]

Hi Houston. There are shortcuts at the bottom of the description below the video.

[@mike_case]

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

[@averagebodybuilder]

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

[@maddinenirajeshbabu3143]

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 KZbin

[@db7erry]

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

[@CollDott]

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!!

[@brianburnside5949]

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.

[@Excelmacromastery]

Thanks Brian.

[@SolidSnake59]

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?

[@Leo_Russo]

Awesome!!!

[@sum1razz]

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

[@rrrprogram8667]

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

[@Excelmacromastery]

Use ByVal where possible. It prevents us accidentally changing a variable in a sub/function.

[@LPAtkins]

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?

[@jmathew6988]

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.

[@Excelmacromastery]

Yes, you can do it.

[@thearchibaldtuttle]

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

[@Excelmacromastery]

Exactly!

[@gabiold]

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.

[@cristtos]

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

[@Excelmacromastery]

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.

[@markcuello5]

Help me

[@moayyadalkeddeh5764]

❤️❤️

[@iankr]

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.

[@Excelmacromastery]

Like when you pass arguments to an excel function. The values of the arguments are not usually changed.

[@gabiold]

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.

[@tughanozsezer9332]

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

[@Excelmacromastery]

It passes the collection variable. Then it sets the variable to reference a new collection. Is that what you mean?

[@tughanozsezer9332]

@@Excelmacromastery yes 👍

[@alterchannel2501]

not very clear for beginners

[@MercuryAX6]

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.

Vba is the worst

[@Excelmacromastery]

Why watch videos about it then?😀😀