4

The following code produces different results based on how the ChangeString sub is called.

  • If I use ChangeString (str), the message box returns "First String".
  • If I use ChangeString str or Call ChangeString(str) it returns "Second String".

They all should me equivalent ways to call a sub and pass it an argument, so I expected to get "Second String" every time.

I tried the code on both Excel 2016 and 2021.

Sub TryByRef()

    Dim str As String

    str = "First String"

    ChangeString (str)
    'ChangeString str
    'Call ChangeString(str)

    MsgBox str

End Sub


Sub ChangeString(ByRef s As String)
    s = "Second String"
End Sub
double-beep
  • 5,031
  • 17
  • 33
  • 41
Gabriele
  • 41
  • 2
  • Don't use parenthesis when calling a Sub. `ChangeString str` - If you do, expect problems in some cases. – braX Nov 30 '22 at 02:08
  • https://stackoverflow.com/a/14908329/8422953 – braX Nov 30 '22 at 02:15
  • 2
    The clue it gives you is that it puts a space before the `(` – braX Nov 30 '22 at 02:40
  • 1
    `MsgBox` obeys the same rules: when you don't have or don't care for a procedure's return value, you do `MsgBox str`, without the parentheses. When you need the return value, you do `result = MsgBox(str)`, with the parentheses. `ChangeString` doesn't have a return value, so you do `ChangeString "First String"` – Mathieu Guindon Nov 30 '22 at 03:20
  • 2
    Stuck to the procedure name as in `MsgBox(`, the parens mean "argument list". With a space between, they mean "expression". When you put an _expression_ inside parentheses as an argument, what you're passing `ByRef` is a reference to the _result_ of that expression.... which you don't have a reference to, so `ChangeString` is mutating the result of the `(str)` expression, and `MsgBox str` says `"First String"` because the `str` variable itself was never passed to `ChangeString`. – Mathieu Guindon Nov 30 '22 at 03:26
  • 1
    https://ericlippert.com/2003/09/15/what-do-you-mean-cannot-use-parentheses/ – Lundt Nov 30 '22 at 04:16

1 Answers1

1

The parentheses are converting the original string into a new string. Removing the converting will return the correct result.

Sub TryByRef()

    Dim str As String

    str = "First String"
    Rem Subroutines shou
    ChangeString (str)

    Debug.Print "Call with parentheses str"

    ChangeString str

    Debug.Print "Call without parentheses str"

End Sub


Sub ChangeString(ByRef s As String)

    s = "Second String"

End Sub

Result

TinMan
  • 6,624
  • 2
  • 10
  • 20