VB(A) - removing last character if it's a CR

pickarooney

As part of a Word macro I'm comparing two strings. In order for the comparison to work, I need to ensure there are no trailing return characters at the end of the string, and if there are, I want to remove them.

The strings I'm comparing here are s.TextFrame.TextRange.Text and theSourceID.
The first line counts the number of characters in the string, and in the example I'm testing it on, this value is 20.
The next line checks if the last character is a carriage return and if it is, trims it off the end of the string.
The call to StringAsc outputs a message listing the ascii value of each character in the string. Even though the condition in the if statement seems to be met, as the Then-clause is activated when skipping through the code with F8, the trailing return is not removed, as shown by the following lines which output the ascii value of the strings characters, the last of which is still 13 and the length of the string, which is still 20 characters.
I think the reason it's not being trimmed properly might have to do with the last character being a compound character, vbCrLf. If that's the case, how would I check for it and remove it? If it's not the case, where is it going wrong?

MsgBox (s.TextFrame.TextRange.Text & "is " & Len(s.TextFrame.TextRange.Text) & "characters.")
If Right$(s.TextFrame.TextRange.Text, 1) = Chr(13) Then
        s.TextFrame.TextRange.Text = Left$(s.TextFrame.TextRange.Text, Len(s.TextFrame.TextRange.Text) - 1)
    End If
        MsgBox StringAsc(theSourceID)
    MsgBox (theSourceID & "is " & Len(theSourceID) & "characters.")
    MsgBox StringAsc(s.TextFrame.TextRange.Text)
    MsgBox (s.TextFrame.TextRange.Text & "is " & Len(s.TextFrame.TextRange.Text) & "characters.")



Public Function StringAsc(MyString As String) As String
  Dim I As Long
  Dim strTemp As String
    For I = 1 To Len(MyString)
    strTemp = strTemp & Asc(Mid$(MyString, I, 1)) & " "
  Next I
  
  StringAsc = Trim$(strTemp)
End Function

Evil Phil

Use the InStr function to find chr(13). I tried the following on a document that read Hello World with a carriage return and it worked. It won't work if you've more than one carriage return but it illustrates my point.

Sub Macro4()

Dim s As String
Dim i As Integer

    Selection.WholeStory

    i = InStr(Selection.Text, Chr(13))
    s = Left$(Selection.Text, i - 1)
    Msgbox s

End Sub

Hobbes

you could probably strtok using chr 13. that would remove it and also allow for extra parts after the chr 13.

bonkey

pickarooney wrote:

The next line checks if the last character is a carriage return and if it is, trims it off the end of the string.

This is where I reckon you're going wrong dude.

In Windows, Return characters are expressed as CrLf - Carriage return / Line Feed, or Chr(13) & Chr(10). Depending on which version of Word (and this, which version of VBA) you're using, this string may be available as the constant VbCrLf.

Even though the condition in the if statement seems to be met, as the Then-clause is activated when skipping through the code with F8, the trailing return is not removed, as shown by the following lines which output the ascii value of the strings characters, the last of which is still 13 and the length of the string, which is still 20 characters.

Going from memory, I seem to recall that the VB debugger can sometimes do funny stuff, like highlighting the first line of an If or Else block even if the block isn't going to be executed. Add in a MsgBox or similar as a second line to see whether or not its *really* being called.....

I think the reason it's not being trimmed properly might have to do with the last character being a compound character, vbCrLf.

Heh. I shoulda read your post fully before replying.

If that's the case, how would I check for it and remove it?

Compare Right(myString, 2) with vbCrLf, and do a substring(myString, Len(myString -2)) to strip. CrLf is simply two non-printing characters rather than one.

jc

Hobbes

If you are reading from a file just do

line input #ff, a$

It should automatically read in a line of text only. Otherwise strtok with cr/lf.

pickarooney

Is strtok not C only?
When I try removing two characters instead of one from the string, it removes the next-to-last one, if I remove 10, it removes 10 from the end but leaves the 13 in place.

