Excel VBA Introduction Part 43.1 - ByRef and ByVal 

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!

Happy to hear that you've found the videos useful! Thanks for taking the time to leave a comment and for watching!

You're very welcome, thanks for watching!

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

All true + 1 !

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 RU-vid, we all could learn much more about Excel VBA.

You're very welcome, thanks for watching!

Thanks for watching!

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?

You're welcome, Raju! Happy to hear that you found the video useful and thank you for watching!

Thanks!

Happy to hear that it helped, thanks for watching!

Thank you Muhammed!

Thanks Vijay, glad you enjoyed it!

Thanks!

Hi! There are some good answers here stackoverflow.com/questions/51952304/return-a-value-from-a-userform I hope it helps!

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!

Huh, interesting!

Thx :)

yes i am :D thx :)