Excel VBA Introduction Part 43.1 - ByRef and ByVal

By Andrew Gould

[kebincui]

Thank you Andrew. This video helps me to clear my long time mist in understanding the difference between byRef and byVal. Thank you for sharing your wisdom by explaining this clearly with samples❤👍🌹

[cringerfringe]

If I would have watched this video this morning I wound have wasted 4 hours today pulling my hair out. Best coding teacher online.

[scotolivera]

You are changing peoples life with those videos

[querrythis]

Learnt so much from you. So good at explaining the my current level. thanks!

[memelvin]

Thanks! Can't tell you how many phone interviews where I've stumbled on this difference! Your graphic helped greatly.

[forzaalgiers]

Nice to see you (Hear you ) again. We miss your videos man !!!. Thanks for the video, it's Great

[salvatorescuderi]

Hi A. Your explanation about memory (and is excel modellization) is very direct and easy. Now i understood how values and their storage in memory are treated.
Thank

[zulfikarvirani]

I really did missed your videos sir, you are the best and i really enjoy them. I can't believe I've learnt so much and actually have applied it. Thank God you came back with more video lessons. Thank you so much sir. Please keep on making these videos.

[donbogdala]

Its been a while, but glad that you returned with another lesson! I really look forward to your videos and check this page on a daily basis, They are the best! I wouldn't be at the level that I am without Wiseowl Tutorials! You're the best Andrew!

[michellguzelgul]

Your your videos are the best by far, I have been binge watching. I would Love a video on the SET key So confusing.

[rajusinghtezu]

Thank you!!!..now i understood the concept behind the byval and byref..

[oysteinoren]

So glad you made another one, you are definitely the best!

[MouradBENKADOUR]

Nice Tutorial! But I didn't really understand the error in 23:08 minutes, because if you declare an Integer Array (Dim Mins() as Integer) it will work well. But you can use ByRef as Integer to change an Array as Variant. But in other Hand you have to use a Variant Array to store String after Integer. Thanks!

[easyspeak]

excellent lesson.. How does it relate to temporary, private or public variables passing between modules or the fact that a pair of temp variables rather than byref or byval..? Thx tho..😁

[jmathew]

Thank you! 🙏

"The Force Awakens" ought to be in 4th position. 😊

Sir, I have two question if you could kindly answer.

(1) Can an Argument and Parameter variable inside Caller and Called subroutines respectively be identically named, or is there any downside to this practice?

(2) Given that a called procedure only needs to refer a parameter (without changing it's value), but there also is a need to minimize memory usage. Is it ideal to choose ByRef over ByVal?

I hope that you do not mind my asking. Would appreciate any insights.

[rustamtau]

Great Thanks for the video. But i still have a question regarding the task i am tackling now.
How to pass the OptionButton value to the main Sub?
I have a macro that picks some rows in a table based on criterium. Then i want to allow the user select where he wants to place the result. So i made simple UserForm with 2 OptionButtons. When the user clicks "ok", i somehow need to pass my variable "i" that holds the value of selected OptionButton to the main Sub (if OptionButton1=1 Then i=1, if OptionButton2=1 Then i=2)
But how to do it?

[leeeric]

Thanks for your video. In 23:10, u just show error occurred because data type don't match if byref is applied, however, could u explain a little more details why? And why using byval can solve the problem ? Thanks.

[eziola]

For the Mins array did you mean to type:
Mins = Range("B2", Range("B2").End(xlDown)) ? <-Not Range("B1").End(xlDown)....

[bunc]

interesting...if in SetANumber sub you change First line intoconst OriginalNum as integer = 5at the end  the value stays the same, of course!Even if in IncreaseANumber sub you explicitly write byref... no compile errors :)

[bunc]

Sub test()

Dim temp As Integer

temp = 35

inc temp

Debug.Print "Temp is now: " & temp


End Sub

Sub inc(num As Integer)
num = num + 1

Debug.Print "Num is now: " & num
End Sub

Result:
Num is now: 36
Temp is now: 36

change inc temp to inc (temp) aka just add parenthesis

Result:
Num is now: 36
Temp is now: 35

WHAT IS GOINNG ON??