vba - Type mismatch when passing string variable into Variant parameter, and assigning result of Array function to parameter

Question

I have the following two procedures:

Sub OuterSub()
    Dim s As String
    s = "Lorem ipsum dolor sit amet"
    InnerSub s
End Sub

Sub InnerSub(prm As Variant)
    prm = Array(prm)
End Sub

When I run OuterSub, I get the following error at prm = Array(prm) in InnerSub:

Run-time error '13': Type mismatch

This only happens when I pass in a variable typed as String. Any of the following alternatives doesn't produce the error:

• Defining s in OuterSub as a constant:
  Const s = "Lorem ipsum dolor sit amet"
• Passing in a string literal:
  InnerSub "Lorem ipsum dolor sit amet"
• Defining s in OuterSub as a Variant:
  Dim s As Variant

Defining s as a fixed-length string doesn't help.

Why is this happening? How can I work around it?

---

Update

Declaring a local String variable within InnerSub also doesn't help:

Sub InnerSub(prm As Variant)
    Dim s As String
    s = prm
    prm = Array(s)
End Sub

nor does wrapping the parameter in parentheses:

Sub InnerSub(prm As Variant)
    prm = Array((prm))
End Sub

Answer 1

The parameter definition here:

Sub InnerSub(prm As Variant)

is implicitly ByRef:

Sub InnerSub(ByRef prm As Variant)

This means that any assignments to prm will also be assigned to s in OuterSub. While an array can be assigned to the Variant variable prm, it cannot be assigned to the String variable s in OuterSub.

You can see this in action by defining s As Variant in OuterSub (which has no problem holding an array), and inspecting the value of s after InnerSub has completed.

You can explicitly force a variable to be passed in ByVal:

Sub InnerSub(ByVal prm As Variant)

---

Passing a constant:

Const s = "Lorem ipsum dolor sit amet"

or a string literal:

InnerSub "Lorem ipsum dolor sit amet"

both work, because neither can be passed in ByRef.

Wrapping an argument in brackets also forces the variable to be passed in ByVal, which is why the following works

Sub OuterSub()
    Dim s As String
    s = "Lorem ipsum dolor sit amet"
    InnerSub (s) ' The brackets here do the trick
End Sub

OTOH, neither of your non-working alternatives work, because whether you create a local string variable, or you wrap the argument to Array in parentheses, the problem is the same -- you're trying to assign an array to a string variable via the ByRef prm parameter.

---

See my answer to CallByName won't accept variant arguments. Quoting from the link referenced in that answer:

How to: Force an Argument to Be Passed by Value (Visual Basic)

The procedure declaration determines the passing mechanism. If a parameter is declared ByRef, Visual Basic expects to pass the corresponding argument by reference. This allows the procedure to change the value of the programming element underlying the argument in the calling code. If you wish to protect the underlying element against such change, you can override the ByRef passing mechanism in the procedure call by enclosing the argument name in parentheses. These parentheses are in addition to the parentheses enclosing the argument list in the call.

The calling code cannot override a ByVal mechanism.

To force an argument to be passed by value If the corresponding parameter is declared ByVal in the procedure, you do not need to take any additional steps. Visual Basic already expects to pass the argument by value.

If the corresponding parameter is declared ByRef in the procedure, enclose the argument in parentheses in the procedure call.