PowerPoint TextRange.Replace method not working to replace carriage return character

Jamie Garroch
Jamie Garroch used Ask the Experts™
on
I'm trying to use the Replace method to remove the carriage return in this test text range:

Lorem ipsum dolor sit amet, consectetuer adipiscing elit.
Lorem ipsum dolor sit amet, consectetuer adipiscing elit.

The last character on the first line is Chr(13).

To test this, I add the above text to a text box on a slide, selected it and run the following command:

ActiveWindow.Selection.TextRange.Replace Chr(13), "X"

Open in new window

This is what happens to the text:

Lorem ipsum dolor sit amet, consectetuer adipiscing elit.X
Lorem ipsum dolor sit amet, consectetuer adipiscing elit.

The carriage return is not replaced and is still present after the X that is inserted, checked as follows:

?Asc(ActiveWindow.Selection.TextRange.Sentences(1).Characters(ActiveWindow.Selection.TextRange.Sentences(1).Characters.Count,1))

Open in new window

If I try to replace visible characters, the method works es expected. So with the same example text above, if I run this command:

ActiveWindow.Selection.TextRange.Replace "elit.", "X"

Open in new window


I get this correct result:

Lorem ipsum dolor sit amet, consectetuer adipiscing X
Lorem ipsum dolor sit amet, consectetuer adipiscing elit.

So why does the CR character not get replaced? It appears that this method is broken.

If I use the plain text function, the CR gets replaced but of course I lose the formatting:

ActiveWindow.Selection.TextRange.Text = Replace(ActiveWindow.Selection.TextRange.Text, Chr(13), "X")

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
On Windows system, a new line is 2 characters: CR (Carriage Return) followed by LF (Line Feed).

VBA provide a constant representing a new line: vbCrLf.
Use it in your replace statement:
ActiveWindow.Selection.TextRange.Replace vbCrLf, "X"

Open in new window


Side note:
It is a good practice to use constants whenever you can, it increase readability of your code.
Here are a few:
vbNullString (an empty string).
vbCrLf
vbTab
ect …
Look them up in the object explorer (F2 hotkey).
CEO PowerPoint Alchemy
Commented:
We came up with the exact same problem. I think it is a bug.

This is the workaround we used:

Sub killvbcr(oshp As Shape)
    Dim oRng As TextRange2
    Set oRng = oshp.TextFrame2.TextRange
    Dim L As Long
    With oRng
        For L = .Characters.Count To 1 Step -1
            If .Characters(L) = vbCr Then
                .Characters(L).InsertAfter (" ")
                .Characters(L).Delete
            End If
        Next
    End With
End Sub

Open in new window

Jamie GarrochSenior Technical Consultant at BrightCarbon

Author

Commented:
Brilliant John. Thank you. Now I want to know why this works! I modified it to remove all 3 paragraph/line break characters used by PowerPoint:

Sub DeleteAllParagraphMarks(oShp As Shape)
  Dim oRng As TextRange2
  Set oRng = oShp.TextFrame2.TextRange
  Dim L As Long
  With oRng
    For L = .Characters.Count To 1 Step -1
      Select Case .Characters(L)
        Case vbCr, vbLf, vbVerticalTab ' Chr(13), Chr(10), Chr(11)
          .Characters(L).InsertAfter (" ")
          .Characters(L).Delete
      End Select
    Next
  End With
End Sub

Open in new window

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Now I want to know why this works!
After investigating, I found out that new lines on PPT can also be a  vertical tab, ehence the 3 criterias in your case statement.


        For L = .Characters.Count To 1 Step -1
            If .Characters(L) = vbCr Then
                .Characters(L).InsertAfter (" ")
                .Characters(L).Delete
            End If
        Next

Open in new window

Looping trough every characters of a string isn't what I can call efficient (you are re-inventing the wheel), neither readable.
The Following will produce the exact same results, in a shorter and more understandable fashion:
Sub DeleteAllParagraphMarks(oShp As Shape)
    Dim oRng As TextRange2
    Set oRng = oShp.TextFrame2.TextRange
  
    rng.text = Replace(rng.text, vbCr, vbNullString)
    rng.text = Replace(rng.text, vbLf, vbNullString)
    rng.text = Replace(rng.text, vbVerticalTab, vbNullString)
End Sub

Open in new window

Side note:
Why TextFrame2 instead of TextFrame ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial