How to return a string with line break from Excel VBA user defined function

Question

I am trying to return a string from a VBA function =Showtext(A1,A2) like

Function Showtext(s1 as string, s2 as string) 

Showtext= s1 & chr(10) & s2

end function

however, when calling =Showtext("Hello","World") it displays as Hello World in the cell instead of

Hello
World

I have tried other chars like chr(13), vbnewline, vbcrlf, but no luck

Can someone please help ?

Thanks

Did you enable "Wrap Text" in the cell(s) containing the function ? — Tim Williams
– Tim Williams, Commented Nov 24, 2015 at 0:53
Thank you Tim, that is the key thing I missed. Now it works ! — frank lee
– frank lee, Commented Nov 24, 2015 at 1:05
I had the same thing in MS Access VBA. In the Immediate Window, I could see that any line break would not be shown in the output, yet, the SQL queries that I got from the MS Access queries as a String variable had the line break in that window. I checked vbNewLine and vbCrLf and vbLf. — questionto42
– questionto42, Commented Apr 26, 2024 at 20:23
Strange. I checked it again in the Immediate Window of MS Access VBA and now it worked with any of the line breaks of this Q/A: vbNewLine, vbCrLf, vbLf, chr(10), chr(13). — questionto42
– questionto42, Commented Apr 26, 2024 at 21:02

Taelsin · Accepted Answer · 2015-11-23 23:38:56Z

4

Try Showtext = s1 & vbNewLine & s2. Note the capital 'N' and capital 'L'.

Your Function definition should also have As String after the parameters.

answered Nov 23, 2015 at 23:38

Taelsin

1,09012 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you Taelsin, it works with "Wrap Text" enabled.