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(s tr) /
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

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.

"Gixxer_J_97" wrote:

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(s tr) /
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

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(s tr) /
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" wrote in message
...
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(s tr)
/
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

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" wrote:

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(s tr) /
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" wrote in message
...
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(s tr)
/
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

oops, I meant Len(str) 45

--

HTH

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


"Gixxer_J_97" wrote in message
...
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" wrote:

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(s tr) /
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" wrote in message
...
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(s tr)
/
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