@@WiseOwlTutorials absolutely. Huge help. Lots of confusion on utilizing functions and passing parameters between them and subs. You’re making the world a better place. Keep up the great work, I’m looking forward to moving on to your other programming language lessons after I get more competent in VBA.
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❤👍🌹
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
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.
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!
Andrew, make sure you let us know when and where the book is available. I would love to purchase a copy. If it is anything like what you have presented on TH-cam, we all could learn much more about Excel VBA.
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!
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..😁
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.
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 :)
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?
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.
Hi Lee! When you assign a range to an array, the data type of the values is Variant and the subtype is determined by the values of the cells (in this case it's Double because the cells all contain numbers). If we pass the one of the array elements into another procedure ByRef, the data type of the parameter needs to match. In this example, it doesn't - the m parameter has a data type of Integer. When passing information ByRef the data types need to match because you're pointing to the same location in memory. When passing ByVal you make a copy of the original value in a separate location in memory which can have whatever data type you like (VBA performs the implicit conversion of Variant/Double to Integer). You can solve the problem in at least two other ways: 1) Explicitly convert the value of the array element before passing it to the ByRef parameter Mins(i, 1) = MinsToHours(CInt(Mins(i, 1))) 2) Change the data type of the ByRef parameter to Variant Function MinsToHours(m As Variant) As String I hope that helps!
@@WiseOwlTutorials Many Thanks for ur fast response and help. I think i need some time to digest and practice what you just have mentioned. Again, Thanks !
@@WiseOwlTutorials Thanks for your two solutions last time: mins(i,1)=minstohours(CINT(mins(i,1)))and function minstohours(m as variant) as string. I have tried both of them and of course they work. Apart from them, I also try below : In your last reply, you said in the example of the video using Byref don't work because the values of cell is in Double datatype while the parameter "m" in the function is defined as integer, they need same data type because in Byref they share the same memory location, so I also try this : function minstohours(m as double) as string. However datatype mismatch error pop up when I run the program, is there something I get wrong? Secondly, in your last reply, u said when passing Byval, VBA performs the implicit conversion of Variant/Double to integer. So I believe there is a change in data type operation in Byval, that is the Data type from the call procedure change to the Data type of the parameter in that function when Byval is applied. If this assumption is true, what happens if the Data type from the call procedure is in string in this example, what then the result in the ByVal function would be, I also have tried, again errors window is also popped up. So did it imply that the Byval performs the conversion of Variant/Double to integer only? Thanks.
@@leeeric6292 Hi Lee! The data type of the value in the array is Variant with a subtype of Double - not quite the same thing as a Double. The subtype of Double is assigned at run time when we assign the range to the array. When the project is compiled (when the error message appears), the function doesn't know what subtype the variant will have - it only knows that the value being passed to the Function will be a Variant while the function expects an Integer - a Variant and an Integer can't both occupy the same space in memory at the same time, hence the error. Secondly, yes, when passing ByVal, the data type of the copy of the value is coerced into the data type of the parameter if it is possible to do so. You can't convert the String "elephant" into an Integer so this would cause a Runtime Error when the code reaches the value which can't be converted. Note that this is different to the Compile Error that occurs when the data type of the ByRef parameter doesn't match the type of the value being passed to it. I hope that helps!
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??
If I would have watched this video this morning I wound have wasted 4 hours today pulling my hair out. Best coding teacher online.
Ahh I hate when that happens! Hope the video helped you to find a solution and thanks for watching!
@@WiseOwlTutorials absolutely. Huge help. Lots of confusion on utilizing functions and passing parameters between them and subs.
You’re making the world a better place. Keep up the great work, I’m looking forward to moving on to your other programming language lessons after I get more competent in VBA.
@@cringerfringe2285 Comments like this make it worth doing, thanks!
Learnt so much from you. So good at explaining the fundamentals....... my current level. thanks!
Happy to hear that you've found the videos useful! Thanks for taking the time to leave a comment and for watching!
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❤👍🌹
You're very welcome, thanks for watching!
You are changing peoples life with those videos
Thanks! Can't tell you how many phone interviews where I've stumbled on this difference! Your graphic helped greatly.
Nice to see you (Hear you ) again. We miss your videos man !!!. Thanks for the video, it's Great
WiseOwlTutorials that's going to be a must but for me man.
I've learned a lot from your vids and have made some sick apps in Excel. Thanks!!
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
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.
All true + 1 !
Excellent explanation! Thanks a lot!
You're very welcome, thanks for watching!
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!
Andrew, make sure you let us know when and where the book is available. I would love to purchase a copy. If it is anything like what you have presented on TH-cam, we all could learn much more about Excel VBA.
Your your videos are the best by far, I have been binge watching. I would Love a video on the SET key word!!!!!! So confusing.
Thank you!!!..now i understood the concept behind the byval and byref..
You're welcome, Raju! Happy to hear that you found the video useful and thank you for watching!
As usual: great tutorial!
Thank you. great explanation!!!
Thanks for watching!
So glad you made another one, you are definitely the best!
Sounds fantastic, really looking forward to buy your VBA book!
I'm sure your book will be great! Is there a mailing list I can join to be advised when it's released?
Good example.
You're DA REAL MVP !
thanks clarified some points
Happy to hear that it helped, thanks for watching!
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!
Finally understood this , many thanks
Great stream
Thanks!
excellent
Thank you Muhammed!
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..😁
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.
really good knowledge, thanks!
The best !
I love your videos
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 :)
Huh, interesting!
Great
Thanks!
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?
Hi! There are some good answers here stackoverflow.com/questions/51952304/return-a-value-from-a-userform
I hope it helps!
nice 👍🏻👍🏻👍🏻👍🏻👍🏻
Thanks Vijay, glad you enjoyed it!
For the Mins array did you mean to type:
Mins = Range("B2", Range("B2").End(xlDown)) ?
Thanks so much
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.
Hi Lee! When you assign a range to an array, the data type of the values is Variant and the subtype is determined by the values of the cells (in this case it's Double because the cells all contain numbers). If we pass the one of the array elements into another procedure ByRef, the data type of the parameter needs to match. In this example, it doesn't - the m parameter has a data type of Integer. When passing information ByRef the data types need to match because you're pointing to the same location in memory. When passing ByVal you make a copy of the original value in a separate location in memory which can have whatever data type you like (VBA performs the implicit conversion of Variant/Double to Integer). You can solve the problem in at least two other ways:
1) Explicitly convert the value of the array element before passing it to the ByRef parameter
Mins(i, 1) = MinsToHours(CInt(Mins(i, 1)))
2) Change the data type of the ByRef parameter to Variant
Function MinsToHours(m As Variant) As String
I hope that helps!
@@WiseOwlTutorials Many Thanks for ur fast response and help. I think i need some time to digest and practice what you just have mentioned. Again, Thanks !
@@leeeric6292 My pleasure Lee! And yes, ByRef and ByVal aren't intuitive but practice certainly helps. good luck!
@@WiseOwlTutorials Thanks for your two solutions last time: mins(i,1)=minstohours(CINT(mins(i,1)))and function minstohours(m as variant) as string. I have tried both of them and of course they work. Apart from them, I also try below :
In your last reply, you said in the example of the video using Byref don't work because the values of cell is in Double datatype while the parameter "m" in the function is defined as integer, they need same data type because in Byref they share the same memory location, so I also try this : function minstohours(m as double) as string. However datatype mismatch error pop up when I run the program, is there something I get wrong?
Secondly, in your last reply, u said when passing Byval, VBA performs the implicit conversion of Variant/Double to integer. So I believe there is a change in data type operation in Byval, that is the Data type from the call procedure change to the Data type of the parameter in that function when Byval is applied. If this assumption is true, what happens if the Data type from the call procedure is in string in this example, what then the result in the ByVal function would be, I also have tried, again errors window is also popped up. So did it imply that the Byval performs the conversion of Variant/Double to integer only?
Thanks.
@@leeeric6292 Hi Lee! The data type of the value in the array is Variant with a subtype of Double - not quite the same thing as a Double. The subtype of Double is assigned at run time when we assign the range to the array. When the project is compiled (when the error message appears), the function doesn't know what subtype the variant will have - it only knows that the value being passed to the Function will be a Variant while the function expects an Integer - a Variant and an Integer can't both occupy the same space in memory at the same time, hence the error.
Secondly, yes, when passing ByVal, the data type of the copy of the value is coerced into the data type of the parameter if it is possible to do so. You can't convert the String "elephant" into an Integer so this would cause a Runtime Error when the code reaches the value which can't be converted. Note that this is different to the Compile Error that occurs when the data type of the ByRef parameter doesn't match the type of the value being passed to it.
I hope that helps!
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??
Thx :)
yes i am :D thx :)
Speak too fast