Excel VBA function for concatenation not returning valid cell results (but debug.print shows the correct string)

Question

Inexperienced Stackoverflow user, please feel free to point me to a different forum or subforum if needed.

I created the following VBA (then also looked at other code samples online, which all seem to follow the same logic). I test in the debug window and see exactly the result (multi-line string) I expect. However, when I type the formula in a worksheet cell, I get a #Value error.

• The function is spelled correctly (I actually select it in Excel from the formula auto-complete)
• Excel x64 v2108, local install on Win10 x64

Any ideas why this isn't populating back into the cell with the formula?

Function ConcatCells()
    
    'just to be safe, clear the string
    ConcatCells = ""
    
    'Loop and add each cell text with a line break
    For Each cell In Selection
        ConcatCells = ConcatCells & Chr(13) & cell.Text
    Next
    
    'remove the leading line break
    ConcatCells = Right(ConcatCells, Len(ConcatCells) - 1)
    
    'is the string actually being created correctly? Yes
    Debug.Print ConcatCells
    
End Function

Answer 1

Add Selection As Range as parameter into your function: Function ConcatCells(Selection As Range)

After this, your function will return a value like this: OneTwoThreeFourFive

When you copy this cell and paste as value,

and after hitting F2 and Enter, you will have a view like this: