Procedures and Parentheses

When calling a Function, you enter the function followed by the arguments in parentheses:

myFunction(argument)

When calling a Sub procedure, it may be tempting to also add parentheses out of habit or error, and it works in most cases when there is only one argument, but sometimes you will encounter errors that seem incomprehensible...

Simple Example

In this example, the procedure increments the variable passed as an argument:

Sub increment(value)
    value = value + 1
End Sub

Sub test()

    'Case 1
    value = 5
    increment value
    MsgBox value 'Returns 6

    'Case 2
    value = 5
    increment (value)
    MsgBox value 'Returns 5

End Sub

In the first case, it works fine, the value variable has increased by 1, but in the second case with parentheses, the value variable remains unchanged... Why?

Remember that when nothing is specified, the arguments are of type ByRef, so the reference to the value is transmitted, which allows the value variable to be modified here.

In the first case, the variable is passed directly as an argument, so it works correctly:

increment value

In the second case, the variable is not passed directly as an argument... Excel must first calculate the result of the content in parentheses, here 5, and then pass this result as an argument:

increment (value)

Instead of passing the reference to the value variable, the value 5 is passed as an argument, which does not modify the value variable.