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)
