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

Показать описание

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

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

Related Articles

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

Рекомендации по теме

Комментарии

Enjoy the video.

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

Excelmacromastery

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)

Gougligou

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

faanmuller

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

alterchannel

Thanks for the excellent video Paul ! Thumbs Up !!

Victor-ollo

Thank you for your very clear explanation.

iincitr

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

walerij

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.

christianb

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

shubhambangad

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

sandeepkothari

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

brettnelson

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

rrrprogram

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

panayiotisyannopoulos

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

neetshil

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.

Anomander