Some of the strings here are being read in from tables in Word documents.
You know when you have Show Hidden Text on in Word, the end of every string in a table is represented by a white circle as opposed to a backwards P? Is there a separate VB variable name for that? Its Chr value is 13 when I check it.

stevenmu

Isn't there a replace in VB(A) ? I think it would works like
string = replace(string,vbCrLf,"")

pickarooney

That would remove them all, and I need to keep any carriage returns that are not at the end of the string. It's quite finnicky.

Earthhorse

Maybe I'm way off here but do you need to alter the string or could you just do your comparison to one less the length of the string?

That is where you have:

For I = 1 To Len(MyString)
strTemp = strTemp & Asc(Mid$(MyString, I, 1)) & " "
Next I

Replace with:

For I = 1 To Len(MyString) - 1
strTemp = strTemp & Asc(Mid$(MyString, I, 1)) & " "
Next I

Using some boolean as a trigger (dependant on whether Str(13) was the final character or not).

It's a bit of a messy solution (maybe it's not a solution at all) but if the Trim and Replace aren't working for you...

pickarooney

I see what you mean, and it could work. I'm currently on another messy tangent that selects the text and then shrinks the selection by one if the last char is Chr(13), then assigns the selection.text to the string. It looks like it works so far...

Evil Phil

Soz, didn't think of it just being the last one. InStrRev should find the last chr(13) for you.

Inspector Gadget

I was going to suggest what Evil Phil pointed out (InStrRev() works like InStr() but starts looking at the end of the string instead of the beginning), but if you want a weird alternative, Split() the string into an array using Chr(13) (or vbCr) as the delimiter; if the last entry in the array [i.e. {array}(UBound({array}))] is an empty string, then the last character was a <CR>, this can then be removed [using Redim Preserve {array}(Len({array}) - 1] and Join() the remainder using vbCR as the delimiter.

MyArray = Split(MyString, vbCR)

If (MyArray(UBound(MyArray)) = "") Then
	ReDim Preserve MyArray(Len(MyArray) - 1)
End If

MyString = Join(MyArray, vbCR)

(Remember that DOS/Windows uses the <CR><LF> pair as a delimiter, unlike Mac or Unix systems. If things aren't working exactly as you anticipated, check for this pairing instead of a vanilla <CR>)

Bizarre, perhaps, definitely inefficient, but if you're a bit bored...
Gadget

rsynnott

Inspector Gadget wrote:

(Remember that DOS/Windows uses the <CR><LF> pair as a delimiter, unlike Mac or Unix systems. If things aren't working exactly as you anticipated, check for this pairing instead of a vanilla <CR>)

Unix, and modern macs, use <LF>. MacOS pre-10 used <CR>, as do some weird network protocols.

Inspector Gadget

rsynnott wrote:

Unix, and modern macs, use <LF>. MacOS pre-10 used <CR>, as do some weird network protocols.

Yeah, couldn't remember exactly what the pre-OSX macs used. However, WikiPedia to the rescue

Gadget

Hobbes

pickarooney wrote:

Is strtok not C only?

Pretty sure VB has its own version by now. If not you could just write a method that does the same or find one online.

It is the best way to do this IMHO.

RicardoSmith

Probably not adding anything new, sorry...

Just create a function to trim that char and pass all your stings into it

sString=MyTrim(sString)

Function MyTrim(ByVal Selection as String) as String
If Left(Selection, 1) = Chr(13) Or Left(Selection, 1) = vbCr Then
Selection = Right(Selection, Len(Selection) - 1)
End If
End function

Though as someone else said make sure that its the same ASC code across all version of word. VB/VBA sometime parse things unexpectedly.

If your comparing strings, check for CASE , or removed it before you compare.

Inspector Gadget

I have some vague memory about problems with comparing strings... I think the StrCmp() (or is it StrComp() ?) function can be used to do case-insensitive string matching, and watch out for your Option Compare {Text|Binary}...

Gadget