1

i was playing with some VBA when i noticed this (for me) strange behaviour. I know that by default, Excel VBA passes arguments by reference, so i would expect from both 'ways of calling' the sub TestSub the same result. Why is that when i call it with parenthesis it behaves like the argument was passed ByVal?

My expectations:

TestSub (intSomeNumber) 'prints 15
TestSub intSomeNumber   'prints 15

Reality:

TestSub (intSomeNumber) 'prints 10
TestSub intSomeNumber   'prints 15

Here is the code:

Sub Macro_1()
    Dim intSomeNumber As Integer: intSomeNumber = 10

    ' If i'll call TestSub without parenthesis
    ' the value of intSomeNumber will be changed to 15 as expected
    TestSub intSomeNumber

    ' If i'll call TestSub with parenthesis
    ' the value won't change, so the result printed via Debug.Print will be 10
    'TestSub (intSomeNumber)

    Debug.Print CStr(intSomeNumber)
End Sub

Private Sub TestSub(argIntSomeNumber As Integer)
    argIntSomeNumber = 15
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
eren
  • 708
  • 8
  • 20
  • 5
    You only need to use brackets when the function is on the right hand side of an assignment expression. Your use of brackets is causing your parameter to be evaluated as an expression which yields a value hence the difference in behaviours you are seeing. This is the normal correct behaviour of VBA. – freeflow Mar 24 '19 at 19:59
  • 1
    Chip Pearson has a good [explanation](http://www.cpearson.com/excel/byrefbyval.aspx) of `ByRef` vs `ByVal`: "Even if a called procedure has declared its parameters as `ByRef`, you can force those to be `ByVal` by enclosing each argument within parentheses." – BigBen Mar 24 '19 at 20:07
  • 1
    https://blogs.msdn.microsoft.com/ericlippert/2003/09/15/what-do-you-mean-cannot-use-parentheses/ – Noodles Mar 25 '19 at 02:09
  • @Sam Don't think that that's the point of my 'problem'. Freeflows and noodles comments explained it i guess (i would accept either of those as an answer) i do understand now why and when to use parenthesis in VBA when calling subs/functions. If it's duplicate of somthing i'd say it's closer to this one: https://stackoverflow.com/questions/14902134/cannot-use-parentheses-when-calling-a-sub-error-800a0414-vbs – eren Mar 25 '19 at 12:17

0 Answers0