Hello, I've been stumbling my way through learning VBA and lately I've been having issues passing variables by reference to functions. In the past, I've always used individual modules as individual functions to my main sub. I recently learned that you can have multiple functions within a single module and so I started programming as such, but passing variables by reference does not seem to work the same.
Option Explicit
Sub Practice()
Dim MyLong As Long
MyLong = 5
MsgBox (MyLong)
ByReferenceExample (MyLong)
MsgBox (MyLong)
End Sub
Sub ByReferenceExample(ByRef ChangeValue As Long)
ChangeValue = 15
MsgBox (ChangeValue)
End Sub
I expected the above example to display three message boxes: 5, 15 and 15, but the three message boxes display 5, 15, 5. Most examples that I've seen about how to pass arguments by reference use the method above, but they get different results. One example I saw, which works is below:
Option Explicit
Sub Practice()
Dim MyLong As Long
MyLong = 5
MsgBox (MyLong)
ByReferenceExample ChangeValue:=MyLong
MsgBox (MyLong)
End Sub
Sub ByReferenceExample(ByRef ChangeValue As Long)
ChangeValue = 15
MsgBox (ChangeValue)
End Sub
So changing the "ByReferenceExample (MyLong)" to "ByReferenceExample ChangeValue:=MyLong" causes the variable to be passed by reference, but the majority of examples I've seen do it the the way of the first example, why does that not work for me?
My problem actually showed up when I tried to pass an array of type long in the same way as the first example and it wouldn't run because it said the data type that I was trying to pass into the function was not what the function was expecting, even though both of them were an array of type long.
I suspect the answer to the above simplified examples will give me the answer to the array problem. Thanks,
Adrian
Bookmarks