Unexpected syntax error when copying Excel VBA code from a Web site

Question

I was looking for a function to conditionally concatenate a range of cells in Excel. This function

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant

Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.cells(i).Value
    End If
For i = 1 To CriteriaRange.Count
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function

from https://www.extendoffice.com/documents/excel/2723-excel-concatenate-based-on-criteria.html looked just the ticket. Comments suggested it had worked for other coders.

However, when I paste it into a VBA module in Excel2016, there are syntax errors which I can't figure out. "Exit Function" on line 7 can be resolved by deleting "Function". The call of "Exit Function" at the end doesn't register as an error but can removed as superfluous. But

If CriteriaRange.Cells(i).Value = Condition Then

is highlighted as a syntax error (and the matching "End If"). If I simply use "If true Then", the error doesn't go away, which suggests looking at the previous line

For i = 1 To CriteriaRange.Count

and I tried "For i = 1 To 8" but that doesn't fix it either. I just can't see what it wrong?

Further to the helpful responses, here is a screenshot of a direct paste into VBA

and here is a screenshot of the "dodgy" space characters as seen in Word

Answer 1

Edit 2

As the OP discovered, copying and pasting from the Web site gives a bunch of non-breaking spaces:

On my test case (Excel 2013, Win7), those paste in fine, and Excel doesn't barf. However, the OP's installation (Excel 2016) can't handle them.

To change those:

• Paste the code into Word.
• Do a Find/Replace to change ^s (nonbreaking spaces, ChrW(160)) to a single space ().
• Copy from Word to Excel.

Original

All I had to do to get it to compile was:

• Comment out the second For i=... line
• Change Exit Function on the last line to End Function.

Haven't tested it, though. Would you please edit your question to add the test case you are trying?

Edit @YowE3K beat me to it — there are some copy-and-paste errors in the code in your question. Recopy from the site and you should be OK!