Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

vbNewLine, VbCrLf and Char(13) are not working in custom Function

I’m trying to create a function that will split its own content into multiple lines. I need to create a custom one because I will be doing more operations on it so I can’t use built-in functions like CONCATENATE or TEXTJOIN.

My code:

Public Function Description(formula As String) As String
    arr = Split(formula, ";")
    For Each Item In arr
        arr2 = Split(Item, ":")
        Description = Description & "Row " & arr2(0) & " Item " & arr2(1) & vbNewLine
    Next

I use it in a cell with =Description(string) where string is an one-line text like:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

2:1;3:2;4:5

It should return:

Row 2 Item 1
Row 3 Item 2
Row 4 Item 5

Instead it returns:

Row 2 Item 1Row 3 Item 2Row 4 Item 5

I tried to use vbNewLine, vbCrLf, vbCr, vbLf, Char(10) and Char(13), nothing works.
Does anybody know a workaround for this problem?

Screenshot:
Screenshot

>Solution :

Use the vbLf-character (seems that vbNewline also works) to get a line break in Excel.
However, you need to set the WrapText property in the cell, else the text will be displayed as one single line.

ActiveCell = "ABC" & vbLf & "XYZ"
ActiveCell.WrapText = True
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading