formatting text

[Gixxer_J_97]

Hello all!

I am writing a function to format text to be displayed in a MsgBox.
So far it works, however, it will cut off the last word in the last line if
the text is over 45 characters - and error out if there are no spaces in the
last line. - ie the text is 50 characters long and the 'remainder' is the
word "end.")

(Thanks to Tom O. for the base of this code)

what am i missing?

<BEGIN VBA CODE>
Public Function formatString(str As String)

If Len(str) <= 45 Then
formatString = str
Else
Dim temp As String
formatString = ""
For j = 0 To CInt(Application.WorksheetFunction.RoundDown(Len(str) /
45, 0))
temp = Left(str, 45)
i = 45
Do While Mid(temp, i, 1) <> " " And i > 1
i = i - 1
Loop
formatString = formatString + Mid(temp, 1, i) + Chr(13)
str = Mid(str, i + 1)
Next j
End If

End Function
<END VBA CODE>

 

[Gixxer_J_97]

changing
Do While Mid(temp,i,1) <> " " And i > 1

to

Do While Mid(temp,i,1) <> " " And i > 1 And Len(temp) > 44

seems to have fixed the problem.

 

[Bob Phillips]

Small change, an extra test in the Do Loop

Public Function formatString(str As String)
Dim i, j
If Len(str) <= 45 Then
formatString = str
Else
Dim temp As String
formatString = ""
For j = 0 To CInt(Application.WorksheetFunction.RoundDown(Len(str) /
45, 0))
temp = Left(str, 45)
i = 45
Do While Mid(temp, i, 1) <> " " And i > 1 And Len(temp) > 45
i = i - 1
Loop
formatString = formatString + Mid(temp, 1, i) + Chr(13)
str = Mid(str, i + 1)
Next j
End If

End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gixxer_J_97]

just ooc, seeing as we just defined temp as a maximum of 45 characters, how
can len(temp) ever be greater than 45?

or did i miss the point - i tried using len(temp) > 44 and that seems to
work perfectly.

 

[Bob Phillips]

oops, I meant Len(str) > 45

--

HTH

RP
(remove nothere from the email address if mailing direct)