How to resolve a Compile error: Expected: end of statement caused by " ("

I am trying to create a custom formula for the following:

to take Name, Affiliation, and Email in separate cells and combine them in a new cell as Name (Affiliation) .

I am able to do this straight into a new cell using the formula

=A2& " ("& B2&") " & "<"&C2&">" 

where A2, B2, and C2 are the cells containing Name, Affiliation, and Email respectively.

But instead of copy-and-pasting this formula every time, I am trying to create a custom formula as a short cut. However, when I try and use this same formula in VBA to create a new module like this:

Function Combined (name, affiliation, email) Combined = name&" ("&affiliation&") "&"<"&email&">"

the program highlights the " (" and it gives me the following error:

Compile error:
Expected: end of statement

If I could do a range like A2:C2 rather than A2,B2,C2, for the same effect, that would be even better!

2 Answers

Short version

Add spaces between operators and their operands. Problem solved.

Long version

Any identifier that is immediately followed by a &, like name& and affiliation&, is interpreted as a Long variable, so the lack of whitespace in front of what's meant to be concatenation operators is causing a parse error, because VBA doesn't know what literal expression could possibly follow the Combined = name& assignment - the instruction is complete as it is; the only token that should be where " (" is, is an end-of-statement token:

Expected: end of statement

Says exactly that. Everything before " (" is a perfectly valid instruction, except it's not terminated.

So it's not the " (", it's the type hints. Insert spaces to separate the operators from the operands, and you'll fix the problem. More explicitness couldn't hurt, either:

Option Explicit
Public Function Combined(ByVal name As String, ByVal affiliation As String, email As String) As String Combined = name & " (" & affiliation & ") " & "<" & email & ">"
End Function

When a type isn't specified, a declaration is implicitly Variant, which incurs some unnecessary run-time overhead.

When ByVal isn't specified, parameters are passed ByRef by default, which means the function could be assigning to the parameters.

You could also implement the function like so:

Combined = Join(Array(name, "(" & affiliation & ")", "<" & email & ">"), " ")

If you want to pass a Range to your function you could do:

Public Function Combined(rng As Range) As Variant If rng.Cells.Count <> 3 Or rng.Areas.Count <> 1 Then Combined = cvErr(xlErrValue) Else Combined = rng.Cells(1).Value & _ " (" & rng.Cells(2).Value & ")" & _ " <" & rng.Cells(3).Value & ">" End If
End Function

If placed in a standard code module, that function can then be invoked from Excel as, for instance, =Combined(A2:C2)