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

Issue

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:

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

Answered By – FunThomas

Answer Checked By – Robin (BugsFixing Admin)

Leave a Reply

Your email address will not be published.