Excel VBA: The Little-known secrets of ByVal and ByRef
Рет қаралды 45,204
Күн бұрынExcel VBA: The Little-known secrets of ByVal and ByRef
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)
Excel VBA Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Related Articles
The Complete Guide to the VBA Sub(bit.ly/2P5Kq6l)
How VBA Objects work in Memory(bit.ly/2SBOfSN)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
View the Properties Window: F4
@Excelmacromastery 4 жыл бұрын
Enjoy the video. Remember that it's always better to use ByVal where possible as it prevents unintended changes.
@Victor-ol1lo 4 жыл бұрын
Thanks for the excellent video Paul ! Thumbs Up !!
@faanmuller4569 2 жыл бұрын
Mr. Kelly's contributions to making Excel/VBA popular and understandable are unique and will never be equalled. Bless you, Sir!
@panayiotisyannopoulos2668 4 жыл бұрын
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
@sandeepkothari5000 4 жыл бұрын
At last, I get to know the difference between the 2 terms. Thanks a lot Paul.
@Excelmacromastery 4 жыл бұрын
You're welcome Sandeep
@shubhambangad1011 3 жыл бұрын
Very informative and guided with simple examples. Thanks for clearing this out.
@Gougligou 2 жыл бұрын
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)
@iincitr 4 жыл бұрын
Thank you for your very clear explanation.
@brettnelson1592 4 жыл бұрын
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 4 жыл бұрын
Glad you liked it Brett
@jerkorulez Жыл бұрын
Thanks Paul. This helps me a lot!
@mike_case 4 жыл бұрын
Thank you Paul for this explanation :) Once again high level ;)
@CeliaAlvesSolveExcel 4 жыл бұрын
Excellent lesson, Paul! I was not aware of some of the details related to passing arrays and connections. Thank you.
@Excelmacromastery 4 жыл бұрын
Thanks Celia. Glad you liked it.
@blauerbaer8742 4 жыл бұрын
Hello your videos and the homepage helped me a lot. Keep it up bro Greetings from germany
@walerij 4 жыл бұрын
Привет из России. Большое спасибо за видео. Всё очень хорошо объяснено и разделено по темам. Так держать!
@joaocustodio2094 4 жыл бұрын
Another gem of knowledge. Thanks very much Paul.
@Excelmacromastery 4 жыл бұрын
You're welcome Joao
@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!!
@neetshil1434 3 жыл бұрын
After so long got some valued explanation for interview purpose and for real life project too. Thank you !!
@Excelmacromastery 3 жыл бұрын
Glad you liked it
@wayneedmondson1065 4 жыл бұрын
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 4 жыл бұрын
Thanks Wayne.
@iamjojo999 4 жыл бұрын
really nice video,by far the most understandable explanation of difference between byval and by ref I’ve ever seen
@Excelmacromastery 4 жыл бұрын
Awesome, thank you!
@michaelmaguire6709 4 жыл бұрын
Very well explained. I've been using VBA on and off for >20 years and didn't know the Shift+F9 trick! Cool
@Excelmacromastery 4 жыл бұрын
Glad it was helpful!
@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.
@vivvpprof 4 жыл бұрын
11:14 I learned a new trick, thank you! :)
@rrrprogram8667 4 жыл бұрын
Seriously... U are VBA mann.... Simply FANTASSTICCCC.... Absolutely loved it...thanks for sharing ur exceptional knowledge paull...
@Excelmacromastery 4 жыл бұрын
You're welcome
@JulioGarciaLx 4 жыл бұрын
Very useful summary. Thank you.
@Excelmacromastery 4 жыл бұрын
You're welcome Julio.
@kdjamal9525 3 жыл бұрын
Thanks you are very very master
@Anomander1 2 жыл бұрын
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.
@Leo_Russo 3 жыл бұрын
Awesome!!!
@micomc 3 жыл бұрын
Best channel
@MurphyMittens Жыл бұрын
My epiphany @ 2:30 Thank you!
@maddinenirajeshbabu3143 4 жыл бұрын
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 KZfaq
@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" ?
@db7erry 3 жыл бұрын
User Defined Types are like arrays in that they cannot be passed ByVal. UDTs can only be passed ByRef.
@houstonvanhoy7767 3 жыл бұрын
Sir Paul, do you have a video dedicated to VBA keyboard shortcuts? Or would you create a new video for that?
@Excelmacromastery 3 жыл бұрын
Hi Houston. There are shortcuts at the bottom of the description below the video.
@moayyadalkeddeh5764 4 жыл бұрын
❤️❤️
@jmathew6988 2 жыл бұрын
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 2 жыл бұрын
Yes, you can do it.
@thearchibaldtuttle 4 жыл бұрын
Nice! ByRef can lead to very dirty code when subs are used to change data. Better use functions that pass your result back.
@Excelmacromastery 4 жыл бұрын
Exactly!
@gabiold 3 жыл бұрын
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.
@SolidSnake59 4 жыл бұрын
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?
@brianburnside5949 4 жыл бұрын
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 4 жыл бұрын
Thanks Brian.
@averagebodybuilder 2 жыл бұрын
ByRef is faster in terms of execution. Is that correct?
@sum1razz Жыл бұрын
Does byVal and byRef make any difference if we use a function instead of sub?
@rrrprogram8667 4 жыл бұрын
But one more thing... Can u please elaborate... When do we use this in the real world scenario
@Excelmacromastery 4 жыл бұрын
Use ByVal where possible. It prevents us accidentally changing a variable in a sub/function.
@Akens888 4 жыл бұрын
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?
@cristtos 4 жыл бұрын
Can you actually change the behavior and have ByVal as the Default?
@Excelmacromastery 4 жыл бұрын
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.
@tughanozsezer9332 4 жыл бұрын
I am confused about the final condition. No data has passed to the second prosedure on your example. 🙃
@Excelmacromastery 4 жыл бұрын
It passes the collection variable. Then it sets the variable to reference a new collection. Is that what you mean?
@tughanozsezer9332 4 жыл бұрын
@@Excelmacromastery yes 👍
@iankr 3 жыл бұрын
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 3 жыл бұрын
Like when you pass arguments to an excel function. The values of the arguments are not usually changed.
@gabiold 3 жыл бұрын
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.
@markcuello5 2 жыл бұрын
Help me
@MercuryAX6 2 жыл бұрын
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.